Received: from localhost (unknown [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id A4C9C2E2E6F for ; Mon, 21 Jan 2008 12:56:01 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 18405-08 for ; Mon, 21 Jan 2008 12:55:56 -0400 (AST) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 972712E0302 for ; Mon, 21 Jan 2008 12:55:56 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id m0LGttdj008284; Mon, 21 Jan 2008 11:55:55 -0500 (EST) To: "Marten Verhoeven" cc: pgsql-performance@postgresql.org Subject: Re: Slow performance with left outer join In-reply-to: References: Comments: In-reply-to "Marten Verhoeven" message dated "Mon, 21 Jan 2008 16:00:02 +0100" Date: Mon, 21 Jan 2008 11:55:55 -0500 Message-ID: <8283.1200934555@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200801/160 X-Sequence-Number: 28399 "Marten Verhoeven" 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