From: | Claudio Freire <klaussfreire(at)gmail(dot)com> |
---|---|
To: | Robert DiFalco <robert(dot)difalco(at)gmail(dot)com> |
Cc: | "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:19:32 |
Message-ID: | CAGTBQpY5pDZBgpo+huLzUpi6zve69V7KKWUVeLaSBpr6ZvOLwQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On Wed, Aug 7, 2013 at 3:12 PM, Robert DiFalco <robert(dot)difalco(at)gmail(dot)com> wrote:
> Let's say I have a table something like this:
>
> create table call_activity (
> id int8 not null,
> called timestamp,
> user_id int8 not null,
> primary key (id)
> foreign key (user_id) references my_users
> )
>
>
> I want to get the last call_activity record for a single user. Is there ANY
> way to efficiently retrieve the last record for a specified user_id, or do I
> need to de-normalize and update a table with a single row for each user each
> time a new call_activity record is inserted? I know I how to do the query
> without the summary table (subquery or GROUP BY with MAX) but that seems
> like it will never perform well for large data sets. Or am I full of beans
> and it should perform just fine for a huge data set as long as I have an
> index on "called"?
Create an index over (user_id, called desc), and do
select * from call_activity where user_id = blarg order by called desc limit 1
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2013-08-07 18:34:12 | Re: Efficiently query for the most recent record for a given user |
Previous Message | Robert DiFalco | 2013-08-07 18:12:48 | Efficiently query for the most recent record for a given user |