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
