explain.depesz.com

PostgreSQL's explain analyze made readable

Result: uANh : HostFilter_OFF

Settings
# exclusive inclusive rows x rows loops node
1. 2.744 134.472 ↓ 4.8 1,535 1

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

  • Buffers: shared hit=2098 read=307
2. 2.477 109.599 ↓ 3,000.0 3,000 1

Hash Join (cost=3,712.11..3,966.47 rows=1 width=1,215) (actual time=93.549..109.599 rows=3,000 loops=1)

  • Buffers: shared hit=1924 read=92
3. 1.955 63.861 ↓ 3,000.0 3,000 1

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

  • Buffers: shared hit=723 read=92
4. 2.066 61.864 ↓ 3,000.0 3,000 1

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

  • Buffers: shared hit=723 read=91
5. 1.940 59.779 ↓ 3,000.0 3,000 1

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

  • Buffers: shared hit=722 read=91
6. 6.934 57.801 ↓ 750.0 3,000 1

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

  • Buffers: shared hit=722 read=90
7. 0.786 0.786 ↑ 1.0 3,001 1

Seq Scan on ssm_hoststatus c_1 (cost=0..243.01 rows=3,001 width=293) (actual time=0.032..0.786 rows=3,001 loops=1)

  • Buffers: shared hit=212 read=1
8. 3.159 50.081 ↓ 136.4 3,000 1

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

  • Buffers: shared hit=510 read=89
9. 5.344 46.922 ↓ 136.4 3,000 1

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

  • Buffers: shared hit=510 read=89
10. 0.757 0.757 ↑ 1.0 3,001 1

Seq Scan on ssm_hosts b_2 (cost=0..197.01 rows=3,001 width=656) (actual time=0.026..0.757 rows=3,001 loops=1)

  • Buffers: shared hit=160 read=7
11. 1.161 40.821 ↓ 26.5 3,000 1

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

  • Buffers: shared hit=350 read=82
12. 1.334 39.660 ↓ 26.5 3,000 1

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

  • Buffers: shared hit=350 read=82
13. 1.142 1.514 ↑ 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.408..1.514 rows=3,000 loops=1)

  • Filter: (a_1.is_active = 1)
  • Buffers: shared hit=174
14. 0.372 0.372 ↑ 1.0 3,001 1

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

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

Hash (cost=833.6..833.6 rows=569 width=36) (actual time=36.812..36.812 rows=3,000 loops=1)

  • Buffers: shared hit=176 read=82
16. 17.962 35.831 ↓ 5.3 3,000 1

Aggregate (cost=822.22..827.91 rows=569 width=36) (actual time=34.713..35.831 rows=3,000 loops=1)

  • Buffers: shared hit=176 read=82
17. 7.457 17.869 ↓ 5.3 3,000 1

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

  • Sort Key: c_2.type_value, b_3.object_id
  • Sort Method: quicksort Memory: 237kB
  • Buffers: shared hit=176 read=82
18. 1.207 10.412 ↓ 5.3 3,000 1

Hash Join (cost=355.1..644.76 rows=571 width=86) (actual time=2.367..10.412 rows=3,000 loops=1)

  • Buffers: shared hit=176 read=82
19. 4.194 9.000 ↓ 1.0 3,000 1

Hash Join (cost=351.69..624.4 rows=2,998 width=8) (actual time=2.155..9 rows=3,000 loops=1)

  • Buffers: shared hit=176 read=80
20. 2.712 2.712 ↑ 1.0 15,105 1

Seq Scan on ssm_classification_object b_3 (cost=0..233.05 rows=15,105 width=8) (actual time=0.026..2.712 rows=15,105 loops=1)

  • Buffers: shared hit=2 read=80
21. 0.794 2.094 ↑ 1.0 3,001 1

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

  • Buffers: shared hit=174
22. 0.929 1.300 ↑ 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.403..1.3 rows=3,001 loops=1)

  • Buffers: shared hit=174
23. 0.371 0.371 ↑ 1.0 3,001 1

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

  • Index Cond: (d_2.objecttype_id = 1)
  • Buffers: shared hit=21
24. 0.013 0.205 ↑ 1.1 11 1

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

  • Buffers: shared read=2
25. 0.109 0.192 ↑ 1.1 11 1

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

  • Buffers: shared read=2
26. 0.035 0.035 ↑ 1.0 63 1

Seq Scan on ssm_classifications a_2 (cost=0..1.63 rows=63 width=19) (actual time=0.028..0.035 rows=63 loops=1)

  • Buffers: shared read=1
27. 0.016 0.048 ↑ 1.0 12 1

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

  • Buffers: shared read=1
28. 0.032 0.032 ↑ 1.0 12 1

Seq Scan on ssm_tl_hosttype c_2 (cost=0..1.12 rows=12 width=164) (actual time=0.03..0.032 rows=12 loops=1)

  • Buffers: shared read=1
29. 0.012 0.038 ↓ 4.0 4 1

Hash (cost=1.1..1.1 rows=1 width=54) (actual time=0.037..0.038 rows=4 loops=1)

  • Buffers: shared read=1
30. 0.026 0.026 ↓ 4.0 4 1

Seq Scan on ssm_tl_status e_1 (cost=0..1.1 rows=1 width=54) (actual time=0.024..0.026 rows=4 loops=1)

  • Filter: (e_1.status_type = 1)
  • Buffers: shared read=1
31. 0.009 0.019 ↑ 1.0 1 1

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

  • Buffers: shared hit=1
32. 0.010 0.010 ↑ 1.0 1 1

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

  • Buffers: shared hit=1
33. 0.013 0.042 ↑ 1.0 2 1

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

  • Buffers: shared read=1
34. 0.029 0.029 ↑ 1.0 2 1

Seq Scan on ssm_tl_type f_1 (cost=0..1.02 rows=2 width=38) (actual time=0.028..0.029 rows=2 loops=1)

  • Buffers: shared read=1
35. 0.888 43.261 ↑ 1.0 3,000 1

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

  • Buffers: shared hit=1201
36. 0.469 42.373 ↑ 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.108..42.373 rows=3,000 loops=1)

  • Buffers: shared hit=1201
37. 6.123 41.904 ↑ 1.0 3,000 1

Aggregate (cost=2,230.95..2,260.96 rows=3,001 width=8) (actual time=41.107..41.904 rows=3,000 loops=1)

  • Buffers: shared hit=1201
38. 3.980 35.781 ↓ 1.6 12,000 1

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

  • Buffers: shared hit=1201
39. 7.722 31.776 ↓ 1.6 12,000 1

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

  • Buffers: shared hit=1200
40. 2.470 2.470 ↑ 1.0 12,004 1

Seq Scan on ssm_servicestatus f_2 (cost=0..687.04 rows=12,004 width=8) (actual time=0.01..2.47 rows=12,004 loops=1)

  • Buffers: shared hit=567
41. 4.173 21.584 ↓ 1.3 12,000 1

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

  • Buffers: shared hit=633
42. 6.240 17.411 ↓ 1.3 12,000 1

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

  • Buffers: shared hit=633
43. 2.319 2.319 ↑ 1.0 12,004 1

Seq Scan on ssm_services e_2 (cost=0..600.04 rows=12,004 width=8) (actual time=0.01..2.319 rows=12,004 loops=1)

  • Buffers: shared hit=480
44. 3.496 8.852 ↑ 1.0 12,000 1

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

  • Buffers: shared hit=153
45. 5.356 5.356 ↑ 1.0 12,000 1

Seq Scan on ssm_objects a_3 (cost=0..379.83 rows=12,000 width=8) (actual time=0.027..5.356 rows=12,000 loops=1)

  • Filter: ((a_3.is_active = 1) AND (a_3.objecttype_id = 2))
  • Buffers: shared hit=153
