[postgis-users] ST_Crosses doesn't use index?

[Stefan Keller]

2012/2/17 Greg Williamson <gwilliamson39 at yahoo.com>:
> You might provide a description of the tables (\d xxxx at the psql prompt),
> and perhaps the output of an "EXPLAIN ANALYZE" for this command.
> Any non-standard config settings might be of relevance as well.
>
> Greg Williamson
>

Ok; I realize that my first query was taking whole DB whereas the
second takes a spatial subset.
But what I still wonder is, why ST_Contains needs to be applied to the
second geometry (of table b) too since the index should really sort
out any outliers of table b
See below (I'm using PostGIS 1.5 on Linux).

Yours, Stefan


Table def.:
CREATE TABLE osm_line
(
  osm_id integer,
  ...
  ... -- around 20 attributes from osm2pgsql import
  ...
  tags hstore,
  way geometry
) WITH (OIDS=FALSE);


Query 1:

gisdb=> EXPLAIN ANALYZE
gisdb-> SELECT a.name
gisdb-> FROM osm_line a, osm_line b
gisdb-> WHERE ST_Contains(ST_Transform(ST_SetSRID('BOX(8.775 47.245,
8.873 47.208)'::box2d, 4326),900913),a.way)
gisdb-> AND a.osm_id != b.osm_id
gisdb-> AND ST_Crosses(a.way, b.way);

                                         QUERY PLAN
----------
 Nested Loop  (cost=60.59..14659.13 rows=713 width=14) (actual
time=157.097..26896.760 rows=954 loops=1)
   Join Filter: ((a.osm_id <> b.osm_id) AND _st_crosses(a.way, b.way))
   ->  Bitmap Heap Scan on osm_line a  (cost=60.59..5708.36 rows=535
width=1097) (actual time=1.398..16.426 rows=1702 loops=1)
         Recheck Cond: ('01030...'::geometry && way)
         Filter: _st_contains('01030...'::geometry, way)
         ->  Bitmap Index Scan on osm_line_index  (cost=0.00..60.46
rows=1606 width=0) (actual time=1.255..1.255 rows=1849 loops=1)
               Index Cond: ('01030...'::geometry && way)
   ->  Index Scan using osm_line_index on osm_line b
(cost=0.00..15.94 rows=3 width=1083) (actual time=0.036..0.420 rows=28
loops=1702)
         Index Cond: (a.way && b.way)
 Total runtime: 26897.124 ms
(10 rows)


Query 2:

gisdb=> EXPLAIN ANALYZE
gisdb-> SELECT a.name
gisdb-> FROM osm_line a, osm_line b
gisdb-> WHERE ST_Contains(ST_Transform(ST_SetSRID('BOX(8.775 47.245,
8.873 47.208)'::box2d, 4326),900913),a.way)
gisdb-> AND ST_Contains(ST_Transform(ST_SetSRID('BOX(8.775 47.245,
8.873 47.208)'::box2d, 4326),900913),b.way)
gisdb-> AND a.osm_id != b.osm_id
gisdb-> AND ST_Crosses(a.way, b.way);

QUERY PLAN
----------
 Nested Loop  (cost=60.59..10353.28 rows=1 width=14) (actual
time=19.925..3088.990 rows=586 loops=1)
   Join Filter: ((a.osm_id <> b.osm_id) AND _st_crosses(a.way, b.way))
   ->  Bitmap Heap Scan on osm_line a  (cost=60.59..5708.36 rows=535
width=1097) (actual time=1.432..12.943 rows=1702 loops=1)
         Recheck Cond: ('01030...'::geometry && way)
         Filter: _st_contains('01030...'::geometry, way)
         ->  Bitmap Index Scan on osm_line_index  (cost=0.00..60.46
rows=1606 width=0) (actual time=1.285..1.285 rows=1849 loops=1)
               Index Cond: ('01030...'::geometry && way)
   ->  Index Scan using osm_line_index on osm_line b  (cost=0.00..8.42
rows=1 width=1083) (actual time=0.464..1.069 rows=12 loops=1702)
         Index Cond: (('01030...'::geometry && b.way)
AND (a.way && b.way))
         Filter: _st_contains('01030...'::geometry, b.way)
 Total runtime: 3089.228 ms
(11 rows)

gisdb=>