explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2ClK

Settings
# exclusive inclusive rows x rows loops node
1. 3,268.033 4,449.840 ↓ 1.0 2,092,494 1

Hash Left Join (cost=76,611.45..103,856.91 rows=1,996,552 width=687) (actual time=974.080..4,449.840 rows=2,092,494 loops=1)

  • Output: pv.pair_id, pv.src_system_id, pv.src_ctx_id, pv.src_epoch, pv.src_host, pv.src_path, pv.src_processed_status, pv.src_processed_status_msg, pv.dst_system_id, pv.dst_ctx_id, pv.dst_epoch, pv.dst_host, pv.dst_path, pv.dst_processed_status, pv.dst_pr (...)
  • Hash Cond: (pv.pair_id = ctx_1.pair_id)
2. 138.316 1,180.183 ↓ 1.2 139,620 1

Hash Left Join (cost=74,805.37..77,192.33 rows=115,129 width=687) (actual time=972.432..1,180.183 rows=139,620 loops=1)

  • Output: pv.pair_id, pv.src_system_id, pv.src_ctx_id, pv.src_epoch, pv.src_host, pv.src_path, pv.src_processed_status, pv.src_processed_status_msg, pv.dst_system_id, pv.dst_ctx_id, pv.dst_epoch, pv.dst_host, pv.dst_path, pv.dst_processed_status, pv. (...)
  • Hash Cond: (pv.pair_id = ctx.pair_id)
3. 7.785 1,040.546 ↑ 1.0 8,922 1

Hash Left Join (cost=73,384.27..74,240.76 rows=8,922 width=651) (actual time=971.085..1,040.546 rows=8,922 loops=1)

  • Output: pv.pair_id, pv.src_system_id, pv.src_ctx_id, pv.src_epoch, pv.src_host, pv.src_path, pv.src_processed_status, pv.src_processed_status_msg, pv.dst_system_id, pv.dst_ctx_id, pv.dst_epoch, pv.dst_host, pv.dst_path, pv.dst_processed_statu (...)
  • Hash Cond: (pv.pair_id = cascades.pair_id)
4. 49.038 1,032.122 ↑ 1.0 8,922 1

Hash Left Join (cost=72,910.11..73,710.26 rows=8,922 width=647) (actual time=970.414..1,032.122 rows=8,922 loops=1)

  • Output: pv.pair_id, pv.src_system_id, pv.src_ctx_id, pv.src_epoch, pv.src_host, pv.src_path, pv.src_processed_status, pv.src_processed_status_msg, pv.dst_system_id, pv.dst_ctx_id, pv.dst_epoch, pv.dst_host, pv.dst_path, pv.dst_processed (...)
  • Hash Cond: ((pv.src_system_id = hd_processed_space_repl_ctxs.system_id) AND (pv.src_ctx_id = hd_processed_space_repl_ctxs.ctx_id))
5. 12.706 12.706 ↑ 1.0 8,922 1

Seq Scan on views.cd_cfg_repl_pair_view pv (cost=0.00..666.22 rows=8,922 width=631) (actual time=0.010..12.706 rows=8,922 loops=1)

  • Output: pv.pair_id, pv.src_system_id, pv.src_ctx_id, pv.src_epoch, pv.src_host, pv.src_path, pv.src_processed_status, pv.src_processed_status_msg, pv.dst_system_id, pv.dst_ctx_id, pv.dst_epoch, pv.dst_host, pv.dst_path, pv.dst_pro (...)
6. 0.883 970.378 ↓ 20.5 923 1

Hash (cost=72,909.43..72,909.43 rows=45 width=32) (actual time=970.378..970.378 rows=923 loops=1)

  • Output: hist.pre_comp_repl_speed, hist.post_comp_repl_speed, hd_processed_space_repl_ctxs.system_id, hd_processed_space_repl_ctxs.ctx_id
  • Buckets: 1024 Batches: 1 Memory Usage: 66kB
7. 1.480 969.495 ↓ 20.5 923 1

Nested Loop (cost=55,883.30..72,909.43 rows=45 width=32) (actual time=955.384..969.495 rows=923 loops=1)

  • Output: hist.pre_comp_repl_speed, hist.post_comp_repl_speed, hd_processed_space_repl_ctxs.system_id, hd_processed_space_repl_ctxs.ctx_id
8. 714.863 956.016 ↑ 2.4 923 1

HashAggregate (cost=55,882.87..55,905.34 rows=2,247 width=24) (actual time=955.301..956.016 rows=923 loops=1)

  • Output: max(hd_processed_space_repl_ctxs.epoch), hd_processed_space_repl_ctxs.system_id, hd_processed_space_repl_ctxs.ctx_id
  • Group Key: hd_processed_space_repl_ctxs.system_id, hd_processed_space_repl_ctxs.ctx_id
9. 241.153 241.153 ↑ 1.0 740,737 1

Seq Scan on processed.hd_processed_space_repl_ctxs (cost=0.00..50,307.64 rows=743,364 width=24) (actual time=0.034..241.153 rows=740,737 loops=1)

  • Output: hd_processed_space_repl_ctxs.system_id, hd_processed_space_repl_ctxs.interval_mask, hd_processed_space_repl_ctxs.count, hd_processed_space_repl_ctxs.is_missing, hd_processed_space_repl_ctxs.epoch, hd_proc (...)
