explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PVyJ : test

Settings
# exclusive inclusive rows x rows loops node
1. 523.441 874.683 ↓ 4.8 1,535 1

Nested Loop (cost=4,322.08..4,597.98 rows=318 width=2,495) (actual time=132.467..874.683 rows=1,535 loops=1)

  • Buffers: shared hit=11242
2. 1.712 19.682 ↓ 4.8 1,535 1

Hash Join (cost=686.44..748.8 rows=318 width=1,280) (actual time=17.649..19.682 rows=1,535 loops=1)

  • Buffers: shared hit=389
3. 0.345 0.345 ↑ 1.0 1,602 1

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

  • Buffers: shared hit=40
4. 1.435 17.625 ↓ 4.8 1,535 1

Hash (cost=682.46..682.46 rows=318 width=1,161) (actual time=17.625..17.625 rows=1,535 loops=1)

  • Buffers: shared hit=349
5. 1.492 16.190 ↓ 4.8 1,535 1

Hash Join (cost=622.09..682.46 rows=318 width=1,161) (actual time=14.4..16.19 rows=1,535 loops=1)

  • Buffers: shared hit=349
6. 0.322 0.322 ↑ 1.0 1,602 1

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

  • Buffers: shared hit=38
7. 1.416 14.376 ↓ 4.8 1,535 1

Hash (cost=618.12..618.12 rows=318 width=1,156) (actual time=14.376..14.376 rows=1,535 loops=1)

  • Buffers: shared hit=311
8. 1.561 12.960 ↓ 4.8 1,535 1

Hash Join (cost=376.81..618.12 rows=318 width=1,156) (actual time=6.993..12.96 rows=1,535 loops=1)

  • Buffers: shared hit=311
9. 1.333 7.781 ↑ 1.0 3,000 1

Hash Join (cost=274.76..501.63 rows=3,000 width=1,102) (actual time=3.359..7.781 rows=3,000 loops=1)

  • Buffers: shared hit=253
10. 1.511 5.072 ↑ 1.0 3,000 1

Hash Join (cost=193.71..406.16 rows=3,000 width=46) (actual time=1.963..5.072 rows=3,000 loops=1)

  • Buffers: shared hit=208
11. 1.658 2.081 ↑ 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.464..2.081 rows=3,000 loops=1)

  • Filter: (a.is_active = 1)
  • Buffers: shared hit=174
12. 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
13. 0.713 1.480 ↑ 1.0 1,602 1

Hash (cost=50.02..50.02 rows=1,602 width=42) (actual time=1.48..1.48 rows=1,602 loops=1)

  • Buffers: shared hit=34
14. 0.767 0.767 ↑ 1.0 1,602 1

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

  • Buffers: shared hit=34
15. 0.624 1.376 ↑ 1.0 1,602 1

Hash (cost=61.02..61.02 rows=1,602 width=1,060) (actual time=1.376..1.376 rows=1,602 loops=1)

  • Buffers: shared hit=45
16. 0.752 0.752 ↑ 1.0 1,602 1

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

  • Buffers: shared hit=45
17. 0.786 3.618 ↑ 1.0 1,536 1

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

  • Buffers: shared hit=58
18. 2.832 2.832 ↑ 1.0 1,536 1

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

  • Filter: (lower((d.manufacturer)::text) ~~ '%s%'::text)
  • Buffers: shared hit=58
19. 217.637 331.560 ↓ 3,000.0 3,000 1,535

Materialize (cost=3,635.65..3,844.41 rows=1 width=1,215) (actual time=0.065..0.216 rows=3,000 loops=1,535)

  • Buffers: shared hit=10853
20. 2.193 113.923 ↓ 3,000.0 3,000 1

Hash Join (cost=3,635.65..3,844.4 rows=1 width=1,215) (actual time=100.039..113.923 rows=3,000 loops=1)

  • Buffers: shared hit=10853
21. 2.193 75.323 ↓ 200.0 3,000 1

Hash Join (cost=2,794.93..3,003.65 rows=15 width=1,187) (actual time=63.621..75.323 rows=3,000 loops=1)

  • Buffers: shared hit=10595
22. 2.418 30.165 ↓ 200.0 3,000 1

Hash Join (cost=475.91..684.59 rows=15 width=1,183) (actual time=20.621..30.165 rows=3,000 loops=1)

  • Buffers: shared hit=9394
23. 6.417 27.701 ↓ 200.0 3,000 1

Hash Join (cost=473.83..682.24 rows=15 width=1,007) (actual time=20.56..27.701 rows=3,000 loops=1)

  • Buffers: shared hit=9392
24. 0.748 0.748 ↑ 1.0 3,001 1

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

  • Buffers: shared hit=167
25. 3.313 20.536 ↓ 40.5 3,000 1

Hash (cost=472.91..472.91 rows=74 width=351) (actual time=20.536..20.536 rows=3,000 loops=1)

  • Buffers: shared hit=9225
26. 3.142 17.223 ↓ 40.5 3,000 1

Nested Loop (cost=1.4..472.91 rows=74 width=351) (actual time=0.068..17.223 rows=3,000 loops=1)

  • Buffers: shared hit=9225
27. 4.275 5.078 ↓ 8.0 3,001 1

