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

[Syan Tan]

>               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.