[postgis-tickets] [PostGIS] #4139: Unstable behavior of gist nd indexes

[PostGIS]

#4139: Unstable behavior of gist nd indexes
-----------------------+---------------------------
  Reporter:  ezimanyi  |      Owner:  pramsey
      Type:  defect    |     Status:  new
  Priority:  medium    |  Milestone:  PostGIS 2.4.5
 Component:  postgis   |    Version:  2.4.x
Resolution:            |   Keywords:
-----------------------+---------------------------

Comment (by ezimanyi):

 I have made available the SQL scripts for generating the tables.

 The problem does not appear when creating the table tbl_geommultipoint by
 setting limit to 10 or 20. It started to appear when limit is set to 30

 {{{
 drop table if exists tbl_geomcollection_nd;
 create table tbl_geomcollection_nd (
         k serial,
         g geometry
 );
 insert into tbl_geomcollection_nd (g)
 (select g from tbl_geompoint limit 30) union
 (select g from tbl_geompointz limit 30) union
 (select g from tbl_geompointm limit 30) union
 (select g from tbl_geompointzm limit 30) union
 (select g from tbl_geomlinestring limit 30) union
 (select g from tbl_geomlinestringz limit 30) union
 (select g from tbl_geomlinestringm limit 30) union
 (select g from tbl_geomlinestringzm limit 30) union
 (select g from tbl_geompolygon limit 30) union
 (select g from tbl_geompolygonz limit 30) union
 (select g from tbl_geompolygonm limit 30) union
 (select g from tbl_geompolygonzm limit 30) union
 (select g from tbl_geommultipoint limit 30) union
 (select g from tbl_geommultipointz limit 30) union
 (select g from tbl_geommultipointm limit 30) union
 (select g from tbl_geommultipointzm limit 30) union
 (select g from tbl_geommultilinestring limit 30) union
 (select g from tbl_geommultilinestringz limit 30) union
 (select g from tbl_geommultilinestringm limit 30) union
 (select g from tbl_geommultilinestringzm limit 30) union
 (select g from tbl_geommultipolygon limit 30) union
 (select g from tbl_geommultipolygonz limit 30) union
 (select g from tbl_geommultipolygonm limit 30) union
 (select g from tbl_geommultipolygonzm limit 30) ;

 select count(*) from tbl_geomcollection_nd
 -- 720

 set enable_indexscan = off;
 set enable_bitmapscan = off;
 set enable_seqscan = on;

 select count(*) from tbl_geomcollection_nd t1, tbl_geomcollection_nd t2
 where t1.g &&& t2.g
 -- 396140

 drop index if exists tbl_geomcollection_nd_gist_nd_idx;
 create index tbl_geomcollection_nd_gist_nd_idx on tbl_geomcollection_nd
 using gist(g gist_geometry_ops_nd);

 set enable_indexscan = on;
 set enable_bitmapscan = off;
 set enable_seqscan = off;

 select count(*) from tbl_geomcollection_nd t1, tbl_geomcollection_nd t2
 where t1.g &&& t2.g
 -- 396111
 -- 396140
 -- 396119
 -- 396072
 -- 396129
 }}}

-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/4139#comment:2>
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.