Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id BE4952E38A0 for ; Mon, 21 Jan 2008 11:00:14 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 22480-05 for ; Mon, 21 Jan 2008 10:59:58 -0400 (AST) Received: from delta.van-beek.nl (a80-127-149-130.adsl.xs4all.nl [80.127.149.130]) by postgresql.org (Postfix) with ESMTP id 0AF942E3189 for ; Mon, 21 Jan 2008 11:00:07 -0400 (AST) Received: from localhost (localhost [127.0.0.1]) by delta.van-beek.nl (Postfix) with ESMTP id 9079E180FC for ; Mon, 21 Jan 2008 15:59:55 +0100 (CET) Received: from alpha.van-beek.nl (unknown [192.168.100.1]) by delta.van-beek.nl (Postfix) with SMTP id 7C1EF180F6 for ; Mon, 21 Jan 2008 15:59:55 +0100 (CET) X-MimeOLE: Produced By Microsoft Exchange V6.5 Content-class: urn:content-classes:message Subject: Slow performance with left outer join MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C85C3E.4CDF75E0" Date: Mon, 21 Jan 2008 16:00:02 +0100 Message-ID: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Slow performance with left outer join Thread-Index: AchcPkoiI5v4ubleRBWmkgWHkdbBPw== From: "Marten Verhoeven" To: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200801/158 X-Sequence-Number: 28397 This is a multi-part message in MIME format. ------_=_NextPart_001_01C85C3E.4CDF75E0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi, =20 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. =20 I have the query: =20 SELECT * FROM fpuArticle=20 LEFT OUTER JOIN fpuArticleText ON a_No=3Dat_a_No AND = coalesce(at_Type,1)=3D1 AND coalesce(at_Language,0)=3D0=20 WHERE strpos(lower(coalesce(a_Code,'') || ' ' || = coalesce(at_Text,'')), 'string')>0 =20 when I use a normal join, this query is very fast, but with this left = outer join it is slow.=20 =20 This is the query analysis: =20 Nested Loop Left Join (cost=3D1796.69..3327.98 rows=3D5587 width=3D516) Join Filter: (fpuarticle.a_no =3D 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=3D0.00..944.62 rows=3D16762 = width=3D386) -> Materialize (cost=3D1796.69..1796.70 rows=3D1 width=3D130) -> Seq Scan on fpuarticletext (cost=3D0.00..1796.69 rows=3D1 = width=3D130) Filter: ((COALESCE((at_type)::integer, 1) =3D 1) AND = (COALESCE(at_language, 0::numeric) =3D 0::numeric)) =20 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? =20 With kind regards =20 Marten Verhoeven Van Beek B.V. ------_=_NextPart_001_01C85C3E.4CDF75E0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

Hi,

Since = I moved from=20 PostgreSQL 7.3 to 8.2 I have a query which suddenly runs very = slow. In=20 7.3 it was really fast. It seems that the query analyser makes other = choices,=20 which I don't understand.

I have = the=20 query:

SELECT = * FROM=20 fpuArticle

=20 LEFT OUTER JOIN fpuArticleText ON a_No=3Dat_a_No AND = coalesce(at_Type,1)=3D1 AND=20 coalesce(at_Language,0)=3D0

WHERE=20 strpos(lower(coalesce(a_Code,'') || ' ' || coalesce(at_Text,'')),=20 'string')>0

when I = use a normal=20 join, this query is very fast, but with this left outer join it is slow. =

This = is the query=20 analysis:

Nested = Loop Left=20 Join (cost=3D1796.69..3327.98 rows=3D5587 width=3D516)
= Join Filter:=20 (fpuarticle.a_no =3D fpuarticletext.at_a_no)
Filter:=20 (strpos(lower((((COALESCE(fpuarticle.a_code, ''::character = varying))::text || '=20 '::text) || (COALESCE(fpuarticletext.at_text, ''::character = varying))::text)),=20 'string'::text) > 0)
-> Seq Scan on = fpuarticle =20 (cost=3D0.00..944.62 rows=3D16762 width=3D386)
-> = Materialize =20 (cost=3D1796.69..1796.70 rows=3D1=20 width=3D130)
-> = Seq Scan on=20 fpuarticletext (cost=3D0.00..1796.69 rows=3D1=20 width=3D130)
&nb= sp; =20 Filter: ((COALESCE((at_type)::integer, 1) =3D 1) AND = (COALESCE(at_language,=20 0::numeric) =3D 0::numeric))

It = seems that the=20 filter on at_type and at_Language is used at the and, while it is much = faster to=20 use it at the beginning. Why is this, and how can I influence=20 this?

With = kind=20 regards

Marten = Verhoeven

Van = Beek=20 B.V.

------_=_NextPart_001_01C85C3E.4CDF75E0--