explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3OrX : hostdetail_nestloop

Settings
# exclusive inclusive rows x rows loops node
1. 1.633 74.642 ↓ 130.4 3,000 1

Hash Join (cost=3,238.09..3,507.22 rows=23 width=512) (actual time=63.856..74.642 rows=3,000 loops=1)

  • Buffers: shared hit=16654
2. 1.475 72.991 ↓ 750.0 3,000 1

Nested Loop (cost=3,201.99..3,470.3 rows=4 width=478) (actual time=63.828..72.991 rows=3,000 loops=1)

  • Buffers: shared hit=16653
3. 3.989 68.516 ↓ 750.0 3,000 1

Hash Join (cost=3,201.84..3,469.59 rows=4 width=336) (actual time=63.815..68.516 rows=3,000 loops=1)

  • Buffers: shared hit=10653
4. 0.727 0.727 ↑ 1.0 3,033 1

Seq Scan on ssm_hosts b (cost=0..256.33 rows=3,033 width=175) (actual time=0.005..0.727 rows=3,033 loops=1)

  • Buffers: shared hit=226
5. 2.018 63.800 ↓ 200.0 3,000 1

Hash (cost=3,201.66..3,201.66 rows=15 width=165) (actual time=63.8..63.8 rows=3,000 loops=1)

  • Buffers: shared hit=10427
6. 2.261 61.782 ↓ 200.0 3,000 1

Merge Join (cost=3,001.89..3,201.66 rows=15 width=165) (actual time=52.701..61.782 rows=3,000 loops=1)

  • Buffers: shared hit=10427
7. 2.217 44.699 ↓ 200.0 3,000 1

Sort (cost=1,992.63..1,992.67 rows=15 width=133) (actual time=44.382..44.699 rows=3,000 loops=1)

  • Sort Key: a.object_id
  • Sort Method: quicksort Memory: 681kB
  • Buffers: shared hit=10236
8. 1.752 42.482 ↓ 200.0 3,000 1

Hash Join (cost=1,920.29..1,992.34 rows=15 width=133) (actual time=40.082..42.482 rows=3,000 loops=1)

  • Buffers: shared hit=10236
9. 3.689 25.683 ↑ 1.0 3,000 1

Aggregate (cost=1,673.82..1,704.14 rows=3,032 width=8) (actual time=25.028..25.683 rows=3,000 loops=1)

  • Buffers: shared hit=977
10. 2.607 21.994 ↓ 1.8 8,700 1

Hash Join (cost=1,017.25..1,650.22 rows=4,720 width=8) (actual time=13.366..21.994 rows=8,700 loops=1)

  • Buffers: shared hit=977
11. 4.213 19.378 ↓ 1.8 8,700 1

Hash Join (cost=1,003.43..1,623.7 rows=4,720 width=12) (actual time=13.346..19.378 rows=8,700 loops=1)

  • Buffers: shared hit=976
12. 1.839 1.839 ↑ 1.0 8,805 1

Seq Scan on ssm_servicestatus f_1 (cost=0..540.05 rows=8,805 width=8) (actual time=0.005..1.839 rows=8,805 loops=1)

  • Buffers: shared hit=452
13. 2.436 13.326 ↓ 1.4 8,700 1

Hash (cost=923.32..923.32 rows=6,409 width=16) (actual time=13.326..13.326 rows=8,700 loops=1)

  • Buffers: shared hit=524
14. 3.841 10.890 ↓ 1.4 8,700 1

Hash Join (cost=410.14..923.32 rows=6,409 width=16) (actual time=5.358..10.89 rows=8,700 loops=1)

  • Buffers: shared hit=524
15. 1.720 1.720 ↑ 1.0 8,805 1

Seq Scan on ssm_services e_1 (cost=0..490.05 rows=8,805 width=8) (actual time=0.005..1.72 rows=8,805 loops=1)

  • Buffers: shared hit=402
16. 2.182 5.329 ↑ 1.0 8,700 1

Hash (cost=301.36..301.36 rows=8,703 width=8) (actual time=5.329..5.329 rows=8,700 loops=1)

  • Buffers: shared hit=122
17. 3.147 3.147 ↑ 1.0 8,700 1

Seq Scan on ssm_objects a_1 (cost=0..301.36 rows=8,703 width=8) (actual time=0.04..3.147 rows=8,700 loops=1)

  • Filter: ((a_1.is_active = 1) AND (a_1.objecttype_id = 2))
  • Buffers: shared hit=122
18. 0.002 0.009 ↑ 170.0 1 1

Hash (cost=11.7..11.7 rows=170 width=4) (actual time=0.009..0.009 rows=1 loops=1)

  • Buffers: shared hit=1
19. 0.007 0.007 ↑ 170.0 1 1

Seq Scan on ssm_instances d_1 (cost=0..11.7 rows=170 width=4) (actual time=0.007..0.007 rows=1 loops=1)

  • Buffers: shared hit=1
20. 2.055 15.047 ↓ 200.0 3,000 1

Hash (cost=246.27..246.27 rows=15 width=129) (actual time=15.047..15.047 rows=3,000 loops=1)

  • Buffers: shared hit=9259
21. 2.805 12.992 ↓ 200.0 3,000 1

