explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BGC

Settings
# exclusive inclusive rows x rows loops node
1. 0.326 34.842 ↓ 140.0 140 1

GroupAggregate (cost=4,446.70..4,446.72 rows=1 width=16) (actual time=34.384..34.842 rows=140 loops=1)

  • Output: v.diag_id, v.object_id, count(r.metric_num_value)
  • Group Key: v.diag_id, v.object_id
  • Buffers: shared hit=1831
2. 0.318 34.516 ↓ 140.0 140 1

Sort (cost=4,446.70..4,446.70 rows=1 width=16) (actual time=34.374..34.516 rows=140 loops=1)

  • Output: v.diag_id, v.object_id, r.metric_num_value
  • Sort Key: v.diag_id, v.object_id
  • Sort Method: quicksort Memory: 31kB
  • Buffers: shared hit=1831
3. 0.886 34.198 ↓ 140.0 140 1

Nested Loop (cost=102.06..4,446.69 rows=1 width=16) (actual time=12.177..34.198 rows=140 loops=1)

  • Output: v.diag_id, v.object_id, r.metric_num_value
  • Join Filter: (t.metric_id = r.metric_id)
  • Buffers: shared hit=1831
4. 5.573 32.752 ↓ 70.0 140 1

Hash Join (cost=101.63..4,429.76 rows=2 width=12) (actual time=12.151..32.752 rows=140 loops=1)

  • Output: v.diag_id, v.object_id, t.metric_id
  • Hash Cond: ((v.diag_id + 1) = t.metric_id)
  • Buffers: shared hit=1269
5. 26.418 26.418 ↓ 45.5 4,959 1

Seq Scan on endtoend_839_central.dss_violation_statuses v (cost=0.00..4,327.02 rows=109 width=8) (actual time=8.673..26.418 rows=4,959 loops=1)

  • Output: v.diag_id, v.object_id, v.snapshot_id, v.previous_snapshot_id, v.violation_status
  • Filter: ((v.violation_status = 4) AND (v.snapshot_id = 7) AND (v.previous_snapshot_id = 1))
  • Rows Removed by Filter: 173527
  • Buffers: shared hit=1214
6. 0.030 0.761 ↑ 1.7 16 1

Hash (cost=101.29..101.29 rows=27 width=4) (actual time=0.761..0.761 rows=16 loops=1)

  • Output: t.metric_id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=55
7. 0.731 0.731 ↑ 1.7 16 1

Seq Scan on endtoend_839_central.dss_metric_value_types t (cost=0.00..101.29 rows=27 width=4) (actual time=0.268..0.731 rows=16 loops=1)

  • Output: t.metric_id
  • Filter: (t.metric_value_type = ANY ('{7,8}'::integer[]))
  • Rows Removed by Filter: 3687
  • Buffers: shared hit=55
8. 0.560 0.560 ↑ 1.0 1 140

Index Scan using dss_metr_res_idx2 on endtoend_839_central.dss_metric_results r (cost=0.43..8.45 rows=1 width=16) (actual time=0.003..0.004 rows=1 loops=140)

  • Output: r.metric_id, r.object_id, r.metric_value_index, r.metric_num_value, r.metric_char_value, r.metric_object_id, r.snapshot_id, r.position_id
  • Index Cond: ((r.snapshot_id = 7) AND (r.metric_id = (v.diag_id + 1)) AND (r.object_id = v.object_id))
  • Buffers: shared hit=562
Planning time : 0.729 ms