pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedSlow performance with left outer join
4+ messages / 3 participants
[nested] [flat]
* Slow performance with left outer join
@ 2008-01-21 15:00 Marten Verhoeven <m.verhoeven@van-beek.nl>
0 siblings, 2 replies; 4+ messages in thread
From: Marten Verhoeven @ 2008-01-21 15:00 UTC (permalink / raw)
To: pgsql-performance
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Slow performance with left outer join
@ 2008-01-21 15:17 Pavel Stehule <pavel.stehule@gmail.com>
parent: Marten Verhoeven <m.verhoeven@van-beek.nl>
1 sibling, 0 replies; 4+ messages in thread
From: Pavel Stehule @ 2008-01-21 15:17 UTC (permalink / raw)
To: Marten Verhoeven <m.verhoeven@van-beek.nl>; +Cc: pgsql-performance
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Slow performance with left outer join
@ 2008-01-21 16:55 Tom Lane <tgl@sss.pgh.pa.us>
parent: Marten Verhoeven <m.verhoeven@van-beek.nl>
1 sibling, 1 reply; 4+ messages in thread
From: Tom Lane @ 2008-01-21 16:55 UTC (permalink / raw)
To: Marten Verhoeven <m.verhoeven@van-beek.nl>; +Cc: pgsql-performance
"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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Slow performance with left outer join
@ 2008-01-21 18:04 Pavel Stehule <pavel.stehule@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Pavel Stehule @ 2008-01-21 18:04 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Bruce Momjian <bruce@momjian.us>; +Cc: Marten Verhoeven <m.verhoeven@van-beek.nl>; pgsql-performance
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2008-01-21 18:04 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-01-21 15:00 Slow performance with left outer join Marten Verhoeven <m.verhoeven@van-beek.nl>
2008-01-21 15:17 ` Pavel Stehule <pavel.stehule@gmail.com>
2008-01-21 16:55 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-01-21 18:04 ` Pavel Stehule <pavel.stehule@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox