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

Re: pessimal trivial-update performance

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: pessimal trivial-update performance
Date: 2010-07-04 04:17:32
Message-ID: AANLkTikO81Wqgxe2ULNwrbGHRfVqVcuRUQ61cFX3vjlo@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
On Sun, Jul 4, 2010 at 12:11 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> 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;
>
> I believe that none of the dead row versions can be vacuumed during this
> test.

Yep, you seem to be right.  The table grows to 802 pages.  But why is
it that we can't vacuum them as we go along?

> So yes, it sucks, but is it representative of real-world cases?

Hard to say, but I think it probably is to some degree.  I stumbled on
it more-or-less by accident, but it wouldn't surprise me to find out
that there are people doing such things in real applications.  It's
not uncommon to want to store an updateable counter somewhere.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

In response to

Responses

pgsql-hackers by date

Next:From: Pavel StehuleDate: 2010-07-04 06:41:35
Subject: proof concept: do statement parametrization
Previous:From: Tom LaneDate: 2010-07-04 04:11:19
Subject: Re: pessimal trivial-update performance

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