Re: [Gnumed-devel] Praxis encounter location slow to populate

[Busser, Jim]

On 2014-06-05, at 1:42 PM, Karsten Hilbert <address@hidden> wrote:

> On Thu, Jun 05, 2014 at 08:05:40PM +0000, Jim Busser wrote:
> 
>> Not a big difference, as I can see …
> 
> Hm, at this point we should probably make sure we are still
> talking about the exact same database layout/run queries
> against the desired layout:
> 
> - connect to v19
> 
> \d+ dem.org
> \d+ dem.org_unit
> \d+ dem.v_orgs
> \d+ dem.v_orgs_no_praxis_check
> \d+ dem.v_org_units
> \d+ dem.v_org_units_no_praxis_check
> \d+ dem.v_praxis_branches
> 
> Karsten

gnumed_v19=> \d+ dem.org
                                                                                
                         Table "dem.org"
     Column     |           Type           |                               
Modifiers                               | Storage  |                            
               Description                                            
----------------+--------------------------+-----------------------------------------------------------------------+----------+--------------------------------------------------------------------------------------------------
 pk_audit       | integer                  | not null default 
nextval('audit.audit_fields_pk_audit_seq'::regclass) | plain    | 
 row_version    | integer                  | not null default 0                 
                                   | plain    | 
 modified_when  | timestamp with time zone | not null default now()             
                                   | plain    | 
 modified_by    | name                     | not null default "current_user"()  
                                   | plain    | 
 pk             | integer                  | not null default 
nextval('dem.org_pk_seq'::regclass)                  | plain    | 
 description    | text                     |                                    
                                   | extended | High-level, conceptual 
description (= name) of organization, such as "University of Manchester".
 fk_category    | integer                  |                                    
                                   | plain    | 
 fk_data_source | integer                  |                                    
                                   | plain    | Source of the organization data.
Indexes:
    "org_pkey1" PRIMARY KEY, btree (pk)
    "dem_org_uniq_desc" UNIQUE, btree (description)
    "idx_dem_org_fk_category" btree (fk_category)
Check constraints:
    "org_sane_description" CHECK (gm.is_null_or_blank_string(description) IS 
FALSE)
Foreign-key constraints:
    "org_fk_category_fkey" FOREIGN KEY (fk_category) REFERENCES 
dem.org_category(pk) ON UPDATE CASCADE ON DELETE RESTRICT
    "org_fk_data_source_fkey" FOREIGN KEY (fk_data_source) REFERENCES 
ref.data_source(pk) ON UPDATE CASCADE ON DELETE RESTRICT
Referenced by:
    TABLE "dem.org_unit" CONSTRAINT "org_unit_fk_org_fkey" FOREIGN KEY (fk_org) 
REFERENCES dem.org(pk) ON UPDATE CASCADE ON DELETE RESTRICT
Triggers:
    zt_del_org BEFORE DELETE ON dem.org FOR EACH ROW EXECUTE PROCEDURE 
audit.ft_del_org()
    zt_ins_org BEFORE INSERT ON dem.org FOR EACH ROW EXECUTE PROCEDURE 
audit.ft_ins_org()
    zt_upd_org BEFORE UPDATE ON dem.org FOR EACH ROW EXECUTE PROCEDURE 
audit.ft_upd_org()
Inherits: audit.audit_fields
Has OIDs: no

gnumed_v19=> \d+ dem.org_unit
                                                                                
                       Table "dem.org_unit"
    Column     |           Type           |                               
Modifiers                               | Storage  |                            
                Description                                             
---------------+--------------------------+-----------------------------------------------------------------------+----------+----------------------------------------------------------------------------------------------------
 pk_audit      | integer                  | not null default 
nextval('audit.audit_fields_pk_audit_seq'::regclass) | plain    | 
 row_version   | integer                  | not null default 0                  
                                  | plain    | 
 modified_when | timestamp with time zone | not null default now()              
                                  | plain    | 
 modified_by   | name                     | not null default "current_user"()   
                                  | plain    | 
 pk            | integer                  | not null default 
nextval('dem.org_unit_pk_seq'::regclass)             | plain    | 
 description   | text                     |                                     
                                  | extended | Description (= name) of branch 
of organization, such as "Elms Street office of Jim Busser Praxis".
 fk_org        | integer                  | not null                            
                                  | plain    | 
 fk_address    | integer                  |                                     
                                  | plain    | 
 fk_category   | integer                  |                                     
                                  | plain    | 
Indexes:
    "org_unit_pkey" PRIMARY KEY, btree (pk)
    "org_unit_uniq_per_org" UNIQUE, btree (fk_org, description)
    "idx_dem_org_unit_fk_address" btree (fk_address)
    "idx_dem_org_unit_fk_category" btree (fk_category)
Check constraints:
    "org_unit_sane_description" CHECK (gm.is_null_or_blank_string(description) 
IS FALSE)
Foreign-key constraints:
    "org_unit_fk_address_fkey" FOREIGN KEY (fk_address) REFERENCES 
