explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ogFL

Settings
# exclusive inclusive rows x rows loops node
1. 402.069 1,867.592 ↓ 4.8 1,535 1

Nested Loop Left Join (cost=3,908.91..4,379.98 rows=318 width=2,495) (actual time=137.167..1,867.592 rows=1,535 loops=1)

  • Join Filter: (a.object_id = b_1.host_object_id)
  • Rows Removed by Join Filter: 4603465
  • Buffers: shared hit=20237
2. 1.431 18.018 ↓ 4.8 1,535 1

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

  • Hash Cond: (c.host_object_id = a.object_id)
  • Buffers: shared hit=389
3. 0.325 0.325 ↑ 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.015..0.325 rows=1,602 loops=1)

  • Buffers: shared hit=45
4. 1.459 16.262 ↓ 4.8 1,535 1

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

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

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

  • Hash Cond: (f.host_object_id = a.object_id)
  • Buffers: shared hit=344
6. 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.013..0.291 rows=1,602 loops=1)

  • Buffers: shared hit=40
7. 1.188 12.893 ↓ 4.8 1,535 1

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

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

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

  • Hash Cond: (e.host_object_id = a.object_id)
  • Buffers: shared hit=304
9. 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.014..0.295 rows=1,602 loops=1)

  • Buffers: shared hit=38
10. 1.146 10.099 ↓ 4.8 1,535 1

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

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

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

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

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

  • Buffers: shared hit=34
13. 0.870 7.342 ↓ 4.8 1,535 1

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

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

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

  • Hash Cond: (a.object_id = d.host_object_id)
  • Buffers: shared hit=232
15. 1.300 1.694 ↑ 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.435..1.694 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
16. 0.394 0.394 ↑ 1.0 3,001 1

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

  • Index Cond: (objecttype_id = 1)
  • Buffers: shared hit=21
17. 0.806 3.570 ↑ 1.0 1,536 1

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

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

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

  • Filter: (lower((manufacturer)::text) ~~ '%s%'::text)
  • Rows Removed by Filter: 66
  • Buffers: shared hit=58
19. 167.676 1,447.505 ↓ 3,000.0 3,000 1,535

Materialize (cost=3,275.74..3,674.67 rows=1 width=1,215) (actual time=0.056..0.943 rows=3,000 loops=1,535)

  • Buffers: shared hit=19848
20. 317.582 1,279.829 ↓ 3,000.0 3,000 1

Nested Loop Left Join (cost=3,275.74..3,674.67 rows=1 width=1,215) (actual time=85.623..1,279.829 rows=3,000 loops=1)

  • Join Filter: (a_1.object_id = e_2.host_object_id)
  • Rows Removed by Join Filter: 4498500
  • Buffers: shared hit=19848
21. 16.434 92.247 ↓ 3,000.0 3,000 1

Nested Loop (cost=1,054.25..1,355.65 rows=1 width=1,211) (actual time=44.917..92.247 rows=3,000 loops=1)

  • Join Filter: (c_1.state_type = f_1.type_key)
  • Rows Removed by Join Filter: 3000
  • Buffers: shared hit=18647
22. 3.761 72.813 ↓ 3,000.0 3,000 1

Nested Loop (cost=1,054.25..1,354.60 rows=1 width=1,177) (actual time=44.898..72.813 rows=3,000 loops=1)

  • Buffers: shared hit=15647
23. 1.364 66.052 ↓ 3,000.0 3,000 1

Nested Loop (cost=1,054.12..1,354.44 rows=1 width=1,035) (actual time=44.892..66.052 rows=3,000 loops=1)

  • Buffers: shared hit=9647
24. 3.025 52.688 ↓ 42.3 3,000 1

Hash Join (cost=1,053.84..1,313.97 rows=71 width=1,035) (actual time=44.876..52.688 rows=3,000 loops=1)

  • Hash Cond: (c_1.host_object_id = b_1.host_object_id)
  • Buffers: shared hit=639
25. 4.069 4.837 ↓ 8.0 3,001 1

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

  • Hash Cond: (c_1.current_state = e_1.status_key)
  • Buffers: shared hit=214
26. 0.750 0.750 ↑ 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.750 rows=3,001 loops=1)

  • Buffers: shared hit=213
27. 0.005 0.018 ↓ 4.0 4 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
28. 0.013 0.013 ↓ 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.013 rows=4 loops=1)

  • Filter: (status_type = 1)
  • Rows Removed by Filter: 4
  • Buffers: shared hit=1
29. 3.030 44.826 ↓ 5.3 3,000 1

