explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8pr5

Settings
# exclusive inclusive rows x rows loops node
1. 2.655 213,611.223 ↑ 23.2 168 1

GroupAggregate (cost=2,893,536.50..2,893,692.70 rows=3,905 width=52) (actual time=213,608.099..213,611.223 rows=168 loops=1)

  • Group Key: h.epoch, h.system_id, c.mixed_total_streams
2. 3.161 213,608.568 ↑ 1.5 2,520 1

Sort (cost=2,893,536.50..2,893,546.26 rows=3,905 width=68) (actual time=213,608.062..213,608.568 rows=2,520 loops=1)

  • Sort Key: h.epoch DESC, h.system_id, c.mixed_total_streams
  • Sort Method: quicksort Memory: 451kB
3. 862.766 213,605.407 ↑ 1.5 2,520 1

Hash Join (cost=252,470.03..2,893,303.54 rows=3,905 width=68) (actual time=5,107.057..213,605.407 rows=2,520 loops=1)

  • Hash Cond: (h.system_id = c.system_id)
  • Join Filter: (((h.system_id = 21) AND (h.epoch >= 1,595,259,349) AND (h.epoch <= 1,595,864,149) AND ((h.interval_mask & 1) > 0) AND (h.is_active = 1)) OR ((maxepoch.epoch = h.epoch) AND (maxepoch.system_id = h.system_id)))
  • Rows Removed by Join Filter: 23,997
4. 212,702.610 212,702.610 ↑ 1.0 4,576,458 1

Seq Scan on hd_processed_perf_mtrees h (cost=0.00..2,482,951.83 rows=4,677,976 width=72) (actual time=1.044..212,702.610 rows=4,576,458 loops=1)

  • Filter: (mtree_id <> 0)
  • Rows Removed by Filter: 185,074
5. 0.009 40.031 ↑ 200.0 1 1

Hash (cost=252,467.53..252,467.53 rows=200 width=28) (actual time=40.031..40.031 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
6. 0.033 40.022 ↑ 200.0 1 1

Nested Loop (cost=25,278.47..252,467.53 rows=200 width=28) (actual time=39.744..40.022 rows=1 loops=1)

  • Join Filter: (c.system_id = maxepoch.system_id)
  • Rows Removed by Join Filter: 199
7. 0.189 0.189 ↑ 1.0 200 1

Index Scan using cd_cfg_collections_system_id_idx on cd_cfg_collections c (cost=0.14..690.14 rows=200 width=12) (actual time=0.008..0.189 rows=200 loops=1)

8. 0.089 39.800 ↑ 200.0 1 200

Materialize (cost=25,278.32..251,177.88 rows=200 width=16) (actual time=0.199..0.199 rows=1 loops=200)

9. 0.001 39.711 ↑ 200.0 1 1

Subquery Scan on maxepoch (cost=25,278.32..251,176.88 rows=200 width=16) (actual time=39.710..39.711 rows=1 loops=1)

10. 0.520 39.710 ↑ 200.0 1 1

GroupAggregate (cost=25,278.32..251,174.88 rows=200 width=16) (actual time=39.710..39.710 rows=1 loops=1)

  • Group Key: h_1.system_id
11. 31.677 39.190 ↑ 1.5 2,520 1

Bitmap Heap Scan on hd_processed_perf_mtrees h_1 (cost=25,278.32..251,153.39 rows=3,899 width=16) (actual time=11.268..39.190 rows=2,520 loops=1)

  • Recheck Cond: (system_id = 21)
  • Filter: ((epoch >= 1,595,259,349) AND (epoch <= 1,595,864,149) AND (mtree_id <> 0) AND (is_active = 1) AND ((interval_mask & 1) > 0))
  • Rows Removed by Filter: 25,658
  • Heap Blocks: exact=5,614
12. 7.513 7.513 ↑ 2.3 28,370 1

Bitmap Index Scan on hd_processed_perf_mtrees_system_id_idx (cost=0.00..25,277.35 rows=64,639 width=0) (actual time=7.513..7.513 rows=28,370 loops=1)

  • Index Cond: (system_id = 21)
Planning time : 1.491 ms
Execution time : 213,611.491 ms