DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=from:to:cc:subject:date:message-id:mime-version:content-type
	:content-transfer-encoding:x-priority:x-msmail-priority:x-mailer
	:in-reply-to:x-mimeole:thread-index:importance;
	b=Q2KK2OPjD9DhhtnOBfDD9OXk2e5Exi8HD4b8xgd0kH/TBDW0Z8nxgayx+Mrb7hES2I
	22Lm2b50y0qHdN9Tveu6D1Xx/gOIxOh2jkS7RCvhSOAKPBMREasItmd91c/85K9oQBqg
	lcpej7I8LO1wz9753Z1tpRWvNHI/hnFhw5QyI=
From: "David Rowley" <dgrowley@gmail.com>
To: "'Brad Ediger'" <brad@bradediger.com>
Cc: <pgsql-performance@postgresql.org>
Subject: Re: Nested Loop join being improperly chosen
Date: Fri, 29 Aug 2008 00:01:05 +0100
Message-ID: <2F2B8795E8584EEB97AF9FE82CA1D7A5@amd64>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
In-Reply-To: <D51F400E-CA0D-45A0-825A-40C5240CD230@bradediger.com>
thread-index: AckEcR4gr4eZs+HcR4ii5ra+0DDF9AE8DTOw
Importance: Normal

I had a similar problem here:
http://archives.postgresql.org/pgsql-bugs/2008-07/msg00026.php

Is the nested loop performing a LEFT join with yours? It's a little
difficult to tell just from the query plan you showed.

A work around for mine was to use a full outer join and eliminate the extra
rows in the where clause. A bit of a hack but it changed a 2 min query into
one that ran in under a second.

Of course this is not helping with your problem but at least may trigger
some more feedback.

David.


-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Brad Ediger
Sent: 22 August 2008 16:26
To: pgsql-performance@postgresql.org
Subject: [PERFORM] Nested Loop join being improperly chosen

Hello,
I'm having trouble with a Nested Loop being selected for a rather  
complex query; it turns out this is a pretty bad plan as the nested  
loop's row estimates are quite off (1 estimated / 1207881 actual). If  
I disable enable_nestloop, the query executes much faster (42 seconds  
instead of 605). The tables in the query have all been ANALYZEd just  
before generating these plans.

Here are the plans with and without enable_nestloop:

http://pastie.org/258043

The inventory table is huge; it currently has about 1.3 x 10^9 tuples.  
The items table has around 10,000 tuples, and the other tables in the  
query are tiny.

Any ideas or suggestions would be greatly appreciated. Thanks!
--
Brad Ediger