[postgis-users] Simple update query on a big table is too long

F T oukile at gmail.com
Mon May 9 07:42:48 PDT 2011


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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20110509/282ac55f/attachment.html>


More information about the postgis-users mailing list