From: Szűcs Gábor <surrano@gmail.com>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Wrong index used when ORDER BY LIMIT 1
Date: Thu, 22 Dec 2005 13:52:44 +0100
Message-ID: <43AAA19C.5000909@gmail.com> (raw)
In-Reply-To: <15421.1135193659@sss.pgh.pa.us>
References: <43A998D4.4070300@gmail.com>
<15421.1135193659@sss.pgh.pa.us>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: surrano@gmail.com
Subject: Re: Wrong index used when ORDER BY LIMIT 1
In-Reply-To: <43AAA19C.5000909@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox