explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MnGa : Optimization for: plan #Iaea

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.035 52.552 ↓ 0.0 0 1

Hash Join (cost=765.43..1,191.58 rows=48 width=232) (actual time=52.552..52.552 rows=0 loops=1)

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

CTE selected_points

3. 8.482 16.429 ↓ 57.3 11,123 1

Bitmap Heap Scan on points points_1 (cost=9.79..702.16 rows=194 width=361) (actual time=8.877..16.429 rows=11,123 loops=1)

  • Recheck Cond: ('01030000000100000005000000CDCCCCCCCCCC2840CDCCCCCCCCAC4940CDCCCCCCCCCC2840EC51B81E85AB49409A99999999992840EC51B81E85AB49409A99999999992840CDCCCCCCCCAC4940CDCCCCCCCCCC2840CDCCCCCCCCAC4940'::geometry ~ offset_geom)
  • Heap Blocks: exact=4948
4. 7.947 7.947 ↓ 57.3 11,123 1

Bitmap Index Scan on point_geom_idx (cost=0.00..9.74 rows=194 width=0) (actual time=7.947..7.947 rows=11,123 loops=1)

  • Index Cond: ('01030000000100000005000000CDCCCCCCCCCC2840CDCCCCCCCCAC4940CDCCCCCCCCCC2840EC51B81E85AB49409A99999999992840EC51B81E85AB49409A99999999992840CDCCCCCCCCAC4940CDCCCCCCCCCC2840CDCCCCCCCCAC4940'::geometry ~ offset_geom)
5.          

CTE filtered_points

6. 37.647 37.647 ↓ 160.5 7,702 1

CTE Scan on selected_points points_2 (cost=0.00..3.88 rows=48 width=959) (actual time=8.892..37.647 rows=7,702 loops=1)

  • Filter: ((is_duplicate IS FALSE) AND (is_all_timeslots_latest IS TRUE))
  • Rows Removed by Filter: 3421
7.          

CTE points_with_objects

8. 0.004 43.619 ↓ 0.0 0 1

HashAggregate (cost=57.23..57.83 rows=48 width=548) (actual time=43.619..43.619 rows=0 loops=1)

  • Group Key: points_3.id
9. 2.412 43.615 ↓ 0.0 0 1

Hash Join (cost=3.12..56.70 rows=105 width=526) (actual time=43.615..43.615 rows=0 loops=1)

  • Hash Cond: (detected_objects.point_id = (points_3.id)::text)
10. 0.496 5.980 ↓ 0.0 0 1

Hash Semi Join (cost=1.56..54.69 rows=105 width=563) (actual time=5.979..5.980 rows=0 loops=1)

  • Hash Cond: (detected_objects.point_id = (filtered_points.id)::text)
11. 0.245 0.245 ↑ 1.0 1,898 1

Seq Scan on detected_objects (cost=0.00..46.98 rows=1,898 width=47) (actual time=0.010..0.245 rows=1,898 loops=1)

12. 2.708 5.239 ↓ 160.5 7,702 1

Hash (cost=0.96..0.96 rows=48 width=516) (actual time=5.238..5.239 rows=7,702 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 583kB
13. 2.531 2.531 ↓ 160.5 7,702 1

CTE Scan on filtered_points (cost=0.00..0.96 rows=48 width=516) (actual time=0.001..2.531 rows=7,702 loops=1)

14. 3.152 35.223 ↓ 160.5 7,702 1

Hash (cost=0.96..0.96 rows=48 width=516) (actual time=35.223..35.223 rows=7,702 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 583kB
15. 32.071 32.071 ↓ 160.5 7,702 1

CTE Scan on filtered_points points_3 (cost=0.00..0.96 rows=48 width=516) (actual time=0.002..32.071 rows=7,702 loops=1)

16. 8.895 8.895 ↑ 48.0 1 1

CTE Scan on filtered_points points (cost=0.00..0.96 rows=48 width=674) (actual time=8.895..8.895 rows=1 loops=1)

17. 0.001 43.622 ↓ 0.0 0 1

Hash (cost=0.96..0.96 rows=48 width=548) (actual time=43.622..43.622 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
18. 43.621 43.621 ↓ 0.0 0 1

CTE Scan on points_with_objects objects (cost=0.00..0.96 rows=48 width=548) (actual time=43.621..43.621 rows=0 loops=1)

19.          

SubPlan (forHash Join)

20. 0.000 0.000 ↓ 0.0 0

Limit (cost=0.29..6.30 rows=1 width=9) (never executed)

21. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_segment_stats_segment_id on segment_stats (cost=0.29..12.31 rows=2 width=9) (never executed)

  • Index Cond: (points.segment_id = segment_id)
Planning time : 3.816 ms
Execution time : 59.461 ms