Re: PG9.0 planner difference to 8.3 -> majorly bad performance

<del>

> The duration suddenly goes from 270 milliseconds to 173 seconds! The index
> scan on bprofile_comments_status_idx suddenly shows 15288 loops, where it
> should be 1 loop just like before. So shomehow the 9.0 planner gets it all
> wrong.
>
> I also noticed that normally I get an iowait with a few percent during such
> operations (on 8.3), where with pg9 I get 0 iowait and 100% CPU. PG9 has a
> much smaller memory footprint than 8.3 in the same configuration - so this
> all makes very little sense to me. Maybe someone here has an idea.

Usually someone here has a lot more knowledge than me and comes up with a
viable hint rather quickly. Not so this time I'm afraid.
That tells me that something deep down changed.

Here are a few observations I made:

in PG9 NOT IN queries with a subselect (aka "select x from y where x.id not in
(select id from some_other_table)" ) perform a heck of a lot better than in
8.x. On the same note, when you re-wrote the query to use a left outer join
with a "IS NULL" where clause, PG9 performs horribly slow. A query like
"select x from y left outer join z on z.id=y.id where z.id is null" performs
like a 1000 times slower than in 8.x

I'm not an expert looking at explain output, but every time I try the "left
outer" solution to something that's basically a "not in" I get lousy
performace. Looking at the explain, now a "left outer join" always implies as
many loops over a nested block as there are rows in the referenced table. 8.x
actually returns the rows in one loop. This seems to be an issue of what is
done in what order. 8.x puts the "left outer" scan pretty early in the query,
so the remaining joins already use a limited dataset. 9.0 puts the left outer
towards the end of the nested blocks, which makes it work on the full set.

Maybe this makes sense to someone with more in-depth knowlege of the changes.

So far to me it looks like "if you use 9.x, avoit left outer joins and use
"NOT IN". On pg versions prior to 9.x avoid NOT IN and use left outer joins

odd :-)

Uwe