Skip site navigation (1) Skip section navigation (2)

Re: Slow performance with left outer join

From: "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com>
To: "Marten Verhoeven" <m(dot)verhoeven(at)van-beek(dot)nl>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Slow performance with left outer join
Date: 2008-01-21 15:17:16
Message-ID: 162867790801210717v43c7163eu9b413ae6418b5b5c@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
Hello

please, send output EXPLAIN ANALYZE statement

Regards
Pavel Stehule

On 21/01/2008, Marten Verhoeven <m(dot)verhoeven(at)van-beek(dot)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.

In response to

pgsql-performance by date

Next:From: Tom LaneDate: 2008-01-21 16:55:55
Subject: Re: Slow performance with left outer join
Previous:From: Marten VerhoevenDate: 2008-01-21 15:00:02
Subject: Slow performance with left outer join

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group