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 <mmonc...@gmail.com> > On Mon, May 9, 2011 at 10:29 AM, <t...@fuzzy.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 >