dem.address(id) ON UPDATE CASCADE ON DELETE RESTRICT
    "org_unit_fk_category_fkey" FOREIGN KEY (fk_category) REFERENCES 
dem.org_category(pk) ON UPDATE CASCADE ON DELETE RESTRICT
    "org_unit_fk_org_fkey" FOREIGN KEY (fk_org) REFERENCES dem.org(pk) ON 
UPDATE CASCADE ON DELETE RESTRICT
Referenced by:
    TABLE "clin.encounter" CONSTRAINT "encounter_fk_location_fkey" FOREIGN KEY 
(fk_location) REFERENCES dem.org_unit(pk) ON UPDATE CASCADE ON DELETE RESTRICT
    TABLE "clin.hospital_stay" CONSTRAINT "hospital_stay_fk_org_unit_fkey" 
FOREIGN KEY (fk_org_unit) REFERENCES dem.org_unit(pk) ON UPDATE CASCADE ON 
DELETE RESTRICT
    TABLE "dem.lnk_org_unit2comm" CONSTRAINT 
"lnk_org_unit2comm_fk_org_unit_fkey" FOREIGN KEY (fk_org_unit) REFERENCES 
dem.org_unit(pk) ON UPDATE CASCADE ON DELETE RESTRICT
    TABLE "dem.lnk_org_unit2ext_id" CONSTRAINT 
"lnk_org_unit2ext_id_fk_org_unit_fkey" FOREIGN KEY (fk_org_unit) REFERENCES 
dem.org_unit(pk) ON UPDATE CASCADE ON DELETE RESTRICT
    TABLE "clin.procedure" CONSTRAINT "procedure_fk_org_unit_fkey" FOREIGN KEY 
(fk_org_unit) REFERENCES dem.org_unit(pk) ON UPDATE CASCADE ON DELETE RESTRICT
    TABLE "clin.test_org" CONSTRAINT "test_org_fk_org_unit_fkey" FOREIGN KEY 
(fk_org_unit) REFERENCES dem.org_unit(pk) ON UPDATE CASCADE ON DELETE RESTRICT
Triggers:
    zt_del_org_unit BEFORE DELETE ON dem.org_unit FOR EACH ROW EXECUTE 
PROCEDURE audit.ft_del_org_unit()
    zt_ins_org_unit BEFORE INSERT ON dem.org_unit FOR EACH ROW EXECUTE 
PROCEDURE audit.ft_ins_org_unit()
    zt_upd_org_unit BEFORE UPDATE ON dem.org_unit FOR EACH ROW EXECUTE 
PROCEDURE audit.ft_upd_org_unit()
Inherits: audit.audit_fields
Has OIDs: no

gnumed_v19=> \d+ dem.v_orgs
                       View "dem.v_orgs"
     Column      |  Type   | Modifiers | Storage  | Description 
-----------------+---------+-----------+----------+-------------
 pk_org          | integer |           | plain    | 
 organization    | text    |           | extended | 
 category        | text    |           | extended | 
 l10n_category   | text    |           | extended | 
 is_praxis       | boolean |           | plain    | 
 pk_category_org | integer |           | plain    | 
 xmin_org        | xid     |           | plain    | 
View definition:
 SELECT d_o.pk AS pk_org, 
    d_o.description AS organization, 
    d_oc.description AS category, 
    _(d_oc.description) AS l10n_category, 
    (EXISTS ( SELECT 1
           FROM dem.praxis_branch d_pb
          WHERE (d_pb.fk_org_unit IN ( SELECT d_ou.pk
                   FROM dem.org_unit d_ou
                  WHERE d_ou.fk_org = d_o.pk)))) AS is_praxis, 
    d_o.fk_category AS pk_category_org, 
    d_o.xmin AS xmin_org
   FROM dem.org d_o
   JOIN dem.org_category d_oc ON d_o.fk_category = d_oc.pk;

gnumed_v19=> \d+ dem.v_orgs_no_praxis_check
               View "dem.v_orgs_no_praxis_check"
     Column      |  Type   | Modifiers | Storage  | Description 
-----------------+---------+-----------+----------+-------------
 pk_org          | integer |           | plain    | 
 organization    | text    |           | extended | 
 category        | text    |           | extended | 
 l10n_category   | text    |           | extended | 
 pk_category_org | integer |           | plain    | 
 xmin_org        | xid     |           | plain    | 
View definition:
 SELECT d_o.pk AS pk_org, 
    d_o.description AS organization, 
    d_oc.description AS category, 
    _(d_oc.description) AS l10n_category, 
    d_o.fk_category AS pk_category_org, 
    d_o.xmin AS xmin_org
   FROM dem.org d_o
   JOIN dem.org_category d_oc ON d_o.fk_category = d_oc.pk;

gnumed_v19=> \d+ dem.v_org_units
                          View "dem.v_org_units"
           Column           |  Type   | Modifiers | Storage  | Description 
