explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3aay

Settings
# exclusive inclusive rows x rows loops node
1. 0.172 62,278.266 ↑ 1.0 2,001 1

Limit (cost=14,249,169.88..14,249,174.88 rows=2,001 width=60) (actual time=62,277.713..62,278.266 rows=2,001 loops=1)

2. 80.999 62,278.094 ↑ 105.4 2,001 1

Sort (cost=14,249,169.88..14,249,697.38 rows=211,001 width=60) (actual time=62,277.711..62,278.094 rows=2,001 loops=1)

  • Sort Key: p.eventid
  • Sort Method: top-N heapsort Memory: 269kB
3. 1,433.681 62,197.095 ↓ 2.5 536,118 1

HashAggregate (cost=14,234,435.14..14,236,545.15 rows=211,001 width=60) (actual time=61,909.810..62,197.095 rows=536,118 loops=1)

4. 385.824 60,763.414 ↓ 7.8 1,650,276 1

Nested Loop Anti Join (cost=143,546.07..14,231,270.13 rows=211,001 width=60) (actual time=1,101.603..60,763.414 rows=1,650,276 loops=1)

5. 448.209 58,719.318 ↓ 7.1 1,658,272 1

Hash Join (cost=143,545.65..14,125,962.92 rows=234,754 width=60) (actual time=1,101.579..58,719.318 rows=1,658,272 loops=1)

  • Hash Cond: (i.hostid = hg.hostid)
6. 642.691 58,268.277 ↓ 3.0 1,747,101 1

Hash Join (cost=142,958.06..14,106,412.10 rows=577,937 width=68) (actual time=1,098.719..58,268.277 rows=1,747,101 loops=1)

  • Hash Cond: (f.itemid = i.itemid)
7. 328.767 56,528.631 ↓ 3.0 1,747,101 1

Nested Loop (cost=0.85..13,951,896.15 rows=577,937 width=68) (actual time=0.210..56,528.631 rows=1,747,101 loops=1)

8. 901.583 54,957.206 ↓ 2.0 621,329 1

Index Scan using problem_3 on problem p (cost=0.42..13,748,685.85 rows=306,216 width=60) (actual time=0.204..54,957.206 rows=621,329 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,349.303 54,055.623 ↓ 0.0 0 621,329

HashAggregate (cost=22.33..22.74 rows=27 width=12) (actual time=0.087..0.087 rows=0 loops=621,329)

  • Filter: ((max(r.permission) < 2) OR (min(r.permission) IS NULL) OR (min(r.permission) = 0))
  • Rows Removed by Filter: 1
11. 5,168.801 49,706.320 ↓ 1.6 42 621,329

Nested Loop Left Join (cost=1.56..22.06 rows=27 width=12) (actual time=0.007..0.080 rows=42 loops=621,329)

12. 855.101 9,319.935 ↓ 1.6 14 621,329

Nested Loop (cost=1.27..16.14 rows=9 width=16) (actual time=0.005..0.015 rows=14 loops=621,329)

13. 233.772 4,970.632 ↓ 1.5 3 621,329

Nested Loop (cost=0.85..15.07 rows=2 width=8) (actual time=0.003..0.008 rows=3 loops=621,329)

14. 1,242.658 1,242.658 ↓ 1.5 3 621,329

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

  • Index Cond: (p.objectid = triggerid)
15. 3,494.202 3,494.202 ↑ 1.0 1 1,747,101

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

  • Index Cond: (itemid = f_1.itemid)
16. 3,494.202 3,494.202 ↓ 1.2 5 1,747,101

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

  • Index Cond: (hostid = i_1.hostid)
  • Heap Fetches: 1,016,437
17. 35,217.584 35,217.584 ↑ 1.0 3 8,804,396

Index Scan using test_ind on rights r (cost=0.29..0.63 rows=3 width=12) (actual time=0.001..0.004 rows=3 loops=8,804,396)

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

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

  • Index Cond: (triggerid = p.objectid)
19. 517.560 1,096.955 ↓ 1.0 1,682,678 1

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

  • Buckets: 262,144 Batches: 1 Memory Usage: 78,876kB
20. 579.395 579.395 ↓ 1.0 1,682,678 1

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

21. 0.816 2.832 ↑ 1.0 4,620 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 181kB
22. 1.573 2.016 ↑ 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.484..2.016 rows=4,620 loops=1)

  • Recheck Cond: (groupid = 594)
23. 0.443 0.443 ↑ 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.443..0.443 rows=4,620 loops=1)

  • Index Cond: (groupid = 594)
24. 1,658.272 1,658.272 ↓ 0.0 0 1,658,272

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,658,272)

  • Index Cond: (eventid = p.eventid)
  • Heap Fetches: 2,067
Total runtime : 62,279.855 ms