* 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