Re: simple update query too long

On Mon, May 9, 2011 at 10:29 AM, <tv(at)fuzzy(dot)cz> wrote:
>> 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.

HOT unfortunately does not provide a whole lot of benefit for this
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