Re: [Gnumed-devel] faster clin.v_pat_narrative

[Karsten Hilbert]

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