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