# [postgis-users] Query performance issue

Stephen Woodbridge woodbri at swoodbridge.com
Thu Oct 13 14:47:12 PDT 2005

```So here are the variations on the UPDATE query and the respective query
plans. Given that Plan 1: ran for over 16 hrs and the estimated runtime
based on number of rows that had been updated is 261 hrs, does anyone
have any suggestion as to which might be the next best to try?

-Steve

### Plan 1: ran for 16 hrs and estimate runtime is 261 hrs

explain update streets set prov=prov_ab from province b where
streets.the_geom && b.the_geom and distance(streets.the_geom,
b.the_geom) = 0.0

Nested Loop  (cost=0.00..581.99 rows=17990 width=307)
Join Filter: (distance("inner".the_geom, "outer".the_geom) =
0::double precision)
->  Seq Scan on province b  (cost=0.00..1.14 rows=14 width=92196)
->  Index Scan using streets_gidx on streets  (cost=0.00..38.12
rows=9 width=301)
Index Cond: (streets.the_geom && "outer".the_geom)

### Plan 2:

explain UPDATE streets SET prov = (
SELECT prov_ab
FROM province b
WHERE streets.the_geom && b.the_geom AND intersects(streets.the_geom,
b.the_geom)
ORDER BY prov_ab
LIMIT 1
);

Seq Scan on streets  (cost=0.00..2296390.04 rows=1798897 width=301)
SubPlan
->  Limit  (cost=1.22..1.22 rows=1 width=6)
->  Sort  (cost=1.22..1.22 rows=1 width=6)
Sort Key: prov_ab
->  Seq Scan on province b  (cost=0.00..1.21 rows=1
width=6)
Filter: ((\$0 && the_geom) AND intersects(\$0,
the_geom))

### Plan 3:

explain UPDATE streets SET prov = (
SELECT prov_ab
FROM streets JOIN province b
ON streets.the_geom && b.the_geom WHERE intersects(streets.the_geom,
b.the_geom)
ORDER BY prov_ab
LIMIT 1
);

Seq Scan on streets  (cost=171335.34..264076.56 rows=1798897 width=301)
InitPlan
->  Limit  (cost=171335.34..171335.34 rows=1 width=6)
->  Sort  (cost=171335.34..174333.52 rows=1199270 width=6)
Sort Key: b.prov_ab
->  Nested Loop  (cost=0.00..536.70 rows=1199270 width=6)
Join Filter: intersects("inner".the_geom,
"outer".the_geom)
->  Seq Scan on province b  (cost=0.00..1.14
rows=14 width=92196)
->  Index Scan using streets_gidx on streets
(cost=0.00..38.12 rows=9 width=160)
Index Cond: (streets.the_geom &&
"outer".the_geom)

### Plan 4:

explain update streets set prov=prov_ab from province b where
( select count(*) from streets, province
where streets.the_geom && province.the_geom )=1
or
( streets.the_geom && b.the_geom
and distance(streets.the_geom, b.the_geom) = 0.0 );

Nested Loop  (cost=9530.91..1748998.34 rows=143822 width=307)
Join Filter: ((\$0 = 1) OR (("inner".the_geom && "outer".the_geom) AND
(distance("inner".the_geom, "outer".the_geom) = 0::double precision)))
InitPlan
->  Aggregate  (cost=9530.91..9530.91 rows=1 width=0)
->  Nested Loop  (cost=0.00..536.38 rows=3597808 width=0)
->  Seq Scan on province  (cost=0.00..1.14 rows=14
width=92190)
->  Index Scan using streets_gidx on streets
(cost=0.00..38.12 rows=9 width=160)
Index Cond: (streets.the_geom && "outer".the_geom)
->  Seq Scan on province b  (cost=0.00..1.14 rows=14 width=92196)
->  Seq Scan on streets  (cost=0.00..88243.97 rows=1798897 width=301)

```