[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
 Keywords:            |
----------------------+---------------------------
 Github-mirror commit 8b21fa7d56ea851628a80cf2dd29ca565344c758 (SVN 15778)
 caused a severe performance degradation when the ~ operator is applied to
 the result of a !BitmapIndexScan.  The reason is that full detoasting is
 now applied to every tuple coming out of the index, as opposed to only the
 first BOX2DF-sized slice as the previous code did.

 This doesn't affect tuples coming out of heap (table), because as the new
 comment correctly states, the whole datum has to be fetched anyway; but
 apparently indexes don't support compressed toast so the slice mechanism
 worked just fine for those.

 This causes a serious performance degradation of a real-world use case --
 query goes from 188ms to 3658ms.

 I don't yet have a patch or a readily publishable reproducer to share, but
 I can look into it if necessary.

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