Date: Wed, 21 Dec 2005 11:51:02 -0700
From: Michael Fuhr <mike@fuhr.org>
To: Sz?cs =?iso-8859-1?Q?G=E1bor?= <surrano@gmail.com>
Cc: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: Wrong index used when ORDER BY LIMIT 1
Message-ID: <20051221185101.GA56524@winnie.fuhr.org>
References: <43A998D4.4070300@gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <43A998D4.4070300@gmail.com>

On Wed, Dec 21, 2005 at 07:03:00PM +0100, Sz?cs Gbor wrote:
> Version: 7.4.6
[...]
> Query is:
> SELECT idopont WHERE muvelet = x ORDER BY idopont LIMIT 1.
> 
> I expected the planner to choose the index on muvelet, then sort by idopont.
> Instead, it took the other index.

I think the planner is guessing that since you're ordering on
idopont, scanning the idopont index will find the first matching
row faster than using the muvelet index would.  In many cases that's
a good bet, but in this case the guess is wrong and you end up with
a suboptimal plan.

I just ran some tests with 8.1.1 and it chose the better plan for
a query similar to what you're doing.  One of the developers could
probably explain why; maybe it's because of the changes that allow
better use of multicolumn indexes.  Try 8.1.1 if you can and see
if you get better results.

> -- workaround 2: quite ugly but seems to work (at least for this
> -- one test case):
> # explain analyze
>   select idopont from
>   (select idopont from muvelet_vonalkod
>    where muvelet=6859 order by idopont) foo
>   order by idopont limit 1;

Another workaround is to use OFFSET 0 in the subquery.

-- 
Michael Fuhr