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