[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
 Keywords:            |
----------------------+---------------------------
 I have created a table containing random geometries of mixed dimensions

 {{{
 select count(*) from tbl_geomcollection_nd
 -- 2400

 select distinct substring(st_astext(g) for position('(' in st_astext(g)) -
 1) from tbl_geomcollection_nd order by 1

 "LINESTRING"
 "LINESTRING M "
 "LINESTRING Z "
 "LINESTRING ZM "
 "MULTILINESTRING"
 "MULTILINESTRING M "
 "MULTILINESTRING Z "
 "MULTILINESTRING ZM "
 "MULTIPOINT"
 "MULTIPOINT M "
 "MULTIPOINT Z "
 "MULTIPOINT ZM "
 "MULTIPOLYGON"
 "MULTIPOLYGON M "
 "MULTIPOLYGON Z "
 "MULTIPOLYGON ZM "
 "POINT"
 "POINT M "
 "POINT Z "
 "POINT ZM "
 "POLYGON"
 "POLYGON M "
 "POLYGON Z "
 "POLYGON ZM "
 }}}

 Then I create an n-dimensional index with the {{{gist_geometry_ops_nd}}}
 operator class

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


 However, the index shows an unstable behavior obtaining different number
 of answers each time I drop/create a new index.

 {{{
 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
 -- 4470359

 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
 -- 4470258
 -- 4470594
 -- 4470658
 -- 4470303
 -- 4469945
 }}}

 The 5 numbers above are five different answers to the same query after
 droping/creating the index as done in the two lines above.

 I can make available the file containing the test data I used if needed.

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