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

Re: Order-by and indexes

From: "Jean-Yves F(dot) Barbier" <12ukwn(at)gmail(dot)com>
To: Odd Hogstad <odd(dot)hogstad(at)smartm(dot)no>
Cc: "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Order-by and indexes
Date: 2011-06-29 14:27:31
Message-ID: 20110629162731.4e044dd5@anubis.defcon1 (view raw or flat)
Thread:
Lists: pgsql-novice
On Wed, 29 Jun 2011 15:48:56 +0200, Odd Hogstad <odd(dot)hogstad(at)smartm(dot)no> wrote:

> SELECT * FROM "data" WHERE "data"."fk" = 238496 ORDER BY "data"."id" DESC
> LIMIT 1
> 
> This query is quite slow. If I do a explain on it, it seems that it uses an
> Index Scan Backward.
> 
> If I omit the order by on the query:
> 
> SELECT * FROM "data" WHERE "data"."fk" = 238496 LIMIT 1
> 
> It is very fast. And the explain says that it uses Index scan. This is also
> very fast if there aren't any matches. But I've read that I'm not guaranteed
> to get the correct match If I do not use a order by, postgres just returns
> its fastest possible match. Is this right? But will not the fastest possible
> match always be the first match in the index? Is there another way to make
> the order by query go faster?

Unfortunately (and AFAIK), you don't have any other solution as you want the
*latest* row; may be often clustering this table in this order would help a
bit.
Perhaps creating fragmented indexes could also help (1 >= data.fk < 50001, and so on)

JY
-- 
He asked me if I knew what time it was -- I said yes, but not right now.
		-- Steven Wright

In response to

Responses

pgsql-novice by date

Next:From: Odd HogstadDate: 2011-06-29 14:42:36
Subject: Re: Order-by and indexes
Previous:From: James David SmithDate: 2011-06-29 14:23:06
Subject: Re: Order-by and indexes

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