On Wed, Aug 7, 2013 at 3:12 PM, Robert DiFalco <robert.difalco@xxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
