Thread: Slow performance with left outer join

Hello

please, send output EXPLAIN ANALYZE statement

Regards
Pavel Stehule

On 21/01/2008, Marten Verhoeven <m.verhoeven@van-beek.nl> wrote:
>
>
> Hi,
>
> Since I moved from PostgreSQL 7.3 to 8.2 I have a query which suddenly runs
> very slow. In 7.3 it was really fast. It seems that the query analyser makes
> other choices, which I don't understand.
>
> I have the query:
>
> SELECT * FROM fpuArticle
>     LEFT OUTER JOIN fpuArticleText ON a_No=at_a_No AND coalesce(at_Type,1)=1
> AND coalesce(at_Language,0)=0
>     WHERE strpos(lower(coalesce(a_Code,'') || ' ' ||
> coalesce(at_Text,'')), 'string')>0
>
> when I use a normal join, this query is very fast, but with this left outer
> join it is slow.
>
> This is the query analysis:
>
> Nested Loop Left Join  (cost=1796.69..3327.98 rows=5587 width=516)
>   Join Filter: (fpuarticle.a_no = fpuarticletext.at_a_no)
>   Filter: (strpos(lower((((COALESCE(fpuarticle.a_code, ''::character
> varying))::text || ' '::text) || (COALESCE(fpuarticletext.at_text,
> ''::character varying))::text)), 'string'::text) > 0)
>   ->  Seq Scan on fpuarticle  (cost=0.00..944.62 rows=16762 width=386)
>   ->  Materialize  (cost=1796.69..1796.70 rows=1 width=130)
>         ->  Seq Scan on fpuarticletext  (cost=0.00..1796.69 rows=1
> width=130)
>               Filter: ((COALESCE((at_type)::integer, 1) = 1) AND
> (COALESCE(at_language, 0::numeric) = 0::numeric))
>
> It seems that the filter on at_type and at_Language is used at the and,
> while it is much faster to use it at the beginning. Why is this, and how can
> I influence this?
>
> With kind regards
>
> Marten Verhoeven
> Van Beek B.V.

"Marten Verhoeven" <m.verhoeven@van-beek.nl> writes:
> This is the query analysis:

> Nested Loop Left Join  (cost=1796.69..3327.98 rows=5587 width=516)
>   Join Filter: (fpuarticle.a_no = fpuarticletext.at_a_no)
>   Filter: (strpos(lower((((COALESCE(fpuarticle.a_code, ''::character varying))::text || ' '::text) ||
(COALESCE(fpuarticletext.at_text,''::character varying))::text)), 'string'::text) > 0) 
>   ->  Seq Scan on fpuarticle  (cost=0.00..944.62 rows=16762 width=386)
>   ->  Materialize  (cost=1796.69..1796.70 rows=1 width=130)
>         ->  Seq Scan on fpuarticletext  (cost=0.00..1796.69 rows=1 width=130)
>               Filter: ((COALESCE((at_type)::integer, 1) = 1) AND (COALESCE(at_language, 0::numeric) = 0::numeric))

If this is slow, it must be that the scan of fpuarticletext actually
returns many more rows than the single row the planner is expecting.
The reason the estimate is off is probably that the planner cannot make
any useful estimate about those COALESCE expressions.  Try rewriting
them in the simpler forms

    (at_type = 1 or at_type is null) AND
    (at_language = 0 or at_language is null)

            regards, tom lane

Hello

> >               Filter: ((COALESCE((at_type)::integer, 1) = 1) AND (COALESCE(at_language, 0::numeric) = 0::numeric))
>
> If this is slow, it must be that the scan of fpuarticletext actually
> returns many more rows than the single row the planner is expecting.
> The reason the estimate is off is probably that the planner cannot make
> any useful estimate about those COALESCE expressions.  Try rewriting
> them in the simpler forms
>
>         (at_type = 1 or at_type is null) AND
>         (at_language = 0 or at_language is null)
>

what about put this topic into FAQ.

Regards
Pavel Stehule