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
