Hi list<br>
<br>
I use PostgreSQL 8.4.4. with Postgis 1.4<br>
<br>
I have a simple update query that takes hours to run.<br>
The table is rather big (2 millions records) but it takes more than 5
hours to run !!<br>
<br>
The query is just :<br>
<font size="1"><b style="background-color: rgb(192, 192, 192); font-family: courier new,monospace;">UPDATE grille SET inter = 0</b></font><br>
<br>
The explain command seems ok :<br>
<span style="font-family: courier new,monospace; background-color: rgb(192, 192, 192);">"Seq Scan on grille50 (cost=0.00..499813.56
rows=2125456 width=494)"</span><br>
<br>
The table as a geometry field geom (simple, it only stores squares)<br>
The table définition is :<br>
<font size="1"><b><span style="background-color: rgb(204, 204, 204); font-family: courier new,monospace;">CREATE TABLE grille50</span><br style="background-color: rgb(204, 204, 204); font-family: courier new,monospace;">
<span style="background-color: rgb(204, 204, 204); font-family: courier new,monospace;">(</span><br style="background-color: rgb(204, 204, 204); font-family: courier new,monospace;">
<span style="background-color: rgb(204, 204, 204); font-family: courier new,monospace;"> id integer NOT NULL,</span><br style="background-color: rgb(204, 204, 204); font-family: courier new,monospace;">
<span style="background-color: rgb(204, 204, 204); font-family: courier new,monospace;"> geom geometry,</span><br style="background-color: rgb(204, 204, 204); font-family: courier new,monospace;">
<span style="background-color: rgb(204, 204, 204); font-family: courier new,monospace;"> inter integer DEFAULT 0,</span><br style="background-color: rgb(204, 204, 204); font-family: courier new,monospace;">
<span style="background-color: rgb(204, 204, 204); font-family: courier new,monospace;"> oc1 integer,</span><br style="background-color: rgb(204, 204, 204); font-family: courier new,monospace;">
<span style="background-color: rgb(204, 204, 204); font-family: courier new,monospace;"> oc2 integer,</span><br style="background-color: rgb(204, 204, 204); font-family: courier new,monospace;">
<span style="background-color: rgb(204, 204, 204); font-family: courier new,monospace;"> occalc integer,</span><br style="background-color: rgb(204, 204, 204); font-family: courier new,monospace;">
<span style="background-color: rgb(204, 204, 204); font-family: courier new,monospace;"> CONSTRAINT grille_pkey PRIMARY KEY (id),</span><br style="background-color: rgb(204, 204, 204); font-family: courier new,monospace;">
<span style="background-color: rgb(204, 204, 204); font-family: courier new,monospace;"> CONSTRAINT enforce_dims_geom CHECK
(st_ndims(geom) = 2),</span><br style="background-color: rgb(204, 204, 204); font-family: courier new,monospace;">
<span style="background-color: rgb(204, 204, 204); font-family: courier new,monospace;"> CONSTRAINT enforce_geotype_geom CHECK
(geometrytype(geom) = 'POLYGON'::text OR geom IS NULL),</span><br style="background-color: rgb(204, 204, 204); font-family: courier new,monospace;">
<span style="background-color: rgb(204, 204, 204); font-family: courier new,monospace;"> CONSTRAINT enforce_srid_geom CHECK
(st_srid(geom) = 2154)</span><br style="background-color: rgb(204, 204, 204); font-family: courier new,monospace;">
<span style="background-color: rgb(204, 204, 204); font-family: courier new,monospace;">)</span><br style="background-color: rgb(204, 204, 204); font-family: courier new,monospace;">
<span style="background-color: rgb(204, 204, 204); font-family: courier new,monospace;">WITH (</span><br style="background-color: rgb(204, 204, 204); font-family: courier new,monospace;">
<span style="background-color: rgb(204, 204, 204); font-family: courier new,monospace;"> OIDS=TRUE</span><br style="background-color: rgb(204, 204, 204); font-family: courier new,monospace;">
<span style="background-color: rgb(204, 204, 204); font-family: courier new,monospace;">);</span><br style="background-color: rgb(204, 204, 204); font-family: courier new,monospace;">
<span style="background-color: rgb(204, 204, 204); font-family: courier new,monospace;">ALTER TABLE grille OWNER TO postgres;</span><br style="background-color: rgb(204, 204, 204); font-family: courier new,monospace;">
<span style="background-color: rgb(204, 204, 204); font-family: courier new,monospace;">CREATE INDEX grille_geom ON grille USING gist
(geom);</span><br style="background-color: rgb(204, 204, 204); font-family: courier new,monospace;">
<span style="background-color: rgb(204, 204, 204); font-family: courier new,monospace;">CREATE INDEX grille_id ON grille USING btree
(id);</span></b><br style="background-color: rgb(204, 204, 204); font-family: courier new,monospace;">
</font><br>
<br>
So any ideas why is it soo long???<br>
<br>
Many thanks <br>
<br>
Fabrice