explain.depesz.com

PostgreSQL's explain analyze made readable

Result: m1hi

Settings
# exclusive inclusive rows x rows loops node
1. 0.030 35,137.141 ↓ 2.0 2 1

Sort (cost=2,434.77..2,434.78 rows=1 width=841) (actual time=35,137.135..35,137.141 rows=2 loops=1)

  • Output: ((((COALESCE(t.id, 0))::text || '_'::text) || (COALESCE(tu1.id, 0))::text)), tu1.id, t.id, t.uuid, tu1.name, tu1.system_id, s.name, s.status, s.state, s.type, tu1.state, tu1.status, tu1.uuid, (CASE WHEN (tu1.self_service_enabled = 1) THEN true EL (...)
  • Sort Key: t.id, tu1.id
  • Sort Method: quicksort Memory: 25kB
2. 0.320 35,137.111 ↓ 2.0 2 1

Nested Loop Left Join (cost=1,097.21..2,434.76 rows=1 width=841) (actual time=12,151.200..35,137.111 rows=2 loops=1)

  • Output: (((COALESCE(t.id, 0))::text || '_'::text) || (COALESCE(tu1.id, 0))::text), tu1.id, t.id, t.uuid, tu1.name, tu1.system_id, s.name, s.status, s.state, s.type, tu1.state, tu1.status, tu1.uuid, CASE WHEN (tu1.self_service_enabled = 1) THEN true (...)
  • Join Filter: (tu1.system_id = n.system_id)
  • Rows Removed by Join Filter: 298
3. 4.702 35,105.969 ↓ 2.0 2 1

Hash Full Join (cost=663.64..1,530.90 rows=1 width=1,215) (actual time=12,146.393..35,105.969 rows=2 loops=1)

  • Output: t.id, t.uuid, t.name, t.state, t.admin_name, t.admin_email, tu1.id, tu1.name, tu1.system_id, tu1.state, tu1.status, tu1.uuid, tu1.self_service_enabled, tu1.security_mode, tu1.created_from_ddmc, tu1.admin_name, tu1.admin_email
  • Hash Cond: (tu1.tenant_id = t.id)
  • Filter: (((t.id IS NULL) OR (t.id = 0)) AND CASE WHEN (t.id IS NOT NULL) THEN (t.id >= 0) ELSE true END AND CASE WHEN (tu1.id IS NOT NULL) THEN (tu1.id > 0) ELSE (t.id <> 0) END)
  • Rows Removed by Filter: 1102
4. 5.086 35,101.092 ↓ 2.0 1,050 1

Subquery Scan on tu1 (cost=658.92..1,513.70 rows=526 width=627) (actual time=60.793..35,101.092 rows=1,050 loops=1)

  • Output: tu1.id, tu1.uuid, tu1.tenant_id, tu1.epoch, tu1.system_id, tu1.name, tu1.created_from_ddmc, tu1.state, tu1.self_service_enabled, tu1.admin_name, tu1.admin_email, tu1.dataset_size_in_gib, tu1.expected_growth_size_in_gib, tu1.grow (...)
  • Filter: CASE WHEN (tu1.system_id IS NULL) THEN true ELSE (hashed SubPlan 1) END
  • Rows Removed by Filter: 1
5. 35,078.150 35,078.150 ↑ 1.0 1,051 1

Seq Scan on config.cd_cfg_tenant_units (cost=0.00..841.64 rows=1,051 width=655) (actual time=9.019..35,078.150 rows=1,051 loops=1)

  • Output: cd_cfg_tenant_units.id, cd_cfg_tenant_units.uuid, cd_cfg_tenant_units.tenant_id, NULL::bigint, cd_cfg_tenant_units.system_id, cd_cfg_tenant_units.name, cd_cfg_tenant_units.created_from_ddmc, cd_cfg_tenant_units.state, cd_c (...)
6.          

SubPlan (forSubquery Scan)

7. 0.484 17.856 ↑ 6.5 150 1

HashAggregate (cost=646.68..656.47 rows=979 width=8) (actual time=17.735..17.856 rows=150 loops=1)

  • Output: n_1.system_id
  • Group Key: n_1.system_id
8. 14.369 17.372 ↑ 6.5 150 1

Hash Right Join (cost=341.86..634.45 rows=979 width=7,958) (actual time=3.147..17.372 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::character varying(256), NULL: (...)
  • Hash Cond: (sc_1.system_id = n_1.system_id)
9. 0.003 0.003 ↓ 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.003..0.003 rows=0 loops=1)

  • Output: sc_1.system_id, sc_1.status
10. 0.078 3.000 ↓ 1.6 150 1

Hash (cost=340.66..340.66 rows=96 width=16) (actual time=3.000..3.000 rows=150 loops=1)

  • Output: n_1.system_id, s_1.id
  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
11. 0.128 2.922 ↓ 1.6 150 1

Hash Left Join (cost=66.20..340.66 rows=96 width=16) (actual time=0.790..2.922 rows=150 loops=1)

  • Output: n_1.system_id, s_1.id
  • Hash Cond: (n_1.system_id = l_1.system_id)
12. 0.097 2.501 ↓ 1.6 150 1

Hash Left Join (cost=43.82..316.97 rows=96 width=16) (actual time=0.487..2.501 rows=150 loops=1)

  • Output: n_1.system_id, s_1.id
  • Hash Cond: (n_1.system_id = f_1.system_id)
13. 0.128 2.274 ↓ 1.6 150 1

Hash Left Join (cost=36.42..308.26 rows=96 width=16) (actual time=0.351..2.274 rows=150 loops=1)

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

Seq Scan on config.cfg_nodes n_1 (cost=0.00..270.51 rows=96 width=20) (actual time=0.068..1.879 rows=150 loops=1)

  • Output: n_1.system_id, n_1.tz_id, n_1.id
  • Filter: ((n_1.system_id = 59) OR (n_1.system_id = 98) OR (n_1.system_id = 99) OR (n_1.system_id = 100) OR (n_1.system_id = 103) OR (n_1.system_id = 107) OR (n_1.system_id = 110) OR (n_1.system (...)
  • Rows Removed by Filter: 1
15. 0.055 0.267 ↑ 1.0 151 1

Hash (cost=34.54..34.54 rows=151 width=8) (actual time=0.267..0.267 rows=151 loops=1)

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

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

  • Output: s_1.id
  • Heap Fetches: 98
17. 0.052 0.130 ↑ 1.0 151 1

Hash (cost=5.51..5.51 rows=151 width=8) (actual time=0.130..0.130 rows=151 loops=1)

  • Output: f_1.system_id
  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
18. 0.078 0.078 ↑ 1.0 151 1

Seq Scan on config.cd_cfg_systems_features_status f_1 (cost=0.00..5.51 rows=151 width=8) (actual time=0.009..0.078 rows=151 loops=1)

  • Output: f_1.system_id
19. 0.064 0.293 ↑ 1.0 150 1

Hash (cost=20.50..20.50 rows=150 width=8) (actual time=0.293..0.293 rows=150 loops=1)

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

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

  • Output: l_1.system_id
21. 0.103 0.175 ↓ 1.3 153 1

Hash (cost=3.21..3.21 rows=121 width=596) (actual time=0.175..0.175 rows=153 loops=1)

  • Output: t.id, t.uuid, t.name, t.state, t.admin_name, t.admin_email
  • Buckets: 1024 Batches: 1 Memory Usage: 21kB
22. 0.072 0.072 ↓ 1.3 153 1

Seq Scan on config.cd_cfg_tenants t (cost=0.00..3.21 rows=121 width=596) (actual time=0.016..0.072 rows=153 loops=1)

  • Output: t.id, t.uuid, t.name, t.state, t.admin_name, t.admin_email
23. 28.833 30.822 ↑ 10.2 150 2

Hash Right Join (cost=433.57..869.42 rows=1,530 width=7,172) (actual time=1.133..15.411 rows=150 loops=2)

  • 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::character varying(256), NULL::character varying(2 (...)
  • Hash Cond: (sc.system_id = n.system_id)
24. 0.002 0.002 ↓ 0.0 0 2

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

  • Output: sc.system_id, sc.status
25. 0.137 1.987 ↑ 1.0 150 1

Hash (cost=431.69..431.69 rows=150 width=106) (actual time=1.987..1.987 rows=150 loops=1)

  • Output: n.hd_sync_epoch, n.ddos_version, n.system_id, s.type, s.name, s.cd_sync_epoch, s.status, s.state, s.state_change_epoch, s.id, f.mtree_quota_enabled
  • Buckets: 1024 Batches: 1 Memory Usage: 30kB
26. 0.137 1.850 ↑ 1.0 150 1

Hash Left Join (cost=282.86..431.69 rows=150 width=106) (actual time=1.079..1.850 rows=150 loops=1)

  • Output: n.hd_sync_epoch, n.ddos_version, n.system_id, s.type, s.name, s.cd_sync_epoch, s.status, s.state, s.state_change_epoch, s.id, f.mtree_quota_enabled
  • Hash Cond: (n.system_id = l.system_id)
27. 0.097 1.547 ↑ 1.0 150 1

Hash Left Join (cost=260.48..407.27 rows=150 width=106) (actual time=0.904..1.547 rows=150 loops=1)

  • Output: n.hd_sync_epoch, n.ddos_version, n.system_id, s.type, s.name, s.cd_sync_epoch, s.status, s.state, s.state_change_epoch, s.id, f.mtree_quota_enabled
  • Hash Cond: (n.system_id = f.system_id)
28. 0.206 1.317 ↑ 1.0 150 1

Hash Join (cost=253.09..397.81 rows=150 width=102) (actual time=0.755..1.317 rows=150 loops=1)

  • Output: n.hd_sync_epoch, n.ddos_version, n.system_id, s.type, s.name, s.cd_sync_epoch, s.status, s.state, s.state_change_epoch, s.id
  • Hash Cond: (s.id = n.system_id)
  • Join Filter: ((((s.type)::text = 'HA'::text) AND (c.ha_node_role = 2)) OR ((s.type)::text = 'standalone'::text))
  • Rows Removed by Join Filter: 1
29. 0.380 0.380 ↑ 1.0 151 1

Seq Scan on config.cfg_systems s (cost=0.00..141.51 rows=151 width=71) (actual time=0.008..0.380 rows=151 loops=1)

  • Output: s.type, s.name, s.cd_sync_epoch, s.status, s.state, s.state_change_epoch, s.id
30. 0.071 0.731 ↑ 1.0 151 1

Hash (cost=251.20..251.20 rows=151 width=35) (actual time=0.731..0.731 rows=151 loops=1)

  • Output: n.hd_sync_epoch, n.ddos_version, n.system_id, c.ha_node_role
  • Buckets: 1024 Batches: 1 Memory Usage: 18kB
31. 0.064 0.660 ↑ 1.0 151 1

Hash Right Join (cost=215.77..251.20 rows=151 width=35) (actual time=0.603..0.660 rows=151 loops=1)

  • Output: n.hd_sync_epoch, n.ddos_version, n.system_id, c.ha_node_role
  • Hash Cond: ((c.system_id = n.system_id) AND (c.node_id = n.id))
32. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on config.cd_cfg_ha_node_config c (cost=0.00..24.50 rows=1,450 width=20) (actual time=0.002..0.002 rows=0 loops=1)

  • Output: c.system_id, c.node_id, c.ha_node_id, c.ha_node_state, c.ha_node_role
33. 0.091 0.594 ↑ 1.0 151 1

Hash (cost=213.51..213.51 rows=151 width=43) (actual time=0.594..0.594 rows=151 loops=1)

  • Output: n.hd_sync_epoch, n.ddos_version, n.system_id, n.tz_id, n.id
  • Buckets: 1024 Batches: 1 Memory Usage: 21kB
34. 0.503 0.503 ↑ 1.0 151 1

Seq Scan on config.cfg_nodes n (cost=0.00..213.51 rows=151 width=43) (actual time=0.007..0.503 rows=151 loops=1)

  • Output: n.hd_sync_epoch, n.ddos_version, n.system_id, n.tz_id, n.id
35. 0.062 0.133 ↑ 1.0 151 1

Hash (cost=5.51..5.51 rows=151 width=12) (actual time=0.133..0.133 rows=151 loops=1)

  • Output: f.mtree_quota_enabled, f.system_id
  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
36. 0.071 0.071 ↑ 1.0 151 1

Seq Scan on config.cd_cfg_systems_features_status f (cost=0.00..5.51 rows=151 width=12) (actual time=0.011..0.071 rows=151 loops=1)

  • Output: f.mtree_quota_enabled, f.system_id
37. 0.053 0.166 ↑ 1.0 150 1

Hash (cost=20.50..20.50 rows=150 width=8) (actual time=0.166..0.166 rows=150 loops=1)

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

Seq Scan on config.cd_cfg_collections l (cost=0.00..20.50 rows=150 width=8) (actual time=0.006..0.113 rows=150 loops=1)

  • Output: l.system_id
Planning time : 10.834 ms
Execution time : 35,137.839 ms