[Top][All Lists]
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Gnumed-devel] re: optimizations for inheritance searching problem.
From: |
Syan Tan |
Subject: |
[Gnumed-devel] re: optimizations for inheritance searching problem. |
Date: |
Fri, 10 Mar 2006 12:43:48 +0800 |
> sub_items =
[ 'clin.clin_narrative', 'clin.clin_hx_family', 'clin.clin_aux_note',
'clin.test_res
ult',
>
'clin.allergy', 'clin.lab_request', 'clin.form_instances'
,'clin.clin_medica
tion' , 'au.referral' ,
> 'clin.vaccination' ]
> union_phrase = "select fk_encounter from %s cn inner
> join
(select pk from clin.episode ep where ep.fk_health_issue in %s) as epi on
(cn.fk_episode = epi.pk)"
>
> l = [ union_phrase % (item, tuple(issues) ) for item in
sub_items ]
> cmd_alt = " union ".join ( l )
>
> rows = gmPG.run_ro_query('historica', cmd_alt, None, (
> ) )
> union_phrase = """
> select enc.pk from %s n, clin.encounter enc ,
> clin.episode
ep
> where n.fk_episode = ep.pk and n.fk_encounter = enc.pk
> and ep.pk in %s and enc.fk_patient = %s
> """
> cmd_alt = " union ".join ( [ union_phrase % ( item,
> tuple
(episodes), self.pk_patient ) for item in sub_items ] )
>
>
> rows = gmPG.run_ro_query('historica', cmd_alt, None, ())
the above stops the query parser from using sequential scan on all the child
tables
of clin_root_items , if searching is done on the base table clin_root_item.
this is a problem of postgresql , which is documented in the online
documentation,
but they must have some reason for not wanting to fix it.
by explicitly searching the child tables and then union , the query parser will
detect existing indexes on search attributes /join conditions (e.g. fk_episode,
fk_encounter ) and use them. so for clin_narrative where the row count is
150,000
it will not sequentially scan 150,000 fk_episode values, but lookup the index
to
see if fk_episode value exists in the btree or hash table.
- [Gnumed-devel] re: optimizations for inheritance searching problem.,
Syan Tan <=