explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

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

CTE Scan on get_points (cost=21,178.08..21,180.44 rows=118 width=224) (actual time=0.981..131.139 rows=405 loops=1)

2.          

CTE selected_points

3. 0.000 7.942 ↓ 1.9 448 1

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

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

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

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

CTE filtered_points

6. 8.515 8.515 ↓ 3.4 405 1

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

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

CTE objects

8. 0.000 0.003 ↓ 0.0 0 1

Nested Loop (cost=0.42..5,020.88 rows=650 width=64) (actual time=0.003..0.003 rows=0 loops=1)

9. 0.003 0.003 ↓ 0.0 0 1

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

10. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (id = detected_objects.point_id)
  • Heap Fetches: 0
11.          

CTE points_with_objects

12. 0.004 0.008 ↓ 0.0 0 1

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

  • Group Key: points_3.id
13. 0.000 0.004 ↓ 0.0 0 1

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

  • Hash Cond: (objects.point_id = (points_3.id)::text)
14. 0.004 0.004 ↓ 0.0 0 1

CTE Scan on objects (cost=0.00..13.00 rows=650 width=64) (actual time=0.004..0.004 rows=0 loops=1)

15. 0.000 0.000 ↓ 0.0 0

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

16. 0.000 0.000 ↓ 0.0 0

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

17. 0.000 0.000 ↓ 0.0 0

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

18. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (id = (points_3.id)::text)
  • Heap Fetches: 0
19.          

CTE get_points

20. 119.461 130.567 ↓ 3.4 405 1

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

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

CTE Scan on filtered_points points_5 (cost=0.00..2.36 rows=118 width=666) (actual time=0.628..8.667 rows=405 loops=1)

22. 0.001 0.009 ↓ 0.0 0 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
23. 0.008 0.008 ↓ 0.0 0 1

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

24.          

SubPlan (forHash Left Join)

25. 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)

26. 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_5.segment_id = segment_id)
Planning time : 0.876 ms
Execution time : 131.460 ms