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@gmail.com> wrote:
>> On Fri, Oct 2, 2009 at 4:18 AM, Michal Vitecek <fuf@mageo.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