[postgis-tickets] [PostGIS] #4216: Performance degradation in method `contains`

[PostGIS]

#4216: Performance degradation in method `contains`
-----------------------+---------------------------
  Reporter:  alvherre  |      Owner:  pramsey
      Type:  defect    |     Status:  new
  Priority:  medium    |  Milestone:  PostGIS 2.4.6
 Component:  postgis   |    Version:  2.4.x
Resolution:            |   Keywords:
-----------------------+---------------------------

Comment (by alvherre):

 Hi, thanks for your quick response.

 > Can you share more details on the query?

 Sadly, I cannot share the original query.  I'll try to create a
 reproducer, but I don't have sample GIS data yet, so it'll take some time.
 I'll send more details on the shape of the query and plan in my next
 comment, to be written immediately after sending this one.  The table is
 about 700 GB.

 > GiST index doesn't even store geometry and just keeps the box.

 Oh, that makes sense in retrospect.

 > How did you find the offending commit?  Can it happen that there is
 another reason?

 Well, we can see that there's a much higher buffer hit count in EXPLAIN
 ANALYZE and no explanation for that in the Postgres code; looking (as a
 newcomer to PostGIS) at the code implementing the ~ operator it seems that
 this is the only possibility.

 This is an application running on 2.2; degradation was found upgrading to
 2.4; downgrading to 2.3 restores performance.  I have not yet tried to
 measure performance with only that commit reverted, but I'll do that too
 when I have a reproducer.

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