[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):

 Thanks, I tested the 2.1 branch (r13295), now using PostgreSQL 9.2.6 on
 Linux x86. I can confirm that the picksplit message is gone, but the index
 is still not used. I got it down to a minimal repro. The tables are very
 simple:
 {{{
 CREATE TABLE test_poly
 (
   ts_id integer,
   border geography
 );

 CREATE TABLE test_line
 (
   segment_id integer,
   timerange tsrange,
   line geography,
   segment_length integer
 );
 }}}

 Query:
 {{{
 BEGIN; -- Problem doesn't happen outside of a transaction

 DROP TABLE IF EXISTS _temp_line;

 CREATE TEMP TABLE _temp_line AS
 SELECT * FROM test_line;

 -- Problem doesn't happen without this UPDATE.
 -- Also doesn't happen without the seemingly-irrelevant timerange column.
 UPDATE _temp_line SET segment_length = ST_Length(line);

 CREATE INDEX ix__temp_line_line ON _temp_line USING GIST (line);

 -- ANALYZE _sia_segment; -- This doesn't help!

 EXPLAIN
 SELECT ts_id, segment_id
 FROM test_poly a
 JOIN _temp_line s ON ST_Intersects(a.border, s.line)

 -- ROLLBACK;
 }}}

 EXPLAIN output:
 {{{
 Nested Loop  (cost=0.00..893167397.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=0.00..1363.05 rows=14805 width=880)
   ->  Materialize  (cost=0.00..8264.72 rows=219515 width=36)
         ->  Seq Scan on _temp_line s  (cost=0.00..5559.15 rows=219515
 width=36)
 }}}

 But when running outside of a transaction or without the UPDATE statement
 or without the timerange column:
 {{{
 Nested Loop  (cost=0.00..116283.23 rows=1083307 width=8)
   ->  Seq Scan on test_poly a  (cost=0.00..1363.05 rows=14805 width=880)
   ->  Index Scan using ix__temp_line_line on _temp_line s
 (cost=0.00..7.69 rows=7 width=36)
         Index Cond: (a.border && line)
         Filter: (_st_distance(a.border, line, 0::double precision, false)
 < 1e-05::double precision)
 }}}

 If you want the data I can upload that somewhere and send you the link.
 It's ~10MB compressed.

-- 
Ticket URL: <http://trac.osgeo.org/postgis/ticket/3065#comment:3>
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.