explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LqRh

Settings
# exclusive inclusive rows x rows loops node
1. 264.554 275.921 ↓ 16.6 797 1

Hash Left Join (cost=765.43..1,191.58 rows=48 width=232) (actual time=5.748..275.921 rows=797 loops=1)

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

CTE selected_points

3. 1.572 2.124 ↓ 9.2 1,784 1

Bitmap Heap Scan on points points_1 (cost=9.79..702.16 rows=194 width=361) (actual time=0.732..2.124 rows=1,784 loops=1)

  • Recheck Cond: ('0103000000010000000500000048E17A14AEC72840068195438BAC494048E17A14AEC72840EC51B81E85AB4940C3F5285C8FC22840EC51B81E85AB4940C3F5285C8FC22840068195438BAC494048E17A14AEC72840068195438BAC4940'::geometry ~ offset_geom)
  • Heap Blocks: exact=1213
4. 0.552 0.552 ↓ 9.2 1,784 1

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

  • Index Cond: ('0103000000010000000500000048E17A14AEC72840068195438BAC494048E17A14AEC72840EC51B81E85AB4940C3F5285C8FC22840EC51B81E85AB4940C3F5285C8FC22840068195438BAC494048E17A14AEC72840068195438BAC4940'::geometry ~ offset_geom)
5.          

CTE filtered_points

6. 3.567 3.567 ↓ 16.6 797 1

CTE Scan on selected_points points_2 (cost=0.00..3.88 rows=48 width=959) (actual time=0.740..3.567 rows=797 loops=1)

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

CTE points_with_objects

8. 0.002 4.627 ↓ 0.0 0 1

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

  • Group Key: points_3.id
9. 0.003 4.625 ↓ 0.0 0 1

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

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

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

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

12. 0.234 0.483 ↓ 16.6 797 1

Hash (cost=0.96..0.96 rows=48 width=516) (actual time=0.483..0.483 rows=797 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 62kB
13. 0.249 0.249 ↓ 16.6 797 1

CTE Scan on filtered_points (cost=0.00..0.96 rows=48 width=516) (actual time=0.001..0.249 rows=797 loops=1)

14. 0.264 3.415 ↓ 16.6 797 1

Hash (cost=0.96..0.96 rows=48 width=516) (actual time=3.415..3.415 rows=797 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 62kB
15. 3.151 3.151 ↓ 16.6 797 1

CTE Scan on filtered_points points_3 (cost=0.00..0.96 rows=48 width=516) (actual time=0.001..3.151 rows=797 loops=1)

16. 1.159 1.159 ↓ 16.6 797 1

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

17. 0.001 4.629 ↓ 0.0 0 1

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

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

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

19.          

SubPlan (forHash Left Join)

20. 0.797 5.579 ↑ 1.0 1 797

Limit (cost=0.29..6.30 rows=1 width=9) (actual time=0.007..0.007 rows=1 loops=797)

21. 4.782 4.782 ↑ 2.0 1 797

Index Scan using idx_segment_stats_segment_id on segment_stats (cost=0.29..12.31 rows=2 width=9) (actual time=0.006..0.006 rows=1 loops=797)

  • Index Cond: (points.segment_id = segment_id)
Planning time : 2.273 ms
Execution time : 276.399 ms