explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Iaea

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 2,299.295 2,391.567 ↓ 160.5 7,702 1

CTE Scan on filtered_points points (cost=765.91..1,243.36 rows=48 width=232) (actual time=53.350..2,391.567 rows=7,702 loops=1)

2.          

CTE selected_points

3. 10.589 15.091 ↓ 57.3 11,123 1

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

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

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

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

CTE filtered_points

6. 34.608 34.608 ↓ 160.5 7,702 1

CTE Scan on selected_points points_2 (cost=0.00..3.88 rows=48 width=959) (actual time=5.542..34.608 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.002 46.057 ↓ 0.0 0 1

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

  • Group Key: points_3.id
9. 0.010 46.055 ↓ 0.0 0 1

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

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

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

  • Hash Cond: (detected_objects.point_id = (filtered_points.id)::text)
11. 0.254 0.254 ↑ 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.254 rows=1,898 loops=1)

12. 6.253 9.475 ↓ 160.5 7,702 1

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

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

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

14. 3.265 35.835 ↓ 160.5 7,702 1

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

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

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

16.          

CTE detected_ids

17. 46.059 46.059 ↓ 0.0 0 1

CTE Scan on points_with_objects (cost=0.00..0.96 rows=48 width=516) (actual time=46.059..46.059 rows=0 loops=1)

18.          

SubPlan (forCTE Scan)

19. 7.702 46.212 ↑ 1.0 1 7,702

Limit (cost=0.29..6.30 rows=1 width=9) (actual time=0.006..0.006 rows=1 loops=7,702)

20. 38.510 38.510 ↑ 2.0 1 7,702

Index Scan using idx_segment_stats_segment_id on segment_stats (cost=0.29..12.31 rows=2 width=9) (actual time=0.005..0.005 rows=1 loops=7,702)

  • Index Cond: (points.segment_id = segment_id)
21. 46.060 46.060 ↓ 0.0 0 1

CTE Scan on detected_ids (cost=0.00..0.96 rows=48 width=516) (actual time=46.060..46.060 rows=0 loops=1)

22. 0.000 0.000 ↓ 0.0 0

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

  • Filter: ((points.id)::text = (point_id)::text)
Planning time : 1.645 ms
Execution time : 2,398.113 ms