gnumed-devel
[Top][All Lists]
Advanced

[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)



reply via email to

[Prev in Thread] Current Thread [Next in Thread]