Dear Tom,
On 2005.12.21. 20:34, Tom Lane wrote:
> =?ISO-8859-2?Q?Sz=FBcs_G=E1bor?= <surrano@gmail.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.
