explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

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

CTE Scan on get_points (cost=15,177.08..15,179.44 rows=118 width=224) (actual time=68.347..189.855 rows=405 loops=1)

2.          

CTE selected_points

3. 8.650 65.075 ↓ 1.9 448 1

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

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

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

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

CTE filtered_points

6. 66.477 66.477 ↓ 3.4 405 1

CTE Scan on selected_points points_1 (cost=0.00..4.74 rows=118 width=959) (actual time=0.548..66.477 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.618 67.145 ↓ 3.4 405 1

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

  • Group Key: points_2.id
9. 0.108 66.527 ↓ 1.1 405 1

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

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

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

11. 0.233 66.398 ↓ 3.4 405 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 36kB
12. 66.165 66.165 ↓ 3.4 405 1

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

13.          

CTE get_points

14. 118.584 189.227 ↓ 3.4 405 1

Hash Left Join (cost=3.83..1,066.34 rows=118 width=224) (actual time=68.345..189.227 rows=405 loops=1)

  • Hash Cond: ((points_3.id)::text = (points_with_objects.point_id)::text)
15. 0.719 0.719 ↓ 3.4 405 1

CTE Scan on filtered_points points_3 (cost=0.00..2.36 rows=118 width=666) (actual time=0.549..0.719 rows=405 loops=1)

16. 0.139 67.494 ↓ 3.4 405 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 41kB
17. 67.355 67.355 ↓ 3.4 405 1

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

18.          

SubPlan (forHash Left Join)

19. 0.405 2.430 ↑ 1.0 1 405

Limit (cost=0.42..6.43 rows=1 width=9) (actual time=0.006..0.006 rows=1 loops=405)

20. 2.025 2.025 ↑ 2.0 1 405

Index Scan using idx_segment_stats_segment_id on segment_stats (cost=0.42..12.44 rows=2 width=9) (actual time=0.005..0.005 rows=1 loops=405)

  • Index Cond: (points_3.segment_id = segment_id)
Planning time : 1.371 ms
Execution time : 190.742 ms