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@xxxxxxxxx>
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
