updating a row in a table with only one row

Поиск
Список
Период
Сортировка
От Michal Vitecek
Тема updating a row in a table with only one row
Дата
Msg-id 20091002081805.GA15557@mageo.cz
обсуждение исходный текст
Ответы Re: updating a row in a table with only one row  ("A. Kretschmer" <andreas.kretschmer@schollglas.com>)
Re: updating a row in a table with only one row  (Hélder M. Vieira <hmv@mail.telepac.pt>)
Re: updating a row in a table with only one row  (Merlin Moncure <mmoncure@gmail.com>)
Список pgsql-performance
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.

        Thanks,
--
        Michal        (fuf@mageo.cz)

В списке pgsql-performance по дате отправления:

Предыдущее
От: Scara Maccai
Дата:
Сообщение: Re: Best suiting OS
Следующее
От: "A. Kretschmer"
Дата:
Сообщение: Re: updating a row in a table with only one row