explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rbPB

Settings
# exclusive inclusive rows x rows loops node
1. 0.021 128,416.017 ↑ 21.9 50 1

Unique (cost=19,703,235.88..19,703,244.09 rows=1,095 width=16) (actual time=128,415.995..128,416.017 rows=50 loops=1)

2. 0.072 128,415.996 ↑ 21.9 50 1

Sort (cost=19,703,235.88..19,703,238.62 rows=1,095 width=16) (actual time=128,415.994..128,415.996 rows=50 loops=1)

  • Sort Key: (count(DISTINCT t.triggerid)), i.hostid
  • Sort Method: quicksort Memory: 27kB
3. 109.891 128,415.924 ↑ 21.9 50 1

GroupAggregate (cost=1,001.47..19,703,180.60 rows=1,095 width=16) (actual time=526.770..128,415.924 rows=50 loops=1)

  • Group Key: i.hostid
4. 267.243 128,306.033 ↓ 2.5 285,610 1

Nested Loop (cost=1,001.47..19,702,591.18 rows=115,695 width=16) (actual time=23.258..128,306.033 rows=285,610 loops=1)

5. 0.000 291.662 ↓ 1.2 292,998 1

Gather Merge (cost=1,001.04..317,928.82 rows=239,774 width=16) (actual time=22.709..291.662 rows=292,998 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
6. 113.528 1,403.385 ↑ 1.0 58,600 5

Nested Loop (cost=0.98..288,369.37 rows=59,944 width=16) (actual time=9.823..280.677 rows=58,600 loops=5)

7. 801.355 801.355 ↑ 1.1 10,856 5

Parallel Index Scan using items_11 on items i (cost=0.42..197,810.92 rows=12,276 width=16) (actual time=9.755..160.271 rows=10,856 loops=5)

  • Filter: (((hostid >= '30031'::bigint) AND (hostid <= '30040'::bigint)) OR ((hostid >= '30046'::bigint) AND (hostid <= '30079'::bigint)) OR (hostid = ANY ('{29975,30028,30029,30043,31921,31922}'::bigint[])))
  • Rows Removed by Filter: 174700
8. 488.502 488.502 ↑ 9.0 5 54,278

Index Scan using functions_2 on functions f (cost=0.56..6.93 rows=45 width=16) (actual time=0.004..0.009 rows=5 loops=54,278)

  • Index Cond: (itemid = i.itemid)
9. 1,221.898 127,747.128 ↑ 1.0 1 292,998

Index Scan using triggers_pkey on triggers t (cost=0.43..80.85 rows=1 width=8) (actual time=0.436..0.436 rows=1 loops=292,998)

  • Index Cond: (triggerid = f.triggerid)
  • Filter: ((flags = ANY ('{0,4}'::integer[])) AND (NOT (SubPlan 1)))
  • Rows Removed by Filter: 0
10.          

SubPlan (forIndex Scan)

11. 1,428.050 126,525.230 ↓ 0.0 0 285,610

GroupAggregate (cost=69.16..460.91 rows=35 width=40) (actual time=0.443..0.443 rows=0 loops=285,610)

  • Group Key: i_1.hostid
  • Filter: ((max(r.permission) < 2) OR (min(r.permission) IS NULL) OR (min(r.permission) = 0))
  • Rows Removed by Filter: 1
12. 5,190.869 125,097.180 ↑ 1.2 30 285,610

Nested Loop Left Join (cost=69.16..460.21 rows=35 width=12) (actual time=0.327..0.438 rows=30 loops=285,610)

13. 19,135.870 94,536.910 ↑ 1.1 30 285,610

Merge Join (cost=68.88..446.16 rows=32 width=16) (actual time=0.323..0.331 rows=30 loops=285,610)

  • Merge Cond: (hgg.hostid = i_1.hostid)
14. 70,260.060 70,260.060 ↑ 5.2 1,263 285,610

Index Only Scan using hosts_groups_1 on hosts_groups hgg (cost=0.28..361.97 rows=6,574 width=16) (actual time=0.005..0.246 rows=1,263 loops=285,610)

  • Heap Fetches: 360862861
15. 1,142.440 5,140.980 ↓ 4.0 24 285,610

Sort (cost=67.28..67.29 rows=6 width=8) (actual time=0.016..0.018 rows=24 loops=285,610)

  • Sort Key: i_1.hostid
  • Sort Method: quicksort Memory: 25kB
16. 0.000 3,998.540 ↑ 1.2 5 285,610

Nested Loop (cost=0.86..67.20 rows=6 width=8) (actual time=0.006..0.014 rows=5 loops=285,610)

17. 1,428.050 1,428.050 ↑ 1.2 5 285,610

Index Scan using functions_1 on functions f_1 (cost=0.43..16.55 rows=6 width=8) (actual time=0.003..0.005 rows=5 loops=285,610)

  • Index Cond: (t.triggerid = triggerid)
18. 2,601.860 2,601.860 ↑ 1.0 1 1,300,930

Index Scan using items_pkey on items i_1 (cost=0.42..8.44 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=1,300,930)

  • Index Cond: (itemid = f_1.itemid)
19. 25,369.401 25,369.401 ↑ 1.0 1 8,456,467

Index Scan using rights_2 on rights r (cost=0.28..0.43 rows=1 width=12) (actual time=0.001..0.003 rows=1 loops=8,456,467)

  • Index Cond: (id = hgg.groupid)
  • Filter: (groupid = '51'::bigint)
  • Rows Removed by Filter: 6