explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mNXs : Optimization for: evalution of vi_get_points; plan #K81I

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 44.883 1,418.284 ↓ 3.5 357 1

Hash Left Join (cost=472,699.81..473,838.88 rows=101 width=232) (actual time=1,372.442..1,418.284 rows=357 loops=1)

  • Hash Cond: ((points.id)::text = (objects.point_id)::text)
2.          

CTE selected_points

3. 1,370.241 1,370.241 ↓ 2.4 974 1

Seq Scan on points points_1 (cost=0.00..472,567.42 rows=406 width=377) (actual time=0.143..1,370.241 rows=974 loops=1)

  • Filter: ('0103000000010000000500000085EB51B81EC528405C8FC2F528AC494085EB51B81EC52840EC51B81E85AB4940C3F5285C8FC22840EC51B81E85AB4940C3F5285C8FC228405C8FC2F528AC494085EB51B81EC528405C8FC2F528AC4940'::geometry ~ offset_geom)
  • Rows Removed by Filter: 193493
4.          

CTE filtered_points

5. 1,371.048 1,371.048 ↓ 3.5 357 1

CTE Scan on selected_points points_2 (cost=0.00..8.12 rows=101 width=959) (actual time=0.215..1,371.048 rows=357 loops=1)

  • Filter: ((offset_geom IS NOT NULL) AND (is_duplicate IS FALSE) AND (is_latest IS TRUE))
  • Rows Removed by Filter: 617
6.          

CTE points_with_objects

7. 0.002 1,371.998 ↓ 0.0 0 1

HashAggregate (cost=115.43..116.69 rows=101 width=548) (actual time=1,371.998..1,371.998 rows=0 loops=1)

  • Group Key: points_3.id
8. 0.003 1,371.996 ↓ 0.0 0 1

Hash Join (cost=6.57..114.32 rows=222 width=526) (actual time=1,371.996..1,371.996 rows=0 loops=1)

  • Hash Cond: (detected_objects.point_id = (points_3.id)::text)
9. 0.344 0.772 ↓ 0.0 0 1

Hash Semi Join (cost=3.28..109.99 rows=222 width=563) (actual time=0.772..0.772 rows=0 loops=1)

  • Hash Cond: (detected_objects.point_id = (filtered_points.id)::text)
10. 0.271 0.271 ↑ 1.0 2,316 1

Seq Scan on detected_objects (cost=0.00..98.16 rows=2,316 width=47) (actual time=0.009..0.271 rows=2,316 loops=1)

11. 0.062 0.157 ↓ 3.5 357 1

Hash (cost=2.02..2.02 rows=101 width=516) (actual time=0.157..0.157 rows=357 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 33kB
12. 0.095 0.095 ↓ 3.5 357 1

CTE Scan on filtered_points (cost=0.00..2.02 rows=101 width=516) (actual time=0.001..0.095 rows=357 loops=1)

13. 0.208 1,371.221 ↓ 3.5 357 1

Hash (cost=2.02..2.02 rows=101 width=516) (actual time=1,371.221..1,371.221 rows=357 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 33kB
14. 1,371.013 1,371.013 ↓ 3.5 357 1

CTE Scan on filtered_points points_3 (cost=0.00..2.02 rows=101 width=516) (actual time=0.001..1,371.013 rows=357 loops=1)

15.          

CTE detected_ids

16. 0.000 0.000 ↓ 0.0 0 1

CTE Scan on points_with_objects (cost=0.00..2.02 rows=101 width=516) (actual time=0.000..0.000 rows=0 loops=1)

17. 0.330 0.330 ↓ 3.5 357 1

CTE Scan on filtered_points points (cost=0.00..2.02 rows=101 width=674) (actual time=0.215..0.330 rows=357 loops=1)

18. 0.000 1,371.999 ↓ 0.0 0 1

Hash (cost=2.02..2.02 rows=101 width=516) (actual time=1,371.999..1,371.999 rows=0 loops=1)

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

CTE Scan on points_with_objects objects (cost=0.00..2.02 rows=101 width=516) (actual time=1,371.999..1,371.999 rows=0 loops=1)

20.          

SubPlan (forHash Left Join)

21. 0.357 1.071 ↑ 1.0 1 357

Limit (cost=0.42..6.43 rows=1 width=9) (actual time=0.003..0.003 rows=1 loops=357)

22. 0.714 0.714 ↑ 2.0 1 357

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

  • Index Cond: (points.segment_id = segment_id)
23. 0.001 0.001 ↓ 0.0 0 1

CTE Scan on detected_ids (cost=0.00..2.02 rows=101 width=516) (actual time=0.001..0.001 rows=0 loops=1)

24. 0.000 0.000 ↓ 0.0 0

CTE Scan on points_with_objects objects_1 (cost=0.00..2.27 rows=1 width=32) (never executed)

  • Filter: ((points.id)::text = (point_id)::text)
Planning time : 0.467 ms
Execution time : 1,418.437 ms