Re: updating a row in a table with only one row - Mailing list pgsql-performance

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?

...Robert