explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CPsP

Settings
# exclusive inclusive rows x rows loops node
1. 0.138 168,119.125 ↑ 1.0 2,001 1

Limit (cost=14,249,169.88..14,249,174.88 rows=2,001 width=60) (actual time=168,118.634..168,119.125 rows=2,001 loops=1)

2. 77.424 168,118.987 ↑ 105.4 2,001 1

Sort (cost=14,249,169.88..14,249,697.38 rows=211,001 width=60) (actual time=168,118.632..168,118.987 rows=2,001 loops=1)

  • Sort Key: p.eventid
  • Sort Method: top-N heapsort Memory: 267kB
3. 1,504.958 168,041.563 ↓ 2.5 535,902 1

HashAggregate (cost=14,234,435.14..14,236,545.15 rows=211,001 width=60) (actual time=167,771.094..168,041.563 rows=535,902 loops=1)

4. 895.490 166,536.605 ↓ 7.8 1,649,682 1

Nested Loop Anti Join (cost=143,546.07..14,231,270.13 rows=211,001 width=60) (actual time=1,372.989..166,536.605 rows=1,649,682 loops=1)

5. 470.519 162,325.759 ↓ 7.1 1,657,678 1

Hash Join (cost=143,545.65..14,125,962.92 rows=234,754 width=60) (actual time=1,372.968..162,325.759 rows=1,657,678 loops=1)

  • Hash Cond: (i.hostid = hg.hostid)
6. 686.451 161,850.638 ↓ 3.0 1,746,425 1

Hash Join (cost=142,958.06..14,106,412.10 rows=577,937 width=68) (actual time=1,368.351..161,850.638 rows=1,746,425 loops=1)

  • Hash Cond: (f.itemid = i.itemid)
7. 633.463 159,797.971 ↓ 3.0 1,746,425 1

Nested Loop (cost=0.85..13,951,896.15 rows=577,937 width=68) (actual time=0.601..159,797.971 rows=1,746,425 loops=1)

8. 790.383 156,680.208 ↓ 2.0 621,075 1

Index Scan using problem_3 on problem p (cost=0.42..13,748,685.85 rows=306,216 width=60) (actual time=0.594..156,680.208 rows=621,075 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,968.600 155,889.825 ↓ 0.0 0 621,075

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

  • Filter: ((max(r.permission) < 2) OR (min(r.permission) IS NULL) OR (min(r.permission) = 0))
  • Rows Removed by Filter: 1
11. 2,266.474 150,921.225 ↓ 1.6 42 621,075

Nested Loop Left Join (cost=1.56..22.06 rows=27 width=12) (actual time=0.016..0.243 rows=42 loops=621,075)

12. 584.000 25,464.075 ↓ 1.6 14 621,075

Nested Loop (cost=1.27..16.14 rows=9 width=16) (actual time=0.011..0.041 rows=14 loops=621,075)

13. 817.600 16,147.950 ↓ 1.5 3 621,075

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

14. 3,105.375 3,105.375 ↓ 1.5 3 621,075

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

  • Index Cond: (p.objectid = triggerid)
15. 12,224.975 12,224.975 ↑ 1.0 1 1,746,425

Index Scan using items_pkey on items i_1 (cost=0.43..4.45 rows=1 width=16) (actual time=0.004..0.007 rows=1 loops=1,746,425)

  • Index Cond: (itemid = f_1.itemid)
16. 8,732.125 8,732.125 ↓ 1.2 5 1,746,425

Index Only Scan using hosts_groups_1 on hosts_groups hgg (cost=0.41..0.50 rows=4 width=16) (actual time=0.003..0.005 rows=5 loops=1,746,425)

  • Index Cond: (hostid = i_1.hostid)
  • Heap Fetches: 1,015,135
17. 123,190.676 123,190.676 ↑ 1.0 3 8,799,334

Index Scan using test_ind on rights r (cost=0.29..0.63 rows=3 width=12) (actual time=0.005..0.014 rows=3 loops=8,799,334)

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

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

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

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

  • Buckets: 262,144 Batches: 1 Memory Usage: 78,875kB
20. 886.921 886.921 ↓ 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.016..886.921 rows=1,682,666 loops=1)

21. 0.855 4.602 ↑ 1.0 4,620 1

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

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

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

  • Index Cond: (groupid = 594)
24. 3,315.356 3,315.356 ↓ 0.0 0 1,657,678

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

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