[Gnumed-devel] other output; any clues ?

[Syan Tan]

gnumed_v2=# set enable_seqscan to off;
SET
gnumed_v2=# EXPLAIN ANALYZE select pk_item from clin.clin_root_item cri , clin.encounter e where e.fk_patient = 284 and e.pk = cri.fk_encounter
gnumed_v2-# ;
                                                                     QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=100000008.46..1100017709.10 rows=237 width=4) (actual time=1.730..5284.580 rows=274 loops=1)
   Hash Cond: ("outer".fk_encounter = "inner".pk)
   ->  Append  (cost=100000000.00..1100015858.85 rows=367885 width=8) (actual time=0.061..4020.203 rows=366215 loops=1)
         ->  Seq Scan on clin_root_item cri  (cost=100000000.00..100000014.10 rows=410 width=8) (actual time=0.005..0.005 rows=0 loops=1)
         ->  Seq Scan on referral cri  (cost=100000000.00..100000013.80 rows=380 width=8) (actual time=0.004..0.004 rows=0 loops=1)
         ->  Seq Scan on allergy cri  (cost=100000000.00..100000001.01 rows=1 width=8) (actual time=0.037..0.041 rows=1 loops=1)
         ->  Seq Scan on clin_aux_note cri  (cost=100000000.00..100000014.00 rows=400 width=8) (actual time=0.004..0.004 rows=0 loops=1)
         ->  Seq Scan on clin_hx_family cri  (cost=100000000.00..100000001.01 rows=1 width=8) (actual time=0.040..0.044 rows=1 loops=1)         ->  Seq Scan on clin_medication cri  (cost=100000000.00..100000011.40 rows=140 width=8) (actual time=0.004..0.004 rows=0 loops=1)
         ->  Seq Scan on clin_narrative cri  (cost=100000000.00..100015787.06 rows=366206 width=8) (actual time=0.034..1693.333 rows=366206 loops=1)
         ->  Seq Scan on form_instances cri  (cost=100000000.00..100000013.40 rows=340 width=8) (actual time=0.004..0.004 rows=0 loops=1)
         ->  Seq Scan on lab_request cri  (cost=100000000.00..100000001.02 rows=2 width=8) (actual time=0.061..0.070 rows=2 loops=1)
         ->  Seq Scan on test_result cri  (cost=100000000.00..100000001.04 rows=4 width=8) (actual time=0.035..0.050 rows=4 loops=1)
         ->  Seq Scan on vaccination cri  (cost=100000000.00..100000001.01 rows=1 width=8) (actual time=0.033..0.037 rows=1 loops=1)
   ->  Hash  (cost=8.16..8.16 rows=118 width=4) (actual time=1.190..1.190 rows=118 loops=1)
         ->  Index Scan using idx_pat_per_encounter on encounter e  (cost=0.00..8.16 rows=118 width=4) (actual time=0.161..0.747 rows=118 loops=1)
               Index Cond: (fk_patient = 284)
 Total runtime: 5300.282 ms
(18 rows)

gnumed_v2=# \d clin.clin_narrative
                                           Table "clin.clin_narrative"
    Column     |           Type           |                               Modifiers
---------------+--------------------------+-----------------------------------------------------------------------
 pk_audit      | integer                  | not null default nextval('audit.audit_fields_pk_audit_seq'::regclass)
 row_version   | integer                  | not null default 0
 modified_when | timestamp with time zone | not null default now()
 modified_by   | name                     | not null default "current_user"()
 pk_item       | integer                  | not null default nextval('clin.clin_root_item_pk_item_seq'::regclass)
 clin_when     | timestamp with time zone | not null default now()
 fk_encounter  | integer                  | not null
 fk_episode    | integer                  | not null
 narrative     | text                     |
 soap_cat      | text                     | not null
 pk            | integer                  | not null default nextval('clin.clin_narrative_pk_seq'::regclass)
Indexes:
    "clin_narrative_pkey" PRIMARY KEY, btree (pk)
    "idx_clnarr_encounter" btree (fk_encounter)
    "idx_clnarr_episode" btree (fk_episode)
    "idx_narr_a" btree (soap_cat) WHERE soap_cat = 'a'::text
    "idx_narr_o" btree (soap_cat) WHERE soap_cat = 'o'::text
    "idx_narr_p" btree (soap_cat) WHERE soap_cat = 'p'::text
    "idx_narr_s" btree (soap_cat) WHERE soap_cat = 's'::text
    "idx_narr_soap" btree (soap_cat)
    "idx_narrative_hash_tableoid" hash (tableoid)
    "ix_clin_narrative_fk_episode" hash (fk_episode)
    "ix_hash_pk_item_clin_narrative" hash (pk_item)
Check constraints:
    "clin_root_item_soap_cat_check" CHECK (lower(soap_cat) = 's'::text OR lower(soap_cat) = 'o'::text OR lower(soap_cat) = 'a'::text OR lower(soap_cat) = 'p'::text)
    "narrative_neither_null_nor_empty" CHECK (btrim(COALESCE(narrative, ''::text)) ''::text)
Foreign-key constraints:
    "clin_narrative_fk_encounter_fkey" FOREIGN KEY (fk_encounter) REFERENCES clin.encounter(pk) ON UPDATE CASCADE ON DELETE RESTRICT
    "clin_narrative_fk_episode_fkey" FOREIGN KEY (fk_episode) REFERENCES clin.episode(pk) ON UPDATE CASCADE ON DELETE RESTRICT
Triggers:
    tr_narrative_no_duplicate AFTER INSERT OR UPDATE ON clin.clin_narrative FOR EACH ROW EXECUTE PROCEDURE clin.is_unique_narrative()
    zt_del_clin_narrative BEFORE DELETE ON clin.clin_narrative FOR EACH ROW EXECUTE PROCEDURE audit.ft_del_clin_narrative()
    zt_ins_clin_narrative BEFORE INSERT ON clin.clin_narrative FOR EACH ROW EXECUTE PROCEDURE audit.ft_ins_clin_narrative()
    zt_upd_clin_narrative BEFORE UPDATE ON clin.clin_narrative FOR EACH ROW EXECUTE PROCEDURE audit.ft_upd_clin_narrative()
Inherits: clin_root_item

gnumed_v2=# EXPLAIN ANALYZE select pk_item from clin.clin_narrative cri , clin.encounter e where e.fk_patient = 284 and e.pk = cri.fk_encounter
gnumed_v2-# ;
                                                                   QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..383.02 rows=236 width=4) (actual time=52.673..58.310 rows=274 loops=1)
   ->  Index Scan using idx_pat_per_encounter on encounter e  (cost=0.00..8.16 rows=118 width=4) (actual time=0.143..0.686 rows=118 loops=1)
         Index Cond: (fk_patient = 284)
   ->  Index Scan using idx_clnarr_encounter on clin_narrative cri  (cost=0.00..3.15 rows=2 width=8) (actual time=0.455..0.466 rows=2 loops=118)
         Index Cond: ("outer".pk = cri.fk_encounter)
 Total runtime: 59.289 ms
(6 rows)