Re: [SQL] Performance of NOT IN and <> with PG 9.0.4

2011-05-27 Thread Robert Haas
On Tue, May 24, 2011 at 5:10 PM, Jasmin Dizdarevic wrote: > That's strange... > If I comment out these rows > --sum(coalesce(e.num_wert,0)), > --sum(coalesce(d.num_wert,0)) > in the given statement, it works fine with enable_material = 'on'. > I didn't change any join. That's not that strange. T

Re: [SQL] Performance of NOT IN and <> with PG 9.0.4

2011-05-25 Thread Jasmin Dizdarevic
As I've understood the docs those 2 limits should not take effect, because the performance is going down when adding two aggregated columns, but only when enable_material is on. 2011/5/25 Tom Lane > Robert Haas writes: > > On Tue, May 24, 2011 at 3:32 PM, Jasmin Dizdarevic > > wrote: > >> enab

Re: [SQL] Performance of NOT IN and <> with PG 9.0.4

2011-05-25 Thread Tom Lane
Robert Haas writes: > On Tue, May 24, 2011 at 3:32 PM, Jasmin Dizdarevic > wrote: >> enable_material = off > Is there any chance you can reproduce this with a simpler test case > that doesn't involve quite so many joins? I didn't stop to count, but are there enough that join_collapse_limit or f

Re: [SQL] Performance of NOT IN and <> with PG 9.0.4

2011-05-25 Thread Robert Haas
On Tue, May 24, 2011 at 3:32 PM, Jasmin Dizdarevic wrote: > enable_material = off Is there any chance you can reproduce this with a simpler test case that doesn't involve quite so many joins? It looks to me like shutting off enable_material is saving you mostly by accident here. There's only on

Re: [SQL] Performance of NOT IN and <> with PG 9.0.4

2011-05-25 Thread Robert Haas
On Tue, May 24, 2011 at 7:45 AM, Jasmin Dizdarevic wrote: > Hi, > found the problem. > 238 sec. with set enable_material = 'on' > 4(!) sec. with set enable_material = 'off' > > @Robert Haas: I thought it would be interesting to you, because > you've committed a patch regarding materialization for

Re: [SQL] Performance of NOT IN and <> with PG 9.0.4

2011-05-24 Thread Jasmin Dizdarevic
That's strange... If I comment out these rows --sum(coalesce(e.num_wert,0)), --sum(coalesce(d.num_wert,0)) in the given statement, it works fine with enable_material = 'on'. I didn't change any join. other settings are unchanged. HashAggregate (cost=589873.86..593205.21 rows=12114 width=47) (act

Re: [SQL] Performance of NOT IN and <> with PG 9.0.4

2011-05-24 Thread Jasmin Dizdarevic
Hi, found the problem. 238 sec. with set enable_material = 'on' 4(!) sec. with set enable_material = 'off' @Robert Haas: I thought it would be interesting to you, because you've committed a patch regarding materialization for 9.0. If you like to investigate this further, I can provide you more

Re: [SQL] Performance of NOT IN and <> with PG 9.0.4

2011-05-24 Thread Jasmin Dizdarevic
Hi, now I have an example and a query plan for 8.4 and 9.0.4. See the differences! Performance with 9 is horrible. I've eliminated the NOT-IN-Statements hoping it would be better, but this had no effect. Statement: select kd.datum, kd.filiale, kd.kundart as segment, mis.shore(swiftlcd), sum(coal

Re: [SQL] Performance of NOT IN and <> with PG 9.0.4

2011-05-16 Thread Craig Ringer
On 05/16/2011 06:05 PM, Jasmin Dizdarevic wrote: Hi, is there a reason why Not IN-performance is so poor in 9.0.4 compared to 8.4? Example queries? EXPLAIN ANALYZE output? http://wiki.postgresql.org/wiki/SlowQueryQuestions -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postg

[SQL] Performance of NOT IN and <> with PG 9.0.4

2011-05-16 Thread Jasmin Dizdarevic
Hi, is there a reason why Not IN-performance is so poor in 9.0.4 compared to 8.4? Ty Jasmin