[postgis-tickets] [PostGIS] #3753: Gist penalty misbehaves if points are inserted in gridded ordered fashion

[PostGIS]

#3753: Gist penalty misbehaves if points are inserted in gridded ordered fashion
----------------------+---------------------------
  Reporter:  komzpa   |      Owner:  robe
      Type:  defect   |     Status:  new
  Priority:  medium   |  Milestone:  PostGIS 2.4.0
 Component:  postgis  |    Version:  2.3.x
Resolution:           |   Keywords:
----------------------+---------------------------

Comment (by x4m):

 Hi!

 I've done the way of installing PostGIS :)
 My test machine:
 Ubuntu 16.04 under Hyper-V
 PostgreSQL 10devel at HEAD around Feb 2017
 I've been testing PostGIS rev 15391 and 15392 (before and after).
 I confirm that previous case test do not have statistically significant
 difference.
 One more important observation is that performance is slightly increased
 if we remove lines

 {{{
                 if (size_orig > 0)
                 {
                         *result = pack_float(size_orig, 1); /* REALM 1 */
                 }
                 else
 }}}

 This test with less general case

 {{{
 create extension if not exists postgis;

 create table dataTable as select ST_MakePoint(x/1000,y/1000, 1) c from
 generate_series(1,1e3,1)x, generate_series(1,1e3,1) y;

 \timing on
 create index idx on dataTable using gist(c gist_geometry_ops_nd);

 begin transaction;
 SELECT setseed(.43);
 explain analyze select q,(select count(*) from dataTable dt where dt.c &&&
 q) from (select ST_Expand(ST_MakePoint(x,y,z), 0.1) q from (select
 random() x, random() y , 0.95 z from generate_series(1,1e3,1) s0) s1) s2 ;
 commit transaction;

 select pg_size_pretty(pg_relation_size('idx'));
 \set timing off
 drop table datatable;
 }}}

 sees performance improvement.
 Before patch selection takes 300 seconds (on my machine)
 after patch selection takes 37 seconds.

 In this test we have 2D data in 3D. This scenario is quite rare for 2d
 version: all points have to be on the same grid aligned line.

 I think in cube I was observing performance improvement attributed to data
 features, which are already exploited here by Korotkov split. So this
 patch only fixes degenerate cases of 2D data in ND.

 In March 2017 on PgConf.Russia @Komzpa showed me performance problems with
 data inserted into GiST in-order by stips. This patch should have fixed
 that, but, actually, as for now, I do not observe that performance
 problems in reproducible environment.

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