simple update query too long

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*

The explain command seems ok :
"Seq Scan on grille50 (cost=0.00..499813.56 rows=2125456 width=494)"

The table as a geometry field geom (simple, it only stores squares)
The table d�finition is :
*CREATE TABLE grille50
(
id integer NOT NULL,
geom geometry,
inter integer DEFAULT 0,
oc1 integer,
oc2 integer,
occalc integer,
CONSTRAINT grille_pkey PRIMARY KEY (id),
CONSTRAINT enforce_dims_geom CHECK (st_ndims(geom) = 2),
CONSTRAINT enforce_geotype_geom CHECK (geometrytype(geom) =
'POLYGON'::text OR geom IS NULL),
CONSTRAINT enforce_srid_geom CHECK (st_srid(geom) = 2154)
)
WITH (
OIDS=TRUE
);
ALTER TABLE grille OWNER TO postgres;
CREATE INDEX grille_geom ON grille USING gist (geom);
CREATE INDEX grille_id ON grille USING btree (id);*

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.

How big is your table and each index?

--
Guillaume
http://www.postgresql.fr
http://dalibo.com

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.

How big is your table and each index?

I guess he mentioned there are 2 million rows, each about 500B wide (see
the exlain posted before). That gives about 1GB of data, so with a bit of
overhead I'd say about 1.5GB.

Fabrice, have you done some monitoring (iostat, dstat, ...) when the
update was in progress? I guess it's I/O boundd so I'd recommend to run
this

$ iostat -x 1

and see what is the utilization of the drives.

regards
Tomas

On Mon, May 9, 2011 at 10:29 AM, <tv@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

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@gmail.com>

2011/5/13 F T <oukile@gmail.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

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@gmail.com>

On Mon, May 9, 2011 at 10:29 AM, <tv@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

Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

Hi,

Would it be faster if you create Partial Index on inter field (btree) where
inter > 0

and then UPDATE grille SET inter = 0 WHERE inter > 0

Kind Regards,

Misa

2011/5/9 F T <oukile@gmail.com>

Hi,
I would suggest if you can try one of this options:

0- create a new index on " inter "column for grille table and in your WHERE
clause try to limit the number of update rows instead of 2mills for one
the whole transaction , something like :where inter > x and inter < y;

1- drop at least the grille_geom indexes and next

2- disable All the check constraints on grille tables, there are geometry
functions which possible are called every time when you run the updates.
Isabella

--
View this message in context: http://postgresql.1045698.n5.nabble.com/simple-update-query-too-long-tp4382026p4393874.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On Fri, May 13, 2011 at 2:07 AM, F T <oukile@gmail.com> 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...

my experiences do not match yours:
postgres=# create table foo as select v as id, v as val, lpad('', 100,
'x') as various_data from generate_series(1,2000000) v;
SELECT 2000000
Time: 6985.000 ms
postgres=# create index on foo(id);
CREATE INDEX
Time: 7131.000 ms
postgres=# update foo set val = 0;
UPDATE 2000000
Time: 84524.000 ms
postgres=#

85 seconds is certainly a lot worse than 13, but nowhere near 6
hours...can we see a \d on the table as you have it with just the
primary key?

merlin