Re: simple update query too long

> On 05/09/2011 04:39 PM, F T wrote:
>> Hi list
>>
>> I use PostgreSQL 8.4.4. (with Postgis 1.4)
>>
>> I have a simple update query that takes hours to run.
>> The table is rather big (2 millions records) but it takes more than 5
>> hours
>> to run !!
>>
>> The query is just :
>> *UPDATE grille SET inter = 0*
>>

>> So any ideas why is it soo long???
>>
>
> You've got three indexes, so you have the update on the table *and* the
> three indexes. Moreover, one of your indexes is a GiST with some PostGIS
> geometry. It takes usuaully quite some (long) time to update such index.

That only holds if the index needs to be updated. He's updating a column
that is not indexed, so with a bit of luck the HOT might kick in. In that
case the table would not bloat, the indexes would not need to be updated
(and would no bloat) etc.

The question is whether HOT may work in this particular case.

> How big is your table and each index?

I guess he mentioned there are 2 million rows, each about 500B wide (see
the exlain posted before). That gives about 1GB of data, so with a bit of
overhead I'd say about 1.5GB.

Fabrice, have you done some monitoring (iostat, dstat, ...) when the
update was in progress? I guess it's I/O boundd so I'd recommend to run
this

$ iostat -x 1

and see what is the utilization of the drives.

regards
Tomas