[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
Re: [Gnumed-devel] Praxis encounter location slow to populate
From: |
Busser, Jim |
Subject: |
Re: [Gnumed-devel] Praxis encounter location slow to populate |
Date: |
Thu, 5 Jun 2014 21:22:56 +0000 |
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
- Re: [Gnumed-devel] Praxis encounter location slow to populate, (continued)
- Re: [Gnumed-devel] Praxis encounter location slow to populate, Busser, Jim, 2014/06/04
- Re: [Gnumed-devel] Praxis encounter location slow to populate, Karsten Hilbert, 2014/06/05
- Re: [Gnumed-devel] Praxis encounter location slow to populate, Karsten Hilbert, 2014/06/05
- Re: [Gnumed-devel] Praxis encounter location slow to populate, Busser, Jim, 2014/06/05
- Re: [Gnumed-devel] Praxis encounter location slow to populate, Karsten Hilbert, 2014/06/05
- Re: [Gnumed-devel] Praxis encounter location slow to populate, Busser, Jim, 2014/06/05
- Re: [Gnumed-devel] Praxis encounter location slow to populate, Karsten Hilbert, 2014/06/05
- Re: [Gnumed-devel] Praxis encounter location slow to populate, Karsten Hilbert, 2014/06/05
- Re: [Gnumed-devel] Praxis encounter location slow to populate, Busser, Jim, 2014/06/05
- Re: [Gnumed-devel] Praxis encounter location slow to populate, Karsten Hilbert, 2014/06/05
- Re: [Gnumed-devel] Praxis encounter location slow to populate,
Busser, Jim <=
- Re: [Gnumed-devel] Praxis encounter location slow to populate, Karsten Hilbert, 2014/06/10
- Re: [Gnumed-devel] Praxis encounter location slow to populate, Karsten Hilbert, 2014/06/10
- Re: [Gnumed-devel] Praxis encounter location slow to populate, Busser, Jim, 2014/06/10
- Re: [Gnumed-devel] Praxis encounter location slow to populate, Karsten Hilbert, 2014/06/11
- Re: [Gnumed-devel] Praxis encounter location slow to populate, Busser, Jim, 2014/06/11
- Re: [Gnumed-devel] Praxis encounter location slow to populate, Karsten Hilbert, 2014/06/12
- Re: [Gnumed-devel] Praxis encounter location slow to populate, Karsten Hilbert, 2014/06/12
- Re: [Gnumed-devel] Praxis encounter location slow to populate, Karsten Hilbert, 2014/06/12
- Re: [Gnumed-devel] Praxis encounter location slow to populate, Sebastian Hilbert, 2014/06/13
- Re: [Gnumed-devel] Praxis encounter location slow to populate, Karsten Hilbert, 2014/06/14