Re: [PERFORM] slow joins?

2013-04-06 Thread Julien Cigar
On 04/06/2013 16:22, Kevin Grittner wrote: Julien Cigar wrote: try to increase cpu_tuple_cost to 0.1 I agree that's on the right track, but possibly an overly blunt tool for the job. The following settings are likely to need adjustment, IMO: effective_cache_size: People often set this to so

Re: [PERFORM] slow joins?

2013-04-06 Thread Kevin Grittner
Julien Cigar wrote: > try to increase cpu_tuple_cost to 0.1 I agree that's on the right track, but possibly an overly blunt tool for the job.  The following settings are likely to need adjustment, IMO: effective_cache_size: People often set this to somewhere in the range of 50% to 75% of the RA

Re: [PERFORM] slow joins?

2013-04-06 Thread Julien Cigar
try to increase cpu_tuple_cost to 0.1 On 04/06/2013 03:50, Joe Van Dyk wrote: If I disable sequential scans, hash joins, and merge joins, the query plans become the same and performance on the first slow one is much improved. Is there something else I can do to avoid this problem? below also

Re: [PERFORM] slow joins?

2013-04-05 Thread Greg Williamson
Joe -- > > From: Joe Van Dyk >To: Greg Williamson >Cc: "pgsql-performance@postgresql.org" >Sent: Friday, April 5, 2013 7:56 PM >Subject: Re: [PERFORM] slow joins? > > >On Fri, Apr 5, 2013 at 6:54

Re: [PERFORM] slow joins?

2013-04-05 Thread Joe Van Dyk
On Fri, Apr 5, 2013 at 6:54 PM, Greg Williamson wrote: > Joe -- > > > > > From: Joe Van Dyk > >To: pgsql-performance@postgresql.org > >Sent: Friday, April 5, 2013 6:42 PM > >Subject: Re: [PERFORM] slow joins? > > &g

Re: [PERFORM] slow joins?

2013-04-05 Thread Greg Williamson
Joe -- > > From: Joe Van Dyk >To: pgsql-performance@postgresql.org >Sent: Friday, April 5, 2013 6:42 PM >Subject: Re: [PERFORM] slow joins? > > >(https://gist.github.com/joevandyk/df0df703f3fda6d14ae1/raw/c15cae813913b7f8c35b24b467a0c7

Re: [PERFORM] slow joins?

2013-04-05 Thread Joe Van Dyk
If I disable sequential scans, hash joins, and merge joins, the query plans become the same and performance on the first slow one is much improved. Is there something else I can do to avoid this problem? below also at https://gist.github.com/joevandyk/34e31b3ad5cccb730a50/raw/8081a4298ba50ac93a86

Re: [PERFORM] slow joins?

2013-04-05 Thread Joe Van Dyk
( https://gist.github.com/joevandyk/df0df703f3fda6d14ae1/raw/c15cae813913b7f8c35b24b467a0c732c0100d79/gistfile1.txtshows a non-wrapped version of the queries and plan) On Fri, Apr 5, 2013 at 6:38 PM, Joe Van Dyk wrote: > On 9.2.4, running two identical queries except for the value of a column >

[PERFORM] slow joins?

2013-04-05 Thread Joe Van Dyk
On 9.2.4, running two identical queries except for the value of a column in the WHERE clause. Postgres is picking very different query plans, the first is much slower than the second. Any ideas on how I can speed this up? I have btree indexes for all the columns used in the query. explain analyz

Re: [PERFORM] Slow joins against set-returning functions

2004-08-15 Thread Tom Lane
Michael Fuhr <[EMAIL PROTECTED]> writes: > Is the planner doing something wrong here? Hard to see how it can be very smart with no idea about what the function is going to return :-(. I'd say that the mergejoin plan is actually a good choice given the limited amount of info, because it has the le

[PERFORM] Slow joins against set-returning functions

2004-08-15 Thread Michael Fuhr
PostgreSQL versions: 7.4.3, 8.0.0beta1 Joins against set-returning functions can be slow. Here's a simple example (in 8.0.0beta1, the gen_series function can be replaced with generate_series): CREATE FUNCTION gen_series(INTEGER, INTEGER) RETURNS SETOF INTEGER AS ' DECLARE xstart ALIAS