Hash (cost=1,045.61..1,045.61 rows=569 width=692) (actual time=44.826..44.826 rows=3,000 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 613kB
  • Buffers: shared hit=425
30. 5.208 41.796 ↓ 5.3 3,000 1

Hash Join (cost=840.71..1,045.61 rows=569 width=692) (actual time=35.961..41.796 rows=3,000 loops=1)

  • Hash Cond: (b_1.host_object_id = b_2.object_id)
  • Buffers: shared hit=425
31. 0.649 0.649 ↑ 1.0 3,001 1

Seq Scan on ssm_hosts b_1 (cost=0.00..197.01 rows=3,001 width=656) (actual time=0.009..0.649 rows=3,001 loops=1)

  • Buffers: shared hit=167
32. 0.947 35.939 ↓ 5.3 3,000 1

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

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

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

  • Group Key: b_2.object_id
  • Buffers: shared hit=258
34. 7.361 16.856 ↓ 5.3 3,000 1

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

  • Sort Key: c_2.type_value, b_2.object_id
  • Sort Method: quicksort Memory: 237kB
  • Buffers: shared hit=258
35. 1.228 9.495 ↓ 5.3 3,000 1

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

  • Hash Cond: (b_2.classify_object_id = a_2.classification_object_id)
  • Buffers: shared hit=258
36. 4.104 8.189 ↓ 1.0 3,000 1

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

  • Hash Cond: (b_2.object_id = d_2.object_id)
  • Buffers: shared hit=256
37. 2.006 2.006 ↑ 1.0 15,105 1

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

  • Buffers: shared hit=82
38. 0.796 2.079 ↑ 1.0 3,001 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 138kB
  • Buffers: shared hit=174
39. 0.913 1.283 ↑ 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.407..1.283 rows=3,001 loops=1)

  • Recheck Cond: (objecttype_id = 1)
  • Heap Blocks: exact=153
  • Buffers: shared hit=174
40. 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
41. 0.007 0.078 ↑ 1.1 11 1

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

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

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

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

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

  • Buffers: shared hit=1
44. 0.008 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
45. 0.014 0.014 ↑ 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.014 rows=12 loops=1)

  • Buffers: shared hit=1
46. 12.000 12.000 ↑ 1.0 1 3,000

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

  • Index Cond: (object_id = b_1.host_object_id)
  • Filter: ((is_active = 1) AND (objecttype_id = 1))
  • Buffers: shared hit=9008
47. 3.000 3.000 ↑ 1.0 1 3,000

Index Scan using ssm_instances_pkey on ssm_instances d_1 (cost=0.13..0.15 rows=1 width=150) (actual time=0.001..0.001 rows=1 loops=3,000)

  • Index Cond: (instance_id = a_1.instance_id)
  • Buffers: shared hit=6000
48. 3.000 3.000 ↑ 1.0 2 3,000

Seq Scan on ssm_tl_type f_1 (cost=0.00..1.02 rows=2 width=38) (actual time=0.001..0.001 rows=2 loops=3,000)

  • Buffers: shared hit=3000
49. 835.189 870.000 ↑ 2.0 1,501 3,000

HashAggregate (cost=2,221.49..2,251.50 rows=3,001 width=8) (actual time=0.014..0.290 rows=1,501 loops=3,000)

  • Group Key: e_2.host_object_id
  • Buffers: shared hit=1201
50. 2.209 34.811 ↓ 1.6 12,000 1

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

  • Join Filter: (a_3.instance_id = d_3.instance_id)
  • Buffers: shared hit=1201
51. 0.008 0.008 ↑ 1.0 1 1

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

  • Buffers: shared hit=1
52. 7.888 32.594 ↓ 1.6 12,000 1

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

  • Hash Cond: (f_2.service_object_id = a_3.object_id)
  • Buffers: shared hit=1200
53. 2.607 2.607 ↑ 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.009..2.607 rows=12,004 loops=1)

  • Buffers: shared hit=567
54. 4.284 22.099 ↓ 1.3 12,000 1

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

  • Buckets: 16384 Batches: 1 Memory Usage: 691kB
  • Buffers: shared hit=633
55. 6.394 17.815 ↓ 1.3 12,000 1

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

  • Hash Cond: (e_2.service_object_id = a_3.object_id)
  • Buffers: shared hit=633
56. 2.467 2.467 ↑ 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.467 rows=12,004 loops=1)

  • Buffers: shared hit=480
57. 3.621 8.954 ↑ 1.0 12,000 1

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

  • Buckets: 16384 Batches: 1 Memory Usage: 597kB
  • Buffers: shared hit=153
58. 5.333 5.333 ↑ 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.026..5.333 rows=12,000 loops=1)

  • Filter: ((is_active = 1) AND (objecttype_id = 2))
  • Rows Removed by Filter: 3122
  • Buffers: shared hit=153
Planning time : 32.253 ms