From: | Claudio Freire <klaussfreire(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Robert DiFalco <robert(dot)difalco(at)gmail(dot)com>, "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Efficiently query for the most recent record for a given user |
Date: | 2013-08-07 18:39:45 |
Message-ID: | CAGTBQpZUUOqF6adjmt62Hmc_OYRo3meaoNY9tn93sULRoovO_w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On Wed, Aug 7, 2013 at 3:34 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Claudio Freire <klaussfreire(at)gmail(dot)com> writes:
>> On Wed, Aug 7, 2013 at 3:12 PM, Robert DiFalco <robert(dot)difalco(at)gmail(dot)com> wrote:
>>> I want to get the last call_activity record for a single user.
>
>> Create an index over (user_id, called desc), and do
>> select * from call_activity where user_id = blarg order by called desc limit 1
>
> Note that there's no particular need to specify "desc" in the index
> definition. This same index can support searches in either direction
> on the "called" column.
Yeah, but it's faster if it's in the same direction, because the
kernel read-ahead code detects sequential reads, whereas it doesn't
when it goes backwards. The difference can be up to a factor of 10 for
long index scans.
Though... true... for a limit 1... it wouldn't matter that much. But
it's become habit to match index sort order by now.
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2013-08-07 19:04:09 | Re: Efficiently query for the most recent record for a given user |
Previous Message | Robert DiFalco | 2013-08-07 18:39:07 | Re: Efficiently query for the most recent record for a given user |