explain.depesz.com

PostgreSQL's explain analyze made readable

Result: AnQZ

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

HashAggregate (cost=13,734,262,146.42..13,734,262,148.42 rows=200 width=4) (actual rows= loops=)

  • Group Key: g.account_id
2. 0.000 0.000 ↓ 0.0

Merge Join (cost=51,122,158.43..10,707,113,688.67 rows=605,429,691,550 width=4) (actual rows= loops=)

  • Merge Cond: (l.user_id = ru.user_id)
  • Join Filter: ((array_agg(g.app_id ORDER BY g.app_id)) IS DISTINCT FROM (array_agg(l.app_id ORDER BY l.app_id)))
3. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=9,047,413.97..9,287,617.78 rows=528,483 width=8) (actual rows= loops=)

  • Group Key: l.user_id
4. 0.000 0.000 ↓ 0.0

Sort (cost=9,047,413.97..9,125,279.90 rows=31,146,369 width=8) (actual rows= loops=)

  • Sort Key: l.user_id
5. 0.000 0.000 ↓ 0.0

Hash Join (cost=129,941.83..4,745,018.58 rows=31,146,369 width=8) (actual rows= loops=)

  • Hash Cond: (l.account_id = a.id)
6. 0.000 0.000 ↓ 0.0

Seq Scan on logins l (cost=0.00..3,800,731.40 rows=134,101,776 width=12) (actual rows= loops=)

  • Filter: ((NOT override_roles) AND (app_id IS NOT NULL))
7. 0.000 0.000 ↓ 0.0

Hash (cost=129,502.75..129,502.75 rows=35,126 width=4) (actual rows= loops=)

8. 0.000 0.000 ↓ 0.0

Seq Scan on accounts a (cost=0.00..129,502.75 rows=35,126 width=4) (actual rows= loops=)

  • Filter: ((enabled IS TRUE) AND (cached_last_user_login > (now() - '2 years'::interval)))
9. 0.000 0.000 ↓ 0.0

Materialize (cost=42,074,744.46..50,134,236.18 rows=230,271,192 width=40) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=42,074,744.46..47,255,846.28 rows=230,271,192 width=12) (actual rows= loops=)

  • Group Key: ru.user_id, g.account_id
11. 0.000 0.000 ↓ 0.0

Sort (cost=42,074,744.46..42,650,422.44 rows=230,271,192 width=12) (actual rows= loops=)

  • Sort Key: ru.user_id, g.account_id
12. 0.000 0.000 ↓ 0.0

Merge Join (cost=157,012.41..6,156,203.07 rows=230,271,192 width=12) (actual rows= loops=)

  • Merge Cond: (ru.role_id = g.role_id)
13. 0.000 0.000 ↓ 0.0

Index Only Scan using index_roles_users_role_user on roles_users ru (cost=0.56..2,450,139.31 rows=61,532,341 width=8) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Sort (cost=154,047.07..154,369.58 rows=129,003 width=12) (actual rows= loops=)

  • Sort Key: g.role_id
15. 0.000 0.000 ↓ 0.0

Hash Join (cost=129,941.83..143,096.62 rows=129,003 width=12) (actual rows= loops=)

  • Hash Cond: (g.account_id = a_1.id)
16. 0.000 0.000 ↓ 0.0

Seq Scan on grants g (cost=0.00..9,787.33 rows=553,982 width=12) (actual rows= loops=)

  • Filter: (app_id IS NOT NULL)
17. 0.000 0.000 ↓ 0.0

Hash (cost=129,502.75..129,502.75 rows=35,126 width=4) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Seq Scan on accounts a_1 (cost=0.00..129,502.75 rows=35,126 width=4) (actual rows= loops=)

  • Filter: ((enabled IS TRUE) AND (cached_last_user_login > (now() - '2 years'::interval)))