PostGIS 3.5.2 ST_Contains() query on gist is slow

[Sebastiaan Couwenberg]

On 9/9/25 7:31 AM, Regina Obe wrote:
>> On 9/9/25 6:27 AM, Sebastiaan Couwenberg via postgis-users wrote:
>> I wasn't aware of the @ operator, looking at the documentation shouldn't the
>> equivalent of ST_Contains() be ~:
> 
> @ and ~ are commutators of each other, but neither is equivalent to ST_Contains as they are both bounding box operators.
> I think we always use @ for the short-hand of index check and I was trying to match that
> I think I had my logic backwards, so I would expect the below to be slow as well if your ~ is slow.
> 
> SELECT wn.way_id FROM
> way_nodes
> AS wn, nodes AS n WHERE wn.node_id = 8646506472 AND wn.node_id =
>   n.id
> AND  n.geom @ 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))');

That is also slow indeed:

                                                                                                                  QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Nested Loop  (cost=0.99..17.04 rows=1 width=8) (actual time=4845.847..4845.849 rows=0 loops=1)
    Buffers: shared hit=6876283 read=713674
    ->  Index Scan using idx_way_nodes_node_id on way_nodes wn  (cost=0.57..8.59 rows=1 width=16) (actual time=0.046..0.048 rows=1 loops=1)
          Index Cond: (node_id = '8646506472'::bigint)
          Buffers: shared hit=1 read=4
    ->  Index Scan using idx_nodes_geom on nodes n  (cost=0.42..8.44 rows=1 width=8) (actual time=4845.797..4845.797 rows=0 loops=1)
          Index Cond: (geom @ '0103000020E61000000100000005000000D5A35F11B27A184013A1116C5C4E4A40D5A35F11B27A18402A2F432D179A4A40D529EA27F75E1C402A2F432D179A4A40D529EA27F75E1C4013A1116C5C4E4A40D5A35F11B27A184013A1116C5C4E4A40'::geometry)
          Filter: (id = '8646506472'::bigint)
          Rows Removed by Filter: 9886042
          Buffers: shared hit=6876282 read=713670
  Planning:
    Buffers: shared hit=146 read=15
  Planning Time: 13.788 ms
  Execution Time: 4845.945 ms
(14 rows)

> Have you done a
> 
> vacuum analyze way_nodes;
> vacuum analyze nodes;

Yes, Osmosis does this.

Running those queries manually again does not improve the performance.

> Often in these kind of cases when one side is always filtering by an id I use a btree_gist index.
> 
> What happens if you do:
> 
> CREATE EXTENSION btree_gist;
> 
> CREATE INDEX ix_id_nodes_geom ON nodes USING gist(id, geom);
> 
> As I am guessing you are always matching by node_id and you'd want to force that match first before filtering by geometry containment which would always be slower.

The query in question is executed on all the nodes that are part of boundary ways to find (non-boundary) ways that are connected to those boundaries.

Most of these queries will get 0 results because most of those nodes are not connected to other ways.

The btree_gist index does not significantly speedup the query, because it doesn't get used:

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_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=5046.293..5046.294 rows=0 loops=1)
    Buffers: shared hit=6876048 read=713885 written=5708
    ->  Index Scan using idx_way_nodes_node_id on way_nodes wn  (cost=0.57..8.59 rows=1 width=16) (actual time=0.220..0.223 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=5046.068..5046.068 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=6876048 read=713880 written=5708
  Planning:
    Buffers: shared hit=54 read=3 dirtied=1
  Planning Time: 0.418 ms
  Execution Time: 5046.317 ms
(14 rows)

Kind Regards,

Bas

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