Re: [SQL] Performance of NOT IN and <> with PG 9.0.4
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 9.0. If you like to > investigate this further, I can provide you more details. Well, it makes me glad I insisted we add enable_material. But I can't really tell from this output what is happening. Can we see the EXPLAIN ANALYZE output on 9.0, with and without enable_material? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Performance of NOT IN and <> with PG 9.0.4
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 one materialize node in the whole plan. And just incidentally, do you have any of the other enable_* settings turned off? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Performance of NOT IN and <> with PG 9.0.4
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. The planner thinks that the cost of the plan you're getting with enable_material=on is 729721.34, and the cost of the plan you're getting with enable_material=off is 727904.40, or approximately an 0.2% difference. Any little change you make to anything in the system, or just random changes in your statistics, could cause the plans to bounce back and forth between those two. The real question is why the planner thinks those two cost about the same, when in reality one of them is way faster than the other. You might want to look through the EXPLAIN ANALYZE output and try to figure out which part of the plan is being mis-estimated. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
