PostGIS 3.5.2 ST_Contains() query on gist is slow

[Sebastiaan Couwenberg]

On 9/9/25 4:14 PM, Daryl Herzmann wrote:
> So I guess I
> am at least curious what happens when you run this CTE example.

That's fast too:


osm-nl=# EXPLAIN (ANALYZE, BUFFERS) WITH mycte AS (SELECT wn.way_id, n.geom FROM way_nodes AS wn, nodes AS n WHERE wn.node_id = 8646506472 AND wn.node_id = n.id OFFSET 0)
SELECT way_id FROM mycte WHERE 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))'), geom);
                                                                                                                     QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Subquery Scan on mycte  (cost=1.14..29.69 rows=1 width=8) (actual time=0.088..0.089 rows=0 loops=1)
    Filter: st_contains('0103000020E61000000100000005000000D5A35F11B27A184013A1116C5C4E4A40D5A35F11B27A18402A2F432D179A4A40D529EA27F75E1C402A2F432D179A4A40D529EA27F75E1C4013A1116C5C4E4A40D5A35F11B27A184013A1116C5C4E4A40'::geometry, mycte.geom)
    Rows Removed by Filter: 1
    Buffers: shared hit=1 read=9
    ->  Nested Loop  (cost=1.14..17.18 rows=1 width=40) (actual time=0.082..0.084 rows=1 loops=1)
          Buffers: shared hit=1 read=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.039..0.039 rows=1 loops=1)
                Index Cond: (node_id = '8646506472'::bigint)
                Buffers: shared hit=1 read=4
          ->  Index Scan using pk_nodes on nodes n  (cost=0.57..8.59 rows=1 width=40) (actual time=0.040..0.041 rows=1 loops=1)
                Index Cond: (id = '8646506472'::bigint)
                Buffers: shared read=5
  Planning:
    Buffers: shared hit=235 read=19 dirtied=2
  Planning Time: 14.415 ms
  Execution Time: 0.130 ms
(16 rows)


Kind Regards,

Bas

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