explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lwy9a

Settings
# exclusive inclusive rows x rows loops node
1. 9.952 2,784.979 ↓ 3,223.8 54,805 1

Hash Left Join (cost=37,623.10..38,939.23 rows=17 width=25) (actual time=2,560.371..2,784.979 rows=54,805 loops=1)

  • Hash Cond: (vm.id = busy_object_1.object_id)
2. 0.000 2,775.018 ↓ 3,223.8 54,805 1

Nested Loop Left Join (cost=37,584.53..38,900.58 rows=17 width=25) (actual time=2,560.344..2,775.018 rows=54,805 loops=1)

3. 33.938 2,612.443 ↓ 3,223.8 54,805 1

Hash Right Join (cost=37,583.83..38,885.62 rows=17 width=41) (actual time=2,560.288..2,612.443 rows=54,805 loops=1)

  • Hash Cond: (vv.id = vapp_vm.id)
4. 40.410 115.872 ↑ 1.0 54,804 1

HashAggregate (cost=7,595.12..8,143.17 rows=54,805 width=89) (actual time=97.635..115.872 rows=54,804 loops=1)

  • Group Key: vv.id
5. 8.419 75.462 ↑ 1.0 54,805 1

Hash Left Join (cost=6,103.32..7,458.11 rows=54,805 width=25) (actual time=41.059..75.462 rows=54,805 loops=1)

  • Hash Cond: (vv.svm_id = sf_metrics.vm_id)
6. 19.612 65.457 ↑ 1.0 54,805 1

Hash Join (cost=5,874.11..7,023.04 rows=54,805 width=41) (actual time=39.441..65.457 rows=54,805 loops=1)

  • Hash Cond: (smetrics.vapp_vm_id = vv.id)
7. 6.821 6.821 ↑ 1.0 54,805 1

Seq Scan on vapp_vm_sclass_metrics smetrics (cost=0.00..1,005.05 rows=54,805 width=16) (actual time=0.018..6.821 rows=54,805 loops=1)

8. 13.121 39.024 ↑ 1.0 54,805 1

Hash (cost=5,189.05..5,189.05 rows=54,805 width=41) (actual time=39.024..39.024 rows=54,805 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 4,438kB
9. 25.903 25.903 ↑ 1.0 54,805 1

Seq Scan on vapp_vm vv (cost=0.00..5,189.05 rows=54,805 width=41) (actual time=0.010..25.903 rows=54,805 loops=1)

10. 0.015 1.586 ↑ 1.0 35 1

Hash (cost=228.77..228.77 rows=35 width=16) (actual time=1.586..1.586 rows=35 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
11. 0.005 1.571 ↑ 1.0 35 1

Subquery Scan on sf_metrics (cost=227.89..228.77 rows=35 width=16) (actual time=1.557..1.571 rows=35 loops=1)

12. 0.014 1.566 ↑ 1.0 35 1

GroupAggregate (cost=227.89..228.42 rows=35 width=38) (actual time=1.555..1.566 rows=35 loops=1)

  • Group Key: v.id
13. 0.045 1.552 ↑ 1.0 35 1

Sort (cost=227.89..227.98 rows=35 width=30) (actual time=1.550..1.552 rows=35 loops=1)

  • Sort Key: v.id
  • Sort Method: quicksort Memory: 27kB
14. 0.013 1.507 ↑ 1.0 35 1

Nested Loop (cost=2.20..226.99 rows=35 width=30) (actual time=0.161..1.507 rows=35 loops=1)

15. 0.497 1.284 ↑ 1.0 35 1

Hash Join (cost=1.79..143.51 rows=35 width=16) (actual time=0.133..1.284 rows=35 loops=1)

  • Hash Cond: (snap.id = sdisk.vm_snapshot_id)
16. 0.756 0.756 ↑ 1.0 5,191 1

Seq Scan on vm_snapshot snap (cost=0.00..121.91 rows=5,191 width=32) (actual time=0.012..0.756 rows=5,191 loops=1)

17. 0.015 0.031 ↑ 1.0 35 1

Hash (cost=1.35..1.35 rows=35 width=16) (actual time=0.031..0.031 rows=35 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
18. 0.016 0.016 ↑ 1.0 35 1

Seq Scan on vm_snapshot_disk sdisk (cost=0.00..1.35 rows=35 width=16) (actual time=0.009..0.016 rows=35 loops=1)

19. 0.210 0.210 ↑ 1.0 1 35

Index Scan using pk_vm on vm v (cost=0.41..2.39 rows=1 width=30) (actual time=0.006..0.006 rows=1 loops=35)

  • Index Cond: (id = snap.vm_id)
20. 32.232 2,462.633 ↓ 3,223.8 54,805 1

Hash (cost=29,988.50..29,988.50 rows=17 width=57) (actual time=2,462.633..2,462.633 rows=54,805 loops=1)

  • Buckets: 65,536 (originally 1024) Batches: 1 (originally 1) Memory Usage: 5,306kB
21. 45.762 2,430.401 ↓ 3,223.8 54,805 1

Nested Loop Left Join (cost=22,533.68..29,988.50 rows=17 width=57) (actual time=202.833..2,430.401 rows=54,805 loops=1)

22. 58.989 2,165.419 ↓ 3,223.8 54,805 1

Nested Loop Left Join (cost=22,533.26..29,978.55 rows=17 width=73) (actual time=202.808..2,165.419 rows=54,805 loops=1)

  • Join Filter: (ni.nic_id = net_vm.primary_nic_id)
  • Rows Removed by Join Filter: 8,614
23. 39.977 626.695 ↓ 3,223.8 54,805 1

Hash Join (cost=22,530.60..29,914.73 rows=17 width=89) (actual time=202.597..626.695 rows=54,805 loops=1)

  • Hash Cond: ((vapp_1.user_id = om.member_id) AND (org_prov_vdc.org_id = om.org_id))
24. 21.648 558.795 ↑ 1.0 54,805 1

Hash Join (cost=20,013.00..26,985.92 rows=54,805 width=121) (actual time=174.319..558.795 rows=54,805 loops=1)

  • Hash Cond: (org_prov_vdc.prov_vdc_id = prov_vdc.id)
25. 31.944 537.057 ↑ 1.0 54,805 1

Hash Join (cost=20,004.32..26,830.46 rows=54,805 width=137) (actual time=174.203..537.057 rows=54,805 loops=1)

  • Hash Cond: (vapp_1.org_vdc_id = org_prov_vdc.id)
26. 41.258 500.010 ↑ 1.0 54,805 1

Hash Join (cost=19,398.63..26,080.85 rows=54,805 width=105) (actual time=169.001..500.010 rows=54,805 loops=1)

  • Hash Cond: (vm.id = vm_gosc_status.vapp_vm_id)
27. 42.980 441.156 ↑ 1.0 54,805 1

Hash Join (cost=17,652.52..24,190.86 rows=54,805 width=105) (actual time=151.047..441.156 rows=54,805 loops=1)

  • Hash Cond: (vapp_vm.vapp_id = vapp_1.sg_id)
28. 35.700 366.519 ↑ 1.0 54,805 1

Hash Join (cost=14,795.68..21,190.16 rows=54,805 width=73) (actual time=119.009..366.519 rows=54,805 loops=1)

  • Hash Cond: (vm.id = vapp_vm.id)
29. 14.266 298.558 ↑ 1.0 54,805 1

Hash Left Join (cost=8,921.57..15,172.17 rows=54,805 width=41) (actual time=86.359..298.558 rows=54,805 loops=1)

  • Hash Cond: (vm.id = busy_object.object_id)
30. 13.598 284.285 ↑ 1.0 54,805 1

Hash Left Join (cost=8,883.00..14,915.38 rows=54,805 width=41) (actual time=86.331..284.285 rows=54,805 loops=1)

  • Hash Cond: (vm.id = jv.object_id)
31. 41.626 270.296 ↑ 1.0 54,805 1

Hash Left Join (cost=8,438.59..14,264.79 rows=54,805 width=41) (actual time=85.907..270.296 rows=54,805 loops=1)

  • Hash Cond: (vm.nvm_id = net_vm.id)
32. 12.631 206.729 ↑ 1.0 54,805 1

Hash Left Join (cost=6,535.97..12,218.29 rows=54,805 width=41) (actual time=63.594..206.729 rows=54,805 loops=1)

  • Hash Cond: (vm_1.id = vmsn.vm_id)
33. 42.735 193.291 ↑ 1.0 54,805 1

Hash Left Join (cost=6,401.07..11,877.87 rows=54,805 width=57) (actual time=62.757..193.291 rows=54,805 loops=1)

  • Hash Cond: (vm.svm_id = vm_1.id)
34. 41.375 110.034 ↑ 1.0 54,805 1

Hash Join (cost=2,856.83..8,189.76 rows=54,805 width=57) (actual time=21.818..110.034 rows=54,805 loops=1)

  • Hash Cond: (vm.vapp_id = vapp.sg_id)
35. 47.254 47.254 ↑ 1.0 54,805 1

Seq Scan on vapp_vm vm (cost=0.00..5,189.05 rows=54,805 width=105) (actual time=0.011..47.254 rows=54,805 loops=1)

36. 11.006 21.405 ↑ 1.0 52,437 1

Hash (cost=2,201.37..2,201.37 rows=52,437 width=16) (actual time=21.405..21.405 rows=52,437 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,970kB
37. 10.399 10.399 ↑ 1.0 52,437 1

Seq Scan on vm_container vapp (cost=0.00..2,201.37 rows=52,437 width=16) (actual time=0.013..10.399 rows=52,437 loops=1)

38. 15.646 40.522 ↑ 1.0 54,366 1

Hash (cost=2,864.66..2,864.66 rows=54,366 width=65) (actual time=40.522..40.522 rows=54,366 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 5,751kB
39. 24.876 24.876 ↑ 1.0 54,366 1

Seq Scan on vm vm_1 (cost=0.00..2,864.66 rows=54,366 width=65) (actual time=0.012..24.876 rows=54,366 loops=1)

40. 0.010 0.807 ↓ 35.0 35 1

Hash (cost=134.89..134.89 rows=1 width=16) (actual time=0.807..0.807 rows=35 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
41. 0.797 0.797 ↓ 35.0 35 1

Seq Scan on vm_snapshot vmsn (cost=0.00..134.89 rows=1 width=16) (actual time=0.062..0.797 rows=35 loops=1)

  • Filter: (snapshot_type = 1)
  • Rows Removed by Filter: 5,156
42. 11.610 21.941 ↑ 1.0 55,050 1

Hash (cost=1,214.50..1,214.50 rows=55,050 width=32) (actual time=21.941..21.941 rows=55,050 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 3,753kB
43. 10.331 10.331 ↑ 1.0 55,050 1

Seq Scan on networked_vm net_vm (cost=0.00..1,214.50 rows=55,050 width=32) (actual time=0.019..10.331 rows=55,050 loops=1)

44. 0.020 0.391 ↑ 1.0 65 1

Hash (cost=443.58..443.58 rows=66 width=16) (actual time=0.390..0.391 rows=65 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
45. 0.032 0.371 ↑ 1.0 65 1

Nested Loop (cost=5.07..443.58 rows=66 width=16) (actual time=0.067..0.371 rows=65 loops=1)

46. 0.047 0.079 ↑ 1.0 65 1

Bitmap Heap Scan on last_jobs jv (cost=4.79..19.61 rows=66 width=32) (actual time=0.041..0.079 rows=65 loops=1)

  • Recheck Cond: ((object_type)::text = 'com.vmware.vcloud.entity.vm'::text)
  • Heap Blocks: exact=13
47. 0.032 0.032 ↑ 1.0 65 1

Bitmap Index Scan on ixn_lastjobs_type_id (cost=0.00..4.77 rows=66 width=0) (actual time=0.032..0.032 rows=65 loops=1)

  • Index Cond: ((object_type)::text = 'com.vmware.vcloud.entity.vm'::text)
48. 0.260 0.260 ↑ 1.0 1 65

Index Only Scan using pk_jobs on jobs j (cost=0.29..6.42 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=65)

  • Index Cond: (job_id = jv.job_id)
  • Heap Fetches: 65
49. 0.001 0.007 ↓ 0.0 0 1

Hash (cost=22.70..22.70 rows=1,270 width=16) (actual time=0.007..0.007 rows=0 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 16kB
50. 0.006 0.006 ↓ 0.0 0 1

Seq Scan on busy_object (cost=0.00..22.70 rows=1,270 width=16) (actual time=0.006..0.006 rows=0 loops=1)

51. 13.279 32.261 ↑ 1.0 54,805 1

Hash (cost=5,189.05..5,189.05 rows=54,805 width=32) (actual time=32.261..32.261 rows=54,805 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 3,938kB
52. 18.982 18.982 ↑ 1.0 54,805 1

Seq Scan on vapp_vm (cost=0.00..5,189.05 rows=54,805 width=32) (actual time=0.013..18.982 rows=54,805 loops=1)

53. 13.173 31.657 ↑ 1.0 52,437 1

Hash (cost=2,201.37..2,201.37 rows=52,437 width=48) (actual time=31.657..31.657 rows=52,437 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 4,609kB
54. 18.484 18.484 ↑ 1.0 52,437 1

Seq Scan on vm_container vapp_1 (cost=0.00..2,201.37 rows=52,437 width=48) (actual time=0.015..18.484 rows=52,437 loops=1)

55. 10.019 17.596 ↑ 1.0 54,805 1

Hash (cost=1,061.05..1,061.05 rows=54,805 width=16) (actual time=17.596..17.596 rows=54,805 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 3,081kB
56. 7.577 7.577 ↑ 1.0 54,805 1

Seq Scan on vm_gosc_status (cost=0.00..1,061.05 rows=54,805 width=16) (actual time=0.011..7.577 rows=54,805 loops=1)

57. 2.846 5.103 ↑ 1.0 10,075 1

Hash (cost=479.75..479.75 rows=10,075 width=48) (actual time=5.103..5.103 rows=10,075 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 916kB
58. 2.257 2.257 ↑ 1.0 10,075 1

Seq Scan on org_prov_vdc (cost=0.00..479.75 rows=10,075 width=48) (actual time=0.008..2.257 rows=10,075 loops=1)

59. 0.043 0.090 ↑ 1.0 208 1

Hash (cost=6.08..6.08 rows=208 width=32) (actual time=0.090..0.090 rows=208 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 21kB
60. 0.047 0.047 ↑ 1.0 208 1

Seq Scan on prov_vdc (cost=0.00..6.08 rows=208 width=32) (actual time=0.013..0.047 rows=208 loops=1)

61. 15.041 27.923 ↑ 1.0 60,584 1

Hash (cost=1,608.84..1,608.84 rows=60,584 width=32) (actual time=27.923..27.923 rows=60,584 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 4,299kB
62. 12.882 12.882 ↑ 1.0 60,584 1

Seq Scan on org_member om (cost=0.00..1,608.84 rows=60,584 width=32) (actual time=0.013..12.882 rows=60,584 loops=1)

63. 0.000 1,479.735 ↑ 1.0 1 54,805

Nested Loop Left Join (cost=2.67..3.74 rows=1 width=32) (actual time=0.024..0.027 rows=1 loops=54,805)

64. 63.167 1,315.320 ↑ 1.0 1 54,805

Nested Loop (cost=2.25..3.25 rows=1 width=48) (actual time=0.021..0.024 rows=1 loops=54,805)

65. 67.348 1,150.905 ↑ 1.0 1 54,805

Nested Loop (cost=1.97..2.92 rows=1 width=64) (actual time=0.018..0.021 rows=1 loops=54,805)

66. 67.348 931.685 ↑ 1.0 1 54,805

Nested Loop (cost=1.55..2.43 rows=1 width=64) (actual time=0.015..0.017 rows=1 loops=54,805)

67. 54.805 712.465 ↑ 1.0 1 54,805

Nested Loop Left Join (cost=1.26..1.99 rows=1 width=64) (actual time=0.012..0.013 rows=1 loops=54,805)

68. 16.724 657.660 ↑ 1.0 1 54,805

Nested Loop Left Join (cost=1.12..1.82 rows=1 width=64) (actual time=0.011..0.012 rows=1 loops=54,805)

69. 0.000 438.440 ↑ 1.0 1 54,805

Nested Loop (cost=0.70..1.18 rows=1 width=64) (actual time=0.007..0.008 rows=1 loops=54,805)

70. 219.220 219.220 ↑ 1.0 1 54,805

Index Scan using vapp_vm_pk on vapp_vm vapp_vm_1 (cost=0.41..0.79 rows=1 width=48) (actual time=0.003..0.004 rows=1 loops=54,805)

  • Index Cond: (id = vm.id)
71. 219.220 219.220 ↑ 1.0 1 54,805

Index Scan using ix_ni_netvm_id on network_interface ni (cost=0.29..0.39 rows=1 width=64) (actual time=0.003..0.004 rows=1 loops=54,805)

  • Index Cond: (netvm_id = vapp_vm_1.nvm_id)
72. 202.496 202.496 ↑ 1.0 1 50,624

Index Scan using pk_logical_network on logical_network lnet (cost=0.42..0.63 rows=1 width=32) (actual time=0.004..0.004 rows=1 loops=50,624)

  • Index Cond: (ni.lnet_id = id)
73. 0.000 0.000 ↓ 0.0 0 50,624

Index Scan using ix_addr_translation_nic_id on address_translation addr_trans (cost=0.14..0.16 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=50,624)

  • Index Cond: (ni.nic_id = nic_id)
  • Filter: ((internal_ports IS NULL) AND (external_ports IS NULL))
74. 151.872 151.872 ↑ 1.0 1 50,624

Index Scan using vm_container_pk on vm_container vapp_2 (cost=0.29..0.44 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=50,624)

  • Index Cond: (sg_id = vapp_vm_1.vapp_id)
75. 151.872 151.872 ↑ 1.0 1 50,624

Index Only Scan using pk_vm_gosc_stat_vapp_vm_id on vm_gosc_status vm_gosc_status_1 (cost=0.41..0.49 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=50,624)

  • Index Cond: (vapp_vm_id = vapp_vm_1.id)
  • Heap Fetches: 50,624
76. 101.248 101.248 ↑ 1.0 1 50,624

Index Only Scan using org_prov_vdc_pk on org_prov_vdc org_prov_vdc_1 (cost=0.29..0.33 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=50,624)

  • Index Cond: (id = vapp_2.org_vdc_id)
  • Heap Fetches: 50,624
77. 202.496 202.496 ↑ 1.0 1 50,624

Index Scan using ix_vapplogicalresource_foid on vapp_logical_resource (cost=0.41..0.48 rows=1 width=16) (actual time=0.003..0.004 rows=1 loops=50,624)

  • Index Cond: (fo_id = lnet.id)
  • Filter: ((lr_type)::text = 'NETWORK'::text)
78. 219.220 219.220 ↑ 1.0 1 54,805

Index Scan using ix_vdc_lr_type_vdc_lr_id on vdc_logical_resource vdc_lr_compute (cost=0.41..0.58 rows=1 width=32) (actual time=0.004..0.004 rows=1 loops=54,805)

  • Index Cond: (((lr_type)::text = 'COMPUTE_POLICY'::text) AND (vdc_id = vapp_1.org_vdc_id))
  • Filter: is_default
  • Rows Removed by Filter: 0
79. 22.948 164.415 ↓ 0.0 0 54,805

Nested Loop (cost=0.70..0.87 rows=1 width=16) (actual time=0.003..0.003 rows=0 loops=54,805)

80. 109.610 109.610 ↓ 0.0 0 54,805

Index Scan using ix_catalog_item_entity_id_type on catalog_item cat_item (cost=0.41..0.47 rows=1 width=32) (actual time=0.002..0.002 rows=0 loops=54,805)

  • Index Cond: ((entity_id = vapp_1.sg_id) AND ((entity_type)::text = 'com.vmware.vcloud.entity.vapptemplate'::text))
81. 31.857 31.857 ↑ 1.0 1 10,619

Index Scan using catalog_pk on catalog (cost=0.29..0.40 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=10,619)

  • Index Cond: (id = cat_item.catalog_id)
  • Filter: (NOT deleted)
82. 0.000 0.009 ↓ 0.0 0 1

Hash (cost=22.70..22.70 rows=1,270 width=16) (actual time=0.009..0.009 rows=0 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 16kB
83. 0.009 0.009 ↓ 0.0 0 1

Seq Scan on busy_object busy_object_1 (cost=0.00..22.70 rows=1,270 width=16) (actual time=0.009..0.009 rows=0 loops=1)