Hash Join (cost=1.11..259.13 rows=375 width=343) (actual time=0.042..5.078 rows=3,001 loops=1)

  • Buffers: shared hit=214
28. 0.781 0.781 ↑ 1.0 3,001 1

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

  • Buffers: shared hit=213
29. 0.008 0.022 ↓ 4.0 4 1

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

  • Buffers: shared hit=1
30. 0.014 0.014 ↓ 4.0 4 1

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

  • Filter: (e_1.status_type = 1)
  • Buffers: shared hit=1
31. 9.003 9.003 ↑ 1.0 1 3,001

Index Scan using ssm_objects_pkey on ssm_objects a_1 (cost=0.29..0.57 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=3,001)

  • Index Cond: (a_1.object_id = c_1.host_object_id)
  • Filter: ((a_1.is_active = 1) AND (a_1.objecttype_id = 1))
  • Buffers: shared hit=9011
32. 0.010 0.046 ↑ 1.0 2 1

Hash (cost=2.05..2.05 rows=2 width=188) (actual time=0.046..0.046 rows=2 loops=1)

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

Nested Loop (cost=0..2.05 rows=2 width=188) (actual time=0.033..0.036 rows=2 loops=1)

  • Buffers: shared hit=2
34. 0.021 0.021 ↑ 1.0 1 1

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

  • Buffers: shared hit=1
35. 0.010 0.010 ↑ 1.0 2 1

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

  • Buffers: shared hit=1
36. 0.901 42.965 ↑ 1.0 3,000 1

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

  • Buffers: shared hit=1201
37. 0.445 42.064 ↑ 1.0 3,000 1

Subquery Scan on b_1 (cost=2,221.49..2,281.51 rows=3,001 width=8) (actual time=40.808..42.064 rows=3,000 loops=1)

  • Buffers: shared hit=1201
38. 6.448 41.619 ↑ 1.0 3,000 1

Aggregate (cost=2,221.49..2,251.5 rows=3,001 width=8) (actual time=40.806..41.619 rows=3,000 loops=1)

  • Buffers: shared hit=1201
39. 2.247 35.171 ↓ 1.6 12,000 1

Nested Loop (cost=1,280.47..2,183.68 rows=7,562 width=8) (actual time=22.195..35.171 rows=12,000 loops=1)

  • Buffers: shared hit=1201
40. 0.014 0.014 ↑ 1.0 1 1

Seq Scan on ssm_instances d_2 (cost=0..1.01 rows=1 width=4) (actual time=0.013..0.014 rows=1 loops=1)

  • Buffers: shared hit=1
41. 8.193 32.910 ↓ 1.6 12,000 1

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

  • Buffers: shared hit=1200
42. 2.654 2.654 ↑ 1.0 12,004 1

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

  • Buffers: shared hit=567
43. 4.155 22.063 ↓ 1.3 12,000 1

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

  • Buffers: shared hit=633
44. 6.364 17.908 ↓ 1.3 12,000 1

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

  • Buffers: shared hit=633
45. 2.515 2.515 ↑ 1.0 12,004 1

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

  • Buffers: shared hit=480
46. 3.553 9.029 ↑ 1.0 12,000 1

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

  • Buffers: shared hit=153
47. 5.476 5.476 ↑ 1.0 12,000 1

Seq Scan on ssm_objects a_2 (cost=0..379.83 rows=12,000 width=8) (actual time=0.026..5.476 rows=12,000 loops=1)

  • Filter: ((a_2.is_active = 1) AND (a_2.objecttype_id = 2))
  • Buffers: shared hit=153
48. 0.998 36.407 ↓ 5.3 3,000 1

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

  • Buffers: shared hit=258
49. 18.131 35.409 ↓ 5.3 3,000 1

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

  • Buffers: shared hit=258
50. 7.428 17.278 ↓ 5.3 3,000 1

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

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

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

  • Buffers: shared hit=258
52. 4.205 8.564 ↓ 1.0 3,000 1

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

  • Buffers: shared hit=256
53. 2.185 2.185 ↑ 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.018..2.185 rows=15,105 loops=1)

  • Buffers: shared hit=82
54. 0.835 2.174 ↑ 1.0 3,001 1

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

  • Buffers: shared hit=174
55. 0.955 1.339 ↑ 1.0 3,001 1

Bitmap Heap Scan on ssm_objects d_3 (cost=123.67..314.18 rows=3,001 width=4) (actual time=0.421..1.339 rows=3,001 loops=1)

  • Buffers: shared hit=174
56. 0.384 0.384 ↑ 1.0 3,001 1

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

  • Index Cond: (d_3.objecttype_id = 1)
  • Buffers: shared hit=21
57. 0.015 0.095 ↑ 1.1 11 1

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

  • Buffers: shared hit=2
58. 0.030 0.080 ↑ 1.1 11 1

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

  • Buffers: shared hit=2
59. 0.020 0.020 ↑ 1.0 63 1

Seq Scan on ssm_classifications a_3 (cost=0..1.63 rows=63 width=19) (actual time=0.014..0.02 rows=63 loops=1)

  • Buffers: shared hit=1
60. 0.019 0.030 ↑ 1.0 12 1

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

  • Buffers: shared hit=1
61. 0.011 0.011 ↑ 1.0 12 1

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

  • Buffers: shared hit=1