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

On Tue, May 24, 2011 at 5:10 PM, Jasmin Dizdarevic
<jasmin(dot)dizdarevic(at)gmail(dot)com> 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