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

[Karsten Hilbert]

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.

Karsten
-- 
GPG key ID E4071346 @ gpg-keyserver.de
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346