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

[Busser, Jim]

On 2014-06-04, at 3:56 AM, Karsten Hilbert <address@hidden> wrote:

> On Mon, Jun 02, 2014 at 10:48:24PM +0000, Jim Busser wrote:
> 
>> My org unit table has more than 10,000 records because of my having loaded 
>> it with the addresses of all doctors in my province.
>> 
>> Could this be the reason why, when I try tabbing through the encounter GUI 
>> field for location, I get a pause that costs me several seconds
> 
> Could you post the output of the following queries ?
> 
>       EXPLAIN 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 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
>       ;

The first query returns

                                                                                
                           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)
                                             ->  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)
                                       ->  Seq Scan on org_unit d_ou  
(cost=0.00..349.74 rows=11474 width=57)
                           ->  Hash  (cost=1.10..1.10 rows=10 width=36)
                                 ->  Seq Scan on org_category d_oc  
(cost=0.00..1.10 rows=10 width=36)
                           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)
                                   Index Cond: (fk_org_unit = d_ou.pk)
                           SubPlan 2
                             ->  Seq Scan on praxis_branch d_pb_2  
(cost=0.00..16.90 rows=690 width=4)
               ->  Index Scan using dem_praxis_branch_uniq_fk_org_unit on 
praxis_branch d_pb  (cost=0.15..0.27 rows=1 width=8)
                     Index Cond: (fk_org_unit = d_vou.pk_org_unit)
(33 rows)

gnumed_v19=> 

==================================

The second returns

                                                                                
                           QUERY PLAN                                           
                                                                
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=319708.83..319708.86 rows=11 width=155)
   ->  Sort  (cost=319708.83..319708.86 rows=11 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.62..319708.64 rows=11 width=155)
               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)
               ->  Materialize  (cost=619.62..317766.80 rows=186 width=155)
                     ->  Subquery Scan on d_vou  (cost=619.62..317765.87 
rows=186 width=155)
                           Filter: ((d_vou.unit ~* '( abc)|(^abc)'::text) OR 
(d_vou.organization ~* '( abc)|(^abc)'::text) OR (d_vou.l10n_unit_category ~* 
'( abc)|(^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)
                                       ->  Subquery Scan on d_vo  
(cost=1.23..210728.98 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)
                                                   ->  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)
sing 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)
                                             ->  Seq Scan on org_unit d_ou  
(cost=0.00..349.74 rows=11474 width=57)
                                 ->  Hash  (cost=1.10..1.10 rows=10 width=36)
                                       ->  Seq Scan on org_category d_oc  
(cost=0.00..1.10 rows=10 width=36)
                                 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)
                                         Index Cond: (fk_org_unit = d_ou.pk)
                                 SubPlan 2
                                   ->  Seq Scan on praxis_branch d_pb_2  
(cost=0.00..16.90 rows=690 width=4)
(34 rows)

gnumed_v19=> 


-- Jim