Re: [GENERAL] simple update query too long
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
Re: [GENERAL] simple update query too long
2011/5/13 F T ouk...@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 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] simple update query too long
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 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 Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: o...@sai.msu.su, 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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] simple update query too long
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 ouk...@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
Re: [GENERAL] simple update query too long
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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] simple update query too long
On Fri, May 13, 2011 at 2:07 AM, F T ouk...@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,200) v;
SELECT 200
Time: 6985.000 ms
postgres=# create index on foo(id);
CREATE INDEX
Time: 7131.000 ms
postgres=# update foo set val = 0;
UPDATE 200
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 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
Re: [GENERAL] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 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* 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] simple update query too long
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
