explain.depesz.com

PostgreSQL's explain analyze made readable

Result: F71M

Settings
# exclusive inclusive rows x rows loops node
1. 8,357.055 305,151.216 ↑ 223.8 8,874 1

Unique (cost=2,135,052.21..2,368,385.00 rows=1,985,811 width=687) (actual time=281,786.743..305,151.216 rows=8,874 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 (...)
2. 288,974.581 296,794.161 ↓ 1.1 2,092,446 1

Sort (cost=2,135,052.21..2,140,016.73 rows=1,985,811 width=687) (actual time=281,786.740..296,794.161 rows=2,092,446 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. (...)
  • Sort Key: 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, p (...)
  • Sort Method: external sort Disk: 1058544kB
3. 3,728.861 7,819.580 ↓ 1.1 2,092,446 1

Hash Left Join (cost=87,851.31..115,076.00 rows=1,985,811 width=687) (actual time=3,767.275..7,819.580 rows=2,092,446 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 = ctx_1.pair_id)
4. 206.782 4,084.938 ↓ 1.2 139,572 1

Hash Left Join (cost=86,065.23..88,565.07 rows=114,510 width=687) (actual time=3,761.458..4,084.938 rows=139,572 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.pair_id = ctx.pair_id)
5. 16.744 3,852.277 ↑ 1.0 8,874 1

Hash Left Join (cost=84,660.13..85,637.73 rows=8,874 width=651) (actual time=3,735.557..3,852.277 rows=8,874 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 (...)
  • Hash Cond: (pv.pair_id = cascades.pair_id)
6. 28.490 3,833.259 ↑ 1.0 8,874 1

Hash Left Join (cost=84,370.01..85,291.58 rows=8,874 width=647) (actual time=3,733.259..3,833.259 rows=8,874 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.d (...)
  • 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))
7. 43.784 120.689 ↑ 1.0 8,874 1

Hash Semi Join (cost=657.09..1,445.45 rows=8,874 width=631) (actual time=49.153..120.689 rows=8,874 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 (...)
  • Hash Cond: (pv.src_system_id = n.system_id)
8. 27.783 27.783 ↑ 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.011..27.783 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.ds (...)
9. 0.062 49.122 ↑ 6.5 150 1

Hash (cost=644.98..644.98 rows=969 width=8) (actual time=49.122..49.122 rows=150 loops=1)

  • Output: n.system_id
  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
10. 0.453 49.060 ↑ 6.5 150 1

HashAggregate (cost=625.60..635.29 rows=969 width=8) (actual time=48.966..49.060 rows=150 loops=1)

  • Output: n.system_id
  • Group Key: n.system_id
11. 44.476 48.607 ↑ 6.5 150 1

Hash Right Join (cost=323.50..613.49 rows=969 width=7,958) (actual time=4.597..48.607 rows=150 loops=1)

  • Output: NULL::bigint, NULL::character varying(256), NULL::character varying(256), NULL::character varying(256), NULL::character varying(256), NULL::integer, NULL::character varying(256), NULL::charact (...)
  • Hash Cond: (sc.system_id = n.system_id)
12. 0.006 0.006 ↓ 0.0 0 1

Seq Scan on config.cd_cfg_ha_system_config sc (cost=0.00..30.40 rows=2,040 width=8) (actual time=0.006..0.006 rows=0 loops=1)

  • Output: sc.system_id, sc.status
13. 0.112 4.125 ↓ 1.6 150 1

Hash (cost=322.31..322.31 rows=95 width=16) (actual time=4.125..4.125 rows=150 loops=1)

  • Output: n.system_id, s.id
  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
14. 0.134 4.013 ↓ 1.6 150 1

Hash Left Join (cost=103.26..322.31 rows=95 width=16) (actual time=0.697..4.013 rows=150 loops=1)

  • Output: n.system_id, s.id
  • Hash Cond: (n.system_id = l.system_id)
15. 0.116 3.652 ↓ 1.6 150 1

Hash Left Join (cost=85.89..303.64 rows=95 width=16) (actual time=0.457..3.652 rows=150 loops=1)

  • Output: n.system_id, s.id
  • Hash Cond: (n.system_id = f.system_id)
16. 0.155 3.387 ↓ 1.6 150 1

Hash Left Join (cost=68.40..284.84 rows=95 width=16) (actual time=0.291..3.387 rows=150 loops=1)

  • Output: n.system_id, s.id
  • Hash Cond: (n.system_id = s.id)
17. 2.993 2.993 ↓ 1.6 150 1

Seq Scan on config.cfg_nodes n (cost=0.00..215.14 rows=95 width=20) (actual time=0.037..2.993 rows=150 loops=1)

  • Output: n.system_id, n.tz_id, n.id
  • Filter: ((n.system_id = 5) OR (n.system_id = 7) OR (n.system_id = 8) OR (n.system_id = 9) OR (n.system_id = 10) OR (n.system_id = 11) OR (n.system_id = 13) OR (n.system_i (...)
  • Rows Removed by Filter: 1
18. 0.065 0.239 ↑ 1.0 151 1

Hash (cost=66.51..66.51 rows=151 width=8) (actual time=0.239..0.239 rows=151 loops=1)

  • Output: s.id
  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
19. 0.174 0.174 ↑ 1.0 151 1

Index Only Scan using cfg_systems_pkey on config.cfg_systems s (cost=0.27..66.51 rows=151 width=8) (actual time=0.047..0.174 rows=151 loops=1)

  • Output: s.id
  • Heap Fetches: 38
20. 0.059 0.149 ↑ 1.0 150 1

Hash (cost=15.55..15.55 rows=155 width=8) (actual time=0.149..0.149 rows=150 loops=1)

  • Output: f.system_id
  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
21. 0.090 0.090 ↑ 1.0 150 1

Seq Scan on config.cd_cfg_systems_features_status f (cost=0.00..15.55 rows=155 width=8) (actual time=0.008..0.090 rows=150 loops=1)

  • Output: f.system_id
22. 0.060 0.227 ↑ 1.0 150 1

Hash (cost=15.50..15.50 rows=150 width=8) (actual time=0.227..0.227 rows=150 loops=1)

  • Output: l.system_id
  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
23. 0.167 0.167 ↑ 1.0 150 1

Seq Scan on config.cd_cfg_collections l (cost=0.00..15.50 rows=150 width=8) (actual time=0.021..0.167 rows=150 loops=1)

  • Output: l.system_id
24. 1.039 3,684.080 ↓ 20.5 923 1

Hash (cost=83,712.24..83,712.24 rows=45 width=32) (actual time=3,684.080..3,684.080 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
25. 1.104 3,683.041 ↓ 20.5 923 1

Nested Loop (cost=66,686.11..83,712.24 rows=45 width=32) (actual time=3,655.284..3,683.041 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
26. 629.531 3,656.093 ↑ 2.4 923 1

HashAggregate (cost=66,685.68..66,708.15 rows=2,247 width=24) (actual time=3,655.218..3,656.093 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
27. 665.449 3,026.562 ↑ 1.0 742,465 1

Hash Semi Join (cost=657.09..61,116.82 rows=742,515 width=24) (actual time=25.611..3,026.562 rows=742,465 loops=1)

  • Output: hd_processed_space_repl_ctxs.system_id, hd_processed_space_repl_ctxs.ctx_id, hd_processed_space_repl_ctxs.epoch
  • Hash Cond: (hd_processed_space_repl_ctxs.system_id = n_1.system_id)
28. 2,343.560 2,343.560 ↑ 1.0 742,465 1

Seq Scan on processed.hd_processed_space_repl_ctxs (cost=0.00..50,250.15 rows=742,515 width=24) (actual time=8.030..2,343.560 rows=742,465 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_c (...)
29. 0.052 17.553 ↑ 6.5 150 1

Hash (cost=644.98..644.98 rows=969 width=8) (actual time=17.553..17.553 rows=150 loops=1)

  • Output: n_1.system_id
  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
30. 0.486 17.501 ↑ 6.5 150 1

HashAggregate (cost=625.60..635.29 rows=969 width=8) (actual time=17.401..17.501 rows=150 loops=1)

  • Output: n_1.system_id
  • Group Key: n_1.system_id
31. 14.199 17.015 ↑ 6.5 150 1

Hash Right Join (cost=323.50..613.49 rows=969 width=7,958) (actual time=3.007..17.015 rows=150 loops=1)

  • Output: NULL::bigint, NULL::character varying(256), NULL::character varying(256), NULL::character varying(256), NULL::character varying(256), NULL::integer, NULL::character varying(2 (...)
  • Hash Cond: (sc_1.system_id = n_1.system_id)
32. 0.004 0.004 ↓ 0.0 0 1

Seq Scan on config.cd_cfg_ha_system_config sc_1 (cost=0.00..30.40 rows=2,040 width=8) (actual time=0.004..0.004 rows=0 loops=1)

  • Output: sc_1.system_id, sc_1.status
33. 0.098 2.812 ↓ 1.6 150 1

Hash (cost=322.31..322.31 rows=95 width=16) (actual time=2.812..2.812 rows=150 loops=1)

  • Output: n_1.system_id, s_1.id
  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
34. 0.122 2.714 ↓ 1.6 150 1

Hash Left Join (cost=103.26..322.31 rows=95 width=16) (actual time=0.630..2.714 rows=150 loops=1)

  • Output: n_1.system_id, s_1.id
  • Hash Cond: (n_1.system_id = l_1.system_id)
35. 0.115 2.400 ↓ 1.6 150 1

Hash Left Join (cost=85.89..303.64 rows=95 width=16) (actual time=0.422..2.400 rows=150 loops=1)

  • Output: n_1.system_id, s_1.id
  • Hash Cond: (n_1.system_id = f_1.system_id)
36. 0.110 2.130 ↓ 1.6 150 1

Hash Left Join (cost=68.40..284.84 rows=95 width=16) (actual time=0.253..2.130 rows=150 loops=1)

  • Output: n_1.system_id, s_1.id
  • Hash Cond: (n_1.system_id = s_1.id)
37. 1.850 1.850 ↓ 1.6 150 1

Seq Scan on config.cfg_nodes n_1 (cost=0.00..215.14 rows=95 width=20) (actual time=0.065..1.850 rows=150 loops=1)

  • Output: n_1.system_id, n_1.tz_id, n_1.id
  • Filter: ((n_1.system_id = 5) OR (n_1.system_id = 7) OR (n_1.system_id = 8) OR (n_1.system_id = 9) OR (n_1.system_id = 10) OR (n_1.system_id = 11) OR (n_ (...)
  • Rows Removed by Filter: 1
38. 0.057 0.170 ↑ 1.0 151 1

Hash (cost=66.51..66.51 rows=151 width=8) (actual time=0.170..0.170 rows=151 loops=1)

  • Output: s_1.id
  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
39. 0.113 0.113 ↑ 1.0 151 1

Index Only Scan using cfg_systems_pkey on config.cfg_systems s_1 (cost=0.27..66.51 rows=151 width=8) (actual time=0.013..0.113 rows=151 loops=1)

  • Output: s_1.id
  • Heap Fetches: 33
40. 0.057 0.155 ↑ 1.0 150 1

Hash (cost=15.55..15.55 rows=155 width=8) (actual time=0.155..0.155 rows=150 loops=1)

  • Output: f_1.system_id
  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
41. 0.098 0.098 ↑ 1.0 150 1

Seq Scan on config.cd_cfg_systems_features_status f_1 (cost=0.00..15.55 rows=155 width=8) (actual time=0.009..0.098 rows=150 loops=1)

  • Output: f_1.system_id
42. 0.054 0.192 ↑ 1.0 150 1

Hash (cost=15.50..15.50 rows=150 width=8) (actual time=0.192..0.192 rows=150 loops=1)

  • Output: l_1.system_id
  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
43. 0.138 0.138 ↑ 1.0 150 1

Seq Scan on config.cd_cfg_collections l_1 (cost=0.00..15.50 rows=150 width=8) (actual time=0.007..0.138 rows=150 loops=1)

  • Output: l_1.system_id
44. 25.844 25.844 ↑ 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.027..0.028 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_co (...)
  • 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)
45. 0.024 2.274 ↑ 1.0 40 1

Hash (cost=289.62..289.62 rows=40 width=12) (actual time=2.274..2.274 rows=40 loops=1)

  • Output: cascades.cascade_status, cascades.pair_id
  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
46. 0.017 2.250 ↑ 1.0 40 1

Subquery Scan on cascades (cost=288.82..289.62 rows=40 width=12) (actual time=2.217..2.250 rows=40 loops=1)

  • Output: cascades.cascade_status, cascades.pair_id
47. 0.149 2.233 ↑ 1.0 40 1

HashAggregate (cost=288.82..289.22 rows=40 width=12) (actual time=2.216..2.233 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 (...)
  • Group Key: icas.pair_id
48. 0.128 2.084 ↑ 2.0 81 1

Nested Loop (cost=0.28..286.00 rows=161 width=12) (actual time=0.054..2.084 rows=81 loops=1)

  • Output: icas.pair_id, icas.cascade_status
49. 0.036 0.036 ↑ 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.011..0.036 rows=80 loops=1)

  • Output: icas.cascade_id, icas.pair_id, icas.cascade_sequence, icas.cascade_status, icas.cascade_status_msg
50. 1.920 1.920 ↑ 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..3.48 rows=6 width=8) (actual time=0.024..0.024 rows=1 loops=80)

  • Output: ictx.pair_id, ictx.is_src
  • Index Cond: (ictx.pair_id = icas.pair_id)
  • Heap Fetches: 0
51. 0.229 25.879 ↑ 1.0 378 1

Hash (cost=1,400.38..1,400.38 rows=378 width=44) (actual time=25.879..25.879 rows=378 loops=1)

  • Output: ctx.state, ctx.pair_id, (ROW(ctx.state, ctx.pair_id))
  • Buckets: 1024 Batches: 1 Memory Usage: 40kB
52. 25.268 25.650 ↑ 1.0 378 1

Bitmap Heap Scan on config.cd_cfg_repl_ctxs ctx (cost=355.20..1,400.38 rows=378 width=44) (actual time=0.419..25.650 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=84
53. 0.382 0.382 ↑ 1.0 378 1

Bitmap Index Scan on cd_cfg_repl_ctxs_is_src_idx (cost=0.00..355.11 rows=378 width=0) (actual time=0.382..0.382 rows=378 loops=1)

  • Index Cond: (ctx.is_src = 1)
54. 0.379 5.781 ↑ 1.0 508 1

Hash (cost=1,779.73..1,779.73 rows=508 width=12) (actual time=5.781..5.781 rows=508 loops=1)

  • Output: ctx_1.state, ctx_1.pair_id
  • Buckets: 1024 Batches: 1 Memory Usage: 32kB
55. 4.882 5.402 ↑ 1.0 508 1

Bitmap Heap Scan on config.cd_cfg_repl_ctxs ctx_1 (cost=480.21..1,779.73 rows=508 width=12) (actual time=0.571..5.402 rows=508 loops=1)

  • Output: ctx_1.state, ctx_1.pair_id
  • Recheck Cond: (ctx_1.is_src = 0)
  • Heap Blocks: exact=176
56. 0.520 0.520 ↑ 1.0 508 1

Bitmap Index Scan on cd_cfg_repl_ctxs_is_src_idx (cost=0.00..480.08 rows=508 width=0) (actual time=0.520..0.520 rows=508 loops=1)

  • Index Cond: (ctx_1.is_src = 0)
Planning time : 46.803 ms
Execution time : 305,303.174 ms