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

 I ran this on my windows 64-bit dev
 that has

 {{{
 POSTGIS="2.1.3 r12547" GEOS="3.5.0dev-CAPI-1.9.0 r3989" PROJ="Rel. 4.8.0,
 6 March 2012" GDAL="GDAL 1.11.0, released 2014/04/16" LIBXML="2.7.8"
 LIBJSON="UNKNOWN" RASTER PostgreSQL 9.3.3, compiled by Visual C++ build
 1600, 64-bit
 }}}



 {{{

 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);
 }}}

 and it behaved as expected


 {{{
 Nested Loop  (cost=0.14..585.98 rows=328 width=8)
   ->  Seq Scan on test_poly a  (cost=0.00..22.30 rows=1230 width=36)
   ->  Index Scan using ix__temp_line_line on _temp_line s
 (cost=0.14..0.45 rows=1 width=36)
         Index Cond: (a.border && line)
         Filter: (_st_distance(a.border, line, 0::double precision, false)
 < 1e-005::double precision)

 }}}

 I have to upgrade my dev to see if it's an issue with newer PostGIS and
 PostgreSQL but at least as far as I can tell the issue does not show on
 older.

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