explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

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

CTE Scan on get_points (cost=16,160.70..16,163.06 rows=118 width=224) (actual time=1.212..140.843 rows=405 loops=1)

2.          

CTE selected_points

3. 0.000 12.976 ↓ 1.9 448 1

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

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

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

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

CTE filtered_points

6. 13.561 13.561 ↓ 3.4 405 1

CTE Scan on selected_points points_1 (cost=0.00..4.74 rows=118 width=959) (actual time=0.748..13.561 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.003 0.008 ↓ 0.0 0 1

HashAggregate (cost=1,013.12..1,015.18 rows=118 width=548) (actual time=0.008..0.008 rows=0 loops=1)

  • Group Key: points_2.id
9. 0.001 0.005 ↓ 0.0 0 1

Hash Join (cost=987.46..1,010.24 rows=384 width=548) (actual time=0.005..0.005 rows=0 loops=1)

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

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

11. 0.000 0.000 ↓ 0.0 0

Hash (cost=985.99..985.99 rows=118 width=553) (never executed)

12. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.42..985.99 rows=118 width=553) (never executed)

13. 0.000 0.000 ↓ 0.0 0

CTE Scan on filtered_points points_2 (cost=0.00..2.36 rows=118 width=516) (never executed)

14. 0.000 0.000 ↓ 0.0 0

Index Only Scan using points_pkey on points points_3 (cost=0.42..8.34 rows=1 width=37) (never executed)

  • Index Cond: (id = (points_2.id)::text)
  • Heap Fetches: 0
15.          

CTE get_points

16. 123.619 140.186 ↓ 3.4 405 1

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

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

CTE Scan on filtered_points points_4 (cost=0.00..2.36 rows=118 width=666) (actual time=0.749..13.722 rows=405 loops=1)

18. 0.001 0.010 ↓ 0.0 0 1

Hash (cost=2.36..2.36 rows=118 width=548) (actual time=0.010..0.010 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
19. 0.009 0.009 ↓ 0.0 0 1

CTE Scan on points_with_objects (cost=0.00..2.36 rows=118 width=548) (actual time=0.009..0.009 rows=0 loops=1)

20.          

SubPlan (forHash Left Join)

21. 0.405 2.835 ↑ 1.0 1 405

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

22. 2.430 2.430 ↑ 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.006..0.006 rows=1 loops=405)

  • Index Cond: (points_4.segment_id = segment_id)
Planning time : 3.206 ms
Execution time : 141.238 ms