----------------------------+---------+-----------+----------+-------------
 pk_org_unit                | integer |           | plain    | 
 organization               | text    |           | extended | 
 unit                       | text    |           | extended | 
 organization_category      | text    |           | extended | 
 l10n_organization_category | text    |           | extended | 
 unit_category              | text    |           | extended | 
 l10n_unit_category         | text    |           | extended | 
 is_praxis_branch           | boolean |           | plain    | 
 pk_org                     | integer |           | plain    | 
 pk_category_org            | integer |           | plain    | 
 pk_category_unit           | integer |           | plain    | 
 pk_address                 | integer |           | plain    | 
 xmin_org_unit              | xid     |           | plain    | 
View definition:
 SELECT d_ou.pk AS pk_org_unit, 
    d_vo.organization, 
    d_ou.description AS unit, 
    d_vo.category AS organization_category, 
    _(d_vo.category) AS l10n_organization_category, 
    d_oc.description AS unit_category, 
    _(d_oc.description) AS l10n_unit_category, 
    (EXISTS ( SELECT 1
           FROM dem.praxis_branch d_pb
          WHERE d_pb.fk_org_unit = d_ou.pk)) AS is_praxis_branch, 
    d_vo.pk_org, 
    d_vo.pk_category_org, 
    d_ou.fk_category AS pk_category_unit, 
    d_ou.fk_address AS pk_address, 
    d_ou.xmin AS xmin_org_unit
   FROM dem.org_unit d_ou
   JOIN dem.v_orgs d_vo ON d_ou.fk_org = d_vo.pk_org
   JOIN dem.org_category d_oc ON d_ou.fk_category = d_oc.pk;

gnumed_v19=> \d+ dem.v_org_units_no_praxis_check
                  View "dem.v_org_units_no_praxis_check"
           Column           |  Type   | Modifiers | Storage  | Description 
----------------------------+---------+-----------+----------+-------------
 pk_org_unit                | integer |           | plain    | 
 organization               | text    |           | extended | 
 unit                       | text    |           | extended | 
 organization_category      | text    |           | extended | 
 l10n_organization_category | text    |           | extended | 
 unit_category              | text    |           | extended | 
 l10n_unit_category         | text    |           | extended | 
 pk_org                     | integer |           | plain    | 
 pk_category_org            | integer |           | plain    | 
 pk_category_unit           | integer |           | plain    | 
 pk_address                 | integer |           | plain    | 
 xmin_org_unit              | xid     |           | plain    | 
View definition:
 SELECT d_ou.pk AS pk_org_unit, 
    d_vo.organization, 
    d_ou.description AS unit, 
    d_vo.category AS organization_category, 
    _(d_vo.category) AS l10n_organization_category, 
    d_oc.description AS unit_category, 
    _(d_oc.description) AS l10n_unit_category, 
    d_vo.pk_org, 
    d_vo.pk_category_org, 
    d_ou.fk_category AS pk_category_unit, 
    d_ou.fk_address AS pk_address, 
    d_ou.xmin AS xmin_org_unit
   FROM dem.org_unit d_ou
   JOIN dem.v_orgs_no_praxis_check d_vo ON d_ou.fk_org = d_vo.pk_org
   JOIN dem.org_category d_oc ON d_ou.fk_category = d_oc.pk;

gnumed_v19=> \d+ dem.v_praxis_branches
                       View "dem.v_praxis_branches"
           Column           |  Type   | Modifiers | Storage  | Description 
----------------------------+---------+-----------+----------+-------------
 pk_praxis_branch           | integer |           | plain    | 
 praxis                     | text    |           | extended | 
 branch                     | text    |           | extended | 
 organization_category      | text    |           | extended | 
 l10n_organization_category | text    |           | extended | 
 unit_category              | text    |           | extended | 
 l10n_unit_category         | text    |           | extended | 
 pk_org                     | integer |           | plain    | 
 pk_org_unit                | integer |           | plain    | 
 pk_category_org            | integer |           | plain    | 
 pk_category_unit           | integer |           | plain    | 
 pk_address                 | integer |           | plain    | 
 xmin_praxis_branch         | xid     |           | plain    | 
 xmin_org_unit              | xid     |           | plain    | 
View definition:
 SELECT d_pb.pk AS pk_praxis_branch, 
    d_vou.organization AS praxis, 
    d_vou.unit AS branch, 
    d_vou.organization_category, 
    d_vou.l10n_organization_category, 
    d_vou.unit_category, 
    d_vou.l10n_unit_category, 
    d_vou.pk_org, 
    d_pb.fk_org_unit AS pk_org_unit, 
    d_vou.pk_category_org, 
    d_vou.pk_category_unit, 
    d_vou.pk_address, 
    d_pb.xmin AS xmin_praxis_branch, 
    d_vou.xmin_org_unit
   FROM dem.praxis_branch d_pb
   JOIN dem.v_org_units_no_praxis_check d_vou ON d_pb.fk_org_unit = 
d_vou.pk_org_unit;

gnumed_v19=> 

====
end