Re: Order-by and indexes

Dear Odd,

I am only a novice, but from what I understand from your email, you
want to write a query that selects the newest record from the table
for a certain set of data? To do this, I would use the MIN () function
on a column such as 'time_of_entry' or 'order_id' if the 'order_id' is
a sequence? Perhaps something like the below maybe...? Though my
construction of the query is probably incorrect - I'm only learning!

SELECT *
FROM "data"
WHERE
"data"."fk" = '238496'
AND
"data"."id" = (SELECT MIN("data"."id")

Cheers

James




On 29 June 2011 14:48, Odd Hogstad <odd.hogstad@smartm.no> wrote:
> I need to get the latest entry of a large table matching a certain criteria.
> This is my query:
>
> 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?
>
> Thanks!
>
> Odd-R.
>
>
>