10. 11.999 11.999 ↑ 1.0 1 923

Index Scan using hd_processed_space_repl_ctxs_system_id_ctx_id_epoch_idx on processed.hd_processed_space_repl_ctxs hist (cost=0.42..7.55 rows=1 width=40) (actual time=0.013..0.013 rows=1 loops=923)

  • Output: hist.system_id, hist.interval_mask, hist.count, hist.is_missing, hist.epoch, hist.node_id, hist.ctx_id, hist.pre_comp_remaining, hist.pre_comp_replicated_cumu, hist.post_comp_remaining, hist.post_comp_replicate (...)
  • Index Cond: ((hist.system_id = hd_processed_space_repl_ctxs.system_id) AND (hist.ctx_id = hd_processed_space_repl_ctxs.ctx_id) AND (hist.epoch = (max(hd_processed_space_repl_ctxs.epoch))))
  • Filter: (hist.is_active = 1)
11. 0.035 0.639 ↑ 1.0 40 1

Hash (cost=473.66..473.66 rows=40 width=12) (actual time=0.639..0.639 rows=40 loops=1)

  • Output: cascades.cascade_status, cascades.pair_id
  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
12. 0.006 0.604 ↑ 1.0 40 1

Subquery Scan on cascades (cost=472.86..473.66 rows=40 width=12) (actual time=0.589..0.604 rows=40 loops=1)

  • Output: cascades.cascade_status, cascades.pair_id
13. 0.163 0.598 ↑ 1.0 40 1

HashAggregate (cost=472.86..473.26 rows=40 width=12) (actual time=0.588..0.598 rows=40 loops=1)

  • Output: icas.pair_id, max(CASE WHEN (icas.cascade_status = 1) THEN 2 ELSE CASE WHEN (icas.cascade_status = 2) THEN 3 ELSE CASE WHEN (icas.cascade_status = 3) THEN 1 ELSE CASE WHEN (icas.cascade_status = 4) THEN 1 ELSE CASE W (...)
  • Group Key: icas.pair_id
14. 0.080 0.435 ↑ 2.0 81 1

Nested Loop (cost=0.28..470.05 rows=161 width=12) (actual time=0.050..0.435 rows=81 loops=1)

  • Output: icas.pair_id, icas.cascade_status
15. 0.035 0.035 ↑ 1.0 80 1

Seq Scan on config.cd_cfg_repl_cascade icas (cost=0.00..2.80 rows=80 width=12) (actual time=0.013..0.035 rows=80 loops=1)

  • Output: icas.cascade_id, icas.pair_id, icas.cascade_sequence, icas.cascade_status, icas.cascade_status_msg
16. 0.320 0.320 ↑ 6.0 1 80

Index Only Scan using cd_cfg_repl_ctxs_pair_id_is_src_idx on config.cd_cfg_repl_ctxs ictx (cost=0.28..5.78 rows=6 width=8) (actual time=0.003..0.004 rows=1 loops=80)

  • Output: ictx.pair_id, ictx.is_src
  • Index Cond: (ictx.pair_id = icas.pair_id)
  • Heap Fetches: 0
17. 0.182 1.321 ↑ 1.0 378 1

Hash (cost=1,416.38..1,416.38 rows=378 width=44) (actual time=1.321..1.321 rows=378 loops=1)

  • Output: ctx.state, ctx.pair_id, (ROW(ctx.state, ctx.pair_id))
  • Buckets: 1024 Batches: 1 Memory Usage: 40kB
18. 0.978 1.139 ↑ 1.0 378 1

Bitmap Heap Scan on config.cd_cfg_repl_ctxs ctx (cost=371.20..1,416.38 rows=378 width=44) (actual time=0.196..1.139 rows=378 loops=1)

  • Output: ctx.state, ctx.pair_id, ROW(ctx.state, ctx.pair_id)
  • Recheck Cond: (ctx.is_src = 1)
  • Heap Blocks: exact=76
19. 0.161 0.161 ↓ 1.0 391 1

Bitmap Index Scan on cd_cfg_repl_ctxs_is_src_idx (cost=0.00..371.11 rows=378 width=0) (actual time=0.161..0.161 rows=391 loops=1)

  • Index Cond: (ctx.is_src = 1)
20. 0.230 1.624 ↑ 1.0 508 1

Hash (cost=1,799.73..1,799.73 rows=508 width=12) (actual time=1.624..1.624 rows=508 loops=1)

  • Output: ctx_1.state, ctx_1.pair_id
  • Buckets: 1024 Batches: 1 Memory Usage: 32kB
21. 1.160 1.394 ↑ 1.0 508 1

Bitmap Heap Scan on config.cd_cfg_repl_ctxs ctx_1 (cost=500.21..1,799.73 rows=508 width=12) (actual time=0.277..1.394 rows=508 loops=1)

  • Output: ctx_1.state, ctx_1.pair_id
  • Recheck Cond: (ctx_1.is_src = 0)
  • Heap Blocks: exact=157
22. 0.234 0.234 ↓ 1.0 512 1

Bitmap Index Scan on cd_cfg_repl_ctxs_is_src_idx (cost=0.00..500.08 rows=508 width=0) (actual time=0.234..0.234 rows=512 loops=1)

  • Index Cond: (ctx_1.is_src = 0)