On Fri, Oct 2, 2009 at 9:54 AM, Merlin Moncure <mmoncure@xxxxxxxxx> wrote:
> On Fri, Oct 2, 2009 at 4:18 AM, Michal Vitecek <fuf@xxxxxxxx> 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
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
