[postgis-users] Query performance issue

[Stephen Woodbridge]

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)