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
