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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Odd Hogstad | 2011-06-29 14:42:36 | Re: Order-by and indexes |
Previous Message | James David Smith | 2011-06-29 14:23:06 | Re: Order-by and indexes |