Re: slow commits with heavy temp table usage in 8.4.0

Tom Lane wrote:
> 
> So what I'm seeing is entirely explained by the buildup of dead versions
> of the temp table's pg_class row --- the index_getnext time is spent
> scanning over dead HOT-chain members.  It might be possible to avoid
> that by special-casing temp tables in TRUNCATE to recycle the existing
> file instead of assigning a new one.  However, there is no reason to
> think that 8.3 would be any better than 8.4 on that score.  Also, I'm
> not seeing the very long CPU-bound commit phase that Todd is seeing.

The commit looks CPU-bound when I let the residual I/O from the function
execution die out before I issue the commit.

> 
> I'm actually testing CVS HEAD, not 8.4.0, but I don't recall that we've
> done anything in the past month that would be likely to affect this ...
> 
>             regards, tom lane
> .
> 

Tom's theory may explain the different commit results I get when
testing on two different databases:
    db         truncations     function      commit
------------   -----------   -----------   ----------
test           10000           29603.624     6054.889
test           10000           34740.167    14551.177
test           10000           30608.260    11144.503
test           10000           32239.049     9846.676

test           30000          227115.850    50206.947
test           30000          201859.698    46083.222
test           30000          231926.642    46681.009
test           30000          235665.970    47113.137

production     10000           32982.069    17654.772
production     10000           33297.524    17396.792
production     10000           35503.185    18343.045
production     10000           34251.753    18284.725

production     30000          200899.786    75480.448
production     30000          206793.209    73316.405
production     30000          260491.759    72570.297
production     30000          191363.168    66659.129


The "test" DB is nearly empty with 251 entries in pg_class, whereas
"production" has real data with 9981 entries in pg_class.

-- todd