Skip site navigation (1) Skip section navigation (2)

Re: Wrong index used when ORDER BY LIMIT 1

From: Szűcs Gábor <surrano(at)gmail(dot)com>
To: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Wrong index used when ORDER BY LIMIT 1
Date: 2005-12-22 12:52:44
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
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.


In response to

pgsql-performance by date

Next:From: Merlin MoncureDate: 2005-12-22 13:29:53
Subject: Re: Speed of different procedural language
Previous:From: Richard HuxtonDate: 2005-12-22 09:19:51
Subject: Re: CPU and RAM

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group