[Top][All Lists]
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
Re: [Gnumed-devel] re: optimizations for inheritance searching problem.
From: |
Karsten Hilbert |
Subject: |
Re: [Gnumed-devel] re: optimizations for inheritance searching problem. |
Date: |
Fri, 10 Mar 2006 23:38:04 +0100 |
User-agent: |
Mutt/1.5.11+cvs20060126 |
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