[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:                 |  
---------------------------+------------------------------------------------
 POSTGIS="2.1.5 r13152" GEOS="3.4.2-CAPI-1.8.2 r3924" PROJ="Rel. 4.8.0, 6
 March 2012" GDAL="GDAL 1.11.1, released 2014/09/24" LIBXML="2.7.8"
 LIBJSON="UNKNOWN" RASTER

 PostgreSQL 9.3.6, compiled by Visual C++ build 1600, 64-bit

 Windows 7 SP1 x64

 I have a complex query that intersects linestrings with polygons. It first
 builds a temp table of the linestrings with a GIST index on the linestring
 column, then does the main intersection query, which normally takes ~20
 minutes. With a particular set of linestrings this GIST index is not used
 (as shown by the EXPLAIN output) and the main query takes nearly 3 hours.

 The temp table was created like this:
 {{{
 CREATE TEMP TABLE _sia_segment
 AS SELECT ST_MakeLine(start_pos::geometry, end_pos::geometry)::geography
 AS line
         , NULL::integer AS segment_length, ... other stuff ...
 FROM ...

 UPDATE _sia_segment
 SET segment_length = ST_Length(line);

 CREATE INDEX ix__sia_segment_line ON _sia_segment USING GIST (line);
 }}}
 Also, not sure if it's related, but the CREATE INDEX command outputs
 messages like this:
 {{{
 DEBUG:  picksplit method for column 1 of index "ix__sia_segment_line"
 failed
 HINT:  The index is not optimal. To optimize it, contact a developer, or
 try to use the column as the second one in the CREATE INDEX command.
 }}}
 If I run CREATE INDEX before the UPDATE then the UPDATE also outputs those
 messages.

 I found that if I move the ST_Length() into the original SELECT statement
 the problem goes away - the index is used. So my original problem is
 solved, but if anyone wants to investigate the PostGIS behaviour I can try
 to create a repro. It should be easy for the picksplit error, though not
 so easy for the original problem (index not used).

 Also, the problem only occurred when the CREATE TEMP TABLE and the main
 intersection query were done inside a transaction. Outside of a
 transaction the index was used just fine, despite the picksplit error. So
 the circumstances are quite particular, but reliably reproducible in my
 database.

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