DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:user-agent:mime-version:to:subject:references:in-reply-to:content-type:content-transfer-encoding;
	b=SRNU64VTun6Th3mGzcTHex+JTfM4UQtonz7KWQyVzmz1J+r8+a2XoU4yNHdKUtNGVnciDOIpzcdDHnSPGVUz4MYmltu7ujLMrfJMoZIXo4g25Hl1VT6AIP7Veekr+5SM/jE1KGiKiIMPbBL2cDnMcNqkiHYpMcw+wfE5I0mBD5Q=
Message-ID: <43AAA19C.5000909@gmail.com>
Date: Thu, 22 Dec 2005 13:52:44 +0100
From: =?ISO-8859-2?Q?Sz=FBcs_G=E1bor?= <surrano@gmail.com>
User-Agent: Thunderbird 1.5 (Windows/20051025)
MIME-Version: 1.0
To: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: Wrong index used when ORDER BY LIMIT 1
References: <43A998D4.4070300@gmail.com> <15421.1135193659@sss.pgh.pa.us>
In-Reply-To: <15421.1135193659@sss.pgh.pa.us>
Content-Type: text/plain; charset=ISO-8859-2; format=flowed
Content-Transfer-Encoding: 7bit

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.