Re: simple update query too long

On Fri, 13 May 2011, F T wrote:

> Thanks for your ideas.
>
> 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...

I don't surprised, sequential read is a way faster than random.

>
> Fabrice
>
>
>
>
>
> 2011/5/9 Merlin Moncure <mmoncure(at)gmail(dot)com>
>
>> 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
>>
>

Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru)
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83