Thread: simple update query too long
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???
Many thanks
Fabrice
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???
Many thanks
Fabrice
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
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>
On Mon, May 9, 2011 at 10:29 AM, <tv@fuzzy.cz> wrote:HOT unfortunately does not provide a whole lot of benefit for this
>> 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.
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
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 > > 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 > >
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 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???
Many thanks
Fabrice
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