[postgis-tickets] [PostGIS] #3065: Spatial index on LINESTRING not used under some circumstances

[PostGIS]

#3065: Spatial index on LINESTRING not used under some circumstances
---------------------------+------------------------------------------------
 Reporter:  realityexists  |       Owner:  pramsey
     Type:  defect         |      Status:  new    
 Priority:  medium         |   Milestone:         
Component:  postgis        |     Version:  2.1.x  
 Keywords:                 |  
---------------------------+------------------------------------------------

Comment(by realityexists):

 I don't know what to make of the EXPLAIN ANALYZE output. Here it is. In a
 transaction:
 {{{
 Nested Loop  (cost=0.00..893167397.30 rows=1083307 width=8) (actual
 time=53.944..3005465.506 rows=821046 loops=1)
   Join Filter: ((a.border && s.line) AND (_st_distance(a.border, s.line,
 0::double precision, false) < 1e-05::double precision))
   Rows Removed by Join Filter: 1624338609
   ->  Seq Scan on test_poly a  (cost=0.00..1363.05 rows=14805 width=880)
 (actual time=0.065..66.477 rows=14805 loops=1)
   ->  Materialize  (cost=0.00..8264.72 rows=219515 width=36) (actual
 time=0.006..46.816 rows=109771 loops=14805)
         ->  Seq Scan on _temp_line s  (cost=0.00..5559.15 rows=219515
 width=36) (actual time=0.124..68.740 rows=109771 loops=1)
 Total runtime: 3005878.650 ms
 }}}

 Oustide of a transaction:
 {{{
 Nested Loop  (cost=0.00..116283.23 rows=1083307 width=8) (actual
 time=1.224..869916.660 rows=821046 loops=1)
   ->  Seq Scan on test_poly a  (cost=0.00..1363.05 rows=14805 width=880)
 (actual time=0.043..36.121 rows=14805 loops=1)
   ->  Index Scan using ix__temp_line_line on _temp_line s
 (cost=0.00..7.69 rows=7 width=36) (actual time=1.642..58.708 rows=55
 loops=14805)
         Index Cond: (a.border && line)
         Filter: (_st_distance(a.border, line, 0::double precision, false)
 < 1e-05::double precision)
         Rows Removed by Filter: 41
 Total runtime: 870215.643 ms
 }}}

 Even with enable_seqscan=off the index is not used. EXPLAIN output (in a
 transaction):
 {{{
 Nested Loop  (cost=20000000000.00..20893167397.30 rows=1083307 width=8)
   Join Filter: ((a.border && s.line) AND (_st_distance(a.border, s.line,
 0::double precision, false) < 1e-05::double precision))
   ->  Seq Scan on test_poly a  (cost=10000000000.00..10000001363.05
 rows=14805 width=880)
   ->  Materialize  (cost=10000000000.00..10000008264.73 rows=219515
 width=36)
         ->  Seq Scan on _temp_line s  (cost=10000000000.00..10000005559.15
 rows=219515 width=36)
 }}}

-- 
Ticket URL: <http://trac.osgeo.org/postgis/ticket/3065#comment:5>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.