> * 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 8.3 instance is also just started. I run both on the same system (for
testing) so I turn one off to have the memory available.
But yes, I did run the queries multiple times in a row with no major
improvement.
>
> > 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?
What has me bummed is the index scan on
Index Scan using bprofile_comments_status_idx on bprofile_comments c
(cost=0.00..2558.77 rows=1531 width=12) (actual time=0.140..21.559 rows=1660
loops=1)
vs
Index Scan using bprofile_comments_status_idx on bprofile_comments c
(cost=0.00..4328.64 rows=1751 width=12) (actual time=0.033..8.097 rows=1872
loops=15288)
Unless I read this wrong, the upper (8.3) index scan fetches 1660 rows in up
to 21ms
the 9.0 plan comes up with an index scan on the same data which fetches 1872
rows in 8 ms but loops 15288 times (that's actually the number of records in
the referenced table), which in my book makes this scan take up to 8 x 15288 =
122304 ms or 122 seconds
work_mem is set to 50MB and increasing it to 80MB makes no difference
>
> 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