explain.depesz.com

PostgreSQL's explain analyze made readable

Result: prE

Settings
# exclusive inclusive rows x rows loops node
1. 1.555 2,218,459.358 ↑ 74.4 182 1

HashAggregate (cost=2,604,725.83..2,604,861.24 rows=13,541 width=12) (actual time=2,218,459.099..2,218,459.358 rows=182 loops=1)

  • Output: av.transaction_id, 67921, av.metric_id, count(av.object_id), 51
  • Buffers: local hit=24209204 read=38244392
2.          

CTE added_violations

3. 85,309.371 2,218,456.668 ↑ 473.4 286 1

Merge Anti Join (cost=539.26..2,601,002.27 rows=135,402 width=12) (actual time=591,548.568..2,218,456.668 rows=286 loops=1)

  • Output: cur.transaction_id, cur.metric_id, cur.object_id
  • Merge Cond: ((cur.transaction_id = previous.transaction_id) AND (cur.metric_id = previous.metric_id) AND (cur.object_id = previous.object_id))
  • Buffers: local hit=24209204 read=38244392
4. 338,304.766 338,304.766 ↑ 1.0 31,121,737 1

Index Only Scan using wk_cur_idx on pg_temp_31.wk_cur cur (cost=0.00..1,042,948.86 rows=31,121,748 width=12) (actual time=0.110..338,304.766 rows=31,121,737 loops=1)

  • Output: cur.transaction_id, cur.metric_id, cur.object_id
  • Heap Fetches: 31121737
  • Buffers: local hit=12103736 read=19121682
5. 1,794,842.531 1,794,842.531 ↓ 1.0 31,124,732 1

Index Only Scan using wk_prev_idx on pg_temp_31.wk_prev previous (cost=0.00..1,043,044.29 rows=31,124,700 width=12) (actual time=0.050..1,794,842.531 rows=31,124,732 loops=1)

  • Output: previous.transaction_id, previous.metric_id, previous.object_id
  • Heap Fetches: 31124732
  • Buffers: local hit=12105468 read=19122710
6. 2,218,457.803 2,218,457.803 ↑ 473.4 286 1

CTE Scan on added_violations av (cost=0.00..2,708.04 rows=135,402 width=12) (actual time=591,548.577..2,218,457.803 rows=286 loops=1)

  • Output: av.transaction_id, av.metric_id, av.object_id
  • Buffers: local hit=24209204 read=38244392