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@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
Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@xxxxxxxxxx, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
