explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Gecy

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

HashAggregate (cost=31,013,332.20..31,013,334.20 rows=200 width=4) (actual rows= loops=)

  • Group Key: sym_diff.account_id
2.          

CTE expected_apps

3. 0.000 0.000 ↓ 0.0

Merge Join (cost=157,011.99..6,156,701.69 rows=230,264,636 width=12) (actual rows= loops=)

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

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

5. 0.000 0.000 ↓ 0.0

Sort (cost=154,046.65..154,369.15 rows=128,999 width=12) (actual rows= loops=)

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

Hash Join (cost=129,941.82..143,096.57 rows=128,999 width=12) (actual rows= loops=)

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

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

9. 0.000 0.000 ↓ 0.0

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

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

CTE actual_apps

11. 0.000 0.000 ↓ 0.0

Hash Join (cost=129,941.82..4,748,724.08 rows=31,170,550 width=12) (actual rows= loops=)

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

Seq Scan on logins l (cost=0.00..3,803,790.37 rows=134,209,706 width=12) (actual rows= loops=)

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

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

14. 0.000 0.000 ↓ 0.0

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

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

CTE unused

16. 0.000 0.000 ↓ 0.0

HashSetOp Except (cost=0.00..9,803,819.47 rows=8,000,000 width=12) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Append (cost=0.00..7,843,055.58 rows=261,435,186 width=12) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 1 (cost=0.00..6,907,939.08 rows=230,264,636 width=12) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

CTE Scan on expected_apps (cost=0.00..4,605,292.72 rows=230,264,636 width=12) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 2 (cost=0.00..935,116.50 rows=31,170,550 width=12) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

CTE Scan on actual_apps (cost=0.00..623,411.00 rows=31,170,550 width=12) (actual rows= loops=)

22.          

CTE unaccounted

23. 0.000 0.000 ↓ 0.0

HashSetOp Except (cost=0.00..9,803,819.47 rows=3,117,055 width=12) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Append (cost=0.00..7,843,055.58 rows=261,435,186 width=12) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 1_1 (cost=0.00..935,116.50 rows=31,170,550 width=12) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

CTE Scan on actual_apps actual_apps_1 (cost=0.00..623,411.00 rows=31,170,550 width=12) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 2_1 (cost=0.00..6,907,939.08 rows=230,264,636 width=12) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

CTE Scan on expected_apps expected_apps_1 (cost=0.00..4,605,292.72 rows=230,264,636 width=12) (actual rows= loops=)

29.          

CTE sym_diff

30. 0.000 0.000 ↓ 0.0

Append (cost=0.00..222,341.10 rows=11,117,055 width=12) (actual rows= loops=)

31. 0.000 0.000 ↓ 0.0

CTE Scan on unused (cost=0.00..160,000.00 rows=8,000,000 width=12) (actual rows= loops=)

32. 0.000 0.000 ↓ 0.0

CTE Scan on unaccounted (cost=0.00..62,341.10 rows=3,117,055 width=12) (actual rows= loops=)

33. 0.000 0.000 ↓ 0.0

CTE Scan on sym_diff (cost=0.00..222,341.10 rows=11,117,055 width=4) (actual rows= loops=)