Re: Slow performance with left outer join

"Marten Verhoeven" <m(dot)verhoeven(at)van-beek(dot)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