explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Tuyf

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 5,004.259 ↓ 0.0 0 1

Limit (cost=560,027.63..560,027.94 rows=5 width=335) (actual time=5,004.258..5,004.259 rows=0 loops=1)

2. 0.001 5,004.258 ↓ 0.0 0 1

Unique (cost=560,027.63..560,027.94 rows=5 width=335) (actual time=5,004.257..5,004.258 rows=0 loops=1)

3. 0.044 5,004.257 ↓ 0.0 0 1

Sort (cost=560,027.63..560,027.64 rows=5 width=335) (actual time=5,004.257..5,004.257 rows=0 loops=1)

  • Sort Key: c.db_cluster_id, c.customer_id, i.db_instance_id, scu.sw_conf_upgrade_id, from_sc.hostmanager_config_id, from_os.os_name, from_os.os_major_version, from_os.os_minor_version, from_os.os_patch_version, from_db.engine_major_version, from_db.engine_minor_version, from_db.engine_patch_version, from_sc.storage_config_id, to_sc.hostmanager_config_id, to_os.os_name, to_os.os_major_version, to_os.os_minor_version, to_os.os_patch_version, to_db.engine_major_version, to_db.engine_minor_version, to_db.db_patch_online_artifact_url, to_db.db_patch_online_prev_patch_ver, to_db.engine_patch_version, to_sc.storage_config_id
  • Sort Method: quicksort Memory: 25kB
4. 0.000 5,004.213 ↓ 0.0 0 1

Nested Loop Anti Join (cost=223,882.15..560,027.57 rows=5 width=335) (actual time=5,004.213..5,004.213 rows=0 loops=1)

5. 0.000 5,004.213 ↓ 0.0 0 1

Nested Loop Anti Join (cost=223,880.76..560,013.34 rows=5 width=335) (actual time=5,004.213..5,004.213 rows=0 loops=1)

6. 0.669 5,004.213 ↓ 0.0 0 1

Nested Loop (cost=223,879.92..560,000.42 rows=5 width=335) (actual time=5,004.212..5,004.213 rows=0 loops=1)

  • Join Filter: (to_sc.dbi_engine_config_id = to_db.dbi_engine_config_id)
7. 0.111 0.111 ↑ 1.0 1,413 1

Seq Scan on dbi_engine_config to_db (cost=0.00..116.13 rows=1,413 width=166) (actual time=0.004..0.111 rows=1,413 loops=1)

8. 0.000 5,003.433 ↓ 0.0 0 1,413

Materialize (cost=223,879.92..559,778.33 rows=5 width=185) (actual time=3.541..3.541 rows=0 loops=1,413)

9. 0.248 5,003.919 ↓ 0.0 0 1

Nested Loop (cost=223,879.92..559,778.30 rows=5 width=185) (actual time=5,003.919..5,003.919 rows=0 loops=1)

  • Join Filter: (from_sc.dbi_engine_config_id = from_db.dbi_engine_config_id)
10. 0.238 0.238 ↑ 1.0 1,413 1

Seq Scan on dbi_engine_config from_db (cost=0.00..116.13 rows=1,413 width=26) (actual time=0.001..0.238 rows=1,413 loops=1)

11. 0.000 5,003.433 ↓ 0.0 0 1,413

Materialize (cost=223,879.92..559,556.21 rows=5 width=175) (actual time=3.541..3.541 rows=0 loops=1,413)

12. 0.000 5,003.493 ↓ 0.0 0 1

Nested Loop (cost=223,879.92..559,556.18 rows=5 width=175) (actual time=5,003.493..5,003.493 rows=0 loops=1)

13. 0.001 5,003.493 ↓ 0.0 0 1

Nested Loop (cost=223,879.63..559,554.53 rows=5 width=138) (actual time=5,003.493..5,003.493 rows=0 loops=1)

14. 17.498 5,003.492 ↓ 0.0 0 1

Nested Loop (cost=223,879.35..559,552.87 rows=5 width=101) (actual time=5,003.492..5,003.492 rows=0 loops=1)

  • Join Filter: (from_sc.dbi_engine_config_id <> to_sc.dbi_engine_config_id)
  • Rows Removed by Join Filter: 44,173
15. 20.077 4,941.821 ↓ 8,834.6 44,173 1

Nested Loop (cost=223,879.06..559,551.10 rows=5 width=77) (actual time=695.529..4,941.821 rows=44,173 loops=1)

16. 54.338 4,877.571 ↓ 8,834.6 44,173 1

Nested Loop (cost=223,878.77..559,549.56 rows=5 width=61) (actual time=695.521..4,877.571 rows=44,173 loops=1)

17. 88.303 4,721.764 ↓ 33,823.0 101,469 1

Nested Loop (cost=223,878.35..559,548.07 rows=3 width=37) (actual time=695.313..4,721.764 rows=101,469 loops=1)

18. 8.002 4,531.992 ↓ 33,823.0 101,469 1

Nested Loop (cost=223,877.92..559,546.48 rows=3 width=37) (actual time=695.304..4,531.992 rows=101,469 loops=1)

  • Join Filter: (cm.db_instance_id = h.db_instance_id)
19. 76.955 4,219.583 ↓ 33,823.0 101,469 1

Nested Loop (cost=223,877.50..559,544.71 rows=3 width=45) (actual time=695.294..4,219.583 rows=101,469 loops=1)

20. 16.088 3,330.476 ↓ 25,379.8 101,519 1

Nested Loop (cost=223,877.06..559,540.54 rows=4 width=58) (actual time=695.275..3,330.476 rows=101,519 loops=1)

  • Join Filter: ((c.customer_id)::text = (i.db_instance_owner)::text)
21. 42.184 3,009.831 ↓ 1.2 101,519 1

Nested Loop (cost=223,876.51..394,482.42 rows=88,155 width=37) (actual time=695.259..3,009.831 rows=101,519 loops=1)

22. 80.752 835.118 ↓ 1.1 101,549 1

Hash Join (cost=223,875.71..228,512.28 rows=90,443 width=24) (actual time=695.228..835.118 rows=101,549 loops=1)

  • Hash Cond: (pcm.db_instance_id = cm.db_instance_id)
23. 9.535 709.851 ↓ 1.1 101,549 1

Subquery Scan on pcm (cost=217,589.21..220,523.36 rows=90,443 width=8) (actual time=650.181..709.851 rows=101,549 loops=1)

24. 29.233 700.316 ↓ 1.1 101,549 1

GroupAggregate (cost=217,589.21..219,618.93 rows=90,443 width=16) (actual time=650.180..700.316 rows=101,549 loops=1)

  • Group Key: icm.db_cluster_id
25. 87.930 671.083 ↓ 1.0 151,128 1

Sort (cost=217,589.21..217,964.31 rows=150,039 width=16) (actual time=650.173..671.083 rows=151,128 loops=1)

  • Sort Key: icm.db_cluster_id
  • Sort Method: external merge Disk: 3,856kB
26. 138.003 583.153 ↓ 1.0 151,128 1

Hash Join (cost=6,286.50..203,219.96 rows=150,039 width=16) (actual time=45.181..583.153 rows=151,128 loops=1)

  • Hash Cond: (ii_1.db_instance_id = icm.db_instance_id)
27. 400.570 400.570 ↑ 1.0 530,213 1

Seq Scan on db_instances ii_1 (cost=0.00..190,613.65 rows=532,874 width=8) (actual time=0.005..400.570 rows=530,213 loops=1)

  • Filter: (((lifecycle)::text = 'ACTIVE'::text) AND ((change_state)::text = 'NONE'::text))
  • Rows Removed by Filter: 17,185
28. 22.276 44.580 ↑ 1.0 154,813 1

Hash (cost=3,593.11..3,593.11 rows=154,911 width=16) (actual time=44.580..44.580 rows=154,813 loops=1)

  • Buckets: 131,072 Batches: 4 Memory Usage: 2,851kB
29. 22.304 22.304 ↑ 1.0 154,813 1

Seq Scan on db_cluster_memberships icm (cost=0.00..3,593.11 rows=154,911 width=16) (actual time=0.006..22.304 rows=154,813 loops=1)

30. 21.947 44.515 ↑ 1.0 154,813 1

Hash (cost=3,593.11..3,593.11 rows=154,911 width=16) (actual time=44.515..44.515 rows=154,813 loops=1)

  • Buckets: 131,072 Batches: 4 Memory Usage: 2,851kB
31. 22.568 22.568 ↑ 1.0 154,813 1

Seq Scan on db_cluster_memberships cm (cost=0.00..3,593.11 rows=154,911 width=16) (actual time=0.005..22.568 rows=154,813 loops=1)

32. 1,726.333 2,132.529 ↑ 1.0 1 101,549

Bitmap Heap Scan on db_clusters c (cost=0.81..1.84 rows=1 width=21) (actual time=0.021..0.021 rows=1 loops=101,549)

  • Recheck Cond: (db_cluster_id = cm.db_cluster_id)
  • Filter: (((lifecycle)::text = 'ACTIVE'::text) AND ((change_state)::text = 'NONE'::text))
  • Rows Removed by Filter: 0
  • Heap Blocks: exact=2,175,864
33. 406.196 406.196 ↓ 22.0 22 101,549

Bitmap Index Scan on pk_db_cluster_id (cost=0.00..0.81 rows=1 width=0) (actual time=0.004..0.004 rows=22 loops=101,549)

  • Index Cond: (db_cluster_id = cm.db_cluster_id)
34. 304.557 304.557 ↑ 1.0 1 101,519

