explain.depesz.com

PostgreSQL's explain analyze made readable

Result: E5nt

Settings
# exclusive inclusive rows x rows loops node
1. 0.164 61,248.035 ↑ 1.0 2,001 1

Limit (cost=14,249,169.88..14,249,174.88 rows=2,001 width=60) (actual time=61,247.528..61,248.035 rows=2,001 loops=1)

2. 78.566 61,247.871 ↑ 105.4 2,001 1

Sort (cost=14,249,169.88..14,249,697.38 rows=211,001 width=60) (actual time=61,247.525..61,247.871 rows=2,001 loops=1)

  • Sort Key: p.eventid
  • Sort Method: top-N heapsort Memory: 271kB
3. 1,336.202 61,169.305 ↓ 2.5 536,230 1

HashAggregate (cost=14,234,435.14..14,236,545.15 rows=211,001 width=60) (actual time=60,902.117..61,169.305 rows=536,230 loops=1)

4. 340.416 59,833.103 ↓ 7.8 1,650,557 1

Nested Loop Anti Join (cost=143,546.07..14,231,270.13 rows=211,001 width=60) (actual time=911.962..59,833.103 rows=1,650,557 loops=1)

5. 429.600 57,834.132 ↓ 7.1 1,658,555 1

Hash Join (cost=143,545.65..14,125,962.92 rows=234,754 width=60) (actual time=911.941..57,834.132 rows=1,658,555 loops=1)

  • Hash Cond: (i.hostid = hg.hostid)
6. 608.500 57,401.903 ↓ 3.0 1,747,477 1

Hash Join (cost=142,958.06..14,106,412.10 rows=577,937 width=68) (actual time=909.295..57,401.903 rows=1,747,477 loops=1)

  • Hash Cond: (f.itemid = i.itemid)
7. 268.453 55,885.996 ↓ 3.0 1,747,477 1

Nested Loop (cost=0.85..13,951,896.15 rows=577,937 width=68) (actual time=0.368..55,885.996 rows=1,747,477 loops=1)

8. 305.378 54,374.573 ↓ 2.0 621,485 1

Index Scan using problem_3 on problem p (cost=0.42..13,748,685.85 rows=306,216 width=60) (actual time=0.362..54,374.573 rows=621,485 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,971.880 54,069.195 ↓ 0.0 0 621,485

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

  • Filter: ((max(r.permission) < 2) OR (min(r.permission) IS NULL) OR (min(r.permission) = 0))
  • Rows Removed by Filter: 1
11. 3,303.830 49,097.315 ↓ 1.6 42 621,485

Nested Loop Left Join (cost=1.56..22.06 rows=27 width=12) (actual time=0.006..0.079 rows=42 loops=621,485)

12. 1,476.926 10,565.245 ↓ 1.6 14 621,485

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

13. 855.441 5,593.365 ↓ 1.5 3 621,485

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

14. 1,242.970 1,242.970 ↓ 1.5 3 621,485

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,485)

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

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,477)

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

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,477)

  • Index Cond: (hostid = i_1.hostid)
  • Heap Fetches: 1,017,072
17. 35,228.240 35,228.240 ↑ 1.0 3 8,807,060

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,807,060)

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

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,485)

  • Index Cond: (triggerid = p.objectid)
19. 376.485 907.407 ↓ 1.0 1,682,682 1

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

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

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

21. 0.728 2.629 ↑ 1.0 4,620 1

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

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

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

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

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,555)

  • Index Cond: (eventid = p.eventid)
  • Heap Fetches: 2,069
Total runtime : 61,249.299 ms