[postgis-users] Another Question on PostGIS Performance

[david blasby]

> EXPLAIN ANALYZE SELECT
> asbinary(force_collection(force_2d(the_geom)),'XDR'),gid::text from (
> SELECT map.the_geom, data.hs_zoller, map.gid from veg as map, veg_atr as
> data where map.pg = data.pg )as foo WHERE the_geom &&
> setSRID('BOX3D(784993.862745 149994.224698,835019.524963
> 199999.514866)'::BOX3D, find_srid('','veg','the_geom') ) ;
> NOTICE:  QUERY PLAN:
>
> Nested Loop  (cost=0.00..7.91 rows=1 width=44) (actual
> time=16.08..12505.91 rows=6591 loops=1)
>   ->  Index Scan using veg_gist_index on veg map  (cost=0.00..6.01
> rows=1 width=40) (actual time=7.05..1758.70 rows=6591 loops=1)
>   ->  Seq Scan on veg_atr data  (cost=0.00..1.40 rows=40 width=4)
> (actual time=0.03..0.78 rows=40 loops=6591)
> Total runtime: 12538.11 msec

I'm thinking that postgresql is incorrectly using the spatial index (see my
message entitled PostGIS spatial index use optimization (Proposal)).  Try
this:

EXPLAIN ANALYZE SELECT
asbinary(force_collection(force_2d(the_geom)),'XDR'),gid::text from (
SELECT map.the_geom, data.hs_zoller, map.gid from veg as map, veg_atr as
data where map.pg = data.pg )as foo ;

Its the same as before but it no longer uses the "&&" operator.

You can see from the above query plan that postgresql is expecting only 1
result after using the spatial index (but in actuality it gets all 6591).
This query plan is probably lickity-split if your BOX3D is small, but
s-l-o-w when its big - and postgresql doesnt currently have a measure of
BOX3D "sizes".

dave