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

 I put this note on pull request as well.  I'll keep this open, since we
 may need to roll the change back:

 This trick doesn't seem useful based on my cursory tests. The index
 building took longer, size of index is the same, and no gain in
 performance.

 I tested PostGIS 2.3.2 on 9.6 64-bit window 7 and got these timings using
 @Komzpa tests and then repeated using PostGIS 2.4.0dev that has this
 patch. The index size are the same and don't see much of a difference in
 speed, but 2.4 seems slightly worse. Hard to tell absolutely with random
 data and I won't rule out cause is other changes in 2.4 code base, though
 I don't think we've touched the gist section except for this.



 {{{
 Time 1: index build
 Time 2: query
 Time 3: index size check
 Time 4: drop table

 PostGIS 2.3 test results:

 First run:

 Time: 14299.226 ms
 Time: 63453.484 ms
 Time: 12.544 ms
 Time: 4.672 ms

 Second run:

 Time: 12983.263 ms
 Time: 62485.045 ms
 Time: 0.454 ms
 Time: 7.881 ms

 Third run:

 Time: 13205.724 ms
 Time: 63193.318 ms
 Time: 3.722 ms
 Time: 8.986 ms
 Time: 62.018 ms

 `

 PostGIS 2.4 results:


 First run:

 Time: 18914.431 ms
 Time: 64785.811 ms
 Time: 7.872 ms
 Time: 7.698 ms

 Second run:

 Time: 14776.264 ms
 Time: 64393.529 ms
 Time: 7.124 ms
 Time: 6.232 ms

 Third run:

 Time: 14909.494 ms
 Time: 64252.608 ms
 Time: 11.676 ms
 Time: 6.020 ms
 }}}




 @Komzpa did you get a chance to test this?

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