explain.depesz.com

PostgreSQL's explain analyze made readable

Result: K81I : evalution of vi_get_points

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 45.681 1,420.405 ↓ 3.5 357 1

Hash Left Join (cost=472,843.82..473,982.89 rows=101 width=232) (actual time=1,374.291..1,420.405 rows=357 loops=1)

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

CTE selected_points

3. 1,370.854 1,370.854 ↓ 2.4 974 1

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

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

CTE filtered_points

5. 1,371.619 1,371.619 ↓ 3.5 357 1

CTE Scan on selected_points points_2 (cost=0.00..8.12 rows=101 width=959) (actual time=0.218..1,371.619 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.001 1,372.434 ↓ 0.0 0 1

HashAggregate (cost=113.46..114.72 rows=101 width=548) (actual time=1,372.434..1,372.434 rows=0 loops=1)

  • Group Key: points_3.id
8. 0.360 1,372.433 ↓ 0.0 0 1

Hash Join (cost=3.28..112.35 rows=222 width=526) (actual time=1,372.433..1,372.433 rows=0 loops=1)

  • Hash Cond: (detected_objects.point_id = (points_3.id)::text)
9. 0.270 0.270 ↑ 1.0 2,316 1

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

10. 0.222 1,371.803 ↓ 3.5 357 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 33kB
11. 1,371.581 1,371.581 ↓ 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.581 rows=357 loops=1)

12.          

CTE detected_ids

13. 0.304 0.304 ↑ 1.0 2,316 1

Seq Scan on detected_objects detected_objects_1 (cost=0.00..98.16 rows=2,316 width=37) (actual time=0.007..0.304 rows=2,316 loops=1)

14. 0.337 0.337 ↓ 3.5 357 1

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

15. 0.000 1,372.435 ↓ 0.0 0 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
16. 1,372.435 1,372.435 ↓ 0.0 0 1

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

17.          

SubPlan (forHash Left Join)

18. 0.357 1.071 ↑ 1.0 1 357

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

19. 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)
20. 0.881 0.881 ↑ 1.0 2,316 1

CTE Scan on detected_ids (cost=0.00..46.32 rows=2,316 width=32) (actual time=0.008..0.881 rows=2,316 loops=1)

21. 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.342 ms
Execution time : 1,420.577 ms