2011/5/13 F T <oukile@xxxxxxxxx>: > 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@xxxxxxxxx> >> >> On Mon, May 9, 2011 at 10:29 AM, Â<tv@xxxxxxxx> 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 > > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
