[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 16:33:46 +0000 |
On 2014-06-05, at 3:07 AM, Karsten Hilbert <address@hidden> wrote:
> On Wed, Jun 04, 2014 at 05:19:01PM +0000, Jim Busser wrote:
>
>>
>> QUERY PLAN
>>
>> --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>> Limit (cost=317898.29..317898.36 rows=28 width=155)
>> -> Sort (cost=317898.29..317898.36 rows=28 width=155)
>> Sort Key: (((((((d_vou.unit || COALESCE(((' ('::text ||
>> d_vou.l10n_unit_category) || ')'::text), ''::text)) || ' of '::text) ||
>> d_vou.l10n_organization_category) || ' "'::text) || d_vou.organization) ||
>> '"'::text))
>> -> Nested Loop (cost=619.77..317897.62 rows=28 width=155)
>> -> Subquery Scan on d_vou (cost=619.62..317765.87 rows=461
>> width=155)
>> Filter: ((d_vou.unit ~~* '%abc%'::text) OR
>> (d_vou.organization ~~* '%abc%'::text) OR (d_vou.l10n_unit_category ~~*
>> '%abc%'::text))
>> -> Hash Left Join (cost=619.62..317565.08 rows=11474
>> width=175)
>> Hash Cond: (d_ou.fk_category = d_oc.pk)
>> -> Hash Right Join (cost=618.39..212103.45
>> rows=11474 width=143)
>> Hash Cond: (d_vo.pk_org = d_ou.fk_org)
>> 8 rows=12365 width=90)
>> -> Hash Left Join
>> (cost=1.23..210605.33 rows=12365 width=94)
>> Hash Cond: (d_o.fk_category =
>> d_oc_1.pk)
>
> This is suspicious:
>
>> -> Seq Scan on org d_o
>> (cost=0.00..383.65 rows=12365 width=62)
>
>> -> Hash (cost=1.10..1.10
>> rows=10 width=36)
>> -> Seq Scan on
>> org_category d_oc_1 (cost=0.00..1.10 rows=10 width=36)
>> SubPlan 3
>> -> Nested Loop
>> (cost=0.44..16.48 rows=1 width=0)
>> -> Index Scan using
>> org_unit_uniq_per_org on org_unit d_ou_1 (cost=0.29..8.30 rows=1 width=4)
>> Index Cond:
>> (fk_org = d_o.pk)
>> -> Index Only Scan
>> using dem_praxis_branch_uniq_fk_org_unit on praxis_branch d_pb_3
>> (cost=0.15..8.17 rows=1 width=4)
>> Index Cond:
>> (fk_org_unit = d_ou_1.pk)
>> -> Hash (cost=349.74..349.74 rows=11474
>> width=57)
>
> As is this:
>
>> -> Seq Scan on org_unit d_ou
>> (cost=0.00..349.74 rows=11474 width=57)
>
> I have somewhat improved the queries. Please send the output:
>
>
> EXPLAIN ANALYZE SELECT
> pk_praxis_branch AS data,
> branch || ' (' || praxis || ')' AS field_label,
> branch || coalesce(' (' || l10n_unit_category || ')', '') || '
> of ' || l10n_organization_category || ' "' || praxis || '"' AS list_label
> FROM
> dem.v_praxis_branches
> WHERE
> branch ILIKE '%abc%'
> OR
> praxis ILIKE '%abc%'
> OR
> l10n_unit_category ILIKE '%abc%'
> ORDER BY
> list_label
> LIMIT 50
> ;
>
>
> EXPLAIN ANALYZE SELECT
> pk_praxis_branch AS data,
> branch || ' (' || praxis || ')' AS field_label,
> branch || coalesce(' (' || l10n_unit_category || ')', '') || '
> of ' || l10n_organization_category || ' "' || praxis || '"' AS list_label
> FROM
> dem.v_praxis_branches
> WHERE
> branch ~* '( abc)|(^abc)'
> OR
> praxis ~* '( abc)|(^abc)'
> OR
> l10n_unit_category ~* '( abc)|(^abc)'
> ORDER BY
> list_label
> LIMIT 50
> ;
>
> Posting the output into explain.depezs.com usually gives good insight, too.
The output of each query is appended. There was a misspell in the URL
(explain.depe*sz*.com). After re-running the query without a limit (which
seemed to make no difference) and omitting the last two rows (runtime and row
count), the web site accepted my output.
In both cases, the website tab "Stats" shows a ""Hash Left Join" as taking 3.9
seconds:
http://explain.depesz.com/s/FZHs
http://explain.depesz.com/s/arCC
-- Jim
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=317898.29..317898.36 rows=28 width=155) (actual
time=4669.857..4669.857 rows=0 loops=1)
-> Sort (cost=317898.29..317898.36 rows=28 width=155) (actual
time=4669.855..4669.855 rows=0 loops=1)
Sort Key: (((((((d_vou.unit || COALESCE(((' ('::text ||
d_vou.l10n_unit_category) || ')'::text), ''::text)) || ' of '::text) ||
d_vou.l10n_organization_category) || ' "'::text) || d_vou.organization) ||
'"'::text))
Sort Method: quicksort Memory: 25kB
-> Nested Loop (cost=619.77..317897.62 rows=28 width=155) (actual
time=4669.527..4669.527 rows=0 loops=1)
-> Subquery Scan on d_vou (cost=619.62..317765.87 rows=461
width=155) (actual time=4669.526..4669.526 rows=0 loops=1)
Filter: ((d_vou.unit ~~* '%abc%'::text) OR
(d_vou.organization ~~* '%abc%'::text) OR (d_vou.l10n_unit_category ~~*
'%abc%'::text))
Rows Removed by Filter: 11483
-> Hash Left Join (cost=619.62..317565.08 rows=11474
width=175) (actual time=432.600..4588.504 rows=11483 loops=1)
Hash Cond: (d_ou.fk_category = d_oc.pk)
-> Hash Right Join (cost=618.39..212103.45
rows=11474 width=143) (actual time=431.697..2060.581 rows=11483 loops=1)
Hash Cond: (d_vo.pk_org = d_ou.fk_org)
-> Subquery Scan on d_vo
(cost=1.23..210728.98 rows=12365 width=90) (actual time=381.686..1965.895
rows=12365 loops=1)
-> Hash Left Join
(cost=1.23..210605.33 rows=12365 width=94) (actual time=381.684..1959.548
rows=12365 loops=1)
Hash Cond: (d_o.fk_category =
d_oc_1.pk)
-> Seq Scan on org d_o
(cost=0.00..383.65 rows=12365 width=62) (actual time=0.009..13.963 rows=12365
loops=1)
-> Hash (cost=1.10..1.10 rows=10
width=36) (actual time=0.017..0.017 rows=11 loops=1)
Buckets: 1024 Batches: 1
Memory Usage: 1kB
-> Seq Scan on org_category
d_oc_1 (cost=0.00..1.10 rows=10 width=36) (actual time=0.006..0.010 rows=11
loops=1)
SubPlan 3
-> Nested Loop
(cost=0.44..16.48 rows=1 width=0) (actual time=0.011..0.011 rows=0 loops=12365)
it_uniq_per_org on org_unit d_ou_1 (cost=0.29..8.30 rows=1 width=4) (actual
time=0.005..0.005 rows=1 loops=12365)
Index Cond: (fk_org
= d_o.pk)
-> Index Only Scan using
dem_praxis_branch_uniq_fk_org_unit on praxis_branch d_pb_3 (cost=0.15..8.17
rows=1 width=4) (actual time=0.005..0.005 rows=0 loops=11483)
Index Cond:
(fk_org_unit = d_ou_1.pk)
Heap Fetches: 1
-> Hash (cost=349.74..349.74 rows=11474
width=57) (actual time=49.868..49.868 rows=11483 loops=1)
Buckets: 2048 Batches: 2 Memory Usage:
516kB
-> Seq Scan on org_unit d_ou
(cost=0.00..349.74 rows=11474 width=57) (actual time=0.024..10.549 rows=11483
loops=1)
-> Hash (cost=1.10..1.10 rows=10 width=36) (actual
time=0.018..0.018 rows=11 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 1kB
-> Seq Scan on org_category d_oc
(cost=0.00..1.10 rows=10 width=36) (actual time=0.006..0.010 rows=11 loops=1)
SubPlan 1
_org_unit on praxis_branch d_pb_1 (cost=0.15..8.17 rows=1 width=0) (never
executed)
Index Cond: (fk_org_unit = d_ou.pk)
Heap Fetches: 0
SubPlan 2
-> Seq Scan on praxis_branch d_pb_2
(cost=0.00..16.90 rows=690 width=4) (actual time=0.004..0.005 rows=1 loops=1)
-> Index Scan using dem_praxis_branch_uniq_fk_org_unit on
praxis_branch d_pb (cost=0.15..0.27 rows=1 width=8) (never executed)
Index Cond: (fk_org_unit = d_vou.pk_org_unit)
Total runtime: 4672.608 ms
(41 rows)
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=319708.83..319708.86 rows=11 width=155) (actual
time=4662.040..4662.040 rows=0 loops=1)
-> Sort (cost=319708.83..319708.86 rows=11 width=155) (actual
time=4662.039..4662.039 rows=0 loops=1)
Sort Key: (((((((d_vou.unit || COALESCE(((' ('::text ||
d_vou.l10n_unit_category) || ')'::text), ''::text)) || ' of '::text) ||
d_vou.l10n_organization_category) || ' "'::text) || d_vou.organization) ||
'"'::text))
Sort Method: quicksort Memory: 25kB
-> Nested Loop (cost=619.62..319708.64 rows=11 width=155) (actual
time=4662.031..4662.031 rows=0 loops=1)
Join Filter: (d_pb.fk_org_unit = d_vou.pk_org_unit)
-> Seq Scan on praxis_branch d_pb (cost=0.00..16.90 rows=690
width=8) (actual time=0.007..0.010 rows=1 loops=1)
-> Materialize (cost=619.62..317766.80 rows=186 width=155)
(actual time=4662.018..4662.018 rows=0 loops=1)
-> Subquery Scan on d_vou (cost=619.62..317765.87
rows=186 width=155) (actual time=4662.013..4662.013 rows=0 loops=1)
Filter: ((d_vou.unit ~* '( abc)|(^abc)'::text) OR
(d_vou.organization ~* '( abc)|(^abc)'::text) OR (d_vou.l10n_unit_category ~*
'( abc)|(^abc)'::text))
Rows Removed by Filter: 11483
-> Hash Left Join (cost=619.62..317565.08
rows=11474 width=175) (actual time=15.459..4529.686 rows=11483 loops=1)
Hash Cond: (d_ou.fk_category = d_oc.pk)
-> Hash Right Join (cost=618.39..212103.45
rows=11474 width=143) (actual time=14.852..1737.478 rows=11483 loops=1)
Hash Cond: (d_vo.pk_org = d_ou.fk_org)
-> Subquery Scan on d_vo
(cost=1.23..210728.98 rows=12365 width=90) (actual time=0.539..1673.278
rows=12365 loops=1)
-> Hash Left Join
(cost=1.23..210605.33 rows=12365 width=94) (actual time=0.538..1666.186
rows=12365 loops=1)
Hash Cond: (d_o.fk_category
= d_oc_1.pk)
-> Seq Scan on org d_o
(cost=0.00..383.65 rows=12365 width=62) (actual time=0.005..13.623 rows=12365
loops=1)
-> Hash (cost=1.10..1.10
rows=10 width=36) (actual time=0.013..0.013 rows=11 loops=1)
Buckets: 1024
Batches: 1 Memory Usage: 1kB
-> Seq Scan on
org_category d_oc_1 (cost=0.00..1.10 rows=10 width=36) (actual
time=0.003..0.005 rows=11 loops=1)
SubPlan 3
-> Nested Loop
(cost=0.44..16.48 rows=1 width=0) (actual time=0.009..0.009 rows=0 loops=12365)
-> Index Scan using
org_unit_uniq_per_org on org_unit d_ou_1 (cost=0.29..8.30 rows=1 width=4)
(actual time=0.005..0.006 rows=1 loops=12365)
Index Cond:
(fk_org = d_o.pk)
-> Index Only Scan
using dem_praxis_branch_uniq_fk_org_unit on praxis_branch d_pb_3
(cost=0.15..8.17 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=11483)
Index Cond:
(fk_org_unit = d_ou_1.pk)
Heap Fetches: 1
-> Hash (cost=349.74..349.74
rows=11474 width=57) (actual time=14.298..14.298 rows=11483 loops=1)
Buckets: 2048 Batches: 2 Memory
Usage: 516kB
)
-> Hash (cost=1.10..1.10 rows=10 width=36)
(actual time=0.013..0.013 rows=11 loops=1)
Buckets: 1024 Batches: 1 Memory Usage:
1kB
-> Seq Scan on org_category d_oc
(cost=0.00..1.10 rows=10 width=36) (actual time=0.003..0.006 rows=11 loops=1)
SubPlan 1
-> Index Only Scan using
dem_praxis_branch_uniq_fk_org_unit on praxis_branch d_pb_1 (cost=0.15..8.17
rows=1 width=0) (never executed)
Index Cond: (fk_org_unit = d_ou.pk)
Heap Fetches: 0
SubPlan 2
-> Seq Scan on praxis_branch d_pb_2
(cost=0.00..16.90 rows=690 width=4) (actual time=0.004..0.004 rows=1 loops=1)
Total runtime: 4662.326 ms
(42 rows)
signature.asc
Description: Message signed with OpenPGP using GPGMail
- Re: [Gnumed-devel] Praxis encounter location slow to populate, (continued)
Re: [Gnumed-devel] Praxis encounter location slow to populate, Karsten Hilbert, 2014/06/04
Re: [Gnumed-devel] Praxis encounter location slow to populate, Karsten Hilbert, 2014/06/04
Re: [Gnumed-devel] Praxis encounter location slow to populate, Karsten Hilbert, 2014/06/04
- 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 <=
- 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, 2014/06/05
- 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