PostGIS 3.5.2 ST_Contains() query on gist is slow

[Regina Obe]

> 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 ~
> '0103000020E61000000100000005000000D5A35F11B27A184013A1116C5C4
> E4A40D5A35F11B27A18402A2F432D179A4A40D529EA27F75E1C402A2F432D
> 179A4A40D529EA27F75E1C4013A1116C5C4E4A40D5A35F11B27A184013A11
> 16C5C4E4A40'::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 ~:

@ 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))');

Have you done a 

vacuum analyze way_nodes;
vacuum analyze nodes;



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.