[Gnumed-devel] faster clin.v_pat_narrative

[syan tan]

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.


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;