Re: Efficiently query for the most recent record for a given user

Thanks guys!

On Wed, Aug 7, 2013 at 11:35 AM, Igor Neyman <ineyman(at)perceptron(dot)com> wrote:

> > -----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
>
>