From: | Igor Neyman <ineyman(at)perceptron(dot)com> |
---|---|
To: | Claudio Freire <klaussfreire(at)gmail(dot)com>, 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:35:05 |
Message-ID: | A76B25F2823E954C9E45E32FA49D70EC1BBF3BDD@mail.corp.perceptron.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
> -----Original Message-----
> From: pgsql-performance-owner(at)postgresql(dot)org [mailto:pgsql-
> performance-owner(at)postgresql(dot)org] On Behalf Of Claudio Freire
> Sent: Wednesday, August 07, 2013 2:20 PM
> To: Robert DiFalco
> Cc: pgsql-performance(at)postgresql(dot)org
> Subject: Re: [PERFORM] Efficiently query for the most recent record for a
> given user
>
> 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
>
And most recent call for every user:
SELECT id, user_id, MAX(called) OVER (PARTITION BY user_id) FROM call_activity;
Regards,
Igor Neyman
From | Date | Subject | |
---|---|---|---|
Next Message | Robert DiFalco | 2013-08-07 18:39:07 | Re: Efficiently query for the most recent record for a given user |
Previous Message | Tom Lane | 2013-08-07 18:34:12 | Re: Efficiently query for the most recent record for a given user |