From: Marten Verhoeven <m.verhoeven@van-beek.nl>
To: pgsql-performance@postgresql.org
Subject: Slow performance with left outer join
Date: Mon, 21 Jan 2008 16:00:02 +0100
Message-ID: <D4E656958EEB52498883B7906D789CD54018EF@alpha2> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: m.verhoeven@van-beek.nl
Subject: Re: Slow performance with left outer join
In-Reply-To: <D4E656958EEB52498883B7906D789CD54018EF@alpha2>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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