I have rerun my tests and I agree with Merlin, PostgreSQL is not adapted at all to handle wide updates.
Summary :
The table contains 2 millions rows.
Test 1 :
UPDATE grille SET inter=0; -> It tooks 10 hours
Test 2 :
I remove the spatial Gist index, and the constraints : I just keep the primary key.
UPDATE grille SET inter=0; -> it tooks 6 hours.
This is better but it is still not acceptable.
And if I run CREATE TABLE test AS SELECT * FROM grille, it only takes 11 seconds, incredible...
Fabrice
2011/5/9 Merlin Moncure <mmoncure@xxxxxxxxx>
On Mon, May 9, 2011 at 10:29 AM, <tv@xxxxxxxx> wrote:HOT unfortunately does not provide a whole lot of benefit for this
>> 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.
case. HOT like brief, small transactions to the in page cleanup work
can be done as early as possible. The nature of postgres is such that
you want to do everything you can to avoid table wide updates (up to
and including building a new table instead).
merlin
