DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:sender:in-reply-to:references:date
	:x-google-sender-auth:message-id:subject:from:to:cc:content-type
	:content-transfer-encoding;
	b=I7tfPLiM4t7av+XiRrci/WWBmNe20u/V++pVXXRfjAOBzstWD2uy11xB6lFeC+QbTO
	tF+SUSe5Ry4Zna43tTuVnYYH0a3cxsS9iXiAqGV5/9QyyquwH/duEx5z4Rnts/tc7ZaN
	uqLpN8cTKfguPvCqqIAXW8G9AdbjKZn1G/Xkw=
MIME-Version: 1.0
In-Reply-To: <603c8f070902271454h5a78a063kcf2b78a10fd956b1@mail.gmail.com>
References: <88daf38c0902141425s6972d849i9dad904f55d93c77@mail.gmail.com>
	<e7f9235d0902142029h5b53baf2gede92b22afbbad35@mail.gmail.com>
	<88daf38c0902150845y2878fcbfmcd06d426b53a886a@mail.gmail.com>
	<88daf38c0902271218p1931ec9dyfdc269bceab24190@mail.gmail.com>
	<603c8f070902271454h5a78a063kcf2b78a10fd956b1@mail.gmail.com>
Date: Sat, 28 Feb 2009 17:20:22 +0100
Message-ID: <88daf38c0902280820p745ea234s85efa7f5e58bd0ed@mail.gmail.com>
Subject: Re: Bad plan for nested loop + limit
From: Alexander Staubo <alex@bengler.no>
To: Robert Haas <robertmhaas@gmail.com>
Cc: David Wilson <david.t.wilson@gmail.com>,
	pgsql-performance <pgsql-performance@postgresql.org>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

On Fri, Feb 27, 2009 at 11:54 PM, Robert Haas <robertmhaas@gmail.com> wrote=
:
> The problem here is that the planner estimates the cost of a Limit
> plan node by adding up (1) the startup cost of the underlying plan
> node, in this case 0 for the nestjoin, and (2) a percentage of the run
> cost, based on the ratio of the number of rows expected to be returned
> to the total number of rows. =C2=A0In this case, the nested loop is
> expected to return 6944 rows, so it figures it won't have to get very
> far to find the 4 you requested.
[...]
> I will think about this some more but nothing is occurring to me off
> the top of my head.

Thanks for explaining. Is there any way to rewrite the query in a way
that will avoid the nested loop join -- other than actually disabling
nested loop joins? If I do the latter, the resulting query uses a hash
join and completes in 80-100 ms, which is still pretty horrible,
especially for a query that returns nothing, but extremely auspicious
compared to the unthinkable 4-5 seconds for the current query.

Alexander.