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
