[Gnumed-devel] enable_seqscan

[Syan Tan]

----- Original Message -----
From: Syan Tan
To: address@hidden, Karsten Hilbert
Sent: Sun Mar 12 14:13
Subject: Fwd: Re: [Gnumed-devel] re: optimizations for inheritance searching




I did try that , but in postgresql.conf and it didn't seem to work. perhaps if I
insert

set enable_seqscan=off in the session , it will be the same, in which case , you

do not have to change the sql. I'll verify it saves time as well.

On Fri Mar 10 23:38 , Karsten Hilbert sent:

    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


    _______________________________________________
    Gnumed-devel mailing list
    address@hidden
    http://lists.gnu.org/mailman/listinfo/gnumed-devel

<