Index Scan using pk_dbi_db_instance_id on db_instances i (cost=0.55..1.86 rows=1 width=21) (actual time=0.003..0.003 rows=1 loops=101,519)

  • Index Cond: (db_instance_id = cm.db_instance_id)
35. 812.152 812.152 ↑ 1.0 1 101,519

Index Scan using pk_customers_customer_aws_id on customers cust (cost=0.43..1.04 rows=1 width=13) (actual time=0.008..0.008 rows=1 loops=101,519)

  • Index Cond: ((customer_aws_id)::text = (i.db_instance_owner)::text)
  • Filter: ((lifecycle)::text = 'ACTIVE'::text)
  • Rows Removed by Filter: 0
36. 304.407 304.407 ↑ 1.0 1 101,469

Index Scan using i_dbi_hosts_db_instance_id on db_instance_hosts h (cost=0.42..0.58 rows=1 width=16) (actual time=0.002..0.003 rows=1 loops=101,469)

  • Index Cond: (db_instance_id = i.db_instance_id)
37. 101.469 101.469 ↑ 1.0 1 101,469

Index Scan using pk_rnbl_dbi_cfg_rnbldbcfgid on runnable_dbi_config r (cost=0.43..0.53 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=101,469)

  • Index Cond: (runnable_dbi_config_id = h.runnable_dbi_config_id)
38. 101.469 101.469 ↓ 0.0 0 101,469

Index Scan using ui_scu_from_sci_to_sci on software_config_upgrades scu (cost=0.42..0.48 rows=2 width=24) (actual time=0.001..0.001 rows=0 loops=101,469)

  • Index Cond: (from_software_cfg_id = r.software_config_id)
  • Filter: ((patch_type)::text = ANY ('{IMMEDIATE,FORCE_ALL_IMMEDIATE}'::text[]))
  • Rows Removed by Filter: 0
39. 44.173 44.173 ↑ 1.0 1 44,173

Index Scan using pk_software_configs_swcfgid on software_configs from_sc (cost=0.29..0.31 rows=1 width=40) (actual time=0.001..0.001 rows=1 loops=44,173)

  • Index Cond: (software_config_id = r.software_config_id)
40. 44.173 44.173 ↑ 1.0 1 44,173

Index Scan using pk_software_configs_swcfgid on software_configs to_sc (cost=0.29..0.34 rows=1 width=40) (actual time=0.001..0.001 rows=1 loops=44,173)

  • Index Cond: (software_config_id = scu.to_software_cfg_id)
41. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_os_config_os_config_id on os_config from_os (cost=0.29..0.33 rows=1 width=53) (never executed)

  • Index Cond: (os_config_id = from_sc.os_config_id)
42. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_os_config_os_config_id on os_config to_os (cost=0.29..0.33 rows=1 width=53) (never executed)

  • Index Cond: (os_config_id = to_sc.os_config_id)
43. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.84..2.58 rows=1 width=8) (never executed)

44. 0.000 0.000 ↓ 0.0 0

Index Only Scan using pk_db_cluster_id on db_clusters cc (cost=0.42..0.91 rows=1 width=8) (never executed)

  • Index Cond: (db_cluster_id = c.db_cluster_id)
  • Heap Fetches: 0
45. 0.000 0.000 ↓ 0.0 0

Index Scan using i_dbc_pend_act_db_cluster_id on db_cluster_pending_actions cpa (cost=0.42..1.66 rows=1 width=8) (never executed)

  • Index Cond: (db_cluster_id = cc.db_cluster_id)
  • Filter: ((action)::text = 'PATCH_DB_CLUSTER'::text)
46. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.39..2.83 rows=1 width=8) (never executed)

47. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.84..1.62 rows=1 width=24) (never executed)

48. 0.000 0.000 ↓ 0.0 0

Index Scan using i_dbc_mem_db_cluster_id on db_cluster_memberships ccm (cost=0.42..0.51 rows=2 width=16) (never executed)

  • Index Cond: (c.db_cluster_id = db_cluster_id)
49. 0.000 0.000 ↓ 0.0 0

Index Scan using u_dbipa_db_instance_id on dbi_pending_actions pa (cost=0.42..0.54 rows=1 width=8) (never executed)

  • Index Cond: (db_instance_id = ccm.db_instance_id)
  • Filter: ((action)::text = ANY ('{PATCH_DB,PATCH_OFFLINE,PATCH_ONLINE,MODIFY_LICENSE_MODEL}'::text[]))
50. 0.000 0.000 ↓ 0.0 0

Index Only Scan using pk_dbi_db_instance_id on db_instances ii (cost=0.55..1.22 rows=1 width=8) (never executed)

  • Index Cond: (db_instance_id = ccm.db_instance_id)
  • Heap Fetches: 0
Planning time : 27.488 ms
Execution time : 5,005.430 ms