explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rUb3

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 17.622 ↓ 0.0 0 1

Unique (cost=61,551.96..61,564.70 rows=1,699 width=16) (actual time=17.622..17.622 rows=0 loops=1)

2. 0.004 17.622 ↓ 0.0 0 1

Sort (cost=61,551.96..61,556.21 rows=1,699 width=16) (actual time=17.622..17.622 rows=0 loops=1)

  • Sort Key: (count(DISTINCT t.triggerid)), i.hostid
  • Sort Method: quicksort Memory: 25kB
3. 0.001 17.618 ↓ 0.0 0 1

GroupAggregate (cost=61,375.85..61,405.59 rows=1,699 width=16) (actual time=17.618..17.618 rows=0 loops=1)

  • Group Key: i.hostid
4. 0.004 17.617 ↓ 0.0 0 1

Sort (cost=61,375.85..61,380.10 rows=1,699 width=16) (actual time=17.617..17.617 rows=0 loops=1)

  • Sort Key: i.hostid
  • Sort Method: quicksort Memory: 25kB
5. 0.415 17.613 ↓ 0.0 0 1

Nested Loop (cost=231.99..61,242.22 rows=1,699 width=16) (actual time=17.613..17.613 rows=0 loops=1)

6. 0.337 3.898 ↑ 7.0 532 1

Nested Loop (cost=231.56..32,662.27 rows=3,699 width=16) (actual time=0.601..3.898 rows=532 loops=1)

7. 1.181 1.381 ↑ 7.1 1,090 1

Bitmap Heap Scan on items i (cost=231.13..8,719.41 rows=7,746 width=16) (actual time=0.246..1.381 rows=1,090 loops=1)

  • Recheck Cond: (((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[])))
  • Heap Blocks: exact=373
8. 0.001 0.200 ↓ 0.0 0 1

BitmapOr (cost=231.13..231.13 rows=7,750 width=0) (actual time=0.200..0.200 rows=0 loops=1)

9. 0.047 0.047 ↑ 7.4 199 1

Bitmap Index Scan on items_1 (cost=0.00..42.75 rows=1,469 width=0) (actual time=0.047..0.047 rows=199 loops=1)

  • Index Cond: ((hostid >= '30031'::bigint) AND (hostid <= '30040'::bigint))
10. 0.003 0.003 ↓ 0.0 0 1

Bitmap Index Scan on items_1 (cost=0.00..134.17 rows=4,671 width=0) (actual time=0.003..0.003 rows=0 loops=1)

  • Index Cond: ((hostid >= '30046'::bigint) AND (hostid <= '30079'::bigint))
11. 0.149 0.149 ↑ 1.8 891 1

Bitmap Index Scan on items_1 (cost=0.00..48.41 rows=1,610 width=0) (actual time=0.149..0.149 rows=891 loops=1)

  • Index Cond: (hostid = ANY ('{29975,30028,30029,30043,31921,31922}'::bigint[]))
12. 2.180 2.180 ↓ 0.0 0 1,090

Index Scan using functions_2 on functions f (cost=0.43..3.06 rows=3 width=16) (actual time=0.002..0.002 rows=0 loops=1,090)

  • Index Cond: (itemid = i.itemid)
13. 2.099 13.300 ↓ 0.0 0 532

Index Scan using triggers_pkey on triggers t (cost=0.43..7.73 rows=1 width=8) (actual time=0.025..0.025 rows=0 loops=532)

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

SubPlan (forIndex Scan)

15. 0.487 11.201 ↑ 1.0 1 487

HashAggregate (cost=7.19..7.25 rows=1 width=40) (actual time=0.023..0.023 rows=1 loops=487)

  • Group Key: i_1.hostid
  • Filter: ((max(r.permission) < 2) OR (min(r.permission) IS NULL) OR (min(r.permission) = 0))
16. 0.024 10.714 ↑ 1.0 4 487

Nested Loop Left Join (cost=1.56..7.15 rows=4 width=12) (actual time=0.012..0.022 rows=4 loops=487)

17. 0.127 4.870 ↑ 1.0 4 487

Nested Loop (cost=1.28..5.83 rows=4 width=16) (actual time=0.007..0.010 rows=4 loops=487)

18. 0.247 2.922 ↑ 1.0 1 487

Nested Loop (cost=0.86..5.30 rows=1 width=8) (actual time=0.005..0.006 rows=1 loops=487)

19. 1.461 1.461 ↑ 1.0 1 487

Index Scan using functions_1 on functions f_1 (cost=0.43..2.65 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=487)

  • Index Cond: (t.triggerid = triggerid)
20. 1.214 1.214 ↑ 1.0 1 607

Index Scan using items_pkey on items i_1 (cost=0.43..2.65 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=607)

  • Index Cond: (itemid = f_1.itemid)
21. 1.821 1.821 ↑ 1.3 3 607

Index Only Scan using hosts_groups_1 on hosts_groups hgg (cost=0.42..0.49 rows=4 width=16) (actual time=0.002..0.003 rows=3 loops=607)

  • Index Cond: (hostid = i_1.hostid)
  • Heap Fetches: 843
22. 5.820 5.820 ↓ 0.0 0 1,940

Index Scan using rights_2 on rights r (cost=0.28..0.32 rows=1 width=12) (actual time=0.003..0.003 rows=0 loops=1,940)

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