Nested Loop (cost=21.59..246.27 rows=15 width=129) (actual time=0.117..12.992 rows=3,000 loops=1)

  • Buffers: shared hit=9259
22. 3.371 4.121 ↓ 49.7 3,033 1

Hash Join (cost=21.3..214.89 rows=61 width=121) (actual time=0.024..4.121 rows=3,033 loops=1)

  • Buffers: shared hit=152
23. 0.740 0.740 ↑ 1.0 3,033 1

Seq Scan on ssm_hoststatus c (cost=0..181.53 rows=3,053 width=71) (actual time=0.006..0.74 rows=3,033 loops=1)

  • Buffers: shared hit=151
24. 0.003 0.010 ↑ 1.0 4 1

Hash (cost=21.25..21.25 rows=4 width=54) (actual time=0.01..0.01 rows=4 loops=1)

  • Buffers: shared hit=1
25. 0.007 0.007 ↑ 1.0 4 1

Seq Scan on ssm_tl_status e (cost=0..21.25 rows=4 width=54) (actual time=0.005..0.007 rows=4 loops=1)

  • Filter: (e.status_type = 1)
  • Buffers: shared hit=1
26. 6.066 6.066 ↑ 1.0 1 3,033

Index Scan using ssm_objects_pkey on ssm_objects a (cost=0.29..0.51 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=3,033)

  • Index Cond: (a.object_id = c.host_object_id)
  • Filter: ((a.is_active = 1) AND (a.objecttype_id = 1))
  • Buffers: shared hit=9107
27. 6.211 14.822 ↑ 2.0 3,000 1

Aggregate (cost=1,009.26..1,132.1 rows=6,142 width=36) (actual time=8.313..14.822 rows=3,000 loops=1)

  • Buffers: shared hit=191
28. 1.480 8.611 ↑ 2.0 3,000 1

Sort (cost=1,009.26..1,024.61 rows=6,142 width=86) (actual time=8.297..8.611 rows=3,000 loops=1)

  • Sort Key: b_1.object_id
  • Sort Method: quicksort Memory: 237kB
  • Buffers: shared hit=191
29. 0.933 7.131 ↑ 2.0 3,000 1

Hash Join (cost=331.33..622.79 rows=6,142 width=86) (actual time=2.28..7.131 rows=3,000 loops=1)

  • Buffers: shared hit=191
30. 2.741 6.131 ↓ 1.0 3,000 1

Hash Join (cost=309.38..524.5 rows=2,996 width=8) (actual time=2.202..6.131 rows=3,000 loops=1)

  • Buffers: shared hit=188
31. 1.202 1.202 ↑ 1.0 11,811 1

Seq Scan on ssm_classification_object b_1 (cost=0..184.11 rows=11,811 width=8) (actual time=0.004..1.202 rows=11,811 loops=1)

  • Buffers: shared hit=66
32. 0.601 2.188 ↑ 1.0 3,033 1

Hash (cost=271.46..271.46 rows=3,033 width=4) (actual time=2.188..2.188 rows=3,033 loops=1)

  • Buffers: shared hit=122
33. 1.587 1.587 ↑ 1.0 3,033 1

Seq Scan on ssm_objects d_2 (cost=0..271.46 rows=3,033 width=4) (actual time=0.004..1.587 rows=3,033 loops=1)

  • Filter: (d_2.objecttype_id = 1)
  • Buffers: shared hit=122
34. 0.004 0.067 ↑ 11.7 11 1

Hash (cost=20.34..20.34 rows=129 width=86) (actual time=0.067..0.067 rows=11 loops=1)

  • Buffers: shared hit=3
35. 0.013 0.063 ↑ 11.7 11 1

Hash Join (cost=3.42..20.34 rows=129 width=86) (actual time=0.056..0.063 rows=11 loops=1)

  • Buffers: shared hit=3
36. 0.009 0.009 ↑ 34.2 12 1

Seq Scan on ssm_tl_hosttype c_1 (cost=0..14.1 rows=410 width=164) (actual time=0.008..0.009 rows=12 loops=1)

  • Buffers: shared hit=1
37. 0.021 0.041 ↑ 1.0 63 1

Hash (cost=2.63..2.63 rows=63 width=19) (actual time=0.041..0.041 rows=63 loops=1)

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

Seq Scan on ssm_classifications a_2 (cost=0..2.63 rows=63 width=19) (actual time=0.005..0.02 rows=63 loops=1)

  • Buffers: shared hit=2
39. 3.000 3.000 ↑ 1.0 1 3,000

Index Scan using ssm_instances_pkey on ssm_instances d (cost=0.14..0.18 rows=1 width=150) (actual time=0.001..0.001 rows=1 loops=3,000)

  • Index Cond: (d.instance_id = a.instance_id)
  • Buffers: shared hit=6000
40. 0.004 0.018 ↑ 580.0 2 1

Hash (cost=21.6..21.6 rows=1,160 width=38) (actual time=0.018..0.018 rows=2 loops=1)

  • Buffers: shared hit=1
41. 0.014 0.014 ↑ 580.0 2 1

Seq Scan on ssm_tl_type f (cost=0..21.6 rows=1,160 width=38) (actual time=0.013..0.014 rows=2 loops=1)

  • Buffers: shared hit=1