Re: [PERFORM] Trouble with plan statistics for behaviour for query.

2012-05-31 Thread Craig James
On Thu, May 31, 2012 at 4:34 PM, Trevor Campbell wrote: > Thanks Craig, that certainly leads down the right path. > > The following is all done in pgAdmin3: > > Using an actual value we I get the plan I expect > explain analyze select CG.ID, CG.ISSUEID, CG.AUTHOR, CG.CREATED, CI.ID, > CI.FIELDTYPE

Re: [PERFORM] Trouble with plan statistics for behaviour for query.

2012-05-31 Thread Trevor Campbell
Thanks Craig, that certainly leads down the right path. The following is all done in pgAdmin3: Using an actual value we I get the plan I expect explain analyze select CG.ID, CG.ISSUEID, CG.AUTHOR, CG.CREATED, CI.ID, CI.FIELDTYPE, CI.FIELD, CI.OLDVALUE, CI.OLDSTRING, CI.NEWVALUE, CI.NEWSTRING

Re: [PERFORM] Trouble with plan statistics for behaviour for query.

2012-05-31 Thread Craig James
On Thu, May 31, 2012 at 4:01 PM, Trevor Campbell wrote: > On 01/06/12 08:55, Craig James wrote: > > > > On Thu, May 31, 2012 at 3:29 PM, Trevor Campbell > wrote: > >> We are having trouble with a particular query being slow in a strange >> manner. >> >> The query is a join over two large tables

Re: [PERFORM] Trouble with plan statistics for behaviour for query.

2012-05-31 Thread Trevor Campbell
On 01/06/12 08:55, Craig James wrote: On Thu, May 31, 2012 at 3:29 PM, Trevor Campbell mailto:tcampb...@atlassian.com>> wrote: We are having trouble with a particular query being slow in a strange manner. The query is a join over two large tables that are suitably indexed. selec

Re: [PERFORM] does the query planner consider work_mem?

2012-05-31 Thread Mark Kirkwood
On 31/05/12 05:57, Murat Tasan wrote: hi all - i'm having a bit of trouble with some queries that are running painfully slowly after migrating my database from one machine using PostgreSQL 8.2 to another machine with PostgreSQL 8.4. as far as i can tell, the two *servers* (not the physical machin

Re: [PERFORM] Trouble with plan statistics for behaviour for query.

2012-05-31 Thread Craig James
On Thu, May 31, 2012 at 3:29 PM, Trevor Campbell wrote: > We are having trouble with a particular query being slow in a strange > manner. > > The query is a join over two large tables that are suitably indexed. > > select CG.ID, CG.ISSUEID, CG.AUTHOR, CG.CREATED, CI.ID, CI.FIELDTYPE, > CI.FIELD,

[PERFORM] Trouble with plan statistics for behaviour for query.

2012-05-31 Thread Trevor Campbell
We are having trouble with a particular query being slow in a strange manner. The query is a join over two large tables that are suitably indexed. select CG.ID, CG.ISSUEID, CG.AUTHOR, CG.CREATED, CI.ID, CI.FIELDTYPE, CI.FIELD, CI.OLDVALUE, CI.OLDSTRING, CI.NEWVALUE, CI.NEWSTRING from PUBLIC.

Re: [HACKERS] [PERFORM] pg_dump and thousands of schemas

2012-05-31 Thread Claudio Freire
On Thu, May 31, 2012 at 12:25 PM, Tom Lane wrote: >> No, Tatsuo's patch attacks a phase dominated by latency in some >> setups. > > No, it does not.  The reason it's a win is that it avoids the O(N^2) > behavior in the server.  Whether the bandwidth savings is worth worrying > about cannot be prov

Re: [HACKERS] [PERFORM] pg_dump and thousands of schemas

2012-05-31 Thread Tom Lane
Claudio Freire writes: > On Thu, May 31, 2012 at 11:50 AM, Tom Lane wrote: >> The performance patches we applied to pg_dump over the past couple weeks >> were meant to relieve pain in situations where the big server-side >> lossage wasn't the dominant factor in runtime (ie, partial dumps). >> But

Re: [HACKERS] [PERFORM] pg_dump and thousands of schemas

2012-05-31 Thread Bruce Momjian
On Thu, May 31, 2012 at 11:04:12AM -0400, Robert Haas wrote: > On Thu, May 31, 2012 at 10:50 AM, Tom Lane wrote: > > Robert Haas writes: > >> On Thu, May 31, 2012 at 10:31 AM, Tom Lane wrote: > >>> I'm not; Jeff Janes is.  But you shouldn't be holding your breath > >>> anyway, since it's 9.3 mat

Re: [HACKERS] [PERFORM] pg_dump and thousands of schemas

