Re: [Gnumed-devel] re: optimizations for inheritance searching problem.

[Karsten Hilbert]

Syan,

I cannot verify the problem with child table indexes when
scanning the parent table:

=============================================================
Script started on Fr 10 Mär 2006 23:24:32 CET
address@hidden:~$ psql -d gnumed_v2 -U any-doc
Passwort: 
Willkommen bei psql 7.4.9, dem interaktiven PostgreSQL-Terminal.

Geben Sie ein:  \copyright für Urheberrechtsinformationen
                \h für Hilfe über SQL-Anweisungen
                \? für Hilfe über interne Anweisungen
                \g oder Semikolon, um eine Anfrage auszuführen
                \q um zu beenden

gnumed_v2=> explain analyze select * from clin.clin_root_item where 
fk_encounter=1;
                                                              QUERY PLAN        
                                                      
--------------------------------------------------------------------------------------------------------------------------------------
 Result  (cost=0.00..6.19 rows=18 width=203) (actual time=0.074..0.337 rows=11 
loops=1)
   ->  Append  (cost=0.00..6.19 rows=18 width=203) (actual time=0.064..0.260 
rows=11 loops=1)
         ->  Seq Scan on clin_root_item  (cost=0.00..0.00 rows=1 width=164) 
(actual time=0.006..0.006 rows=0 loops=1)
               Filter: (fk_encounter = 1)
         ->  Seq Scan on clin_narrative clin_root_item  (cost=0.00..1.07 rows=5 
width=203) (actual time=0.051..0.083 rows=5 loops=1)
               Filter: (fk_encounter = 1)
         ->  Seq Scan on clin_hx_family clin_root_item  (cost=0.00..1.01 rows=1 
width=116) (actual time=0.016..0.016 rows=0 loops=1)
               Filter: (fk_encounter = 1)
         ->  Seq Scan on clin_aux_note clin_root_item  (cost=0.00..0.00 rows=1 
width=164) (actual time=0.002..0.002 rows=0 loops=1)
               Filter: (fk_encounter = 1)
         ->  Seq Scan on allergy clin_root_item  (cost=0.00..1.01 rows=1 
width=177) (actual time=0.012..0.012 rows=0 loops=1)
               Filter: (fk_encounter = 1)
         ->  Seq Scan on form_instances clin_root_item  (cost=0.00..0.00 rows=1 
width=164) (actual time=0.002..0.002 rows=0 loops=1)
               Filter: (fk_encounter = 1)
         ->  Seq Scan on clin_medication clin_root_item  (cost=0.00..0.00 
rows=1 width=164) (actual time=0.002..0.002 rows=0 loops=1)
               Filter: (fk_encounter = 1)
         ->  Seq Scan on vaccination clin_root_item  (cost=0.00..1.01 rows=1 
width=129) (actual time=0.018..0.021 rows=1 loops=1)
               Filter: (fk_encounter = 1)
         ->  Seq Scan on test_result clin_root_item  (cost=0.00..1.05 rows=4 
width=137) (actual time=0.015..0.036 rows=4 loops=1)
               Filter: (fk_encounter = 1)
         ->  Seq Scan on lab_request clin_root_item  (cost=0.00..1.02 rows=1 
width=160) (actual time=0.012..0.017 rows=1 loops=1)
               Filter: (fk_encounter = 1)
         ->  Seq Scan on referral clin_root_item  (cost=0.00..0.00 rows=1 
width=164) (actual time=0.002..0.002 rows=0 loops=1)
               Filter: (fk_encounter = 1)
 Total runtime: 1.216 ms
(25 Zeilen)

gnumed_v2=> set enable_seqscan to off;
SET
gnumed_v2=> explain analyze select * from clin.clin_root_item where 
fk_encounter=1;
                                                                            
QUERY PLAN                                                                      
      
------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Result  (cost=0.00..200000034.23 rows=18 width=203) (actual time=0.124..0.481 
rows=11 loops=1)
   ->  Append  (cost=0.00..200000034.23 rows=18 width=203) (actual 
time=0.113..0.406 rows=11 loops=1)
         ->  Index Scan using idx_cri_encounter on clin_root_item  
(cost=0.00..3.68 rows=1 width=164) (actual time=0.055..0.055 rows=0 loops=1)
               Index Cond: (fk_encounter = 1)
         ->  Index Scan using idx_clnarr_encounter on clin_narrative 
clin_root_item  (cost=0.00..3.07 rows=5 width=203) (actual time=0.049..0.095 
rows=5 loops=1)
               Index Cond: (fk_encounter = 1)
         ->  Seq Scan on clin_hx_family clin_root_item  
(cost=100000000.00..100000001.01 rows=1 width=116) (actual time=0.025..0.025 
rows=0 loops=1)
               Filter: (fk_encounter = 1)
         ->  Index Scan using idx_clanote_encounter on clin_aux_note 
clin_root_item  (cost=0.00..3.68 rows=1 width=164) (actual time=0.016..0.016 
rows=0 loops=1)
               Index Cond: (fk_encounter = 1)
         ->  Index Scan using idx_allg_encounter on allergy clin_root_item  
(cost=0.00..4.68 rows=1 width=177) (actual time=0.022..0.022 rows=0 loops=1)
               Index Cond: (fk_encounter = 1)
         ->  Index Scan using idx_formi_encounter on form_instances 
clin_root_item  (cost=0.00..3.68 rows=1 width=164) (actual time=0.008..0.008 
rows=0 loops=1)
               Index Cond: (fk_encounter = 1)
         ->  Index Scan using idx_cmeds_encounter on clin_medication 
clin_root_item  (cost=0.00..3.68 rows=1 width=164) (actual time=0.006..0.006 
rows=0 loops=1)
               Index Cond: (fk_encounter = 1)
         ->  Index Scan using idx_vacc_encounter on vaccination clin_root_item  
(cost=0.00..4.68 rows=1 width=129) (actual time=0.027..0.030 rows=1 loops=1)
               Index Cond: (fk_encounter = 1)
         ->  Index Scan using idx_tres_encounter on test_result clin_root_item  
(cost=0.00..3.05 rows=4 width=137) (actual time=0.028..0.049 rows=4 loops=1)
               Index Cond: (fk_encounter = 1)
         ->  Index Scan using idx_lreq_encounter on lab_request clin_root_item  
(cost=0.00..3.01 rows=1 width=160) (actual time=0.027..0.030 rows=1 loops=1)
               Index Cond: (fk_encounter = 1)
         ->  Seq Scan on referral clin_root_item  
(cost=100000000.00..100000000.00 rows=1 width=164) (actual time=0.003..0.003 
rows=0 loops=1)
               Filter: (fk_encounter = 1)
 Total runtime: 1.067 ms
(25 Zeilen)

gnumed_v2=> \q
address@hidden:~$ exit
Script done on Fr 10 Mär 2006 23:25:07 CET

=============================================================


-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346