[Top][All Lists]
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
Re: [Gnumed-devel] enable_seqscan
|
From: |
Karsten Hilbert |
|
Subject: |
Re: [Gnumed-devel] enable_seqscan |
|
Date: |
Mon, 13 Mar 2006 18:32:27 +0100 |
|
User-agent: |
Mutt/1.5.11+cvs20060126 |
On Mon, Mar 13, 2006 at 04:11:31PM +0800, Syan Tan wrote:
I have removed an "order by" from the view definition of
clin.v_pat_items. Please rerun the queries !
> LOG: statement: select distinct pk_encounter
> from clin.v_pat_items
> where pk_episode in (58637, 58637) and pk_patient = 1687;
Try running
- without "distinct"
- with "pk_episode = 58637"
What happens ?
> LOG: statement: select distinct pk_encounter
> from clin.v_pat_items
> where pk_health_issue in (3913, 3913) and pk_patient = 1687;
Same here, try without distinct and with "pk_health_issue = 3913".
> --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> 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)
>
>
>
> _______________________________________________
> Gnumed-devel mailing list
> address@hidden
> http://lists.gnu.org/mailman/listinfo/gnumed-devel
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346