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

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?

merlin