Re: Performance problem on 8.2.4, but not 8.2.3

Thanks for the quick responses. :-) The data is almost identical,
between the two servers: 8.2.3 has 882198 records, 8.2.4 has 893121.
For background, I pg_dump'ed the data into the 8.2.4 server
yesterday, and analyzed with the stats target of 250, then reanalyzed
with target 10. So, the statistics should theoretically be ok.
Running a vacuum full analyze on referrer_paths, per Kristo's
suggestion, didn't affect the query plan.

We downgraded to 8.2.3 just to rule that out, upped stats target to
100, analyzed, and are still experiencing the same behavior -- it's
still coming up with the same bogus rowcount estimates. Over the
weekend I'll lower the memory and see if that does anything, just to
rule that out... Any other thoughts? Thanks so much for your time
and suggestions thus far.

Cheers,
Dave

On May 25, 2007, at 4:33 PM, Tom Lane wrote:

> "Steinar H. Gunderson" <sgunderson(at)bigfoot(dot)com> writes:
>> It looks like the estimated cost is lower for 8.2.4 -- could it be
>> that the
>> fact that he's giving it more memory lead to the planner picking a
>> plan that
>> happens to be worse?
>
> Offhand I don't think so. More work_mem might make a hash join look
> cheaper (or a sort for a mergejoin), but the problem here seems to be
> that it's switching away from a hash and to a nestloop. Which is a
> loser because there are many more outer-relation rows than it's
> expecting.
>
> regards, tom lane
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
> http://www.postgresql.org/about/donate
>

Dave Pirotte
Director of Technology
Media Matters for America
dpirotte(at)mediamatters(dot)org
phone: 202-756-4122