Re: Wrong index used when ORDER BY LIMIT 1

Dear Tom,

On 2005.12.21. 20:34, Tom Lane wrote:
> =?ISO-8859-2?Q?Sz=FBcs_G=E1bor?= <surrano(at)gmail(dot)com> writes:
>> Query is:
>> SELECT idopont WHERE muvelet = x ORDER BY idopont LIMIT 1.
>
> Much the best solution for this would be to have an index on
> (muvelet, idopont)
> --- perhaps you can reorder the columns of "muvelet_vonalkod_muvelet"
> instead of making a whole new index --- and then say
>
> SELECT idopont WHERE muvelet = x ORDER BY muvelet, idopont LIMIT 1

I was far too tired yesterday evening to produce such a clean solution but
finally came to this conclusion this morning :) Even without the new index,
it picks the index on muvelet, which decreases time to ~1.5ms. The new index
takes it down to 0.1ms.

However, this has a problem; namely, what if I don't (or can't) tell the
exact int value in the WHERE clause? In general: will the following query:

SELECT indexed_ts_field FROM table WHERE indexed_int_field IN (100,200)
-- or even: indexed_int_field BETWEEN 100 AND 200
ORDER BY indexed_ts_field LIMIT n

always pick the index on the timestamp field, or does it depend on something
else, say the limit size n and the attributes' statistics?

> PG 8.1 can apply such an index to your original query, but older
> versions will need the help of the modified ORDER BY to recognize
> that the index is usable.

So the direct cause is that 7.x planners prefer ORDER BY to WHERE when
picking indexes? But only when there is a LIMIT clause present?

I'd like to know how much of our code should I review; if it's explicitly
connected to LIMIT, I'd probably have to check far less code.

--
G.