Re: Wrong index used when ORDER BY LIMIT 1

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux