explain.depesz.com

PostgreSQL's explain analyze made readable

Result: eK8p

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 6,868.290 137,942.622 ↑ 2.4 8,874 1

Unique (cost=105,757.03..108,234.76 rows=21,087 width=752) (actual time=127,196.972..137,942.622 rows=8,874 loops=1)

  • Output: l_1.pair_id, l_1.system_id, l_1.ctx_id, l_1.epoch, l_1.source_host, l_1.source_path, l_1.processed_status, l_1.processed_status_msg, r.system_id, r.ctx_id, r.epoch, l_1.dest_host, l_1.dest_path, r.processed_status, r.processed_status_msg, l_1.pai (...)
2. 117,110.255 131,074.332 ↓ 99.2 2,092,446 1

Sort (cost=105,757.03..105,809.75 rows=21,087 width=752) (actual time=127,196.969..131,074.332 rows=2,092,446 loops=1)

  • Output: l_1.pair_id, l_1.system_id, l_1.ctx_id, l_1.epoch, l_1.source_host, l_1.source_path, l_1.processed_status, l_1.processed_status_msg, r.system_id, r.ctx_id, r.epoch, l_1.dest_host, l_1.dest_path, r.processed_status, r.processed_status_msg, l (...)
  • Sort Key: l_1.pair_id, l_1.system_id, l_1.ctx_id, l_1.epoch, l_1.source_host, l_1.source_path, l_1.processed_status, l_1.processed_status_msg, r.system_id, r.ctx_id, r.epoch, l_1.dest_host, l_1.dest_path, r.processed_status, r.processed_status_msg, (...)
  • Sort Method: external merge Disk: 1059144kB
3. 3,038.621 13,964.077 ↓ 99.2 2,092,446 1

Hash Left Join (cost=79,646.03..97,249.56 rows=21,087 width=752) (actual time=8,828.295..13,964.077 rows=2,092,446 loops=1)

  • Output: l_1.pair_id, l_1.system_id, l_1.ctx_id, l_1.epoch, l_1.source_host, l_1.source_path, l_1.processed_status, l_1.processed_status_msg, r.system_id, r.ctx_id, r.epoch, l_1.dest_host, l_1.dest_path, r.processed_status, r.processed_status_ (...)
  • Hash Cond: (l_1.pair_id = ctx_1.pair_id)
4. 141.515 10,923.966 ↓ 16.8 139,572 1

Hash Left Join (cost=79,051.68..96,091.50 rows=8,302 width=752) (actual time=8,826.782..10,923.966 rows=139,572 loops=1)

  • Output: l_1.pair_id, l_1.system_id, l_1.ctx_id, l_1.epoch, l_1.source_host, l_1.source_path, l_1.processed_status, l_1.processed_status_msg, r.system_id, r.ctx_id, r.epoch, l_1.dest_host, l_1.dest_path, r.processed_status, r.processed_s (...)
  • Hash Cond: (l_1.pair_id = ctx.pair_id)
5. 10.740 10,779.130 ↓ 2.0 8,874 1

Hash Left Join (cost=78,460.26..95,230.41 rows=4,392 width=716) (actual time=8,823.446..10,779.130 rows=8,874 loops=1)

  • Output: l_1.pair_id, l_1.system_id, l_1.ctx_id, l_1.epoch, l_1.source_host, l_1.source_path, l_1.processed_status, l_1.processed_status_msg, r.system_id, r.ctx_id, r.epoch, l_1.dest_host, l_1.dest_path, r.processed_status, r.proce (...)
  • Hash Cond: (l_1.pair_id = cascades.pair_id)
6. 72.880 10,767.572 ↓ 2.0 8,874 1

Hash Right Join (cost=78,166.14..94,911.04 rows=4,392 width=712) (actual time=8,822.608..10,767.572 rows=8,874 loops=1)

  • Output: l_1.pair_id, l_1.system_id, l_1.ctx_id, l_1.epoch, l_1.source_host, l_1.source_path, l_1.processed_status, l_1.processed_status_msg, r.system_id, r.ctx_id, r.epoch, l_1.dest_host, l_1.dest_path, r.processed_status, r (...)
  • Hash Cond: ((hd_processed_space_repl_ctxs.system_id = l_1.system_id) AND (hd_processed_space_repl_ctxs.ctx_id = l_1.ctx_id))
