explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Et9S

Settings
# exclusive inclusive rows x rows loops node
1. 2,836.753 17,873.724 ↓ 297.1 2,092,494 1

Merge Left Join (cost=64,335.31..82,226.62 rows=7,042 width=687) (actual time=11,390.429..17,873.724 rows=2,092,494 loops=1)

  • Output: cd_cfg_repl_pair_view.pair_id, cd_cfg_repl_pair_view.src_system_id, cd_cfg_repl_pair_view.src_ctx_id, cd_cfg_repl_pair_view.src_epoch, cd_cfg_repl_pair_view.src_host, cd_cfg_repl_pair_view.src_path, cd_cfg_repl_pair_view.src_processed_status, cd_ (...)
  • Merge Cond: (cd_cfg_repl_pair_view.pair_id = ctx_1.pair_id)
2. 139.348 14,576.026 ↓ 64.6 139,620 1

Merge Left Join (cost=61,075.34..78,855.62 rows=2,162 width=687) (actual time=11,385.398..14,576.026 rows=139,620 loops=1)

  • Output: cd_cfg_repl_pair_view.pair_id, cd_cfg_repl_pair_view.src_system_id, cd_cfg_repl_pair_view.src_ctx_id, cd_cfg_repl_pair_view.src_epoch, cd_cfg_repl_pair_view.src_host, cd_cfg_repl_pair_view.src_path, cd_cfg_repl_pair_view.src_processed_statu (...)
  • Merge Cond: (cd_cfg_repl_pair_view.pair_id = ctx.pair_id)
3. 1,998.007 13,966.623 ↓ 10.0 8,922 1

Nested Loop Left Join (cost=57,912.25..75,648.08 rows=892 width=651) (actual time=10,948.296..13,966.623 rows=8,922 loops=1)

  • Output: cd_cfg_repl_pair_view.pair_id, cd_cfg_repl_pair_view.src_system_id, cd_cfg_repl_pair_view.src_ctx_id, cd_cfg_repl_pair_view.src_epoch, cd_cfg_repl_pair_view.src_host, cd_cfg_repl_pair_view.src_path, cd_cfg_repl_pair_view.src_processed (...)
  • Join Filter: ((cd_cfg_repl_pair_view.src_system_id = hd_processed_space_repl_ctxs.system_id) AND (cd_cfg_repl_pair_view.src_ctx_id = hd_processed_space_repl_ctxs.ctx_id))
  • Rows Removed by Join Filter: 8226132
4. 17.209 352.172 ↓ 10.0 8,922 1

Merge Left Join (cost=1,963.78..1,970.91 rows=892 width=635) (actual time=307.742..352.172 rows=8,922 loops=1)

  • Output: cd_cfg_repl_pair_view.pair_id, cd_cfg_repl_pair_view.src_system_id, cd_cfg_repl_pair_view.src_ctx_id, cd_cfg_repl_pair_view.src_epoch, cd_cfg_repl_pair_view.src_host, cd_cfg_repl_pair_view.src_path, cd_cfg_repl_pair_view.src_pro (...)
  • Merge Cond: (cd_cfg_repl_pair_view.pair_id = cascades.pair_id)
5. 130.197 334.186 ↓ 10.0 8,922 1

Sort (cost=1,664.58..1,666.81 rows=892 width=631) (actual time=306.969..334.186 rows=8,922 loops=1)

  • Output: cd_cfg_repl_pair_view.pair_id, cd_cfg_repl_pair_view.src_system_id, cd_cfg_repl_pair_view.src_ctx_id, cd_cfg_repl_pair_view.src_epoch, cd_cfg_repl_pair_view.src_host, cd_cfg_repl_pair_view.src_path, cd_cfg_repl_pair_view.s (...)
  • Sort Key: cd_cfg_repl_pair_view.pair_id
  • Sort Method: external merge Disk: 4296kB
6. 78.505 203.989 ↓ 10.0 8,922 1

HashAggregate (cost=1,603.03..1,611.95 rows=892 width=631) (actual time=187.459..203.989 rows=8,922 loops=1)

  • Output: cd_cfg_repl_pair_view.pair_id, cd_cfg_repl_pair_view.src_system_id, cd_cfg_repl_pair_view.src_ctx_id, cd_cfg_repl_pair_view.src_epoch, cd_cfg_repl_pair_view.src_host, cd_cfg_repl_pair_view.src_path, cd_cfg_repl_pair_ (...)
  • Group Key: cd_cfg_repl_pair_view.pair_id, cd_cfg_repl_pair_view.src_system_id, cd_cfg_repl_pair_view.src_ctx_id, cd_cfg_repl_pair_view.src_epoch, cd_cfg_repl_pair_view.src_host, cd_cfg_repl_pair_view.src_path, cd_cfg_repl_pa (...)
