[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
Resolution:            |   Keywords:
-----------------------+---------------------------
Description changed by strk:

Old description:

> 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.

New description:

 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#comment:12>
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.