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

 For completeness note from Andrey Borodin on pull request:


 {{{
 @robe2 1.5x and 3x was an improvement with old Guttman split algorithm.
 Since PostGIS is using one of the most advanced split algorithms, that
 split algorithm may be fixing data issues being solved by this patch.
 If you can benchmark I'd recommend something like this

 create extension if not exists cube;

 begin transaction;
 SELECT setseed(.43);

 create table dataTable as select cube(array[x/1000,y/1000]) c from
 generate_series(1,1e3,1)x, generate_series(1,1e3,1) y;

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

 select q,(select count(*) from dataTable dt where dt.c<@q) from (select
 cube(array[x,y],array[x+0.1,y+0.1]) q from (select random() x,random() y
 from generate_series(1,1e4,1) s0) s1) s2 ;

 select pg_size_pretty(pg_relation_size('idx'));

 drop table datatable;

 Replace cube with appropriate PostGIS type. Here we create grid of 1M
 points and do 10K searches in this grid. If search time is not reduced
 significantly, may be this trick is not usefull.
 }}}

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