PostGIS 3.5.2 ST_Contains() query on gist is slow

[Sebastiaan Couwenberg]

On 9/9/25 6:27 AM, Sebastiaan Couwenberg via postgis-users wrote:
> On 9/9/25 6:04 AM, Regina Obe wrote:
>> To take GEOS out of the equation, what is performance if you just do below compared to your old performance
> 
> That is significantly faster:
> 
> osm-nl=# 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_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..17.04 rows=1 width=8) (actual time=0.066..0.066 rows=0 loops=1)
>     Buffers: shared hit=9
>     ->  Index Scan using idx_way_nodes_node_id on way_nodes wn  (cost=0.57..8.59 rows=1 width=16) (actual time=0.024..0.025 rows=1 loops=1)
>           Index Cond: (node_id = '8646506472'::bigint)
>           Buffers: shared hit=5
>     ->  Index Scan using idx_nodes_geom on nodes n  (cost=0.42..8.44 rows=1 width=8) (actual time=0.040..0.040 rows=0 loops=1)
>           Index Cond: (geom ~ '0103000020E61000000100000005000000D5A35F11B27A184013A1116C5C4E4A40D5A35F11B27A18402A2F432D179A4A40D529EA27F75E1C402A2F432D179A4A40D529EA27F75E1C4013A1116C5C4E4A40D5A35F11B27A184013A1116C5C4E4A40'::geometry)
>           Filter: (id = '8646506472'::bigint)
>           Buffers: shared hit=4
>   Planning Time: 0.174 ms
>   Execution Time: 0.087 ms
> (11 rows)

I wasn't aware of the @ operator, looking at the documentation shouldn't the equivalent of ST_Contains() be ~:

  ST_Contains:

   Returns TRUE if geometry A contains geometry B. A contains B if and only if all points of B lie inside (i.e. in the interior or boundary of) A (or equivalently, no points of B lie in the exterior of A), and the interiors of A and B have at least one point in common.

  @:

   The @ operator returns TRUE if the bounding box of geometry A is completely contained by the bounding box of geometry B.

  ~:

   The ~ operator returns TRUE if the bounding box of geometry A completely contains the bounding box of geometry B.


Using ~ is also slow:

                                                                                                                  QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Nested Loop  (cost=0.99..17.04 rows=1 width=8) (actual time=5201.289..5201.290 rows=0 loops=1)
    Buffers: shared hit=6790768 read=799189
    ->  Index Scan using idx_way_nodes_node_id on way_nodes wn  (cost=0.57..8.59 rows=1 width=16) (actual time=0.031..0.034 rows=1 loops=1)
          Index Cond: (node_id = '8646506472'::bigint)
          Buffers: shared hit=3 read=2
    ->  Index Scan using idx_nodes_geom on nodes n  (cost=0.42..8.44 rows=1 width=8) (actual time=5201.254..5201.254 rows=0 loops=1)
          Index Cond: (geom @ '0103000020E61000000100000005000000D5A35F11B27A184013A1116C5C4E4A40D5A35F11B27A18402A2F432D179A4A40D529EA27F75E1C402A2F432D179A4A40D529EA27F75E1C4013A1116C5C4E4A40D5A35F11B27A184013A1116C5C4E4A40'::geometry)
          Filter: (id = '8646506472'::bigint)
          Rows Removed by Filter: 9886042
          Buffers: shared hit=6790765 read=799187
  Planning:
    Buffers: shared hit=109 read=55
  Planning Time: 12.481 ms
  Execution Time: 5201.392 ms
(14 rows)

Kind Regards,

Bas

-- 
  GPG Key ID: 4096R/6750F10AE88D4AF1
Fingerprint: 8182 DE41 7056 408D 6146  50D1 6750 F10A E88D 4AF1