Consider updating a PL/pgsql variable repeatedly, and then consider
updating a single-column, single-row table repeatedly, thus:
CREATE OR REPLACE FUNCTION update_var() RETURNS void AS $$
DECLARE
x int := 0;
BEGIN
FOR i IN 1..100000 LOOP
x := x + 1;
END LOOP;
END
$$ LANGUAGE plpgsql;
CREATE TABLE tab (x integer);
CREATE OR REPLACE FUNCTION update_tab() RETURNS void AS $$
BEGIN
INSERT INTO tab VALUES (0);
FOR i IN 1..100000 LOOP
UPDATE tab SET x = x + 1;
END LOOP;
END
$$ LANGUAGE plpgsql;
On my Fedora 12 VM, the first of these takes 33-36 ms, and the second
takes 114-121 s. While you'd expect updating a table to be slower
than updating a variable, a factor of 3000x seems rather excessive to
me. Profiling reveals that 80%+ of the time is spend testing tuple
visibility, which apparently needs to be done an average of over 7000
times per loop iteration. Full gprof results are attached, bzip'd so
as to avoid hitting the attachment size limit for this list.
Highlights below:
27.00 42.60 42.60 1410265409 0.00 0.00
TransactionIdIsCurrentTransactionId
23.51 79.69 37.09 705082704 0.00 0.00 HeapTupleSatisfiesMVCC
19.65 110.69 31.00 705182704 0.00 0.00 HeapTupleHeaderGetCmin
13.04 131.26 20.57 704982704 0.00 0.00 HeapTupleHeaderGetCmax
8.09 144.02 12.76 22173923 0.00 0.01 heapgetpage
1.09 145.74 1.72 XidInMVCCSnapshot
heapgettup_pagemode is called 200,000 times exactly; it makes
2,217,932 calls to heapgetpage (or approximately 110 per call), which
makes 705,082,704 calls to HeapTupleSatisfiesMVCC (or approximately
317 per heapgetpage call). Is there anything we can do about this?
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company
