Re: slow commits with heavy temp table usage in 8.4.0

"Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> "Todd A. Cook" <tcook(at)blackducksoftware(dot)com> writes:
>>> I've noticed that on 8.4.0, commits can take a long time when a
>>> temp table is repeatedly filled and truncated within a loop.

>> The commit time doesn't seem tremendously out of line, but it looks
>> like there's something O(N^2)-ish in the function execution. Do
>> you see a similar pattern? With so many temp files there could well
>> be some blame on the kernel side. (This is a Fedora 10 box.)

> This sounds very similar to my experience here:
> http://archives.postgresql.org/pgsql-hackers/2008-11/msg01472.php

I did some more poking with oprofile, and got this:

samples % image name symbol name
559375 39.9848 postgres index_getnext
167626 11.9821 postgres TransactionIdIsCurrentTransactionId
107421 7.6786 postgres HeapTupleSatisfiesNow
65689 4.6955 postgres HeapTupleHeaderGetCmin
47220 3.3753 postgres HeapTupleHeaderGetCmax
46799 3.3452 postgres hash_search_with_hash_value
29331 2.0966 postgres heap_hot_search_buffer
23737 1.6967 postgres CatalogCacheFlushRelation
20562 1.4698 postgres LWLockAcquire
19838 1.4180 postgres heap_page_prune_opt
19044 1.3613 postgres _bt_checkkeys
17400 1.2438 postgres LWLockRelease
12993 0.9288 postgres PinBuffer

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.
So I think there's something happening on his box that's different from
what I'm measuring.

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