pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedEfficiently query for the most recent record for a given user
10+ messages / 5 participants
[nested] [flat]
* Efficiently query for the most recent record for a given user
@ 2013-08-07 18:12 Robert DiFalco <robert.difalco@gmail.com>
2013-08-07 18:19 ` Re: Efficiently query for the most recent record for a given user Claudio Freire <klaussfreire@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Robert DiFalco @ 2013-08-07 18:12 UTC (permalink / raw)
To: pgsql-performance
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"?
Thanks in advance!
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Efficiently query for the most recent record for a given user
2013-08-07 18:12 Efficiently query for the most recent record for a given user Robert DiFalco <robert.difalco@gmail.com>
@ 2013-08-07 18:19 ` Claudio Freire <klaussfreire@gmail.com>
2013-08-07 18:34 ` Re: Efficiently query for the most recent record for a given user Tom Lane <tgl@sss.pgh.pa.us>
2013-08-07 18:35 ` Re: Efficiently query for the most recent record for a given user Igor Neyman <ineyman@perceptron.com>
0 siblings, 2 replies; 10+ messages in thread
From: Claudio Freire @ 2013-08-07 18:19 UTC (permalink / raw)
To: Robert DiFalco <robert.difalco@gmail.com>; +Cc: pgsql-performance
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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Efficiently query for the most recent record for a given user
2013-08-07 18:12 Efficiently query for the most recent record for a given user Robert DiFalco <robert.difalco@gmail.com>
2013-08-07 18:19 ` Re: Efficiently query for the most recent record for a given user Claudio Freire <klaussfreire@gmail.com>
@ 2013-08-07 18:34 ` Tom Lane <tgl@sss.pgh.pa.us>
2013-08-07 18:39 ` Re: Efficiently query for the most recent record for a given user Claudio Freire <klaussfreire@gmail.com>
1 sibling, 1 reply; 10+ messages in thread
From: Tom Lane @ 2013-08-07 18:34 UTC (permalink / raw)
To: Claudio Freire <klaussfreire@gmail.com>; +Cc: Robert DiFalco <robert.difalco@gmail.com>; pgsql-performance
Claudio Freire <klaussfreire@gmail.com> writes:
> On Wed, Aug 7, 2013 at 3:12 PM, Robert DiFalco <robert.difalco@gmail.com> wrote:
>> I want to get the last call_activity record for a single user.
> Create an index over (user_id, called desc), and do
> select * from call_activity where user_id = blarg order by called desc limit 1
Note that there's no particular need to specify "desc" in the index
definition. This same index can support searches in either direction
on the "called" column.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Efficiently query for the most recent record for a given user
2013-08-07 18:12 Efficiently query for the most recent record for a given user Robert DiFalco <robert.difalco@gmail.com>
2013-08-07 18:19 ` Re: Efficiently query for the most recent record for a given user Claudio Freire <klaussfreire@gmail.com>
2013-08-07 18:34 ` Re: Efficiently query for the most recent record for a given user Tom Lane <tgl@sss.pgh.pa.us>
@ 2013-08-07 18:39 ` Claudio Freire <klaussfreire@gmail.com>
2013-08-07 19:04 ` Re: Efficiently query for the most recent record for a given user Tom Lane <tgl@sss.pgh.pa.us>
2013-08-07 19:05 ` Re: Efficiently query for the most recent record for a given user Alvaro Herrera <alvherre@2ndquadrant.com>
0 siblings, 2 replies; 10+ messages in thread
From: Claudio Freire @ 2013-08-07 18:39 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Robert DiFalco <robert.difalco@gmail.com>; pgsql-performance
On Wed, Aug 7, 2013 at 3:34 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Claudio Freire <klaussfreire@gmail.com> writes:
>> On Wed, Aug 7, 2013 at 3:12 PM, Robert DiFalco <robert.difalco@gmail.com> wrote:
>>> I want to get the last call_activity record for a single user.
>
>> Create an index over (user_id, called desc), and do
>> select * from call_activity where user_id = blarg order by called desc limit 1
>
> Note that there's no particular need to specify "desc" in the index
> definition. This same index can support searches in either direction
> on the "called" column.
Yeah, but it's faster if it's in the same direction, because the
kernel read-ahead code detects sequential reads, whereas it doesn't
when it goes backwards. The difference can be up to a factor of 10 for
long index scans.
Though... true... for a limit 1... it wouldn't matter that much. But
it's become habit to match index sort order by now.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Efficiently query for the most recent record for a given user
2013-08-07 18:12 Efficiently query for the most recent record for a given user Robert DiFalco <robert.difalco@gmail.com>
2013-08-07 18:19 ` Re: Efficiently query for the most recent record for a given user Claudio Freire <klaussfreire@gmail.com>
2013-08-07 18:34 ` Re: Efficiently query for the most recent record for a given user Tom Lane <tgl@sss.pgh.pa.us>
2013-08-07 18:39 ` Re: Efficiently query for the most recent record for a given user Claudio Freire <klaussfreire@gmail.com>
@ 2013-08-07 19:04 ` Tom Lane <tgl@sss.pgh.pa.us>
2013-08-07 19:13 ` Re: Efficiently query for the most recent record for a given user Claudio Freire <klaussfreire@gmail.com>
1 sibling, 1 reply; 10+ messages in thread
From: Tom Lane @ 2013-08-07 19:04 UTC (permalink / raw)
To: Claudio Freire <klaussfreire@gmail.com>; +Cc: Robert DiFalco <robert.difalco@gmail.com>; pgsql-performance
Claudio Freire <klaussfreire@gmail.com> writes:
> On Wed, Aug 7, 2013 at 3:34 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Note that there's no particular need to specify "desc" in the index
>> definition. This same index can support searches in either direction
>> on the "called" column.
> Yeah, but it's faster if it's in the same direction, because the
> kernel read-ahead code detects sequential reads, whereas it doesn't
> when it goes backwards. The difference can be up to a factor of 10 for
> long index scans.
Color me skeptical. Index searches are seldom purely sequential block
accesses. Maybe if you had a freshly built index that'd never yet
suffered any inserts/updates, but in practice any advantage would
disappear very quickly after a few index page splits.
> Though... true... for a limit 1... it wouldn't matter that much.
That's the other point.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Efficiently query for the most recent record for a given user
2013-08-07 18:12 Efficiently query for the most recent record for a given user Robert DiFalco <robert.difalco@gmail.com>
2013-08-07 18:19 ` Re: Efficiently query for the most recent record for a given user Claudio Freire <klaussfreire@gmail.com>
2013-08-07 18:34 ` Re: Efficiently query for the most recent record for a given user Tom Lane <tgl@sss.pgh.pa.us>
2013-08-07 18:39 ` Re: Efficiently query for the most recent record for a given user Claudio Freire <klaussfreire@gmail.com>
2013-08-07 19:04 ` Re: Efficiently query for the most recent record for a given user Tom Lane <tgl@sss.pgh.pa.us>
@ 2013-08-07 19:13 ` Claudio Freire <klaussfreire@gmail.com>
2013-08-08 20:08 ` Re: Efficiently query for the most recent record for a given user Claudio Freire <klaussfreire@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Claudio Freire @ 2013-08-07 19:13 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Robert DiFalco <robert.difalco@gmail.com>; pgsql-performance
On Wed, Aug 7, 2013 at 4:04 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Yeah, but it's faster if it's in the same direction, because the
>> kernel read-ahead code detects sequential reads, whereas it doesn't
>> when it goes backwards. The difference can be up to a factor of 10 for
>> long index scans.
>
> Color me skeptical. Index searches are seldom purely sequential block
> accesses. Maybe if you had a freshly built index that'd never yet
> suffered any inserts/updates, but in practice any advantage would
> disappear very quickly after a few index page splits.
Maybe.
I've tested on pgbench test databases, which I'm not sure whether
they're freshly built indexes or incrementally built ones, and it
applies there (in fact backward index-only scans was one of the
workloads the read-ahead patch improved the most).
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Efficiently query for the most recent record for a given user
2013-08-07 18:12 Efficiently query for the most recent record for a given user Robert DiFalco <robert.difalco@gmail.com>
2013-08-07 18:19 ` Re: Efficiently query for the most recent record for a given user Claudio Freire <klaussfreire@gmail.com>
2013-08-07 18:34 ` Re: Efficiently query for the most recent record for a given user Tom Lane <tgl@sss.pgh.pa.us>
2013-08-07 18:39 ` Re: Efficiently query for the most recent record for a given user Claudio Freire <klaussfreire@gmail.com>
2013-08-07 19:04 ` Re: Efficiently query for the most recent record for a given user Tom Lane <tgl@sss.pgh.pa.us>
2013-08-07 19:13 ` Re: Efficiently query for the most recent record for a given user Claudio Freire <klaussfreire@gmail.com>
@ 2013-08-08 20:08 ` Claudio Freire <klaussfreire@gmail.com>
0 siblings, 0 replies; 10+ messages in thread
From: Claudio Freire @ 2013-08-08 20:08 UTC (permalink / raw)
To: Kevin Grittner <kgrittn@ymail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Robert DiFalco <robert.difalco@gmail.com>; pgsql-performance
On Thu, Aug 8, 2013 at 5:01 PM, Kevin Grittner <kgrittn@ymail.com> wrote:
> Claudio Freire <klaussfreire@gmail.com> wrote:
>> On Wed, Aug 7, 2013 at 4:04 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>>> Yeah, but it's faster if it's in the same direction, because the
>>>> kernel read-ahead code detects sequential reads, whereas it doesn't
>>>> when it goes backwards. The difference can be up to a factor of 10 for
>>>> long index scans.
>>>
>>> Color me skeptical. Index searches are seldom purely sequential block
>>> accesses. Maybe if you had a freshly built index that'd never yet
>>> suffered any inserts/updates, but in practice any advantage would
>>> disappear very quickly after a few index page splits.
>>
>> Maybe.
>>
>> I've tested on pgbench test databases, which I'm not sure whether
>> they're freshly built indexes or incrementally built ones, and it
>> applies there (in fact backward index-only scans was one of the
>> workloads the read-ahead patch improved the most).
>
> It's been a while, but when I was touching the btree code for the
> SSI implementation I thought I saw something about a reverse scan
> needing to visit the parent page in cases where a forward scan
> doesn't, due to the locking techniques used in btree. I don't know
> how significant those extra trips up and down the tree are, but
> they must cost *something*.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Efficiently query for the most recent record for a given user
2013-08-07 18:12 Efficiently query for the most recent record for a given user Robert DiFalco <robert.difalco@gmail.com>
2013-08-07 18:19 ` Re: Efficiently query for the most recent record for a given user Claudio Freire <klaussfreire@gmail.com>
2013-08-07 18:34 ` Re: Efficiently query for the most recent record for a given user Tom Lane <tgl@sss.pgh.pa.us>
2013-08-07 18:39 ` Re: Efficiently query for the most recent record for a given user Claudio Freire <klaussfreire@gmail.com>
@ 2013-08-07 19:05 ` Alvaro Herrera <alvherre@2ndquadrant.com>
1 sibling, 0 replies; 10+ messages in thread
From: Alvaro Herrera @ 2013-08-07 19:05 UTC (permalink / raw)
To: Claudio Freire <klaussfreire@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Robert DiFalco <robert.difalco@gmail.com>; pgsql-performance
Claudio Freire escribió:
> On Wed, Aug 7, 2013 at 3:34 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > Note that there's no particular need to specify "desc" in the index
> > definition. This same index can support searches in either direction
> > on the "called" column.
>
> Yeah, but it's faster if it's in the same direction, because the
> kernel read-ahead code detects sequential reads, whereas it doesn't
> when it goes backwards. The difference can be up to a factor of 10 for
> long index scans.
That might be true when an index is new, but as it grows, the leaf pages
are not going to be sequential anymore. And this doesn't much apply for
an equality lookup anyway, does it?
--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Efficiently query for the most recent record for a given user
2013-08-07 18:12 Efficiently query for the most recent record for a given user Robert DiFalco <robert.difalco@gmail.com>
2013-08-07 18:19 ` Re: Efficiently query for the most recent record for a given user Claudio Freire <klaussfreire@gmail.com>
@ 2013-08-07 18:35 ` Igor Neyman <ineyman@perceptron.com>
2013-08-07 18:39 ` Re: Efficiently query for the most recent record for a given user Robert DiFalco <robert.difalco@gmail.com>
1 sibling, 1 reply; 10+ messages in thread
From: Igor Neyman @ 2013-08-07 18:35 UTC (permalink / raw)
To: Claudio Freire <klaussfreire@gmail.com>; Robert DiFalco <robert.difalco@gmail.com>; +Cc: pgsql-performance
> -----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;
Regards,
Igor Neyman
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Efficiently query for the most recent record for a given user
2013-08-07 18:12 Efficiently query for the most recent record for a given user Robert DiFalco <robert.difalco@gmail.com>
2013-08-07 18:19 ` Re: Efficiently query for the most recent record for a given user Claudio Freire <klaussfreire@gmail.com>
2013-08-07 18:35 ` Re: Efficiently query for the most recent record for a given user Igor Neyman <ineyman@perceptron.com>
@ 2013-08-07 18:39 ` Robert DiFalco <robert.difalco@gmail.com>
0 siblings, 0 replies; 10+ messages in thread
From: Robert DiFalco @ 2013-08-07 18:39 UTC (permalink / raw)
To: Igor Neyman <ineyman@perceptron.com>; +Cc: Claudio Freire <klaussfreire@gmail.com>; pgsql-performance
Thanks guys!
On Wed, Aug 7, 2013 at 11:35 AM, Igor Neyman <ineyman@perceptron.com> wrote:
> > -----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;
>
> Regards,
> Igor Neyman
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2013-08-08 20:08 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-08-07 18:12 Efficiently query for the most recent record for a given user Robert DiFalco <robert.difalco@gmail.com>
2013-08-07 18:19 ` Claudio Freire <klaussfreire@gmail.com>
2013-08-07 18:34 ` Tom Lane <tgl@sss.pgh.pa.us>
2013-08-07 18:39 ` Claudio Freire <klaussfreire@gmail.com>
2013-08-07 19:04 ` Tom Lane <tgl@sss.pgh.pa.us>
2013-08-07 19:13 ` Claudio Freire <klaussfreire@gmail.com>
2013-08-08 20:08 ` Claudio Freire <klaussfreire@gmail.com>
2013-08-07 19:05 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2013-08-07 18:35 ` Igor Neyman <ineyman@perceptron.com>
2013-08-07 18:39 ` Robert DiFalco <robert.difalco@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox