explain.depesz.com

PostgreSQL's explain analyze made readable

Result: d2h0

Settings
# exclusive inclusive rows x rows loops node
1. 2.738 132.147 ↓ 4.8 1,535 1

Hash Right Join (cost=4,416.62..4,670.99 rows=318 width=2,495) (actual time=113.270..132.147 rows=1,535 loops=1)

  • Hash Cond: (b_2.host_object_id = a.object_id)
  • Buffers: shared hit=2405
2. 2.498 109.534 ↓ 3,000.0 3,000 1

Hash Left Join (cost=3,712.11..3,966.47 rows=1 width=1,215) (actual time=93.286..109.534 rows=3,000 loops=1)

  • Hash Cond: (a_1.object_id = b_1.object_id)
  • Buffers: shared hit=2016
3. 1.945 63.374 ↓ 3,000.0 3,000 1

Hash Join (cost=1,383.62..1,637.98 rows=1 width=1,211) (actual time=49.586..63.374 rows=3,000 loops=1)

  • Hash Cond: (c_1.state_type = f_1.type_key)
  • Buffers: shared hit=815
4. 2.105 61.409 ↓ 3,000.0 3,000 1

Hash Join (cost=1,382.58..1,636.92 rows=1 width=1,177) (actual time=49.555..61.409 rows=3,000 loops=1)

  • Hash Cond: (a_1.instance_id = d_1.instance_id)
  • Buffers: shared hit=814
5. 1.868 59.289 ↓ 3,000.0 3,000 1

Hash Join (cost=1,381.56..1,635.88 rows=1 width=1,035) (actual time=49.516..59.289 rows=3,000 loops=1)

  • Hash Cond: (c_1.current_state = e_1.status_key)
  • Buffers: shared hit=813
6. 6.986 57.405 ↓ 750.0 3,000 1

Hash Join (cost=1,380.44..1,634.75 rows=4 width=985) (actual time=49.494..57.405 rows=3,000 loops=1)

  • Hash Cond: (c_1.host_object_id = a_1.object_id)
  • Buffers: shared hit=812
7. 0.944 0.944 ↑ 1.0 3,001 1

Seq Scan on ssm_hoststatus c_1 (cost=0.00..243.01 rows=3,001 width=293) (actual time=0.009..0.944 rows=3,001 loops=1)

  • Buffers: shared hit=213
8. 3.198 49.475 ↓ 136.4 3,000 1

Hash (cost=1,380.17..1,380.17 rows=22 width=700) (actual time=49.474..49.475 rows=3,000 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 637kB
  • Buffers: shared hit=599
9. 5.402 46.277 ↓ 136.4 3,000 1

Hash Join (cost=1,171.68..1,380.17 rows=22 width=700) (actual time=40.158..46.277 rows=3,000 loops=1)

  • Hash Cond: (b_2.host_object_id = a_1.object_id)
  • Buffers: shared hit=599
10. 0.739 0.739 ↑ 1.0 3,001 1

Seq Scan on ssm_hosts b_2 (cost=0.00..197.01 rows=3,001 width=656) (actual time=0.008..0.739 rows=3,001 loops=1)

  • Buffers: shared hit=167
11. 1.160 40.136 ↓ 26.5 3,000 1

Hash (cost=1,170.27..1,170.27 rows=113 width=44) (actual time=40.136..40.136 rows=3,000 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 185kB
  • Buffers: shared hit=432
12. 1.313 38.976 ↓ 26.5 3,000 1

Hash Join (cost=964.38..1,170.27 rows=113 width=44) (actual time=36.584..38.976 rows=3,000 loops=1)

  • Hash Cond: (a_1.object_id = b_3.object_id)
  • Buffers: shared hit=432
13. 1.125 1.498 ↑ 1.0 3,000 1

Bitmap Heap Scan on ssm_objects a_1 (cost=123.67..321.68 rows=3,000 width=8) (actual time=0.410..1.498 rows=3,000 loops=1)

  • Recheck Cond: (objecttype_id = 1)
  • Filter: (is_active = 1)
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=153
  • Buffers: shared hit=174
14. 0.373 0.373 ↑ 1.0 3,001 1

Bitmap Index Scan on u_objects (cost=0.00..122.92 rows=3,001 width=0) (actual time=0.373..0.373 rows=3,001 loops=1)

  • Index Cond: (objecttype_id = 1)
  • Buffers: shared hit=21
15. 0.971 36.165 ↓ 5.3 3,000 1

Hash (cost=833.60..833.60 rows=569 width=36) (actual time=36.164..36.165 rows=3,000 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 161kB
  • Buffers: shared hit=258
16. 18.343 35.194 ↓ 5.3 3,000 1

HashAggregate (cost=822.22..827.91 rows=569 width=36) (actual time=34.066..35.194 rows=3,000 loops=1)

  • Group Key: b_3.object_id
  • Buffers: shared hit=258
17. 7.378 16.851 ↓ 5.3 3,000 1

Sort (cost=670.91..672.33 rows=571 width=86) (actual time=16.659..16.851 rows=3,000 loops=1)

  • Sort Key: c_2.type_value, b_3.object_id
  • Sort Method: quicksort Memory: 237kB
  • Buffers: shared hit=258
18. 1.181 9.473 ↓ 5.3 3,000 1

Hash Join (cost=355.10..644.76 rows=571 width=86) (actual time=2.212..9.473 rows=3,000 loops=1)

  • Hash Cond: (b_3.classify_object_id = a_2.classification_object_id)
  • Buffers: shared hit=258
19. 4.187 8.212 ↓ 1.0 3,000 1

Hash Join (cost=351.69..624.40 rows=2,998 width=8) (actual time=2.127..8.212 rows=3,000 loops=1)

  • Hash Cond: (b_3.object_id = d_2.object_id)
  • Buffers: shared hit=256
20. 1.943 1.943 ↑ 1.0 15,105 1

Seq Scan on ssm_classification_object b_3 (cost=0.00..233.05 rows=15,105 width=8) (actual time=0.014..1.943 rows=15,105 loops=1)

  • Buffers: shared hit=82
21. 0.793 2.082 ↑ 1.0 3,001 1

Hash (cost=314.18..314.18 rows=3,001 width=4) (actual time=2.082..2.082 rows=3,001 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 138kB
  • Buffers: shared hit=174
22. 0.919 1.289 ↑ 1.0 3,001 1

Bitmap Heap Scan on ssm_objects d_2 (cost=123.67..314.18 rows=3,001 width=4) (actual time=0.402..1.289 rows=3,001 loops=1)

  • Recheck Cond: (objecttype_id = 1)
  • Heap Blocks: exact=153
  • Buffers: shared hit=174
23. 0.370 0.370 ↑ 1.0 3,001 1

Bitmap Index Scan on u_objects (cost=0.00..122.92 rows=3,001 width=0) (actual time=0.370..0.370 rows=3,001 loops=1)

  • Index Cond: (objecttype_id = 1)
  • Buffers: shared hit=21
24. 0.008 0.080 ↑ 1.1 11 1

Hash (cost=3.26..3.26 rows=12 width=86) (actual time=0.080..0.080 rows=11 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=2
25. 0.032 0.072 ↑ 1.1 11 1

Hash Join (cost=1.27..3.26 rows=12 width=86) (actual time=0.048..0.072 rows=11 loops=1)

  • Hash Cond: ((a_2.classification_name)::text = (c_2.type_key)::text)
  • Buffers: shared hit=2
26. 0.018 0.018 ↑ 1.0 63 1

Seq Scan on ssm_classifications a_2 (cost=0.00..1.63 rows=63 width=19) (actual time=0.013..0.018 rows=63 loops=1)

  • Buffers: shared hit=1
27. 0.010 0.022 ↑ 1.0 12 1

Hash (cost=1.12..1.12 rows=12 width=164) (actual time=0.022..0.022 rows=12 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
28. 0.012 0.012 ↑ 1.0 12 1

Seq Scan on ssm_tl_hosttype c_2 (cost=0.00..1.12 rows=12 width=164) (actual time=0.009..0.012 rows=12 loops=1)

  • Buffers: shared hit=1
29. 0.005 0.016 ↓ 4.0 4 1

Hash (cost=1.10..1.10 rows=1 width=54) (actual time=0.016..0.016 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
30. 0.011 0.011 ↓ 4.0 4 1

Seq Scan on ssm_tl_status e_1 (cost=0.00..1.10 rows=1 width=54) (actual time=0.009..0.011 rows=4 loops=1)

  • Filter: (status_type = 1)
  • Rows Removed by Filter: 4
  • Buffers: shared hit=1
31. 0.004 0.015 ↑ 1.0 1 1

Hash (cost=1.01..1.01 rows=1 width=150) (actual time=0.015..0.015 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
32. 0.011 0.011 ↑ 1.0 1 1

Seq Scan on ssm_instances d_1 (cost=0.00..1.01 rows=1 width=150) (actual time=0.011..0.011 rows=1 loops=1)

  • Buffers: shared hit=1
33. 0.005 0.020 ↑ 1.0 2 1

Hash (cost=1.02..1.02 rows=2 width=38) (actual time=0.020..0.020 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
34. 0.015 0.015 ↑ 1.0 2 1

Seq Scan on ssm_tl_type f_1 (cost=0.00..1.02 rows=2 width=38) (actual time=0.014..0.015 rows=2 loops=1)

  • Buffers: shared hit=1
35. 0.916 43.662 ↑ 1.0 3,000 1

Hash (cost=2,290.97..2,290.97 rows=3,001 width=8) (actual time=43.662..43.662 rows=3,000 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 150kB
  • Buffers: shared hit=1201
36. 0.480 42.746 ↑ 1.0 3,000 1

Subquery Scan on b_1 (cost=2,230.95..2,290.97 rows=3,001 width=8) (actual time=41.463..42.746 rows=3,000 loops=1)

  • Buffers: shared hit=1201
37. 6.169 42.266 ↑ 1.0 3,000 1

HashAggregate (cost=2,230.95..2,260.96 rows=3,001 width=8) (actual time=41.461..42.266 rows=3,000 loops=1)

  • Group Key: e_2.host_object_id
  • Buffers: shared hit=1201
38. 3.965 36.097 ↓ 1.6 12,000 1

Hash Join (cost=1,281.49..2,193.14 rows=7,562 width=8) (actual time=21.899..36.097 rows=12,000 loops=1)

  • Hash Cond: (a_3.instance_id = d_3.instance_id)
  • Buffers: shared hit=1201
39. 7.797 32.104 ↓ 1.6 12,000 1

Hash Join (cost=1,280.47..2,088.14 rows=7,562 width=12) (actual time=21.860..32.104 rows=12,000 loops=1)

  • Hash Cond: (f_2.service_object_id = a_3.object_id)
  • Buffers: shared hit=1200
40. 2.558 2.558 ↑ 1.0 12,004 1

Seq Scan on ssm_servicestatus f_2 (cost=0.00..687.04 rows=12,004 width=8) (actual time=0.010..2.558 rows=12,004 loops=1)

  • Buffers: shared hit=567
41. 4.224 21.749 ↓ 1.3 12,000 1

Hash (cost=1,161.39..1,161.39 rows=9,526 width=16) (actual time=21.749..21.749 rows=12,000 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 691kB
  • Buffers: shared hit=633
42. 6.276 17.525 ↓ 1.3 12,000 1

Hash Join (cost=529.83..1,161.39 rows=9,526 width=16) (actual time=9.002..17.525 rows=12,000 loops=1)

  • Hash Cond: (e_2.service_object_id = a_3.object_id)
  • Buffers: shared hit=633
43. 2.361 2.361 ↑ 1.0 12,004 1

Seq Scan on ssm_services e_2 (cost=0.00..600.04 rows=12,004 width=8) (actual time=0.010..2.361 rows=12,004 loops=1)

  • Buffers: shared hit=480
44. 3.516 8.888 ↑ 1.0 12,000 1

Hash (cost=379.83..379.83 rows=12,000 width=8) (actual time=8.888..8.888 rows=12,000 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 597kB
  • Buffers: shared hit=153
45. 5.372 5.372 ↑ 1.0 12,000 1

Seq Scan on ssm_objects a_3 (cost=0.00..379.83 rows=12,000 width=8) (actual time=0.025..5.372 rows=12,000 loops=1)

  • Filter: ((is_active = 1) AND (objecttype_id = 2))
  • Rows Removed by Filter: 3122
  • Buffers: shared hit=153
46. 0.009 0.028 ↑ 1.0 1 1

Hash (cost=1.01..1.01 rows=1 width=4) (actual time=0.027..0.028 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
47. 0.019 0.019 ↑ 1.0 1 1

Seq Scan on ssm_instances d_3 (cost=0.00..1.01 rows=1 width=4) (actual time=0.018..0.019 rows=1 loops=1)

  • Buffers: shared hit=1
48. 1.636 19.875 ↓ 4.8 1,535 1

Hash (cost=700.54..700.54 rows=318 width=1,280) (actual time=19.875..19.875 rows=1,535 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 323kB
  • Buffers: shared hit=389
49. 1.548 18.239 ↓ 4.8 1,535 1

Hash Right Join (cost=633.17..700.54 rows=318 width=1,280) (actual time=16.396..18.239 rows=1,535 loops=1)

  • Hash Cond: (c.host_object_id = a.object_id)
  • Buffers: shared hit=389
50. 0.317 0.317 ↑ 1.0 1,602 1

Seq Scan on systeminfo_chassis c (cost=0.00..61.02 rows=1,602 width=1,060) (actual time=0.013..0.317 rows=1,602 loops=1)

  • Buffers: shared hit=45
51. 1.450 16.374 ↓ 4.8 1,535 1

Hash (cost=629.20..629.20 rows=318 width=224) (actual time=16.374..16.374 rows=1,535 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 286kB
  • Buffers: shared hit=344
52. 1.613 14.924 ↓ 4.8 1,535 1

Hash Right Join (cost=566.83..629.20 rows=318 width=224) (actual time=13.042..14.924 rows=1,535 loops=1)

  • Hash Cond: (f.host_object_id = a.object_id)
  • Buffers: shared hit=344
53. 0.291 0.291 ↑ 1.0 1,602 1

Seq Scan on systeminfo_ipmi f (cost=0.00..56.02 rows=1,602 width=123) (actual time=0.014..0.291 rows=1,602 loops=1)

  • Buffers: shared hit=40
54. 1.175 13.020 ↓ 4.8 1,535 1

Hash (cost=562.85..562.85 rows=318 width=105) (actual time=13.020..13.020 rows=1,535 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 224kB
  • Buffers: shared hit=304
55. 1.334 11.845 ↓ 4.8 1,535 1

Hash Right Join (cost=502.49..562.85 rows=318 width=105) (actual time=10.237..11.845 rows=1,535 loops=1)

  • Hash Cond: (e.host_object_id = a.object_id)
  • Buffers: shared hit=304
56. 0.295 0.295 ↑ 1.0 1,602 1

Seq Scan on systeminfo_bios e (cost=0.00..54.02 rows=1,602 width=9) (actual time=0.015..0.295 rows=1,602 loops=1)

  • Buffers: shared hit=38
57. 1.126 10.216 ↓ 4.8 1,535 1

Hash (cost=498.51..498.51 rows=318 width=100) (actual time=10.216..10.216 rows=1,535 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 216kB
  • Buffers: shared hit=266
58. 1.321 9.090 ↓ 4.8 1,535 1

Hash Right Join (cost=442.14..498.51 rows=318 width=100) (actual time=7.499..9.090 rows=1,535 loops=1)

  • Hash Cond: (b.host_object_id = a.object_id)
  • Buffers: shared hit=266
59. 0.296 0.296 ↑ 1.0 1,602 1

Seq Scan on systeminfo_baseboard b (cost=0.00..50.02 rows=1,602 width=42) (actual time=0.018..0.296 rows=1,602 loops=1)

  • Buffers: shared hit=34
60. 0.866 7.473 ↓ 4.8 1,535 1

Hash (cost=438.17..438.17 rows=318 width=62) (actual time=7.473..7.473 rows=1,535 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 158kB
  • Buffers: shared hit=232
61. 1.255 6.607 ↓ 4.8 1,535 1

Hash Join (cost=225.72..438.17 rows=318 width=62) (actual time=4.066..6.607 rows=1,535 loops=1)

  • Hash Cond: (a.object_id = d.host_object_id)
  • Buffers: shared hit=232
62. 1.346 1.753 ↑ 1.0 3,000 1

Bitmap Heap Scan on ssm_objects a (cost=123.67..321.68 rows=3,000 width=8) (actual time=0.447..1.753 rows=3,000 loops=1)

  • Recheck Cond: (objecttype_id = 1)
  • Filter: (is_active = 1)
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=153
  • Buffers: shared hit=174
63. 0.407 0.407 ↑ 1.0 3,001 1

Bitmap Index Scan on u_objects (cost=0.00..122.92 rows=3,001 width=0) (actual time=0.407..0.407 rows=3,001 loops=1)

  • Index Cond: (objecttype_id = 1)
  • Buffers: shared hit=21
64. 0.815 3.599 ↑ 1.0 1,536 1

Hash (cost=82.03..82.03 rows=1,602 width=58) (actual time=3.598..3.599 rows=1,536 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 154kB
  • Buffers: shared hit=58
65. 2.784 2.784 ↑ 1.0 1,536 1

Seq Scan on systeminfo_computer d (cost=0.00..82.03 rows=1,602 width=58) (actual time=0.028..2.784 rows=1,536 loops=1)

  • Filter: (lower((manufacturer)::text) ~~ '%s%'::text)
  • Rows Removed by Filter: 66
  • Buffers: shared hit=58
Planning time : 32.225 ms