Skip site navigation (1) Skip section navigation (2)

pessimal trivial-update performance

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: pessimal trivial-update performance
Date: 2010-07-04 04:05:45
Message-ID: AANLkTilng6sINyEhC46OIOCCGCcjxlb3nuifrggWGAyz@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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

Attachment: gprof.tab.out.bz2
Description: application/x-bzip2 (54.4 KB)

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2010-07-04 04:11:19
Subject: Re: pessimal trivial-update performance
Previous:From: Robert HaasDate: 2010-07-04 03:22:01
Subject: Re: Needs Suggestion

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group