[Gnumed-devel] revisited tweak for v_emr_journal

[syan tan]

there is actually no need to make gnumed asynchronous ; 
what is required if optimization is wanted is to tweak the views so that 
sequential scanning on tables with tens of thousands of rows is not done.

This can be checked by doing "explain analyse" after a view has 
been "tweaked" to see if any sequential scanning on large tables is 
eliminated.
 
Then emr journal ( as well as emr browser) will work in < half a second for 
even the largest history in my test data. ( 6 years, almost weekly).

One of the common join conditions is on clin_root_item.pk_item =  T.pk_item
where T is a table or view , but examination shows that the views only
want tables from v_pat_episodes, which has been optimized in the previous
post . 

manually, this came up with v_emr_journal,  v_hx_family,  v_lab_requests, 
and I think that was all. 

A dependant  v_narrative4search had to be dropped , and then recreated, 
after v_hx_family was modified ( v_narrative4search also uses v_pat_items, but 
there doesn't seem to be an alternative here).

This is what is modified  


                    View "clin.v_pat_episodes"
          Column           |           Type           | Modifiers
---------------------------+--------------------------+-----------
 pk_patient                | integer                  |
 description               | text                     |
 episode_open              | boolean                  |
 health_issue              | text                     |
 issue_active              | boolean                  |
 issue_clinically_relevant | boolean                  |
 pk_episode                | integer                  |
 pk_health_issue           | integer                  |
 episode_modified_when     | timestamp with time zone |
 episode_modified_by       | name                     |
 xmin_episode              | xid                      |
View definition:
 SELECT cep.fk_patient AS pk_patient, cep.description, cep.is_open AS 
episode_open, NULL::"unknown" AS health_issue, NULL::"unknown" AS 
issue_active, NULL::"unknown" AS issue_clinically_relevant, cep.pk AS 
pk_episode, NULL::"unknown" AS pk_health_issue, cep.modified_when AS 
episode_modified_when, cep.modified_by AS episode_modified_by, cep.xmin AS 
xmin_episode
   FROM clin.episode cep
  WHERE cep.fk_health_issue IS NULL
UNION ALL
 SELECT chi.id_patient AS pk_patient, cep.description, cep.is_open AS 
episode_open, chi.description AS health_issue, chi.is_active AS issue_active, 
chi.clinically_relevant AS issue_clinically_relevant, cep.pk AS pk_episode, 
cep.fk_health_issue AS pk_health_issue, cep.modified_when AS 
episode_modified_when, cep.modified_by AS episode_modified_by, cep.xmin AS 
xmin_episode
   FROM clin.episode cep, clin.health_issue chi
  WHERE cep.fk_health_issue = chi.pk;


                  View "clin.v_lab_requests"
        Column         |           Type           | Modifiers
-----------------------+--------------------------+-----------
 pk_patient            | integer                  |
 pk_request            | integer                  |
 lab_name              | text                     |
 request_id            | text                     |
 lab_request_id        | text                     |
 sampled_when          | timestamp with time zone |
 lab_rxd_when          | timestamp with time zone |
 results_reported_when | timestamp with time zone |
 request_status        | text                     |
 l10n_request_status   | text                     |
 is_pending            | boolean                  |
 progress_note         | text                     |
 pk_test_org           | integer                  |
 pk_requestor          | integer                  |
 pk_encounter          | integer                  |
 pk_episode            | integer                  |
 pk_health_issue       | integer                  |
 pk_item               | integer                  |
 modified_when         | timestamp with time zone |
 modified_by           | name                     |
 soap_cat              | text                     |
 xmin_lab_request      | xid                      |
View definition:
 SELECT vpi.pk_patient, lr.pk AS pk_request, torg.internal_name AS lab_name, 
lr.request_id, lr.lab_request_id, lr.clin_when AS sampled_when, 
lr.lab_rxd_when, lr.results_reported_when, lr.request_status, 
_(lr.request_status) AS l10n_request_status, lr.is_pending, lr.narrative AS 
progress_note, lr.fk_test_org AS pk_test_org, lr.fk_requestor AS 
pk_requestor, lr.fk_encounter AS pk_encounter, lr.fk_episode AS pk_episode, 
vpi.pk_health_issue, lr.pk_item, lr.modified_when, lr.modified_by, 
lr.soap_cat, lr.xmin AS xmin_lab_request
   FROM clin.lab_request lr, clin.test_org torg, clin.v_pat_episodes vpi
  WHERE lr.fk_test_org = torg.pk AND lr.fk_episode = vpi.pk_episode;



                  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;


               View "clin.v_emr_journal"
     Column      |           Type           | Modifiers
-----------------+--------------------------+-----------
 pk_patient      | integer                  |
 modified_when   | timestamp with time zone |
 clin_when       | timestamp with time zone |
 modified_by     | text                     |
 soap_cat        | text                     |
 narrative       | text                     |
 pk_encounter    | integer                  |
 pk_episode      | integer                  |
 pk_health_issue | integer                  |
 src_pk          | integer                  |
 src_table       | text                     |
View definition:
((((((( SELECT cn.pk_patient, cn.date AS modified_when, cn.date AS clin_when,
        CASE
            WHEN (( SELECT 1
               FROM dem.v_staff
              WHERE v_staff.db_user::text = cn.provider)) IS NULL THEN 
('<'::text || cn.provider) || '>'::text
            ELSE ( SELECT v_staff.short_alias
               FROM dem.v_staff
              WHERE v_staff.db_user::text = cn.provider)
        END AS modified_by, cn.soap_cat, cn.narrative, cn.pk_encounter, 
cn.pk_episode, cn.pk_health_issue, cn.pk_narrative AS 
src_pk, 'clin.clin_narrative'::text AS src_table
   FROM clin.v_pat_narr3 cn
UNION ALL
 SELECT chi.id_patient AS pk_patient, chi.modified_when, chi.modified_when AS 
clin_when,
        CASE
            WHEN (( SELECT 1
               FROM dem.v_staff
              WHERE v_staff.db_user = chi.modified_by)) IS NULL THEN 
('<'::text || chi.modified_by::text) || '>'::text
            ELSE ( SELECT v_staff.short_alias
               FROM dem.v_staff
              WHERE v_staff.db_user = chi.modified_by)
        END AS modified_by, 'a' AS soap_cat, ((((((_('health 
issue'::text) || ': '::text) || chi.description) || '; '::text) || _('noted 
at age'::text)) || ': '::text) || 
COALESCE(chi.age_noted::text, '?'::text)) || ';'::text AS narrative, -1 AS 
pk_encounter, -1 AS pk_episode, chi.pk AS pk_health_issue, chi.pk AS 
src_pk, 'clin.health_issue'::text AS src_table
   FROM clin.health_issue chi)
UNION ALL
 SELECT cenc.fk_patient AS pk_patient, cenc.modified_when, cenc.started AS 
clin_when,
        CASE
            WHEN (( SELECT 1
               FROM dem.v_staff
              WHERE v_staff.db_user = cenc.modified_by)) IS NULL THEN 
('<'::text || cenc.modified_by::text) || '>'::text
            ELSE ( SELECT v_staff.short_alias
               FROM dem.v_staff
              WHERE v_staff.db_user = cenc.modified_by)
        END AS modified_by, 's' AS soap_cat, 
((((((_('encounter'::text) || ': '::text) || 
_('RFE'::text)) || ': '::text) || 
cenc.reason_for_encounter) || '; '::text) || _('AOE'::text)) || ':'::text AS 
narrative, cenc.pk AS pk_encounter, -1 AS pk_episode, -1 AS pk_health_issue, 
cenc.pk AS src_pk, 'clin.encounter'::text AS src_table
   FROM clin.encounter cenc)
UNION ALL
 SELECT vpep.pk_patient, vpep.episode_modified_when AS modified_when, 
vpep.episode_modified_when AS clin_when,
        CASE
            WHEN (( SELECT 1
               FROM dem.v_staff
              WHERE v_staff.db_user = vpep.episode_modified_by)) IS NULL THEN 
('<'::text || vpep.episode_modified_by::text) || '>'::text
            ELSE ( SELECT v_staff.short_alias
               FROM dem.v_staff
              WHERE v_staff.db_user = vpep.episode_modified_by)
        END AS modified_by, 's' AS soap_cat, 
(_('episode'::text) || ': '::text) || vpep.description AS narrative, -1 AS 
pk_encounter, vpep.pk_episode, -1 AS pk_health_issue, vpep.pk_episode AS 
src_pk, 'clin.episode'::text AS src_table
   FROM clin.v_pat_episodes vpep)
UNION ALL
 SELECT vhxf.pk_patient, vhxf.modified_when, vhxf.clin_when,
        CASE
            WHEN (( SELECT 1
               FROM dem.v_staff
              WHERE v_staff.db_user = vhxf.modified_by)) IS NULL THEN 
('<'::text || vhxf.modified_by::text) || '>'::text
            ELSE ( SELECT v_staff.short_alias
               FROM dem.v_staff
              WHERE v_staff.db_user = vhxf.modified_by)
        END AS modified_by, vhxf.soap_cat, 
(((((_(vhxf.relationship) || ' '::text) || vhxf.name_relative) || ' 
@ '::text) || vhxf.age_noted) || ': '::text) || vhxf.condition AS narrative, 
vhxf.pk_encounter, vhxf.pk_episode, vhxf.pk_health_issue, 
vhxf.pk_hx_family_item AS src_pk, 'clin.hx_family_item'::text AS src_table
   FROM clin.v_hx_family vhxf)
UNION ALL
 SELECT vpv4i.pk_patient, vpv4i.modified_when, vpv4i.date AS clin_when,
        CASE
            WHEN (( SELECT 1
               FROM dem.v_staff
              WHERE v_staff.db_user = vpv4i.modified_by)) IS NULL THEN 
('<'::text || vpv4i.modified_by::text) || '>'::text
            ELSE ( SELECT v_staff.short_alias
               FROM dem.v_staff
              WHERE v_staff.db_user = vpv4i.modified_by)
        END AS modified_by, 'p' AS soap_cat, 
((((((((((((((((((_('vaccine'::text) || ': '::text) || 
vpv4i.vaccine) || '; '::text) || _('batch no'::text)) || ': '::text) || 
vpv4i.batch_no) || '; '::text) || _('indication'::text)) || ': '::text) || 
vpv4i.l10n_indication) || '; '::text) || _('site'::text)) || ': '::text) || 
vpv4i.site) || '; '::text) || _('notes'::text)) || ': '::text) || 
vpv4i.narrative) || ';'::text AS narrative, vpv4i.pk_encounter, 
vpv4i.pk_episode, vpv4i.pk_health_issue, vpv4i.pk_vaccination AS 
src_pk, 'vaccination'::text AS src_table
   FROM clin.v_pat_vaccinations4indication vpv4i)
UNION ALL
 SELECT vpa.pk_patient, vpa.modified_when, vpa.date AS clin_when,
        CASE
            WHEN (( SELECT 1
               FROM dem.v_staff
              WHERE v_staff.db_user = vpa.modified_by)) IS NULL THEN 
('<'::text || vpa.modified_by::text) || '>'::text
            ELSE ( SELECT v_staff.short_alias
               FROM dem.v_staff
              WHERE v_staff.db_user = vpa.modified_by)
        END AS modified_by, 's' AS soap_cat, 
((((((((((((((((((((((_('allergene'::text) || ': '::text) || 
COALESCE(vpa.allergene, ''::text)) || '; '::text) || 
_('substance'::text)) || ': '::text) || vpa.substance) || '; '::text) || 
_('generic'::text)) || ': '::text) || 
COALESCE(vpa.generics, ''::text)) || '; '::text) || _('ATC 
code'::text)) || ': '::text) || 
COALESCE(vpa.atc_code, ''::text)) || '; '::text) || 
_('type'::text)) || ': '::text) || vpa.l10n_type) || '; '::text) || 
_('reaction'::text)) || ': '::text) || 
COALESCE(vpa.reaction, ''::text)) || ';'::text AS narrative, 
vpa.pk_encounter, vpa.pk_episode, vpa.pk_health_issue, vpa.pk_allergy AS 
src_pk, 'clin.allergy' AS src_table
   FROM clin.v_pat_allergies vpa)
UNION ALL
 SELECT vlr.pk_patient, vlr.modified_when, vlr.sampled_when AS clin_when,
        CASE
            WHEN (( SELECT 1
               FROM dem.v_staff
              WHERE v_staff.db_user = vlr.modified_by)) IS NULL THEN 
('<'::text || vlr.modified_by::text) || '>'::text
            ELSE ( SELECT v_staff.short_alias
               FROM dem.v_staff
              WHERE v_staff.db_user = vlr.modified_by)
        END AS modified_by, vlr.soap_cat, 
((((((((((((((((((_('lab'::text) || ': '::text) || 
vlr.lab_name) || '; '::text) || _('sample ID'::text)) || ': '::text) || 
vlr.request_id) || '; '::text) || _('sample taken'::text)) || ': '::text) || 
vlr.sampled_when::text) || '; '::text) || 
_('status'::text)) || ': '::text) || 
vlr.l10n_request_status) || '; '::text) || 
_('notes'::text)) || ': '::text) || 
COALESCE(vlr.progress_note, ''::text)) || ';'::text AS narrative, 
vlr.pk_encounter, vlr.pk_episode, vlr.pk_health_issue, vlr.pk_item AS 
src_pk, 'lab_request' AS src_table
   FROM clin.v_lab_requests vlr)
UNION ALL
 SELECT vtr.pk_patient, vtr.modified_when, vtr.clin_when,
        CASE
            WHEN (( SELECT 1
               FROM dem.v_staff
              WHERE v_staff.db_user = vtr.modified_by)) IS NULL THEN 
('<'::text || vtr.modified_by::text) || '>'::text
            ELSE ( SELECT v_staff.short_alias
               FROM dem.v_staff
              WHERE v_staff.db_user = vtr.modified_by)
        END AS modified_by, vtr.soap_cat, 
(((((((((((((((((_('code'::text) || ': '::text) || 
vtr.unified_code) || '; '::text) || _('name'::text)) || ': '::text) || 
vtr.unified_name) || '; '::text) || _('value'::text)) || ': '::text) || 
vtr.unified_val) || ' '::text) || vtr.val_unit) || ' ('::text) || 
COALESCE(vtr.unified_target_range, '?'::text)) || '); '::text) || 
_('notes'::text)) || vtr."comment") || ';'::text AS narrative, 
vtr.pk_encounter, vtr.pk_episode, vtr.pk_health_issue, vtr.pk_test_result AS 
src_pk, 'test_result' AS src_table
   FROM clin.v_test_results vtr;