[PostGIS] #5984: PostGIS selectivity is screwing up queries and forcing it to choose a spatial index when it's inappropriate

[PostGIS]

#5984: PostGIS selectivity is screwing up queries and forcing it to choose a
spatial index when it's inappropriate
----------------------+---------------------------
 Reporter:  robe      |      Owner:  pramsey
     Type:  defect    |     Status:  new
 Priority:  critical  |  Milestone:  PostGIS 3.4.5
Component:  postgis   |    Version:  3.5.x
 Keywords:            |
----------------------+---------------------------
 Issue discussed in:

 https://lists.osgeo.org/pipermail/postgis-users/2025-September/046864.html

 Paul suggested reverting the operator introduced in [31bcb7d414/git]

 ```
 ALTER OPERATOR @ (geometry , geometry)
     SET ( RESTRICT = contsel,
     JOIN = contjoinsel )
 ;
 ```

 and that does indeed return to the old 3.3 behavior for this query:

 ```
 EXPLAIN (ANALYZE, BUFFERS) SELECT wn.way_id FROM way_nodes AS wn, nodes AS
 n WHERE wn.node_id = 8646506472 AND wn.node_id = n.id AND
 ST_Contains(ST_GeomFromEWKT('SRID=4326;POLYGON((6.1198199
 52.612195500000006,6.1198199 53.2038323,7.0927397 53.2038323,7.0927397
 52.612195500000006,6.1198199 52.612195500000006))'), n.geom);
 QUERY PLAN
 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   Nested Loop  (cost=0.99..29.54 rows=1 width=8) (actual
 time=5648.531..5648.532 rows=0 loops=1)
     Buffers: shared hit=6744524 read=845409 written=5
     ->  Index Scan using idx_way_nodes_node_id on way_nodes wn
 (cost=0.57..8.59 rows=1 width=16) (actual time=0.021..0.022 rows=1
 loops=1)
           Index Cond: (node_id = '8646506472'::bigint)
           Buffers: shared read=5
     ->  Index Scan using idx_nodes_geom on nodes n  (cost=0.42..20.94
 rows=1 width=8) (actual time=5648.507..5648.507 rows=0 loops=1)
           Index Cond: (geom @
 '0103000020E61000000100000005000000D5A35F11B27A184013A1116C5C4E4A40D5A35F11B27A18402A2F432D179A4A40D529EA27F75E1C402A2F432D179A4A40D529EA27F75E1C4013A1116C5C4E4A40D5A35F11B27A184013A1116C5C4E4A40'::geometry)
           Filter: ((id = '8646506472'::bigint) AND
 st_contains('0103000020E61000000100000005000000D5A35F11B27A184013A1116C5C4E4A40D5A35F11B27A18402A2F432D179A4A40D529EA27F75E1C402A2F432D179A4A40D529EA27F75E1C4013A1116C5C4E4A40D5A35F11B27A184013A1116C5C4E4A40'::geometry,
 geom))
           Rows Removed by Filter: 9886042
           Buffers: shared hit=6744524 read=845404 written=5
   Planning:
     Buffers: shared hit=11 read=15
   Planning Time: 0.294 ms
   Execution Time: 5648.551 ms
 (14 rows)

 ```

 Note the polygon in question covers a good chunk of the table as is
 somewhat obvious from the 9886042 records that needed to be used by a
 filter,

 compared to same query on a 3.3 instance which doesn't use the spatial
 index at all and thus has only one row to remove using Filter.
-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/5984>
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.