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

* Uwe Schroeder (uwe@oss4u.com) wrote:
> Now I turn off the 8.3 instance and start the 9.0 instance. Remember, everything is identical. Here the same query
again:

Everything isn't identical if you just started PG 9.0 though- presumably
the 8.3 instance had everything cache'd already.  What happens if you
run this query again under 9.0..?

> The duration suddenly goes from 270 milliseconds to 173 seconds! The index scan on bprofile_comments_status_idx
suddenlyshows 15288 loops, where it should be 1 loop just like before. So  
> shomehow the 9.0 planner gets it all wrong.

You do have a different plan, but both of them have a Nested Loop, with
a Hash table built inside it.  The 9.0 does also do the index scan
inside the loop, but if you look at the actual time, that's not really
causing a huge difference.  One thing I'm wondering about is if 9.0 is
getting a more accurate view of the amount of data and is realizing that
it might go over work_mem with the big Hash Left Join, and so decides
against it.  What does your work_mem setting look like on each system?
Have you tried increasing it?

    Thanks,

        Stephen

> I also noticed that normally I get an iowait with a few percent during such operations (on 8.3), where with pg9 I get
0iowait 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.
>
> Thanks
>
> Uwe
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general