* Uwe Schroeder (uwe@xxxxxxxxx) 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 suddenly shows 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 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. > > Thanks > > Uwe > > -- > Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
Attachment:
signature.asc
Description: Digital signature
