> -----Original Message----- > From: pgsql-performance-owner@postgresql.org [mailto:pgsql- > performance-owner@postgresql.org] On Behalf Of Claudio Freire > Sent: Wednesday, August 07, 2013 2:20 PM > To: Robert DiFalco > Cc: pgsql-performance@postgresql.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.difalco@gmail.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;
