[postgis-users] Query performance issue

[Bill Binko]

Ok, just to throw my hat into the ring, why don't you try separating the 
SELECT and the UPDATE.

--First, CLUSTER your streets table around its GIST index:
-- (This can take a while, but not hours)

CLUSTER streets_gidx on streets;

-- Second, grab the (possibly many-many) relationships

SELECT streets.oid as soid, prov_ab INTO TEMPORARY street_prov FROM 
streets JOIN province ON (streets.the_grom && province.the_geom AND 
distance(streets.the_geom, province.the_geom) <= 0;

-- Then, index them with both fields in the index...for fast lookup

CREATE INDEX street_prov_idx ON street_prov(soid, prov_ab);

-- I am only taking the first one.

UPDATE streets set prov = (select prov_ab from street_prov_idx where soid 
= streets.oid limit 1); 

If nothing else, you'd know which part of this is causing your pain.

Bill

PS: the CLUSTER is a good idea anyway

On Thu, 13 Oct 2005, Stephen Woodbridge wrote:

> 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)
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
> 
>