explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ntYL

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 603.579 603.579 ↓ 3.4 405 1

CTE Scan on get_points (cost=54,344.89..54,347.25 rows=118 width=200) (actual time=488.432..603.579 rows=405 loops=1)

2.          

CTE selected_points

3. 12.871 76.263 ↓ 1.9 448 1

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

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

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

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

CTE filtered_points

6. 76.720 76.720 ↓ 3.4 405 1

CTE Scan on selected_points points_1 (cost=0.00..4.74 rows=118 width=959) (actual time=1.726..76.720 rows=405 loops=1)

  • Filter: ((offset_geom IS NOT NULL) AND (is_duplicate IS FALSE))
  • Rows Removed by Filter: 43
7.          

CTE points_with_objects

8. 0.808 76.635 ↓ 3.4 405 1

HashAggregate (cost=29.49..31.56 rows=118 width=548) (actual time=76.393..76.635 rows=405 loops=1)

  • Group Key: points_2.id
9. 0.244 75.827 ↓ 1.1 405 1

Hash Right Join (cost=3.83..26.61 rows=384 width=548) (actual time=75.653..75.827 rows=405 loops=1)

  • Hash Cond: (detected_objects.point_id = (points_2.id)::text)
10. 0.020 0.020 ↓ 0.0 0 1

Seq Scan on detected_objects (cost=0.00..16.50 rows=650 width=64) (actual time=0.020..0.020 rows=0 loops=1)

11. 0.267 75.563 ↓ 3.4 405 1

Hash (cost=2.36..2.36 rows=118 width=516) (actual time=75.563..75.563 rows=405 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 36kB
12. 75.296 75.296 ↓ 3.4 405 1

CTE Scan on filtered_points points_2 (cost=0.00..2.36 rows=118 width=516) (actual time=0.002..75.296 rows=405 loops=1)

13.          

CTE points_with_planet_data

14. 201.549 408.674 ↓ 3.4 405 1

Hash Right Join (cost=3.83..39,921.15 rows=118 width=524) (actual time=50.495..408.674 rows=405 loops=1)

  • Hash Cond: (planet.id = points_3.segment_id)
15. 206.661 206.661 ↑ 1.0 1,076,301 1

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

16. 0.114 0.464 ↓ 3.4 405 1

Hash (cost=2.36..2.36 rows=118 width=524) (actual time=0.464..0.464 rows=405 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 40kB
17. 0.350 0.350 ↓ 3.4 405 1

CTE Scan on filtered_points points_3 (cost=0.00..2.36 rows=118 width=524) (actual time=0.003..0.350 rows=405 loops=1)

18.          

CTE get_points

19. 114.069 602.942 ↓ 3.4 405 1

Hash Left Join (cost=7.67..313.00 rows=118 width=200) (actual time=488.425..602.942 rows=405 loops=1)

  • Hash Cond: ((points_4.id)::text = (planet_1.point_id)::text)
20. 0.723 79.691 ↓ 3.4 405 1

Hash Left Join (cost=3.83..7.82 rows=118 width=690) (actual time=78.794..79.691 rows=405 loops=1)

  • Hash Cond: ((points_4.id)::text = (points_with_objects.point_id)::text)
21. 1.935 1.935 ↓ 3.4 405 1

CTE Scan on filtered_points points_4 (cost=0.00..2.36 rows=118 width=658) (actual time=1.727..1.935 rows=405 loops=1)

22. 0.160 77.033 ↓ 3.4 405 1

Hash (cost=2.36..2.36 rows=118 width=548) (actual time=77.033..77.033 rows=405 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 41kB
23. 76.873 76.873 ↓ 3.4 405 1

CTE Scan on points_with_objects (cost=0.00..2.36 rows=118 width=548) (actual time=76.400..76.873 rows=405 loops=1)

24. 0.220 409.182 ↓ 3.4 405 1

Hash (cost=2.36..2.36 rows=118 width=524) (actual time=409.181..409.182 rows=405 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 39kB
25. 408.962 408.962 ↓ 3.4 405 1

CTE Scan on points_with_planet_data planet_1 (cost=0.00..2.36 rows=118 width=524) (actual time=50.501..408.962 rows=405 loops=1)

Planning time : 0.750 ms
Execution time : 604.322 ms