Thanks guys!
On Wed, Aug 7, 2013 at 11:35 AM, Igor Neyman <iney...@perceptron.com> wrote: > > -----Original Message----- > > From: pgsql-performance-ow...@postgresql.org [mailto:pgsql- > > performance-ow...@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.difa...@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; > > Regards, > Igor Neyman > >
