From: | James David Smith <james(dot)david(dot)smith(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:23:06 |
Message-ID: | BANLkTin_Dwz63ucmcryE4v3gDZWRiay1Lg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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(dot)hogstad(at)smartm(dot)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.
>
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Jean-Yves F. Barbier | 2011-06-29 14:27:31 | Re: Order-by and indexes |
Previous Message | Jean-Yves F. Barbier | 2011-06-29 14:17:18 | Re: Locking out a user after several failed login attempts |