From: Michael Fuhr <mike@fuhr.org>
To: Sz?cs Gábor <surrano@gmail.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Wrong index used when ORDER BY LIMIT 1
Date: Wed, 21 Dec 2005 11:51:02 -0700
Message-ID: <20051221185101.GA56524@winnie.fuhr.org> (raw)
In-Reply-To: <43A998D4.4070300@gmail.com>
References: <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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mike@fuhr.org, surrano@gmail.com
Subject: Re: Wrong index used when ORDER BY LIMIT 1
In-Reply-To: <20051221185101.GA56524@winnie.fuhr.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox