Re: simple update query too long

2011/5/13 F T <oukile(at)gmail(dot)com>:
> 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...

This is problem of GiST index. CREATE TABLE AS SELECT doesn't create
any indexes.

Regards

Pavel Stehule

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