Re: simple update query too long

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

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
>