Re: updating a row in a table with only one row

Robert Haas wrote:
> On Fri, Oct 2, 2009 at 9:54 AM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
>> On Fri, Oct 2, 2009 at 4:18 AM, Michal Vitecek <fuf(at)mageo(dot)cz> wrote:
>>> Hello everyone,
>>>
>>> I'm using PostgreSQL 8.3.8 running on a server with 2 Xeon CPUs, 4GB
>>> RAM, 4+2 disks in RAID 5 and CentOS 5.3. There's only one database
>>> which dumped with pgdump takes ~0.5GB.
>>>
>>> There are ~100 tables in the database and one of them (tableOne) always
>>> contains only a single row. There's one index on it. However performing
>>> update on the single row (which occurs every 60 secs) takes a
>>> considerably long time -- around 200ms. The system is not loaded in any
>>> way.
>>>
>>> The table definition is:
>>>
>>> CREATE TABLE tableOne (
>>> value1 BIGINT NOT NULL,
>>> value2 INTEGER NOT NULL,
>>> value3 INTEGER NOT NULL,
>>> value4 INTEGER NOT NULL,
>>> value5 INTEGER NOT NULL,
>>> );
>>> CREATE INDEX tableOne_index1 ON tableOne (value5);
>>>
>>> And the SQL query to update the _only_ row in the above table is:
>>> ('value5' can't be used to identify the row as I don't know it at the
>>> time)
>>>
>>> UPDATE tableOne SET value1 = newValue1, value2 = newValue2, value5 = newValue5;
>>>
>>> And this is what EXPLAIN says on the above SQL query:
>>>
>>> DB=> EXPLAIN UPDATE tableOne SET value1 = newValue1, value2 = newValue2, value5 = newValue5;
>>> LOG: duration: 235.948 ms statement: EXPLAIN UPDATE tableOne SET value1 = newValue1, value2 = newValue2, value5 = newValue5;
>>> QUERY PLAN
>>> --------------------------------------------------------
>>> Seq Scan on jackpot (cost=0.00..1.01 rows=1 width=14)
>>> (1 row)
>>>
>>> What takes PostgreSQL so long? I guess I could add a fake 'id' column,
>>> create an index on it to identify the single row, but still -- the time
>>> seems quite ridiculous to me.
>> it is ridiculous. your problem is almost definitely dead rows. I
>> can't recall (and I can't find the info anywhere) if the 'hot' feature
>> requires an index to be active -- I think it does. If so, creating a
>> dummy field and indexing it should resolve the problem. Can you
>> confirm the dead row issue by doing vacuum verbose and create the
>> index? please respond with your results, I'm curious. Also, is
>> autovacuum on? Have you measured iowait?
>
> Since he's updating all the fields in the table, an index will
> certainly ensure that HOT does not apply, no?

An extra index shouldn't hurt if you don't update the indexed dummy
column. But the existing tableOne_index1 will cause HOT to not apply, if
value5 is updated. I'd suggest dropping it (and not creating any other
indexes either), it won't do any good on a table with only one row anyway.

If the table is indeed bloated, VACUUM FULL should shrink it back. I
wonder how it got to be that way, though. Autovacuum should keep a table
like that in check.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com