2012-05-31 Thread Robert Haas
On Thu, May 31, 2012 at 10:50 AM, Tom Lane wrote: > Robert Haas writes: >> On Thu, May 31, 2012 at 10:31 AM, Tom Lane wrote: >>> I'm not; Jeff Janes is.  But you shouldn't be holding your breath >>> anyway, since it's 9.3 material at this point. > >> I agree we can't back-patch that change, but

Re: [HACKERS] [PERFORM] pg_dump and thousands of schemas

2012-05-31 Thread Claudio Freire
On Thu, May 31, 2012 at 11:50 AM, Tom Lane wrote: > The performance patches we applied to pg_dump over the past couple weeks > were meant to relieve pain in situations where the big server-side > lossage wasn't the dominant factor in runtime (ie, partial dumps). > But this one is targeting exactly

Re: [HACKERS] [PERFORM] pg_dump and thousands of schemas

2012-05-31 Thread Bruce Momjian
On Thu, May 31, 2012 at 10:50:51AM -0400, Tom Lane wrote: > Robert Haas writes: > > On Thu, May 31, 2012 at 10:31 AM, Tom Lane wrote: > >> I'm not; Jeff Janes is. �But you shouldn't be holding your breath > >> anyway, since it's 9.3 material at this point. > > > I agree we can't back-patch that

Re: [HACKERS] [PERFORM] pg_dump and thousands of schemas

2012-05-31 Thread Tom Lane
Robert Haas writes: > On Thu, May 31, 2012 at 10:31 AM, Tom Lane wrote: >> I'm not; Jeff Janes is.  But you shouldn't be holding your breath >> anyway, since it's 9.3 material at this point. > I agree we can't back-patch that change, but then I think we ought to > consider back-patching some var

Re: [HACKERS] [PERFORM] pg_dump and thousands of schemas

2012-05-31 Thread Robert Haas
On Thu, May 31, 2012 at 10:31 AM, Tom Lane wrote: > Claudio Freire writes: >> It's not clear whether Tom is already working on that O(N^2) fix in locking. > > I'm not; Jeff Janes is.  But you shouldn't be holding your breath > anyway, since it's 9.3 material at this point. I agree we can't back-

Re: [HACKERS] [PERFORM] pg_dump and thousands of schemas

2012-05-31 Thread Tom Lane
Claudio Freire writes: > It's not clear whether Tom is already working on that O(N^2) fix in locking. I'm not; Jeff Janes is. But you shouldn't be holding your breath anyway, since it's 9.3 material at this point. regards, tom lane -- Sent via pgsql-performance mailing

Re: [PERFORM] pg_dump and thousands of schemas

2012-05-31 Thread Claudio Freire
On Thu, May 31, 2012 at 11:17 AM, Robert Klemme wrote: > > OK, my fault was to assume you wanted to measure only your part, while > apparently you meant overall savings.  But Tom had asked for separate > measurements if I understood him correctly.  Also, that measurement of > your change would go

Re: [PERFORM] pg_dump and thousands of schemas

2012-05-31 Thread Robert Klemme
On Thu, May 31, 2012 at 4:07 PM, Tatsuo Ishii wrote: >> On Thu, May 31, 2012 at 10:45 AM, Tatsuo Ishii wrote: >>> Just for record, I rerun the test again with my single-LOCK patch, and >>> now total runtime of pg_dump is 113 minutes. >>> 188 minutes(9.0)->125 minutes(git master)->113 minutes(with

Re: [PERFORM] pg_dump and thousands of schemas

2012-05-31 Thread Tatsuo Ishii
> On Thu, May 31, 2012 at 10:45 AM, Tatsuo Ishii wrote: >> Just for record, I rerun the test again with my single-LOCK patch, and >> now total runtime of pg_dump is 113 minutes. >> 188 minutes(9.0)->125 minutes(git master)->113 minutes(with my patch). >> >> So far, I'm glad to see 40% time savings

Re: [PERFORM] pg_dump and thousands of schemas

2012-05-31 Thread Robert Klemme
On Thu, May 31, 2012 at 10:45 AM, Tatsuo Ishii wrote: > Just for record, I rerun the test again with my single-LOCK patch, and > now total runtime of pg_dump is 113 minutes. > 188 minutes(9.0)->125 minutes(git master)->113 minutes(with my patch). > > So far, I'm glad to see 40% time savings at thi

Re: [PERFORM] pg_dump and thousands of schemas

2012-05-31 Thread Tatsuo Ishii
>>> We recently fixed a couple of O(N^2) loops in pg_dump, but those covered >>> extremely specific cases that might or might not have anything to do >>> with what you're seeing. The complainant was extremely helpful about >>> tracking down the problems: >>> http://archives.postgresql.org/pgsql-ge