46. 0.010 0.025 ↑ 1.0 1 1

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

  • Buffers: shared hit=1
47. 0.015 0.015 ↑ 1.0 1 1

Seq Scan on ssm_instances d_3 (cost=0..1.01 rows=1 width=4) (actual time=0.015..0.015 rows=1 loops=1)

  • Buffers: shared hit=1
48. 1.639 22.129 ↓ 4.8 1,535 1

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

  • Buffers: shared hit=174 read=215
49. 1.505 20.490 ↓ 4.8 1,535 1

Hash Join (cost=633.17..700.54 rows=318 width=1,280) (actual time=18.356..20.49 rows=1,535 loops=1)

  • Buffers: shared hit=174 read=215
50. 0.672 0.672 ↑ 1.0 1,602 1

Seq Scan on systeminfo_chassis c (cost=0..61.02 rows=1,602 width=1,060) (actual time=0.035..0.672 rows=1,602 loops=1)

  • Buffers: shared read=45
51. 1.480 18.313 ↓ 4.8 1,535 1

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

  • Buffers: shared hit=174 read=170
52. 1.619 16.833 ↓ 4.8 1,535 1

Hash Join (cost=566.83..629.2 rows=318 width=224) (actual time=14.562..16.833 rows=1,535 loops=1)

  • Buffers: shared hit=174 read=170
53. 0.684 0.684 ↑ 1.0 1,602 1

Seq Scan on systeminfo_ipmi f (cost=0..56.02 rows=1,602 width=123) (actual time=0.024..0.684 rows=1,602 loops=1)

  • Buffers: shared read=40
54. 1.244 14.530 ↓ 4.8 1,535 1

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

  • Buffers: shared hit=174 read=130
55. 1.340 13.286 ↓ 4.8 1,535 1

Hash Join (cost=502.49..562.85 rows=318 width=105) (actual time=11.317..13.286 rows=1,535 loops=1)

  • Buffers: shared hit=174 read=130
56. 0.662 0.662 ↑ 1.0 1,602 1

Seq Scan on systeminfo_bios e (cost=0..54.02 rows=1,602 width=9) (actual time=0.027..0.662 rows=1,602 loops=1)

  • Buffers: shared read=38
57. 1.157 11.284 ↓ 4.8 1,535 1

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

  • Buffers: shared hit=174 read=92
58. 1.349 10.127 ↓ 4.8 1,535 1

Hash Join (cost=442.14..498.51 rows=318 width=100) (actual time=8.209..10.127 rows=1,535 loops=1)

  • Buffers: shared hit=174 read=92
59. 0.608 0.608 ↑ 1.0 1,602 1

Seq Scan on systeminfo_baseboard b (cost=0..50.02 rows=1,602 width=42) (actual time=0.032..0.608 rows=1,602 loops=1)

  • Buffers: shared read=34
60. 0.874 8.170 ↓ 4.8 1,535 1

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

  • Buffers: shared hit=174 read=58
61. 1.212 7.296 ↓ 4.8 1,535 1

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

  • Buffers: shared hit=174 read=58
62. 1.556 1.979 ↑ 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.465..1.979 rows=3,000 loops=1)

  • Filter: (a.is_active = 1)
  • Buffers: shared hit=174
63. 0.423 0.423 ↑ 1.0 3,001 1

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

  • Index Cond: (a.objecttype_id = 1)
  • Buffers: shared hit=21
64. 0.819 4.105 ↑ 1.0 1,536 1

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

  • Buffers: shared read=58
65. 3.286 3.286 ↑ 1.0 1,536 1

Seq Scan on systeminfo_computer d (cost=0..82.03 rows=1,602 width=58) (actual time=0.05..3.286 rows=1,536 loops=1)

  • Filter: (lower((d.manufacturer)::text) ~~ '%s%'::text)
  • Buffers: shared read=58