explain.depesz.com

PostgreSQL's explain analyze made readable

Result: E0g : Optimization for: get osm_id per point; plan #pn6i

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.203 508.573 ↑ 1,423.5 448 1

Hash Join (cost=58,784.19..93,414.15 rows=637,708 width=524) (actual time=130.950..508.573 rows=448 loops=1)

  • Hash Cond: (existing_segments.id = points.segment_id)
2.          

CTE selected_points

3. 10.628 88.018 ↓ 1.9 448 1

Gather (cost=1,000.00..14,074.44 rows=237 width=361) (actual time=5.367..88.018 rows=448 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
4. 77.390 77.390 ↓ 1.5 149 3

Parallel Seq Scan on points points_1 (cost=0.00..13,050.74 rows=99 width=361) (actual time=3.923..77.390 rows=149 loops=3)

  • Filter: ('01030000000100000005000000F853E3A59BC4284079E9263108AC4940F853E3A59BC42840B29DEFA7C6AB4940DD24068195C32840B29DEFA7C6AB4940DD24068195C3284079E9263108AC4940F853E3A59BC4284079E9263108AC4940'::geometry ~ offset_geom)
  • Rows Removed by Filter: 79002
5.          

CTE existing_segments

6. 199.152 508.036 ↑ 5,174.5 104 1

Hash Join (cost=9.83..44,702.05 rows=538,150 width=16) (actual time=130.677..508.036 rows=104 loops=1)

  • Hash Cond: (planet_osm_line_segments.id = selected_points.segment_id)
7. 220.100 220.100 ↑ 1.0 1,076,301 1

Seq Scan on planet_osm_line_segments (cost=0.00..35,880.01 rows=1,076,301 width=16) (actual time=0.011..220.100 rows=1,076,301 loops=1)

8. 0.070 88.784 ↑ 1.9 104 1

Hash (cost=7.33..7.33 rows=200 width=8) (actual time=88.784..88.784 rows=104 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
9. 0.405 88.714 ↑ 1.9 104 1

HashAggregate (cost=5.33..7.33 rows=200 width=8) (actual time=88.667..88.714 rows=104 loops=1)

  • Group Key: selected_points.segment_id
10. 88.309 88.309 ↓ 1.9 448 1

CTE Scan on selected_points (cost=0.00..4.74 rows=237 width=8) (actual time=5.370..88.309 rows=448 loops=1)

11. 508.121 508.121 ↑ 5,174.5 104 1

CTE Scan on existing_segments (cost=0.00..10,763.00 rows=538,150 width=16) (actual time=130.682..508.121 rows=104 loops=1)

12. 0.129 0.249 ↓ 1.9 448 1

Hash (cost=4.74..4.74 rows=237 width=524) (actual time=0.249..0.249 rows=448 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 43kB
13. 0.120 0.120 ↓ 1.9 448 1

CTE Scan on selected_points points (cost=0.00..4.74 rows=237 width=524) (actual time=0.002..0.120 rows=448 loops=1)

Planning time : 0.326 ms
Execution time : 509.008 ms