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@xxxxxxxx)
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
