Re: Performance regression on CVS head

Heikki Linnakangas wrote:
> Tom Lane wrote:
>> Heikki Linnakangas <heikki@enterprisedb.com> writes:
>>> I tried to repeat the DBT-2 runs with the "oldestxmin refresh" patch, 
>>> but to my surprise the baseline run with CVS head, without the patch, 
>>> behaved very differently than it did back in March.
>>
>>> I rerun the a shorter 1h test with CVS head from May 20th, and March 
>>> 6th (which is when I ran the earlier tests), and something has 
>>> clearly been changed between those dates that affects the test. Test 
>>> run 248 is with CVS checkout from May 20th, and 249 is from March 6th:
>>
>> May 20th is not quite my idea of "HEAD" ;-).  It might be worth checking
>> current code before investing any think-time on this.  But having said
>> that, it looks a bit like a planner problem --- if I'm reading the
>> graphs correctly, I/O wait time goes through the roof, suggesting a
>> change to a much less efficient plan.
> 
> I tracked this down to the patch to enable plan invalidation for SPI plans:
> 
> http://archives.postgresql.org/pgsql-committers/2007-03/msg00136.php
> 
> Apparently the vacuum causes a plan invalidation and a worse plan is 
> chosen. I'll dig deeper into which queries are being affected and why. 
> Unless someone has any better ideas.

Ok, found it. The plan for stock-level transaction changed as a result 
of a lot of dead tuples in the district table.

I turned autovacuum on for the small, frequently-updated tables, and 
that fixed the problem.

--   Heikki Linnakangas  EnterpriseDB   http://www.enterprisedb.com