explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Cxm6

Settings
# exclusive inclusive rows x rows loops node
1. 0.311 718.013 ↑ 1.6 338 1

Sort (cost=217,744.08..217,745.41 rows=532 width=84) (actual time=717.966..718.013 rows=338 loops=1)

  • Sort Key: h.epoch DESC, h.system_id, h.net_id
  • Sort Method: quicksort Memory: 72kB
2. 0.591 717.702 ↑ 1.6 338 1

GroupAggregate (cost=217,682.76..217,720.00 rows=532 width=84) (actual time=717.086..717.702 rows=338 loops=1)

  • Group Key: h.system_id, h.epoch, (max(h_1.epoch)), (max(h_2.epoch)), h.net_id, names.port, names.speed
3. 0.691 717.111 ↑ 1.6 338 1

Sort (cost=217,682.76..217,684.09 rows=532 width=70) (actual time=717.067..717.111 rows=338 loops=1)

  • Sort Key: h.system_id, h.epoch DESC, (max(h_1.epoch)), (max(h_2.epoch)), h.net_id, names.port, names.speed
  • Sort Method: quicksort Memory: 72kB
4. 0.198 716.420 ↑ 1.6 338 1

Hash Left Join (cost=33,197.59..217,658.67 rows=532 width=70) (actual time=12.154..716.420 rows=338 loops=1)

  • Hash Cond: ((h.net_id = names.id) AND (h.system_id = names.system_id))
5. 80.410 716.198 ↑ 1.6 338 1

Hash Join (cost=33,168.84..217,625.92 rows=532 width=60) (actual time=12.114..716.198 rows=338 loops=1)

  • Hash Cond: (h.system_id = h_1.system_id)
  • Join Filter: ((((h.interval_mask & 1) > 0) AND (h.system_id = 21) AND (h.epoch >= 1,595,875,727) AND (h.epoch <= 1,596,480,527) AND (h.is_active = 1)) OR (((max(h_1.epoch)) = h.epoch) AND (h_1.system_id = h.system_id)))
  • Rows Removed by Join Filter: 3,500
6. 624.251 624.251 ↓ 1.0 414,154 1

Seq Scan on hd_processed_perf_nets h (cost=0.00..162,076.33 rows=411,033 width=48) (actual time=0.047..624.251 rows=414,154 loops=1)

7. 0.005 11.537 ↑ 188.0 1 1

Hash (cost=33,166.49..33,166.49 rows=188 width=32) (actual time=11.537..11.537 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
8. 0.022 11.532 ↑ 188.0 1 1

Hash Join (cost=19,123.73..33,166.49 rows=188 width=32) (actual time=11.531..11.532 rows=1 loops=1)

  • Hash Cond: (h_1.system_id = h_2.system_id)
9. 0.496 6.718 ↑ 200.0 1 1

GroupAggregate (cost=2,542.76..16,580.90 rows=200 width=16) (actual time=6.717..6.718 rows=1 loops=1)

  • Group Key: h_1.system_id
10. 4.904 6.222 ↓ 1.6 2,158 1

Bitmap Heap Scan on hd_processed_perf_nets h_1 (cost=2,542.76..16,572.23 rows=1,334 width=16) (actual time=1.594..6.222 rows=2,158 loops=1)

  • Recheck Cond: (system_id = 21)
  • Filter: ((is_active = 1) AND ((interval_mask & 1) > 0) AND ((interval_mask & 1) > 0))
  • Rows Removed by Filter: 1,680
  • Heap Blocks: exact=1,093
11. 1.318 1.318 ↓ 1.1 4,390 1

Bitmap Index Scan on hd_processed_perf_nets_system_id_idx (cost=0.00..2,542.43 rows=4,001 width=0) (actual time=1.318..1.318 rows=4,390 loops=1)

  • Index Cond: (system_id = 21)
12. 0.010 4.792 ↑ 188.0 1 1

Hash (cost=16,578.62..16,578.62 rows=188 width=16) (actual time=4.792..4.792 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
13. 0.084 4.782 ↑ 188.0 1 1

GroupAggregate (cost=2,542.57..16,576.74 rows=188 width=16) (actual time=4.782..4.782 rows=1 loops=1)

  • Group Key: h_2.system_id
14. 3.584 4.698 ↑ 1.7 336 1

Bitmap Heap Scan on hd_processed_perf_nets h_2 (cost=2,542.57..16,572.03 rows=565 width=16) (actual time=1.368..4.698 rows=336 loops=1)

  • Recheck Cond: (system_id = 21)
  • Filter: ((epoch >= 1,595,875,727) AND (epoch <= 1,596,480,527) AND (is_active = 1) AND ((interval_mask & 1) > 0))
  • Rows Removed by Filter: 3,502
  • Heap Blocks: exact=1,093
15. 1.114 1.114 ↓ 1.1 4,390 1

Bitmap Index Scan on hd_processed_perf_nets_system_id_idx (cost=0.00..2,542.43 rows=4,001 width=0) (actual time=1.114..1.114 rows=4,390 loops=1)

  • Index Cond: (system_id = 21)
16. 0.000 0.024 ↓ 0.0 0 1

Hash (cost=28.74..28.74 rows=1 width=26) (actual time=0.024..0.024 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
17. 0.000 0.024 ↓ 0.0 0 1

Subquery Scan on names (cost=28.72..28.74 rows=1 width=26) (actual time=0.024..0.024 rows=0 loops=1)

18. 0.007 0.024 ↓ 0.0 0 1

Sort (cost=28.72..28.73 rows=1 width=26) (actual time=0.023..0.024 rows=0 loops=1)

  • Sort Key: h_3.id
  • Sort Method: quicksort Memory: 25kB
19.          

Initplan (for Sort)

20. 0.001 0.006 ↑ 1.0 1 1

Aggregate (cost=9.51..9.52 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=1)

21. 0.001 0.005 ↓ 0.0 0 1

Bitmap Heap Scan on hd_perf_nets h_4 (cost=4.16..9.50 rows=2 width=8) (actual time=0.005..0.005 rows=0 loops=1)

  • Recheck Cond: (system_id = 21)
22. 0.004 0.004 ↓ 0.0 0 1

Bitmap Index Scan on hd_perf_nets_system_id_node_id_id_epoch_us_idx (cost=0.00..4.16 rows=2 width=0) (actual time=0.004..0.004 rows=0 loops=1)

  • Index Cond: (system_id = 21)
23. 0.000 0.011 ↓ 0.0 0 1

Nested Loop (cost=4.44..19.19 rows=1 width=26) (actual time=0.011..0.011 rows=0 loops=1)

  • Join Filter: ((h_3.port_name)::text = (cd.port)::text)
24. 0.011 0.011 ↓ 0.0 0 1

Index Scan using hd_perf_nets_system_id_node_id_id_epoch_us_idx on hd_perf_nets h_3 (cost=0.15..8.18 rows=1 width=44) (actual time=0.011..0.011 rows=0 loops=1)

  • Index Cond: ((system_id = 21) AND (epoch_us = $1))
25. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on cd_hw_net_if cd (cost=4.29..10.99 rows=2 width=18) (never executed)

  • Recheck Cond: (system_id = 21)
26. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on cd_hw_net_if_system_id_idx (cost=0.00..4.29 rows=2 width=0) (never executed)

  • Index Cond: (system_id = 21)
Planning time : 2.454 ms
Execution time : 718.489 ms