[Top][All Lists]
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
Re: [Gnumed-devel] enable_seqscan
From: |
Syan Tan |
Subject: |
Re: [Gnumed-devel] enable_seqscan |
Date: |
Mon, 13 Mar 2006 16:11:31 +0800 |
missing the statement when using \o file in psql.
the statements are:
LOG: statement: show enable_seqscan ;
LOG: duration: 0.589 ms statement: show enable_seqscan ;
LOG: statement: select distinct pk_encounter
from clin.v_pat_items
where pk_episode in (58637, 58637) and pk_patient = 1687;
LOG: duration: 42.344 ms statement: select distinct pk_encounter
from clin.v_pat_items
where pk_episode in (58637, 58637) and pk_patient = 1687;
LOG: statement: explain analyze select distinct pk_encounter
from clin.v_pat_items
where pk_episode in (58637, 58637) and pk_patient = 1687;
LOG: duration: 29.382 ms statement: explain analyze select distinct pk_encounter
from clin.v_pat_items
where pk_episode in (58637, 58637) and pk_patient = 1687;
LOG: statement: show enable_seqscan ;
LOG: duration: 0.220 ms statement: show enable_seqscan ;
LOG: autovacuum: processing database "gnumed_v2"
LOG: statement: select distinct pk_encounter
from clin.v_pat_items
where pk_health_issue in (3913, 3913) and pk_patient = 1687;
LOG: duration: 893.459 ms statement: select distinct pk_encounter
from clin.v_pat_items
where pk_health_issue in (3913, 3913) and pk_patient = 1687;
LOG: statement: explain analyze select distinct pk_encounter
from clin.v_pat_items
where pk_health_issue in (3913, 3913) and pk_patient = 1687;
LOG: duration: 5878.998 ms statement: explain analyze select distinct pk_encounter
from clin.v_pat_items
where pk_health_issue in (3913, 3913) and pk_patient = 1687;
enable_seqscan
----------------
off
(1 row)
pk_encounter
--------------
58644
(1 row)
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=500000433.69..500000433.85 rows=32 width=4) (actual time=10.000..10.018 rows=1 loops=1)
-> Sort (cost=500000433.69..500000433.77 rows=32 width=4) (actual time=9.992..9.998 rows=2 loops=1)
Sort Key: pk_encounter
-> Subquery Scan v_pat_items (cost=400000432.49..400000432.89 rows=32 width=4) (actual time=9.938..9.959 rows=2 loops=1)
-> Sort (cost=400000432.49..400000432.57 rows=32 width=320) (actual time=9.930..9.936 rows=2 loops=1)
Sort Key: cri.clin_when
-> Nested Loop (cost=300000090.71..300000431.69 rows=32 width=320) (actual time=5.772..9.886 rows=2 loops=1)
-> Hash Join (cost=200000062.94..200000403.13 rows=16 width=312) (actual time=5.488..9.520 rows=2 loops=1)
Hash Cond: ("outer".oid = "inner".tableoid)
-> Index Scan using pg_class_oid_index on pg_class pgc (cost=0.00..292.34 rows=1122 width=68) (actual time=0.029..5.436 rows=1122 loops=1)
-> Hash (cost=200000062.90..200000062.90 rows=16 width=225) (actual time=0.194..0.194 rows=2 loops=1)
-> Append (cost=0.00..200000062.90 rows=16 width=225) (actual time=0.044..0.168 rows=2 loops=1)
-> Index Scan using idx_cri_episode on clin_root_item cri (cost=0.00..8.30 rows=2 width=164) (actual time=0.009..0.009 rows=0 loops=1)
Index Cond: (fk_episode = 58637)
-> Index Scan using idx_clnarr_episode on clin_narrative cri (cost=0.00..3.03 rows=2 width=225) (actual time=0.024..0.036 rows=2 loops=1)
Index Cond: (fk_episode = 58637)
-> Seq Scan on clin_hx_family cri (cost=100000000.00..100000001.01 rows=1 width=116) (actual time=0.014..0.014 rows=0 loops=1)
Filter: (fk_episode = 58637)
-> Index Scan using idx_clanote_episode on clin_aux_note cri (cost=0.00..8.30 rows=2 width=164) (actual time=0.007..0.007 rows=0 loops=1)
Index Cond: (fk_episode = 58637)
-> Index Scan using idx_allg_episode on allergy cri (cost=0.00..4.68 rows=1 width=177) (actual time=0.009..0.009 rows=0 loops=1)
Index Cond: (fk_episode = 58637)
-> Index Scan using idx_formi_episode on form_instances cri (cost=0.00..8.30 rows=2 width=164) (actual time=0.006..0.006 rows=0 loops=1)
Index Cond: (fk_episode = 58637)
-> Index Scan using idx_cmeds_episode on clin_medication cri (cost=0.00..4.82 rows=1 width=164) (actual time=0.006..0.006 rows=0 loops=1)
Index Cond: (fk_episode = 58637)
-> Index Scan using idx_vacc_episode on vaccination cri (cost=0.00..4.68 rows=1 width=129) (actual time=0.008..0.008 rows=0 loops=1)
Index Cond: (fk_episode = 58637)
-> Index Scan using idx_tres_episode on test_result cri (cost=0.00..2.01 rows=1 width=137) (actual time=0.009..0.009 rows=0 loops=1)
Index Cond: (fk_episode = 58637)
-> Index Scan using idx_lreq_episode on lab_request cri (cost=0.00..3.01 rows=1 width=160) (actual time=0.007..0.007 rows=0 loops=1)
Index Cond: (fk_episode = 58637)
-> Seq Scan on referral cri (cost=100000000.00..100000014.75 rows=2 width=164) (actual time=0.003..0.003 rows=0 loops=1)
Filter: (fk_episode = 58637)
-> Materialize (cost=27.77..27.79 rows=2 width=12) (actual time=0.126..0.151 rows=1 loops=2)
-> Subquery Scan vpep (cost=0.00..27.77 rows=2 width=12) (actual time=0.231..0.273 rows=1 loops=1)
-> Append (cost=0.00..27.75 rows=2 width=134) (actual time=0.222..0.257 rows=1 loops=1)
-> Subquery Scan "*SELECT* 1" (cost=0.00..3.03 rows=1 width=103) (actual time=0.035..0.035 rows=0 loops=1)
-> Index Scan using episode_pkey on episode cep (cost=0.00..3.02 rows=1 width=103) (actual time=0.027..0.027 rows=0 loops=1)
Index Cond: (58637 = pk)
Filter: ((fk_health_issue IS NULL) AND (fk_patient = 1687))
-> Hash Join (cost=3.02..24.71 rows=1 width=134) (actual time=0.177..0.205 rows=1 loops=1)
Hash Cond: ("outer".pk = "inner".fk_health_issue)
-> Index Scan using health_issue_id_patient_key on health_issue chi (cost=0.00..21.63 rows=11 width=35) (actual time=0.026..0.074 rows=11 loops=1)
Index Cond: (id_patient = 1687)
-> Hash (cost=3.01..3.01 rows=1 width=103) (actual time=0.039..0.039 rows=1 loops=1)
-> Index Scan using episode_pkey on episode cep (cost=0.00..3.01 rows=1 width=103) (actual time=0.015..0.019 rows=1 loops=1)
Index Cond: (58637 = pk)
Total runtime: 10.634 ms
(49 rows)
enable_seqscan
----------------
off
(1 row)
pk_encounter
--------------
58483
58489
58492
58513
58545
58607
58609
58610
58616
58617
58618
58624
58625
58626
58628
58629
58630
58631
58632
58634
58637
58644
58645
58648
58650
58653
58656
58662
58665
(29 rows)
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=1300025601.52..1300025610.71 rows=200 width=4) (actual time=5860.774..5861.159 rows=29 loops=1)
-> Sort (cost=1300025601.52..1300025606.12 rows=1839 width=4) (actual time=5860.767..5860.904 rows=43 loops=1)
Sort Key: pk_encounter
-> Subquery Scan v_pat_items (cost=1200025478.81..1200025501.80 rows=1839 width=4) (actual time=5860.120..5860.555 rows=43 loops=1)
-> Sort (cost=1200025478.81..1200025483.41 rows=1839 width=320) (actual time=5860.110..5860.259 rows=43 loops=1)
Sort Key: cri.clin_when
-> Hash Join (cost=100007412.65..1100025379.10 rows=1839 width=320) (actual time=1855.312..5859.802 rows=43 loops=1)
Hash Cond: ("outer".tableoid = "inner".oid)
-> Hash Join (cost=100007117.50..1100025047.17 rows=1839 width=260) (actual time=1843.573..5847.670 rows=43 loops=1)
Hash Cond: ("outer".fk_episode = "inner".pk_episode)
-> Append (cost=100000000.00..1100016071.85 rows=367885 width=225) (actual time=0.084..4202.818 rows=366215 loops=1)
-> Seq Scan on clin_root_item cri (cost=100000000.00..100000014.10 rows=410 width=164) (actual time=0.006..0.006 rows=0 loops=1)
-> Seq Scan on clin_narrative cri (cost=100000000.00..100016000.06 rows=366206 width=225) (actual time=0.067..1881.333 rows=366206 loops=1)
-> Seq Scan on clin_hx_family cri (cost=100000000.00..100000001.01 rows=1 width=116) (actual time=0.067..0.071 rows=1 loops=1)
-> Seq Scan on clin_aux_note cri (cost=100000000.00..100000014.00 rows=400 width=164) (actual time=0.005..0.005 rows=0 loops=1)
-> Seq Scan on allergy cri (cost=100000000.00..100000001.01 rows=1 width=177) (actual time=0.036..0.040 rows=1 loops=1)
-> Seq Scan on form_instances cri (cost=100000000.00..100000013.40 rows=340 width=164) (actual time=0.004..0.004 rows=0 loops=1)
-> Seq Scan on clin_medication cri (cost=100000000.00..100000011.40 rows=140 width=164) (actual time=0.005..0.005 rows=0 loops=1)
-> Seq Scan on vaccination cri (cost=100000000.00..100000001.01 rows=1 width=129) (actual time=0.037..0.041 rows=1 loops=1)
-> Seq Scan on test_result cri (cost=100000000.00..100000001.04 rows=4 width=137) (actual time=0.035..0.054 rows=4 loops=1)
-> Seq Scan on lab_request cri (cost=100000000.00..100000001.02 rows=2 width=160) (actual time=0.033..0.042 rows=2 loops=1)
-> Seq Scan on referral cri (cost=100000000.00..100000013.80 rows=380 width=164) (actual time=0.004..0.004 rows=0 loops=1)
-> Hash (cost=7117.50..7117.50 rows=1 width=12) (actual time=380.911..380.911 rows=29 loops=1)
-> Subquery Scan vpep (cost=2.11..7117.50 rows=1 width=12) (actual time=359.381..380.753 rows=29 loops=1)
Filter: (pk_health_issue = 3913)
-> Append (cost=2.11..7116.20 rows=104 width=134) (actual time=0.211..379.971 rows=195 loops=1)
-> Subquery Scan "*SELECT* 1" (cost=2.11..119.41 rows=8 width=103) (actual time=0.205..0.625 rows=31 loops=1)
-> Bitmap Heap Scan on episode cep (cost=2.11..119.33 rows=8 width=103) (actual time=0.195..0.386 rows=31 loops=1)
Recheck Cond: (fk_patient = 1687)
Filter: (fk_health_issue IS NULL)
-> Bitmap Index Scan on ix_hash_episode_pat (cost=0.00..2.11 rows=31 width=0) (actual time=0.144..0.144 rows=31 loops=1)
Index Cond: (fk_patient = 1687)
-> Merge Join (cost=0.00..6995.83 rows=96 width=134) (actual time=358.373..378.130 rows=164 loops=1)
Merge Cond: ("outer".pk = "inner".fk_health_issue)
-> Index Scan using health_issue_pkey on health_issue chi (cost=0.00..562.40 rows=11 width=35) (actual time=4.763..22.377 rows=11 loops=1)
Filter: (id_patient = 1687)
-> Index Scan using idx_episode_issue on episode cep (cost=0.00..8134.34 rows=183136 width=103) (actual time=0.146..214.966 rows=40249 loops=1)
-> Hash (cost=292.34..292.34 rows=1122 width=68) (actual time=11.672..11.672 rows=1122 loops=1)
-> Index Scan using pg_class_oid_index on pg_class pgc (cost=0.00..292.34 rows=1122 width=68) (actual time=0.150..6.928 rows=1122 loops=1)
Total runtime: 5861.785 ms
(40 rows)