[PostGIS] #5959: Query planner vastly underestimates number of rows matched by GIST index

[PostGIS]

#5959: Query planner vastly underestimates number of rows matched by GIST index
---------------------+---------------------------
 Reporter:  alexobs  |      Owner:  pramsey
     Type:  defect   |     Status:  new
 Priority:  medium   |  Milestone:  PostGIS 3.5.4
Component:  postgis  |    Version:  3.5.x
 Keywords:           |
---------------------+---------------------------
 We have a table with ~140M rows, with a geometry column containing point
 data.
 That column has a GIST index: `"wrn_geo_point_idx" gist (geo_point)`.

 When performing an ST_CONTAINS query on that column, the query planner
 always estimates that only a single row matches the WHERE statement.
 In reality there might be several million rows matching the WHERE.
 This causes the planner to favor the wrn_geo_point_idx index when a better
 strategy would be to use a different index, such as the primary key index.

 Example query:
 {{{
 SELECT id FROM test WHERE id = 123 AND ST_Contains(ST_GeomFromEWKB('...'),
 geo_point)
 }}}

 Relevant part of EXPLAIN on such a query:
 {{{
  ->  Index Scan using wrn_geo_point_idx on waarneming  (cost=0.55..15.27
 rows=1 width=924) (actual time=4748.831..9128.025 rows=1 loops=1)
        Index Cond: (geo_point @ '...'::geometry)
        Filter: (id = 363975318) AND st_contains('...'::geometry,
 geo_point))
        Rows Removed by Filter: 3972650
 }}}

 So far, I was unable to replicate this behaviour with a test table.
 I'll update the issue when I find a way to do that.

 These are the software versions I'm using:
  * Ubuntu 22.04.5 LTS
  * postgresql-15 15.13-1.pgdg22.04+1
  * postgresql-15-postgis-3 3.5.3+dfsg-1~exp1.pgdg22.04+1

 Every 3.5.x version seemed to be affected, so our workaround was to
 downgrade to 3.4.3.

 We could only reproduce the problem on the amd64 architecture, not on arm.

 If you need more info, please let me know.
-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/5959>
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.