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

On Wed, Aug 7, 2013 at 4:04 PM, Tom Lane 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 ske

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

Claudio Freire escribió: > On Wed, Aug 7, 2013 at 3:34 PM, Tom Lane 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 d

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

Claudio Freire writes: > On Wed, Aug 7, 2013 at 3:34 PM, Tom Lane 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 directio

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

On Wed, Aug 7, 2013 at 3:34 PM, Tom Lane wrote: > Claudio Freire writes: >> On Wed, Aug 7, 2013 at 3:12 PM, Robert DiFalco >> 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 u

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

Thanks guys! On Wed, Aug 7, 2013 at 11:35 AM, Igor Neyman 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 >

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

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

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

Claudio Freire writes: > On Wed, Aug 7, 2013 at 3:12 PM, Robert DiFalco > 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 th

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

On Wed, Aug 7, 2013 at 3:12 PM, Robert DiFalco 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 m

[PERFORM] Efficiently query for the most recent record for a given user

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 sing

Re: [PERFORM] Better performance possible for a pathological query?

On Wed, Aug 7, 2013 at 12:07 PM, Tom Lane wrote: > =?UTF-8?B?QWxleGlzIEzDqi1RdcO0Yw==?= writes: > > The query itself is very simple: a primary key lookup on a 1.5x10^7 rows. > > The issue is that we are looking up over 11,000 primary keys at once, > > causing the db to consume a lot of CPU. > >

Re: [PERFORM] Better performance possible for a pathological query?

=?UTF-8?B?QWxleGlzIEzDqi1RdcO0Yw==?= writes: > The query itself is very simple: a primary key lookup on a 1.5x10^7 rows. > The issue is that we are looking up over 11,000 primary keys at once, > causing the db to consume a lot of CPU. It looks like most of the runtime is probably going into check

Re: [PERFORM] RE: [PERFORM] Re: [PERFORM] Sub-optimal plan for a paginated query on a view with another view inside of it.

writes: > "Total runtime: 9.313 ms" in pgAdmin > "Total runtime: 9.363 ms" in psql. > But timing after the query finished was 912.842 ms in psql. Well, that's the downside of increasing join_collapse_limit and from_collapse_limit: you might get a better plan, but it takes a lot longer to get it

Re: [PERFORM] Re: [PERFORM] Sub-optimal plan for a paginated query on a view with another view inside of it.

From: sl...@centrum.sk [mailto:sl...@centrum.sk] Sent: Wednesday, August 07, 2013 11:34 AM To: Igor Neyman; Pavel Stehule Cc: pgsql-performance@postgresql.org Subject: RE: [PERFORM] Re: [PERFORM] Sub-optimal plan for a paginated query on a view with another view inside of it. I got: "Total run

[PERFORM] Better performance possible for a pathological query?

Hi, I've seen a couple of bad queries go through one instance and I'm wondering whether there's something simple that can be done to help. Not running the query in the first place is what I am looking to do ultimately but in the meantime, I'm interested in understanding more about the plan below.

[PERFORM] RE: [PERFORM] Re: [PERFORM] Sub-optimal plan for a paginated query on a view with another view inside of it.

I got: "Total runtime: 9.313 ms" in pgAdmin "Total runtime: 9.363 ms" in psql. But timing after the query finished was 912.842 ms in psql.   Cheers,   Peter Slapansky __ Od: Igor Neyman Komu: "sl...@centrum.sk" , Pavel Stehule Dátum:

Re: [PERFORM] Re: [PERFORM] Sub-optimal plan for a paginated query on a view with another view inside of it.

2013/8/7 : > You're right, it does... but it's quite odd, because I re-ran the > explain-analyze statement and got the same results. > > Still, the query now runs for about a second as mentioned before, so it's > almost like something's missing from the explain, but I'm certain I copied > it all.

Re: [PERFORM] Re: [PERFORM] Sub-optimal plan for a paginated query on a view with another view inside of it.

2013/8/7 Igor Neyman : > > > From: pgsql-performance-ow...@postgresql.org > [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of sl...@centrum.sk > Sent: Wednesday, August 07, 2013 8:43 AM > To: Pavel Stehule > Cc: pgsql-performance@postgresql.org > Subject: [PERFORM] Re: [PERFORM] Sub-opt

Re: [PERFORM] Re: [PERFORM] Sub-optimal plan for a paginated query on a view with another view inside of it.

From: sl...@centrum.sk [mailto:sl...@centrum.sk] Sent: Wednesday, August 07, 2013 10:43 AM To: Igor Neyman; Pavel Stehule Cc: pgsql-performance@postgresql.org Subject: RE: [PERFORM] Re: [PERFORM] Sub-optimal plan for a paginated query on a view with another view inside of it. You're right, it

[PERFORM] RE: [PERFORM] Re: [PERFORM] Sub-optimal plan for a paginated query on a view with another view inside of it.

You're right, it does... but it's quite odd, because I re-ran the explain-analyze statement and got the same results. Still, the query now runs for about a second as mentioned before, so it's almost like something's missing from the explain, but I'm certain I copied it all.   I did this via pga

Re: [PERFORM] Re: [PERFORM] Sub-optimal plan for a paginated query on a view with another view inside of it.

From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of sl...@centrum.sk Sent: Wednesday, August 07, 2013 8:43 AM To: Pavel Stehule Cc: pgsql-performance@postgresql.org Subject: [PERFORM] Re: [PERFORM] Sub-optimal plan for a paginated query on a

[PERFORM] Re: [PERFORM] Sub-optimal plan for a paginated query on a view with another view inside of it.

Good day,   I have included a link to the result of EXPLAIN ANALYZE. It's this one: https://app.box.com/s/u8nk6qvkjs4ae7l7dh4h   Here's a link to Depesz's explain (if links to the site are okay): http://explain.depesz.com/s/gCk