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

 Replying to [comment:3 komzpa]:
 > Presence of BitmapIndexScan in PostGIS queries is also usually a sign of
 work_mem being set too low.

 This is not at all the case here:

 {{{
                Sort Method: quicksort  Memory: 25kB
 }}}

 The reason there's a !BitmapIndexScan here is because there are two
 indexes involved (one is a Btree; the other is the geometry column in
 question), and they're being !BitmapAnd'ed.

 > Please also figure out what storage your table uses, main or extended.
 If it's extended it's not supposed to be affected by this change. If it's
 main, interesting who and why changed it from default :)

 Storage is per column, not per table, and it's "main" for all the geometry
 columns... which matches what Ramsey mentioned in the code comment he
 added in the commit I cited.

 > As a temporary solution, ND index still has the detoast-header code, so
 you can try to rebuild with ND opclass.

 Sounds like it'd be slower than the 2D 2.3 code, in which case it's not a
 great solution going forward.

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