explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wnSF

Settings
# exclusive inclusive rows x rows loops node
1. 453.666 2,099,173.660 ↓ 18.2 3,648 1

HashAggregate (cost=13,593,458,289.05..13,593,458,291.05 rows=200 width=4) (actual time=2,099,171.111..2,099,173.660 rows=3,648 loops=1)

  • Group Key: g.account_id
2. 24,548.659 2,098,719.994 ↑ 1,161,961.9 515,698 1

Merge Join (cost=50,629,508.86..10,597,351,068.67 rows=599,221,444,076 width=4) (actual time=1,443,108.814..2,098,719.994 rows=515,698 loops=1)

  • Merge Cond: (l.user_id = ru.user_id)
  • Join Filter: ((array_agg(DISTINCT g.app_id ORDER BY g.app_id)) IS DISTINCT FROM (array_agg(DISTINCT l.app_id ORDER BY l.app_id)))
  • Rows Removed by Join Filter: 15,784,520
3. 119,179.511 878,044.090 ↓ 31.3 16,544,179 1

GroupAggregate (cost=8,974,990.16..9,212,246.90 rows=528,483 width=8) (actual time=601,391.303..878,044.090 rows=16,544,179 loops=1)

  • Group Key: l.user_id
4. 390,672.723 758,864.579 ↓ 4.2 128,551,917 1

Sort (cost=8,974,990.16..9,051,873.73 rows=30,753,426 width=8) (actual time=601,386.938..758,864.579 rows=128,551,917 loops=1)

  • Sort Key: l.user_id
  • Sort Method: external merge Disk: 2,261,744kB
5. 158,832.429 368,191.856 ↓ 4.2 128,551,917 1

Hash Join (cost=129,936.30..4,729,690.58 rows=30,753,426 width=8) (actual time=6,942.718..368,191.856 rows=128,551,917 loops=1)

  • Hash Cond: (l.account_id = a.id)
6. 202,417.352 202,417.352 ↓ 1.0 135,242,759 1

Seq Scan on logins l (cost=0.00..3,790,669.65 rows=133,746,767 width=12) (actual time=0.556..202,417.352 rows=135,242,759 loops=1)

  • Filter: ((NOT override_roles) AND (app_id IS NOT NULL))
  • Rows Removed by Filter: 804,017
7. 28.027 6,942.075 ↓ 1.0 34,800 1

Hash (cost=129,501.77..129,501.77 rows=34,762 width=4) (actual time=6,942.075..6,942.075 rows=34,800 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 1,736kB
8. 6,914.048 6,914.048 ↓ 1.0 34,800 1

Seq Scan on accounts a (cost=0.00..129,501.77 rows=34,762 width=4) (actual time=0.013..6,914.048 rows=34,800 loops=1)

  • Filter: ((enabled IS TRUE) AND (cached_last_user_login > (now() - '2 years'::interval)))
  • Rows Removed by Filter: 116,044
9. 18,466.251 1,196,127.245 ↑ 14.0 16,336,947 1

Materialize (cost=41,654,518.69..49,631,366.10 rows=227,909,926 width=40) (actual time=841,713.194..1,196,127.245 rows=16,336,947 loops=1)

10. 140,860.368 1,177,660.994 ↑ 14.0 16,336,947 1

GroupAggregate (cost=41,654,518.69..46,782,492.03 rows=227,909,926 width=12) (actual time=841,713.187..1,177,660.994 rows=16,336,947 loops=1)

  • Group Key: ru.user_id, g.account_id
11. 474,982.991 1,036,800.626 ↑ 1.5 156,661,326 1

Sort (cost=41,654,518.69..42,224,293.51 rows=227,909,926 width=12) (actual time=841,698.303..1,036,800.626 rows=156,661,326 loops=1)

  • Sort Key: ru.user_id, g.account_id
  • Sort Method: external merge Disk: 3,368,912kB
12. 209,105.253 561,817.635 ↑ 1.5 156,661,326 1

Merge Join (cost=156,841.97..6,121,242.81 rows=227,909,926 width=12) (actual time=1,456.056..561,817.635 rows=156,661,326 loops=1)

  • Merge Cond: (ru.role_id = g.role_id)
13. 261,577.718 261,577.718 ↑ 1.0 61,411,120 1

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 time=0.567..261,577.718 rows=61,411,120 loops=1)

  • Heap Fetches: 19,512,585
14. 89,938.498 91,134.664 ↓ 1,227.2 156,689,213 1

Sort (cost=153,903.42..154,222.62 rows=127,680 width=12) (actual time=1,454.658..91,134.664 rows=156,689,213 loops=1)

  • Sort Key: g.role_id
  • Sort Method: quicksort Memory: 28,152kB
15. 545.196 1,196.166 ↓ 2.7 338,411 1

Hash Join (cost=129,936.30..143,074.77 rows=127,680 width=12) (actual time=274.781..1,196.166 rows=338,411 loops=1)

  • Hash Cond: (g.account_id = a_1.id)
16. 376.292 376.292 ↓ 1.0 554,388 1

Seq Scan on grants g (cost=0.00..9,784.78 rows=553,837 width=12) (actual time=0.010..376.292 rows=554,388 loops=1)

  • Filter: (app_id IS NOT NULL)
  • Rows Removed by Filter: 40,416
17. 24.978 274.678 ↓ 1.0 34,800 1

Hash (cost=129,501.77..129,501.77 rows=34,762 width=4) (actual time=274.678..274.678 rows=34,800 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 1,736kB
18. 249.700 249.700 ↓ 1.0 34,800 1

Seq Scan on accounts a_1 (cost=0.00..129,501.77 rows=34,762 width=4) (actual time=0.021..249.700 rows=34,800 loops=1)

  • Filter: ((enabled IS TRUE) AND (cached_last_user_login > (now() - '2 years'::interval)))
  • Rows Removed by Filter: 116,044
Planning time : 16.360 ms
Execution time : 2,099,550.203 ms