7. 2.447 10,034.129 ↓ 20.1 924 1

Nested Loop (cost=65,676.08..82,370.22 rows=46 width=32) (actual time=8,123.452..10,034.129 rows=924 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. 545.507 8,123.622 ↑ 2.4 924 1

HashAggregate (cost=65,675.66..65,698.13 rows=2,247 width=24) (actual time=8,122.019..8,123.622 rows=924 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. 561.543 7,578.115 ↑ 1.0 732,484 1

Hash Semi Join (cost=1,077.82..60,181.14 rows=732,603 width=24) (actual time=36.651..7,578.115 rows=732,484 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.system_id)
10. 6,997.063 6,997.063 ↑ 1.0 732,484 1

Seq Scan on processed.hd_processed_space_repl_ctxs (cost=0.00..49,030.03 rows=732,603 width=24) (actual time=17.106..6,997.063 rows=732,484 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.ep (...)
11. 0.043 19.509 ↑ 6.5 150 1

Hash (cost=1,065.70..1,065.70 rows=969 width=8) (actual time=19.509..19.509 rows=150 loops=1)

  • Output: n.system_id
  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
12. 0.478 19.466 ↑ 6.5 150 1

HashAggregate (cost=1,046.32..1,056.01 rows=969 width=8) (actual time=19.343..19.466 rows=150 loops=1)

  • Output: n.system_id
  • Group Key: n.system_id
13. 15.235 18.988 ↑ 6.5 150 1

Hash Right Join (cost=744.22..1,034.21 rows=969 width=7,958) (actual time=3.958..18.988 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), N (...)
  • Hash Cond: (sc.system_id = n.system_id)
14. 0.009 0.009 ↓ 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.009..0.009 rows=0 loops=1)

  • Output: sc.system_id, sc.status
15. 0.083 3.744 ↓ 1.6 150 1

Hash (cost=743.03..743.03 rows=95 width=16) (actual time=3.744..3.744 rows=150 loops=1)

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

Hash Left Join (cost=179.00..743.03 rows=95 width=16) (actual time=0.975..3.661 rows=150 loops=1)

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

Hash Left Join (cost=166.73..729.47 rows=95 width=16) (actual time=0.667..3.237 rows=150 loops=1)

  • Output: n.system_id, s.id
  • Hash Cond: (n.system_id = f.system_id)
18. 0.115 2.972 ↓ 1.6 150 1

Hash Left Join (cost=151.35..712.80 rows=95 width=16) (actual time=0.496..2.972 rows=150 loops=1)

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

Seq Scan on config.cfg_nodes n (cost=0.00..560.13 rows=95 width=20) (actual time=0.052..2.424 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 (...)
  • Rows Removed by Filter: 1
20. 0.062 0.433 ↑ 1.0 151 1

Hash (cost=149.47..149.47 rows=151 width=8) (actual time=0.433..0.433 rows=151 loops=1)

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

Index Only Scan using cfg_systems_pkey on config.cfg_systems s (cost=0.27..149.47 rows=151 width=8) (actual time=0.015..0.371 rows=151 loops=1)

  • Output: s.id
  • Heap Fetches: 247
22. 0.049 0.160 ↑ 1.0 150 1

Hash (cost=13.50..13.50 rows=150 width=8) (actual time=0.160..0.160 rows=150 loops=1)

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

Seq Scan on config.cd_cfg_systems_features_status f (cost=0.00..13.50 rows=150 width=8) (actual time=0.011..0.111 rows=150 loops=1)

  • Output: f.system_id
24. 0.060 0.297 ↑ 1.0 150 1

Hash (cost=10.39..10.39 rows=150 width=8) (actual time=0.297..0.297 rows=150 loops=1)

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

Index Only Scan using cd_cfg_collections_system_id_idx on config.cd_cfg_collections l (cost=0.14..10.39 rows=150 width=8) (actual time=0.016..0.237 rows=150 loops=1)

  • Output: l.system_id
  • Heap Fetches: 150
26. 1,908.060 1,908.060 ↑ 1.0 1 924

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.40 rows=1 width=40) (actual time=1.897..2.065 rows=1 loops=924)

  • 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_rep (...)
  • 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)
27. 24.895 660.563 ↓ 2.0 8,874 1

Hash (cost=12,424.18..12,424.18 rows=4,392 width=696) (actual time=660.563..660.563 rows=8,874 loops=1)

  • Output: l_1.pair_id, l_1.system_id, l_1.ctx_id, l_1.epoch, l_1.source_host, l_1.source_path, l_1.processed_status, l_1.processed_status_msg, r.system_id, r.ctx_id, r.epoch, l_1.dest_host, l_1.dest_path, r.processed_sta (...)
  • Buckets: 8192 (originally 8192) Batches: 2 (originally 1) Memory Usage: 4033kB
28. 15.663 635.668 ↓ 2.0 8,874 1

Hash Semi Join (cost=11,320.02..12,424.18 rows=4,392 width=696) (actual time=591.767..635.668 rows=8,874 loops=1)

  • Output: l_1.pair_id, l_1.system_id, l_1.ctx_id, l_1.epoch, l_1.source_host, l_1.source_path, l_1.processed_status, l_1.processed_status_msg, r.system_id, r.ctx_id, r.epoch, l_1.dest_host, l_1.dest_path, r.process (...)
  • Hash Cond: (l_1.system_id = n_1.system_id)
29. 18.334 461.426 ↓ 1.0 8,922 1

Unique (cost=10,242.20..11,186.59 rows=8,785 width=696) (actual time=433.167..461.426 rows=8,922 loops=1)

  • Output: l_1.pair_id, l_1.system_id, l_1.ctx_id, l_1.epoch, l_1.source_host, l_1.source_path, l_1.processed_status, l_1.processed_status_msg, r.system_id, r.ctx_id, r.epoch, l_1.dest_host, l_1.dest_path, r.p (...)
30. 92.287 443.092 ↓ 1.0 8,922 1

Sort (cost=10,242.20..10,264.16 rows=8,785 width=696) (actual time=433.163..443.092 rows=8,922 loops=1)

  • Output: l_1.pair_id, l_1.system_id, l_1.ctx_id, l_1.epoch, l_1.source_host, l_1.source_path, l_1.processed_status, l_1.processed_status_msg, r.system_id, r.ctx_id, r.epoch, l_1.dest_host, l_1.dest_pat (...)
  • Sort Key: l_1.pair_id, l_1.system_id, l_1.ctx_id, l_1.epoch, l_1.source_host, l_1.source_path, l_1.processed_status, l_1.processed_status_msg, r.system_id, r.ctx_id, r.epoch, l_1.dest_host, l_1.dest_p (...)
  • Sort Method: external merge Disk: 4296kB
31. 1.272 350.805 ↓ 1.0 8,922 1

Append (cost=1,297.68..6,961.25 rows=8,785 width=696) (actual time=82.066..350.805 rows=8,922 loops=1)

32. 14.161 322.303 ↓ 1.0 8,848 1

Hash Join (cost=1,297.68..3,182.10 rows=8,473 width=3,808) (actual time=82.064..322.303 rows=8,848 loops=1)

  • Output: l_1.pair_id, l_1.system_id, l_1.ctx_id, l_1.epoch, l_1.source_host, l_1.source_path, l_1.processed_status, l_1.processed_status_msg, r.system_id, r.ctx_id, r.epoch, l_1.dest_host, (...)
  • Hash Cond: (r.pair_id = l_1.pair_id)
  • Join Filter: (l_1.id <> r.id)
  • Rows Removed by Join Filter: 378
33. 0.637 226.921 ↑ 1.0 886 1

Hash Left Join (cost=34.70..1,775.42 rows=886 width=1,787) (actual time=0.799..226.921 rows=886 loops=1)

  • Output: r.system_id, r.ctx_id, r.epoch, r.processed_status, r.processed_status_msg, r.pair_id, r.id, cctu2.id, cctu2.uuid, cctu2.name, cctu2.state, cct2.uuid, cct2.name, cct2.state, (...)
  • Hash Cond: (cctu2.tenant_id = cct2.id)
34. 1.845 226.251 ↑ 1.0 886 1

Hash Left Join (cost=33.31..1,762.51 rows=886 width=1,125) (actual time=0.753..226.251 rows=886 loops=1)

  • Output: r.system_id, r.ctx_id, r.epoch, r.processed_status, r.processed_status_msg, r.pair_id, r.id, cctu2.id, cctu2.uuid, cctu2.name, cctu2.state, cctu2.tenant_id
  • Hash Cond: (r.tenant_unit_id = cctu2.id)
35. 223.680 223.680 ↑ 1.0 886 1

Seq Scan on config.cd_cfg_repl_ctxs r (cost=0.00..1,721.86 rows=886 width=1,080) (actual time=0.008..223.680 rows=886 loops=1)

  • Output: r.id, r.epoch, r.system_id, r.ctx_id, r.source_host, r.source_path, r.dest_host, r.dest_path, r.is_src, r.repl_scheme, r.propagate_retention_lock, r.delta_enabled (...)
36. 0.310 0.726 ↑ 1.1 554 1

Hash (cost=25.66..25.66 rows=612 width=53) (actual time=0.726..0.726 rows=554 loops=1)

  • Output: cctu2.id, cctu2.uuid, cctu2.name, cctu2.state, cctu2.tenant_id
  • Buckets: 1024 Batches: 1 Memory Usage: 56kB
37. 0.416 0.416 ↑ 1.1 554 1

Seq Scan on config.cd_cfg_tenant_units cctu2 (cost=0.00..25.66 rows=612 width=53) (actual time=0.013..0.416 rows=554 loops=1)

  • Output: cctu2.id, cctu2.uuid, cctu2.name, cctu2.state, cctu2.tenant_id
  • Filter: (cctu2.id <> 0)
  • Rows Removed by Filter: 1
38. 0.011 0.033 ↑ 1.0 15 1

Hash (cost=1.20..1.20 rows=15 width=670) (actual time=0.033..0.033 rows=15 loops=1)

  • Output: cct2.uuid, cct2.name, cct2.state, cct2.id
  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
39. 0.022 0.022 ↑ 1.0 15 1

Seq Scan on config.cd_cfg_tenants cct2 (cost=0.00..1.20 rows=15 width=670) (actual time=0.010..0.022 rows=15 loops=1)

  • Output: cct2.uuid, cct2.name, cct2.state, cct2.id
  • Filter: (cct2.id <> 0)
  • Rows Removed by Filter: 1
40. 0.703 81.221 ↑ 1.0 378 1

Hash (cost=1,258.25..1,258.25 rows=378 width=2,045) (actual time=81.221..81.221 rows=378 loops=1)

  • Output: l_1.pair_id, l_1.system_id, l_1.ctx_id, l_1.epoch, l_1.source_host, l_1.source_path, l_1.processed_status, l_1.processed_status_msg, l_1.dest_host, l_1.dest_path, l_1.pair_st (...)
  • Buckets: 1024 Batches: 1 Memory Usage: 176kB
41. 0.400 80.518 ↑ 1.0 378 1

Hash Left Join (cost=337.90..1,258.25 rows=378 width=2,045) (actual time=75.642..80.518 rows=378 loops=1)

  • Output: l_1.pair_id, l_1.system_id, l_1.ctx_id, l_1.epoch, l_1.source_host, l_1.source_path, l_1.processed_status, l_1.processed_status_msg, l_1.dest_host, l_1.dest_path, l_1.p (...)
  • Hash Cond: (cctu1.tenant_id = cct1.id)
42. 0.841 80.084 ↑ 1.0 378 1

Hash Left Join (cost=336.52..1,251.95 rows=378 width=1,383) (actual time=75.593..80.084 rows=378 loops=1)

  • Output: l_1.pair_id, l_1.system_id, l_1.ctx_id, l_1.epoch, l_1.source_host, l_1.source_path, l_1.processed_status, l_1.processed_status_msg, l_1.dest_host, l_1.dest_path, (...)
  • Hash Cond: (l_1.tenant_unit_id = cctu1.id)
43. 3.867 78.495 ↑ 1.0 378 1

Bitmap Heap Scan on config.cd_cfg_repl_ctxs l_1 (cost=303.20..1,215.50 rows=378 width=1,338) (actual time=74.818..78.495 rows=378 loops=1)

  • Output: l_1.id, l_1.epoch, l_1.system_id, l_1.ctx_id, l_1.source_host, l_1.source_path, l_1.dest_host, l_1.dest_path, l_1.is_src, l_1.repl_scheme, l_1.propagate_ret (...)
  • Recheck Cond: (l_1.is_src = 1)
  • Heap Blocks: exact=580
44. 74.628 74.628 ↓ 12.5 4,725 1

Bitmap Index Scan on cd_cfg_repl_ctxs_is_src_idx (cost=0.00..303.11 rows=378 width=0) (actual time=74.628..74.628 rows=4,725 loops=1)

  • Index Cond: (l_1.is_src = 1)
45. 0.308 0.748 ↑ 1.1 554 1

Hash (cost=25.66..25.66 rows=612 width=53) (actual time=0.748..0.748 rows=554 loops=1)

  • Output: cctu1.id, cctu1.uuid, cctu1.name, cctu1.state, cctu1.tenant_id
  • Buckets: 1024 Batches: 1 Memory Usage: 56kB
46. 0.440 0.440 ↑ 1.1 554 1

Seq Scan on config.cd_cfg_tenant_units cctu1 (cost=0.00..25.66 rows=612 width=53) (actual time=0.019..0.440 rows=554 loops=1)

  • Output: cctu1.id, cctu1.uuid, cctu1.name, cctu1.state, cctu1.tenant_id
  • Filter: (cctu1.id <> 0)
  • Rows Removed by Filter: 1
47. 0.018 0.034 ↑ 1.0 15 1

Hash (cost=1.20..1.20 rows=15 width=670) (actual time=0.034..0.034 rows=15 loops=1)

  • Output: cct1.uuid, cct1.name, cct1.state, cct1.id
  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
48. 0.016 0.016 ↑ 1.0 15 1

Seq Scan on config.cd_cfg_tenants cct1 (cost=0.00..1.20 rows=15 width=670) (actual time=0.009..0.016 rows=15 loops=1)

  • Output: cct1.uuid, cct1.name, cct1.state, cct1.id
  • Filter: (cct1.id <> 0)
  • Rows Removed by Filter: 1
49. 0.053 13.566 ↑ 6.0 26 1

Subquery Scan on *SELECT* 2 (cost=1,844.09..1,847.21 rows=156 width=696) (actual time=13.144..13.566 rows=26 loops=1)

  • Output: "*SELECT* 2".pair_id, "*SELECT* 2".src_system_id, "*SELECT* 2".src_ctx_id, "*SELECT* 2".src_epoch, "*SELECT* 2".src_host, "*SELECT* 2".src_path, "*SELECT* 2".src_processed_status, (...)
50. 7.029 13.513 ↑ 6.0 26 1

HashAggregate (cost=1,844.09..1,845.65 rows=156 width=696) (actual time=13.137..13.513 rows=26 loops=1)

  • Output: ccrc.pair_id, min(ccrc.system_id), min(ccrc.ctx_id), min(ccrc.epoch), min((ccrc.source_host)::text), min((ccrc.source_path)::text), min((ccrc.processed_status)::text), min((c (...)
  • Group Key: ccrc.pair_id
  • Filter: ((count(*) = 1) AND (sum(ccrc.is_src) = 1))
  • Rows Removed by Filter: 130
51. 0.745 6.484 ↑ 1.0 886 1

Hash Left Join (cost=34.70..1,775.42 rows=886 width=2,041) (actual time=0.814..6.484 rows=886 loops=1)

  • Output: ccrc.pair_id, ccrc.system_id, ccrc.ctx_id, ccrc.epoch, ccrc.source_host, ccrc.source_path, ccrc.processed_status, ccrc.processed_status_msg, ccrc.dest_host, ccrc.dest_p (...)
  • Hash Cond: (cctu1_1.tenant_id = cct1_1.id)
52. 1.775 5.709 ↑ 1.0 886 1

Hash Left Join (cost=33.31..1,762.51 rows=886 width=1,379) (actual time=0.768..5.709 rows=886 loops=1)

  • Output: ccrc.pair_id, ccrc.system_id, ccrc.ctx_id, ccrc.epoch, ccrc.source_host, ccrc.source_path, ccrc.processed_status, ccrc.processed_status_msg, ccrc.dest_host, ccrc. (...)
  • Hash Cond: (ccrc.tenant_unit_id = cctu1_1.id)
53. 3.210 3.210 ↑ 1.0 886 1

Seq Scan on config.cd_cfg_repl_ctxs ccrc (cost=0.00..1,721.86 rows=886 width=1,334) (actual time=0.007..3.210 rows=886 loops=1)

  • Output: ccrc.id, ccrc.epoch, ccrc.system_id, ccrc.ctx_id, ccrc.source_host, ccrc.source_path, ccrc.dest_host, ccrc.dest_path, ccrc.is_src, ccrc.repl_scheme, ccrc.pr (...)
54. 0.324 0.724 ↑ 1.1 554 1

Hash (cost=25.66..25.66 rows=612 width=53) (actual time=0.724..0.724 rows=554 loops=1)

  • Output: cctu1_1.id, cctu1_1.uuid, cctu1_1.name, cctu1_1.state, cctu1_1.tenant_id
  • Buckets: 1024 Batches: 1 Memory Usage: 56kB
55. 0.400 0.400 ↑ 1.1 554 1

Seq Scan on config.cd_cfg_tenant_units cctu1_1 (cost=0.00..25.66 rows=612 width=53) (actual time=0.015..0.400 rows=554 loops=1)

  • Output: cctu1_1.id, cctu1_1.uuid, cctu1_1.name, cctu1_1.state, cctu1_1.tenant_id
  • Filter: (cctu1_1.id <> 0)
  • Rows Removed by Filter: 1
56. 0.014 0.030 ↑ 1.0 15 1

Hash (cost=1.20..1.20 rows=15 width=670) (actual time=0.030..0.030 rows=15 loops=1)

  • Output: cct1_1.uuid, cct1_1.name, cct1_1.state, cct1_1.id
  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
57. 0.016 0.016 ↑ 1.0 15 1

Seq Scan on config.cd_cfg_tenants cct1_1 (cost=0.00..1.20 rows=15 width=670) (actual time=0.008..0.016 rows=15 loops=1)

  • Output: cct1_1.uuid, cct1_1.name, cct1_1.state, cct1_1.id
  • Filter: (cct1_1.id <> 0)
  • Rows Removed by Filter: 1
58. 0.095 13.664 ↑ 3.2 48 1

Subquery Scan on *SELECT* 3 (cost=1,844.09..1,847.21 rows=156 width=696) (actual time=13.126..13.664 rows=48 loops=1)

  • Output: "*SELECT* 3".pair_id, NULL::bigint, NULL::bigint, NULL::bigint, "*SELECT* 3".src_host, "*SELECT* 3".src_path, NULL::character varying, NULL::character varying, "*SELECT* 3".dst_sys (...)
59. 7.041 13.569 ↑ 3.2 48 1

HashAggregate (cost=1,844.09..1,845.65 rows=156 width=696) (actual time=13.119..13.569 rows=48 loops=1)

  • Output: ccrc_1.pair_id, NULL::bigint, NULL::bigint, NULL::bigint, min((ccrc_1.source_host)::text), min((ccrc_1.source_path)::text), NULL::character varying, NULL::character varying, (...)
  • Group Key: ccrc_1.pair_id
  • Filter: ((count(*) = 1) AND (sum(ccrc_1.is_src) = 0))
  • Rows Removed by Filter: 108
60. 0.752 6.528 ↑ 1.0 886 1

Hash Left Join (cost=34.70..1,775.42 rows=886 width=2,041) (actual time=0.829..6.528 rows=886 loops=1)

  • Output: ccrc_1.pair_id, ccrc_1.source_host, ccrc_1.source_path, ccrc_1.system_id, ccrc_1.ctx_id, ccrc_1.epoch, ccrc_1.dest_host, ccrc_1.dest_path, ccrc_1.processed_status, ccrc (...)
  • Hash Cond: (cctu2_1.tenant_id = cct2_1.id)
61. 1.767 5.733 ↑ 1.0 886 1

Hash Left Join (cost=33.31..1,762.51 rows=886 width=1,379) (actual time=0.771..5.733 rows=886 loops=1)

  • Output: ccrc_1.pair_id, ccrc_1.source_host, ccrc_1.source_path, ccrc_1.system_id, ccrc_1.ctx_id, ccrc_1.epoch, ccrc_1.dest_host, ccrc_1.dest_path, ccrc_1.processed_status (...)
  • Hash Cond: (ccrc_1.tenant_unit_id = cctu2_1.id)
62. 3.229 3.229 ↑ 1.0 886 1

Seq Scan on config.cd_cfg_repl_ctxs ccrc_1 (cost=0.00..1,721.86 rows=886 width=1,334) (actual time=0.008..3.229 rows=886 loops=1)

  • Output: ccrc_1.id, ccrc_1.epoch, ccrc_1.system_id, ccrc_1.ctx_id, ccrc_1.source_host, ccrc_1.source_path, ccrc_1.dest_host, ccrc_1.dest_path, ccrc_1.is_src, ccrc_1. (...)
63. 0.307 0.737 ↑ 1.1 554 1

Hash (cost=25.66..25.66 rows=612 width=53) (actual time=0.737..0.737 rows=554 loops=1)

  • Output: cctu2_1.id, cctu2_1.uuid, cctu2_1.name, cctu2_1.state, cctu2_1.tenant_id
  • Buckets: 1024 Batches: 1 Memory Usage: 56kB
64. 0.430 0.430 ↑ 1.1 554 1

Seq Scan on config.cd_cfg_tenant_units cctu2_1 (cost=0.00..25.66 rows=612 width=53) (actual time=0.012..0.430 rows=554 loops=1)

  • Output: cctu2_1.id, cctu2_1.uuid, cctu2_1.name, cctu2_1.state, cctu2_1.tenant_id
  • Filter: (cctu2_1.id <> 0)
  • Rows Removed by Filter: 1
65. 0.021 0.043 ↑ 1.0 15 1

Hash (cost=1.20..1.20 rows=15 width=670) (actual time=0.043..0.043 rows=15 loops=1)

  • Output: cct2_1.uuid, cct2_1.name, cct2_1.state, cct2_1.id
  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
66. 0.022 0.022 ↑ 1.0 15 1

Seq Scan on config.cd_cfg_tenants cct2_1 (cost=0.00..1.20 rows=15 width=670) (actual time=0.009..0.022 rows=15 loops=1)

  • Output: cct2_1.uuid, cct2_1.name, cct2_1.state, cct2_1.id
  • Filter: (cct2_1.id <> 0)
  • Rows Removed by Filter: 1
67. 0.035 158.579 ↑ 6.5 150 1

Hash (cost=1,065.70..1,065.70 rows=969 width=8) (actual time=158.579..158.579 rows=150 loops=1)

  • Output: n_1.system_id
  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
68. 0.490 158.544 ↑ 6.5 150 1

HashAggregate (cost=1,046.32..1,056.01 rows=969 width=8) (actual time=158.425..158.544 rows=150 loops=1)

  • Output: n_1.system_id
  • Group Key: n_1.system_id
69. 15.420 158.054 ↑ 6.5 150 1

Hash Right Join (cost=744.22..1,034.21 rows=969 width=7,958) (actual time=143.202..158.054 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::c (...)
  • Hash Cond: (sc_1.system_id = n_1.system_id)
70. 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
71. 0.147 142.630 ↓ 1.6 150 1

Hash (cost=743.03..743.03 rows=95 width=16) (actual time=142.630..142.630 rows=150 loops=1)

  • Output: n_1.system_id, s_1.id
  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
72. 0.182 142.483 ↓ 1.6 150 1

Hash Left Join (cost=179.00..743.03 rows=95 width=16) (actual time=1.131..142.483 rows=150 loops=1)

  • Output: n_1.system_id, s_1.id
  • Hash Cond: (n_1.system_id = l_2.system_id)
73. 0.157 141.997 ↓ 1.6 150 1

Hash Left Join (cost=166.73..729.47 rows=95 width=16) (actual time=0.811..141.997 rows=150 loops=1)

  • Output: n_1.system_id, s_1.id
  • Hash Cond: (n_1.system_id = f_1.system_id)
74. 0.196 141.666 ↓ 1.6 150 1

Hash Left Join (cost=151.35..712.80 rows=95 width=16) (actual time=0.621..141.666 rows=150 loops=1)

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

Seq Scan on config.cfg_nodes n_1 (cost=0.00..560.13 rows=95 width=20) (actual time=0.035..140.898 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_1.system_id (...)
  • Rows Removed by Filter: 1
76. 0.083 0.572 ↑ 1.0 151 1

Hash (cost=149.47..149.47 rows=151 width=8) (actual time=0.572..0.572 rows=151 loops=1)

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

Index Only Scan using cfg_systems_pkey on config.cfg_systems s_1 (cost=0.27..149.47 rows=151 width=8) (actual time=0.026..0.489 rows=151 loops=1)

  • Output: s_1.id
  • Heap Fetches: 280
78. 0.062 0.174 ↑ 1.0 150 1

Hash (cost=13.50..13.50 rows=150 width=8) (actual time=0.174..0.174 rows=150 loops=1)

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

Seq Scan on config.cd_cfg_systems_features_status f_1 (cost=0.00..13.50 rows=150 width=8) (actual time=0.010..0.112 rows=150 loops=1)

  • Output: f_1.system_id
80. 0.059 0.304 ↑ 1.0 150 1

Hash (cost=10.39..10.39 rows=150 width=8) (actual time=0.304..0.304 rows=150 loops=1)

  • Output: l_2.system_id
  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
81. 0.245 0.245 ↑ 1.0 150 1

Index Only Scan using cd_cfg_collections_system_id_idx on config.cd_cfg_collections l_2 (cost=0.14..10.39 rows=150 width=8) (actual time=0.016..0.245 rows=150 loops=1)

  • Output: l_2.system_id
  • Heap Fetches: 150
82. 0.024 0.818 ↑ 1.0 40 1

Hash (cost=293.62..293.62 rows=40 width=12) (actual time=0.818..0.818 rows=40 loops=1)

  • Output: cascades.cascade_status, cascades.pair_id
  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
83. 0.013 0.794 ↑ 1.0 40 1

Subquery Scan on cascades (cost=292.82..293.62 rows=40 width=12) (actual time=0.773..0.794 rows=40 loops=1)

  • Output: cascades.cascade_status, cascades.pair_id
84. 0.155 0.781 ↑ 1.0 40 1

HashAggregate (cost=292.82..293.22 rows=40 width=12) (actual time=0.771..0.781 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
85. 0.090 0.626 ↑ 2.0 81 1

Nested Loop (cost=0.28..290.00 rows=161 width=12) (actual time=0.042..0.626 rows=81 loops=1)

  • Output: icas.pair_id, icas.cascade_status
86. 0.056 0.056 ↑ 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.021..0.056 rows=80 loops=1)

  • Output: icas.cascade_id, icas.pair_id, icas.cascade_sequence, icas.cascade_status, icas.cascade_status_msg
87. 0.480 0.480 ↑ 6.0 1 80

Index Only Scan using cd_cfg_repl_ctxs_state_pair_id_is_src_idx on config.cd_cfg_repl_ctxs ictx (cost=0.28..3.53 rows=6 width=8) (actual time=0.005..0.006 rows=1 loops=80)

  • Output: ictx.pair_id, ictx.state, ictx.is_src
  • Index Cond: (ictx.pair_id = icas.pair_id)
  • Heap Fetches: 116
88. 0.181 3.321 ↑ 1.0 378 1

Hash (cost=586.70..586.70 rows=378 width=44) (actual time=3.321..3.321 rows=378 loops=1)

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

Index Only Scan using cd_cfg_repl_ctxs_state_pair_id_is_src_idx on config.cd_cfg_repl_ctxs ctx (cost=0.28..586.70 rows=378 width=44) (actual time=0.021..3.140 rows=378 loops=1)

  • Output: ctx.state, ctx.pair_id, ROW(ctx.state, ctx.pair_id)
  • Index Cond: (ctx.is_src = 1)
  • Heap Fetches: 3818
90. 0.234 1.490 ↑ 1.0 508 1

Hash (cost=588.00..588.00 rows=508 width=12) (actual time=1.490..1.490 rows=508 loops=1)

  • Output: ctx_1.state, ctx_1.pair_id
  • Buckets: 1024 Batches: 1 Memory Usage: 32kB
91. 1.256 1.256 ↑ 1.0 508 1

Index Only Scan using cd_cfg_repl_ctxs_state_pair_id_is_src_idx on config.cd_cfg_repl_ctxs ctx_1 (cost=0.28..588.00 rows=508 width=12) (actual time=0.017..1.256 rows=508 loops=1)

  • Output: ctx_1.state, ctx_1.pair_id
  • Index Cond: (ctx_1.is_src = 0)
  • Heap Fetches: 949
Planning time : 32.923 ms
Execution time : 138,268.634 ms