To: Ian Westmacott <ianw@intellivid.com>
Cc: pgsql-performance@postgresql.org, pgsql-hackers@postgresql.org
Subject: Re: Planner doesn't look at LIMIT? 
In-reply-to: <1123707833.17725.133.camel@spectre.intellivid.com> 
References: <758d5e7f0507220210bfa4978@mail.gmail.com>
	<29473.1122043197@sss.pgh.pa.us> <6440.1122049220@sss.pgh.pa.us>
	<1123707833.17725.133.camel@spectre.intellivid.com>
Comments: In-reply-to Ian Westmacott <ianw@intellivid.com>
	message dated "Wed, 10 Aug 2005 17:03:53 -0400"
Date: Wed, 10 Aug 2005 18:55:24 -0400
Message-ID: <16040.1123714524@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Ian Westmacott <ianw@intellivid.com> writes:
> In a nutshell, I have a LIMIT query where the planner
> seems to favor a merge join over a nested loop.

The planner is already estimating only one row out of the join, and so
the LIMIT doesn't affect its cost estimates at all.

It appears to me that the reason the nestloop plan is fast is just
chance: a suitable matching row is found very early in the scan of
tableB, so that the indexscan on it can stop after 29 rows, instead
of having to go through all 55000 rows in the given range of bim.
If it'd have had to go through, say, half of the rows to find a match,
the sort/merge plan would show up a lot better.

If this wasn't chance, but was expected because there are many matching
rows and not only one, then there's a statistical problem.

			regards, tom lane