7. 125.484 125.484 ↑ 1.0 8,922 1

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

  • Output: cd_cfg_repl_pair_view.pair_id, cd_cfg_repl_pair_view.src_system_id, cd_cfg_repl_pair_view.src_ctx_id, cd_cfg_repl_pair_view.src_epoch, cd_cfg_repl_pair_view.src_host, cd_cfg_repl_pair_view.src_path, cd_cfg_repl (...)
8. 0.076 0.777 ↑ 1.0 40 1

Sort (cost=299.20..299.30 rows=40 width=12) (actual time=0.762..0.777 rows=40 loops=1)

  • Output: cascades.cascade_status, cascades.pair_id
  • Sort Key: cascades.pair_id
  • Sort Method: quicksort Memory: 26kB
9. 0.016 0.701 ↑ 1.0 40 1

Subquery Scan on cascades (cost=297.34..298.14 rows=40 width=12) (actual time=0.674..0.701 rows=40 loops=1)

  • Output: cascades.cascade_status, cascades.pair_id
10. 0.203 0.685 ↑ 1.0 40 1

HashAggregate (cost=297.34..297.74 rows=40 width=12) (actual time=0.672..0.685 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 (...)
  • Group Key: icas.pair_id
11. 0.121 0.482 ↑ 1.8 81 1

Nested Loop (cost=0.29..294.80 rows=145 width=12) (actual time=0.058..0.482 rows=81 loops=1)

  • Output: icas.pair_id, icas.cascade_status
12. 0.041 0.041 ↑ 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.018..0.041 rows=80 loops=1)

  • Output: icas.cascade_id, icas.pair_id, icas.cascade_sequence, icas.cascade_status, icas.cascade_status_msg
13. 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.29..3.59 rows=6 width=8) (actual time=0.004..0.004 rows=1 loops=80)

  • Output: ictx.pair_id, ictx.is_src
  • Index Cond: (ictx.pair_id = icas.pair_id)
  • Heap Fetches: 10
14. 811.395 11,616.444 ↓ 20.5 923 8,922

Materialize (cost=55,948.47..72,974.83 rows=45 width=32) (actual time=1.058..1.302 rows=923 loops=8,922)

  • 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
15. 2.700 10,805.049 ↓ 20.5 923 1

Nested Loop (cost=55,948.47..72,974.60 rows=45 width=32) (actual time=9,436.615..10,805.049 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
16. 708.914 9,433.540 ↑ 2.4 923 1

HashAggregate (cost=55,948.04..55,970.51 rows=2,247 width=24) (actual time=9,431.748..9,433.540 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
17. 8,724.626 8,724.626 ↑ 1.0 741,580 1

Seq Scan on processed.hd_processed_space_repl_ctxs (cost=0.00..50,366.31 rows=744,231 width=24) (actual time=11.181..8,724.626 rows=741,580 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_processed_ (...)
18. 1,368.809 1,368.809 ↑ 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=1.476..1.483 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_replicated_cumu (...)
  • 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)
19. 33.293 470.055 ↓ 33.9 139,572 1

Sort (cost=3,163.09..3,173.39 rows=4,120 width=44) (actual time=437.094..470.055 rows=139,572 loops=1)

  • Output: ctx.state, ctx.pair_id, (ROW(ctx.state, ctx.pair_id))
  • Sort Key: ctx.pair_id
  • Sort Method: quicksort Memory: 78kB
20. 436.762 436.762 ↑ 10.9 378 1

Seq Scan on config.cd_cfg_repl_ctxs ctx (cost=0.00..2,915.71 rows=4,120 width=44) (actual time=0.021..436.762 rows=378 loops=1)

  • Output: ctx.state, ctx.pair_id, ROW(ctx.state, ctx.pair_id)
  • Filter: (ctx.is_src = 1)
  • Rows Removed by Filter: 508
21. 456.242 460.945 ↓ 377.9 2,092,468 1

Sort (cost=3,259.97..3,273.81 rows=5,537 width=12) (actual time=5.014..460.945 rows=2,092,468 loops=1)

  • Output: ctx_1.state, ctx_1.pair_id
  • Sort Key: ctx_1.pair_id
  • Sort Method: quicksort Memory: 48kB
22. 4.703 4.703 ↑ 10.9 508 1

Seq Scan on config.cd_cfg_repl_ctxs ctx_1 (cost=0.00..2,915.71 rows=5,537 width=12) (actual time=0.015..4.703 rows=508 loops=1)

  • Output: ctx_1.state, ctx_1.pair_id
  • Filter: (ctx_1.is_src = 0)
  • Rows Removed by Filter: 378
Planning time : 72.734 ms
Execution time : 18,010.057 ms