On Sun, Sep 24, 2006 at 06:16:57AM +1000, syan tan wrote: > this view below is faster , but I think it depends on > loading an index in memory after the first run, because often > the first run is slower, and the sometimes it is slow for one > query again, especially after switching to another view ( e.g. > the documents), > > the largest record I have running in my data does will load within > 1 second , whereas the old view took 30seconds. That's good. However, there's one problem:
Given the view definition below v_pat_narr3 does not emulate v_pat_narrative properly. It rather emulates v_pat_narrative_soap. The difference is that v_pat_narrative includes *all* the narrative rows (clin_root_item.narrative) across the entire EMR in unmodified form while *_soap only includes those that are in clin_narrative, that is, "real" progress notes. If you do it that way you will not see rows from, say clin_allergy.narrative in the output. Now, this may or may not be intended. If it is you should use v_pat_narrative_soap and optimize that one if necessary. However, your comments led me to realize that we do, indeed need a partial index on "clin_episode where fk_health_issue is null". That one has been added to CVS. Regards, Karsten > gnumed_v2=# \d clin.v_pat_narr3 > View "clin.v_pat_narr3" > Column | Type | Modifiers > ---------------------+--------------------------+----------- > pk_patient | integer | > date | timestamp with time zone | > provider | text | > soap_cat | text | > narrative | text | > pk_item | integer | > pk_narrative | integer | > pk_health_issue | integer | > pk_episode | integer | > pk_encounter | integer | > xmin_clin_narrative | xid | > View definition: > SELECT vn.pk_patient, vn.date, vn.provider, vn.soap_cat, vn.narrative, > vn.pk_item, vn.pk_narrative, vn.pk_health_issue, vn.pk_episode, > vn.pk_encounter, cn2.xmin AS xmin_clin_narrative > FROM ( SELECT cep.fk_patient AS pk_patient, cn.clin_when AS date, > cn.modified_by::text AS provider, cn.soap_cat, cn.narrative, cn.pk_item, > cn.pk AS pk_narrative, cep.fk_health_issue AS pk_health_issue, cep.pk AS > pk_episode, cn.fk_encounter AS pk_encounter > FROM clin.clin_narrative cn, clin.episode cep > WHERE cep.pk = cn.fk_episode > UNION ALL > SELECT hi.id_patient AS pk_patient, cn.clin_when AS date, > cn.modified_by::text AS provider, cn.soap_cat, cn.narrative, cn.pk_item, > cn.pk AS pk_narrative, cep.fk_health_issue AS pk_health_issue, cep.pk AS > pk_episode, cn.fk_encounter AS pk_encounter > FROM clin.clin_narrative cn, clin.episode cep, > clin.health_issue hi > WHERE cep.pk = cn.fk_episode AND cep.fk_health_issue = hi.pk > ORDER BY 7) vn, clin.clin_narrative cn2 > WHERE cn2.pk = vn.pk_narrative; -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 _______________________________________________ Gnumed-devel mailing list [email protected] http://lists.gnu.org/mailman/listinfo/gnumed-devel
