explain.depesz.com

PostgreSQL's explain analyze made readable

Result: E8ef : test2

Settings
# exclusive inclusive rows x rows loops node
1. 534.478 881.820 ↓ 4.8 1,535 1

Nested Loop (cost=4,030.9..4,575.48 rows=318 width=2,495) (actual time=102.845..881.82 rows=1,535 loops=1)

  • Buffers: shared hit=11242
2. 1.843 18.852 ↓ 4.8 1,535 1

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

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

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

  • Buffers: shared hit=45
4. 1.440 16.626 ↓ 4.8 1,535 1

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

  • Buffers: shared hit=344
5. 1.624 15.186 ↓ 4.8 1,535 1

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

  • Buffers: shared hit=344
6. 0.300 0.300 ↑ 1.0 1,602 1

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

  • Buffers: shared hit=40
7. 1.171 13.262 ↓ 4.8 1,535 1

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

  • Buffers: shared hit=304
8. 1.331 12.091 ↓ 4.8 1,535 1

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

  • Buffers: shared hit=304
9. 0.320 0.320 ↑ 1.0 1,602 1

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

  • Buffers: shared hit=38
10. 1.144 10.440 ↓ 4.8 1,535 1

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

  • Buffers: shared hit=266
11. 1.350 9.296 ↓ 4.8 1,535 1

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

  • Buffers: shared hit=266
12. 0.317 0.317 ↑ 1.0 1,602 1

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

  • Buffers: shared hit=34
13. 0.869 7.629 ↓ 4.8 1,535 1

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

  • Buffers: shared hit=232
14. 1.180 6.760 ↓ 4.8 1,535 1

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

  • Buffers: shared hit=232
15. 1.570 1.991 ↑ 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.463..1.991 rows=3,000 loops=1)

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

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

  • Index Cond: (a.objecttype_id = 1)
  • Buffers: shared hit=21
17. 0.784 3.589 ↑ 1.0 1,536 1

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

  • Buffers: shared hit=58
18. 2.805 2.805 ↑ 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.805 rows=1,536 loops=1)

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

Materialize (cost=3,397.73..3,870.17 rows=1 width=1,215) (actual time=0.056..0.214 rows=3,000 loops=1,535)

  • Buffers: shared hit=10853
20. 2.528 112.994 ↓ 3,000.0 3,000 1

Hash Join (cost=3,397.73..3,870.17 rows=1 width=1,215) (actual time=85.433..112.994 rows=3,000 loops=1)

  • Buffers: shared hit=10853
21. 2.671 74.678 ↓ 200.0 3,000 1

Hash Join (cost=2,557.02..3,029.41 rows=15 width=1,187) (actual time=49.547..74.678 rows=3,000 loops=1)

  • Buffers: shared hit=10595
22. 3.120 71.917 ↓ 200.0 3,000 1

Hash Join (cost=2,554.94..3,027.07 rows=15 width=1,011) (actual time=49.445..71.917 rows=3,000 loops=1)

  • Buffers: shared hit=10593
23. 2.272 61.706 ↓ 40.5 3,000 1

Hash Join (cost=2,320.42..2,792.12 rows=74 width=355) (actual time=42.319..61.706 rows=3,000 loops=1)

  • Buffers: shared hit=10426
24. 3.180 17.222 ↓ 40.5 3,000 1

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

  • Buffers: shared hit=9225
25. 4.166 5.039 ↓ 8.0 3,001 1

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

  • Buffers: shared hit=214
26. 0.849 0.849 ↑ 1.0 3,001 1

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

  • Buffers: shared hit=213
27. 0.010 0.024 ↓ 4.0 4 1

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

  • Buffers: shared hit=1
28. 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.011..0.014 rows=4 loops=1)

  • Filter: (e_1.status_type = 1)
  • Buffers: shared hit=1
29. 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
30. 0.923 42.212 ↑ 1.0 3,000 1

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

  • Buffers: shared hit=1201
31. 0.457 41.289 ↑ 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.022..41.289 rows=3,000 loops=1)

  • Buffers: shared hit=1201
32. 6.230 40.832 ↑ 1.0 3,000 1

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

  • Buffers: shared hit=1201
33. 2.210 34.602 ↓ 1.6 12,000 1

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

  • Buffers: shared hit=1201
34. 0.009 0.009 ↑ 1.0 1 1

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

  • Buffers: shared hit=1
35. 7.801 32.383 ↓ 1.6 12,000 1

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

  • Buffers: shared hit=1200
36. 2.567 2.567 ↑ 1.0 12,004 1

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

  • Buffers: shared hit=567
37. 4.280 22.015 ↓ 1.3 12,000 1

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

  • Buffers: shared hit=633
38. 6.327 17.735 ↓ 1.3 12,000 1

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

  • Buffers: shared hit=633
39. 2.422 2.422 ↑ 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.422 rows=12,004 loops=1)

  • Buffers: shared hit=480
40. 3.633 8.986 ↑ 1.0 12,000 1

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

  • Buffers: shared hit=153
41. 5.353 5.353 ↑ 1.0 12,000 1

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

  • Filter: ((a_2.is_active = 1) AND (a_2.objecttype_id = 2))
  • Buffers: shared hit=153
42. 2.597 7.091 ↑ 1.0 3,001 1

Hash (cost=197.01..197.01 rows=3,001 width=656) (actual time=7.091..7.091 rows=3,001 loops=1)

  • Buffers: shared hit=167
43. 4.494 4.494 ↑ 1.0 3,001 1

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

  • Buffers: shared hit=167
44. 0.013 0.090 ↑ 1.0 2 1

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

  • Buffers: shared hit=2
45. 0.003 0.077 ↑ 1.0 2 1

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

  • Buffers: shared hit=2
46. 0.063 0.063 ↑ 1.0 1 1

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

  • Buffers: shared hit=1
47. 0.011 0.011 ↑ 1.0 2 1

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

  • Buffers: shared hit=1
48. 0.988 35.788 ↓ 5.3 3,000 1

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

  • Buffers: shared hit=258
49. 17.741 34.800 ↓ 5.3 3,000 1

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

  • Buffers: shared hit=258
50. 7.450 17.059 ↓ 5.3 3,000 1

Sort (cost=670.91..672.33 rows=571 width=86) (actual time=16.843..17.059 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.174 9.609 ↓ 5.3 3,000 1

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

  • Buffers: shared hit=258
52. 4.160 8.351 ↓ 1.0 3,000 1

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

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

  • Buffers: shared hit=82
54. 0.803 2.154 ↑ 1.0 3,001 1

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

  • Buffers: shared hit=174
55. 0.971 1.351 ↑ 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.419..1.351 rows=3,001 loops=1)

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

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

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

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

  • Buffers: shared hit=2
58. 0.034 0.077 ↑ 1.1 11 1

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

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

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

  • Buffers: shared hit=1
60. 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)

  • Buffers: shared hit=1
61. 0.012 0.012 ↑ 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.012 rows=12 loops=1)

  • Buffers: shared hit=1