explain.depesz.com

PostgreSQL's explain analyze made readable

Result: NOVvt

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

HashAggregate (cost=13,775,868,353.94..13,775,868,355.94 rows=200 width=4) (actual rows= loops=)

  • Group Key: expected_apps.account_id
2.          

CTE expected_apps

3. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=42,075,894.83..47,257,144.14 rows=230,277,747 width=12) (actual rows= loops=)

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

Sort (cost=42,075,894.83..42,651,589.20 rows=230,277,747 width=12) (actual rows= loops=)

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

Merge Join (cost=157,012.73..6,156,283.72 rows=230,277,747 width=12) (actual rows= loops=)

  • Merge Cond: (ru.role_id = g.role_id)
6. 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=)

7. 0.000 0.000 ↓ 0.0

Sort (cost=154,047.39..154,369.90 rows=129,006 width=12) (actual rows= loops=)

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

Hash Join (cost=129,941.84..143,096.66 rows=129,006 width=12) (actual rows= loops=)

  • Hash Cond: (g.account_id = a.id)
9. 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)
10. 0.000 0.000 ↓ 0.0

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

11. 0.000 0.000 ↓ 0.0

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

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

CTE actual_apps

13. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=9,046,811.47..9,287,002.98 rows=528,483 width=8) (actual rows= loops=)

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

Sort (cost=9,046,811.47..9,124,673.29 rows=31,144,729 width=8) (actual rows= loops=)

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

Hash Join (cost=129,941.84..4,744,653.02 rows=31,144,729 width=8) (actual rows= loops=)

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

Seq Scan on logins l (cost=0.00..3,800,423.03 rows=134,090,897 width=12) (actual rows= loops=)

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

Hash (cost=129,502.75..129,502.75 rows=35,127 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,127 width=4) (actual rows= loops=)

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

Merge Join (cost=42,947,215.48..10,692,089,576.95 rows=605,446,925,975 width=4) (actual rows= loops=)

  • Merge Cond: (actual_apps.user_id = expected_apps.user_id)
  • Join Filter: (expected_apps.expected_apps <> actual_apps.actual_apps)
20. 0.000 0.000 ↓ 0.0

Sort (cost=60,805.93..62,127.13 rows=528,483 width=36) (actual rows= loops=)

  • Sort Key: actual_apps.user_id
21. 0.000 0.000 ↓ 0.0

CTE Scan on actual_apps (cost=0.00..10,569.66 rows=528,483 width=36) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Materialize (cost=42,886,409.56..44,037,798.29 rows=230,277,747 width=40) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Sort (cost=42,886,409.56..43,462,103.92 rows=230,277,747 width=40) (actual rows= loops=)

  • Sort Key: expected_apps.user_id
24. 0.000 0.000 ↓ 0.0

CTE Scan on expected_apps (cost=0.00..4,605,554.94 rows=230,277,747 width=40) (actual rows= loops=)