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

In response to Michal Vitecek :
> 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

In this case, only one row, you don't need an index. Really.

> 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.
>
> 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)

tableOne or jackpot?

>
> 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.

Maybe a lot of dead tuples, can you show us the output generated from
explain analyse?

I would suggest you to do a 'vacuum full' on this table.

Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)