explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4G6s

Settings
# exclusive inclusive rows x rows loops node
1. 0.135 79,225.269 ↑ 1.0 2,001 1

Limit (cost=14,264,192.75..14,264,197.75 rows=2,001 width=60) (actual time=79,224.752..79,225.269 rows=2,001 loops=1)

2. 79.429 79,225.134 ↑ 105.4 2,001 1

Sort (cost=14,264,192.75..14,264,720.25 rows=211,001 width=60) (actual time=79,224.751..79,225.134 rows=2,001 loops=1)

  • Sort Key: p.eventid
  • Sort Method: top-N heapsort Memory: 264kB
3. 1,379.621 79,145.705 ↓ 2.5 535,535 1

HashAggregate (cost=14,249,458.02..14,251,568.03 rows=211,001 width=60) (actual time=78,884.971..79,145.705 rows=535,535 loops=1)

4. 675.098 77,766.084 ↓ 7.8 1,648,662 1

Nested Loop Anti Join (cost=143,546.07..14,246,293.00 rows=211,001 width=60) (actual time=1,260.750..77,766.084 rows=1,648,662 loops=1)

5. 442.357 75,434.349 ↓ 7.1 1,656,637 1

Hash Join (cost=143,545.65..14,140,985.79 rows=234,754 width=60) (actual time=1,260.723..75,434.349 rows=1,656,637 loops=1)

  • Hash Cond: (i.hostid = hg.hostid)
6. 618.034 74,987.258 ↓ 3.0 1,745,342 1

Hash Join (cost=142,958.06..14,121,434.97 rows=577,937 width=68) (actual time=1,255.975..74,987.258 rows=1,745,342 loops=1)

  • Hash Cond: (f.itemid = i.itemid)
7. 546.465 73,115.873 ↓ 3.0 1,745,342 1

Nested Loop (cost=0.85..13,966,919.02 rows=577,937 width=68) (actual time=0.973..73,115.873 rows=1,745,342 loops=1)

8. 573.196 71,328.094 ↓ 2.0 620,657 1

Index Scan using problem_3 on problem p (cost=0.42..13,763,708.72 rows=306,216 width=60) (actual time=0.966..71,328.094 rows=620,657 loops=1)

  • Index Cond: (r_eventid IS NULL)
  • Filter: ((source = 0) AND (object = 0) AND (NOT (SubPlan 1)))
  • Rows Removed by Filter: 115
9.          

SubPlan (for Index Scan)

10. 4,965.256 70,754.898 ↓ 0.0 0 620,657

HashAggregate (cost=22.36..22.76 rows=27 width=12) (actual time=0.114..0.114 rows=0 loops=620,657)

  • Filter: ((max(r.permission) < 2) OR (min(r.permission) IS NULL) OR (min(r.permission) = 0))
  • Rows Removed by Filter: 1
11. 631.330 65,789.642 ↓ 1.6 42 620,657

Nested Loop Left Join (cost=1.56..22.09 rows=27 width=12) (actual time=0.009..0.106 rows=42 loops=620,657)

12. 2,095.229 12,413.140 ↓ 1.6 14 620,657

Nested Loop (cost=1.27..16.14 rows=9 width=16) (actual time=0.006..0.020 rows=14 loops=620,657)

13. 349.887 6,827.227 ↓ 1.5 3 620,657

Nested Loop (cost=0.85..15.07 rows=2 width=8) (actual time=0.004..0.011 rows=3 loops=620,657)

14. 1,241.314 1,241.314 ↓ 1.5 3 620,657

Index Scan using functions_1 on functions f_1 (cost=0.43..6.16 rows=2 width=8) (actual time=0.002..0.002 rows=3 loops=620,657)

  • Index Cond: (p.objectid = triggerid)
15. 5,236.026 5,236.026 ↑ 1.0 1 1,745,342

Index Scan using items_pkey on items i_1 (cost=0.43..4.45 rows=1 width=16) (actual time=0.002..0.003 rows=1 loops=1,745,342)

  • Index Cond: (itemid = f_1.itemid)
16. 3,490.684 3,490.684 ↓ 1.2 5 1,745,342

Index Only Scan using hosts_groups_1 on hosts_groups hgg (cost=0.41..0.50 rows=4 width=16) (actual time=0.002..0.002 rows=5 loops=1,745,342)

  • Index Cond: (hostid = i_1.hostid)
  • Heap Fetches: 1,012,930
17. 52,745.172 52,745.172 ↑ 1.0 3 8,790,862

Index Scan using rights_2 on rights r (cost=0.29..0.63 rows=3 width=12) (actual time=0.002..0.006 rows=3 loops=8,790,862)

  • Index Cond: (id = hgg.groupid)
  • Filter: (groupid = ANY ('{11,13,14,990,1142}'::bigint[]))
  • Rows Removed by Filter: 9
18. 1,241.314 1,241.314 ↓ 1.5 3 620,657

Index Scan using functions_1 on functions f (cost=0.43..0.64 rows=2 width=16) (actual time=0.002..0.002 rows=3 loops=620,657)

  • Index Cond: (triggerid = p.objectid)
19. 398.586 1,253.351 ↓ 1.0 1,682,666 1

Hash (cost=122,464.87..122,464.87 rows=1,639,387 width=16) (actual time=1,253.351..1,253.351 rows=1,682,666 loops=1)

  • Buckets: 262,144 Batches: 1 Memory Usage: 78,875kB
20. 854.765 854.765 ↓ 1.0 1,682,666 1

Seq Scan on items i (cost=0.00..122,464.87 rows=1,639,387 width=16) (actual time=0.024..854.765 rows=1,682,666 loops=1)

21. 0.896 4.734 ↑ 1.0 4,620 1

Hash (cost=529.85..529.85 rows=4,620 width=8) (actual time=4.734..4.734 rows=4,620 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 181kB
22. 3.234 3.838 ↑ 1.0 4,620 1

Bitmap Heap Scan on hosts_groups hg (cost=102.10..529.85 rows=4,620 width=8) (actual time=0.646..3.838 rows=4,620 loops=1)

  • Recheck Cond: (groupid = 594)
23. 0.604 0.604 ↑ 1.0 4,620 1

Bitmap Index Scan on hosts_groups_2 (cost=0.00..100.94 rows=4,620 width=0) (actual time=0.604..0.604 rows=4,620 loops=1)

  • Index Cond: (groupid = 594)
24. 1,656.637 1,656.637 ↓ 0.0 0 1,656,637

Index Only Scan using event_suppress_1 on event_suppress es (cost=0.42..0.47 rows=2 width=8) (actual time=0.001..0.001 rows=0 loops=1,656,637)

  • Index Cond: (eventid = p.eventid)
  • Heap Fetches: 2,055
Total runtime : 79,249.618 ms