explain.depesz.com

PostgreSQL's explain analyze made readable

Result: l9A2l

Settings
# exclusive inclusive rows x rows loops node
1. 2,023.595 5,126.854 ↑ 1.0 8,407 1

GroupAggregate (cost=41,341,503.26..42,088,119.85 rows=8,442 width=132) (actual time=2,981.981..5,126.854 rows=8,407 loops=1)

  • Group Key: u.username
2.          

CTE all_memberships

3. 310.041 1,509.962 ↑ 315.4 545,596 1

Recursive Union (cost=0.00..11,648,233.26 rows=172,055,157 width=301) (actual time=0.012..1,509.962 rows=545,596 loops=1)

4. 20.801 20.801 ↑ 1.0 149,354 1

Seq Scan on group_memberships gm (cost=0.00..3,758.30 rows=151,457 width=30) (actual time=0.009..20.801 rows=149,354 loops=1)

  • Filter: ((expires_at IS NULL) OR (expires_at >= now()))
  • Rows Removed by Filter: 378
5. 116.262 1,179.120 ↑ 76.1 225,900 2

Merge Join (cost=558,683.86..820,337.18 rows=17,190,370 width=301) (actual time=535.138..589.560 rows=225,900 loops=2)

  • Merge Cond: ((gd.groupname)::text = (al_1.groupname)::text)
6. 3.512 4.072 ↑ 1.1 1,998 2

Sort (cost=189.24..194.91 rows=2,270 width=41) (actual time=1.765..2.036 rows=1,998 loops=2)

  • Sort Key: gd.groupname
  • Sort Method: quicksort Memory: 303kB
7. 0.560 0.560 ↓ 1.0 2,292 2

Seq Scan on group_delegations gd (cost=0.00..62.70 rows=2,270 width=41) (actual time=0.019..0.280 rows=2,292 loops=2)

8. 82.972 1,058.786 ↑ 3.1 493,726 2

Materialize (cost=558,494.62..566,067.47 rows=1,514,570 width=548) (actual time=471.017..529.393 rows=493,726 loops=2)

9. 933.200 975.814 ↑ 5.6 272,798 2

Sort (cost=558,494.62..562,281.05 rows=1,514,570 width=548) (actual time=471.011..487.907 rows=272,798 loops=2)

  • Sort Key: al_1.groupname
  • Sort Method: quicksort Memory: 45,085kB
10. 42.614 42.614 ↑ 5.6 272,798 2

WorkTable Scan on all_memberships al_1 (cost=0.00..30,291.40 rows=1,514,570 width=548) (actual time=0.002..21.307 rows=272,798 loops=2)

11. 1,120.056 3,064.219 ↑ 157.5 543,451 1

Sort (cost=29,693,270.00..29,907,263.60 rows=85,597,441 width=342) (actual time=2,980.013..3,064.219 rows=543,451 loops=1)

  • Sort Key: u.username
  • Sort Method: quicksort Memory: 102,972kB
12. 167.293 1,944.163 ↑ 157.5 543,451 1

Hash Right Join (cost=835.73..4,957,158.63 rows=85,597,441 width=342) (actual time=11.121..1,944.163 rows=543,451 loops=1)

  • Hash Cond: ((al.username)::text = (u.username)::text)
13. 1,770.641 1,772.893 ↑ 157.5 543,450 1

CTE Scan on all_memberships al (cost=536.79..4,732,053.61 rows=85,597,441 width=548) (actual time=7.122..1,772.893 rows=543,450 loops=1)

  • Filter: (((groupname)::text !~~ 't-%'::text) AND (NOT (hashed SubPlan 3)))
  • Rows Removed by Filter: 2,146
14.          

SubPlan (for CTE Scan)

15. 2.252 2.252 ↓ 1.0 20,151 1

Seq Scan on disabled_memberships dm (cost=0.00..487.03 rows=19,903 width=30) (actual time=0.006..2.252 rows=20,151 loops=1)

16. 2.367 3.977 ↑ 1.0 8,407 1

Hash (cost=193.42..193.42 rows=8,442 width=68) (actual time=3.977..3.977 rows=8,407 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 985kB
17. 1.610 1.610 ↑ 1.0 8,407 1

Seq Scan on users u (cost=0.00..193.42 rows=8,442 width=68) (actual time=0.011..1.610 rows=8,407 loops=1)

18.          

SubPlan (for GroupAggregate)

19. 9.760 39.040 ↑ 2.0 1 4,880

Sort (cost=12.37..12.38 rows=2 width=794) (actual time=0.008..0.008 rows=1 loops=4,880)

  • Sort Key: ssh_keys.key
  • Sort Method: quicksort Memory: 25kB
20. 4.880 29.280 ↑ 2.0 1 4,880

Bitmap Heap Scan on ssh_keys (cost=4.67..12.36 rows=2 width=794) (actual time=0.006..0.006 rows=1 loops=4,880)

  • Recheck Cond: ((username)::text = (u.username)::text)
  • Heap Blocks: exact=4,259
21. 24.400 24.400 ↑ 2.0 1 4,880

Bitmap Index Scan on u__ssh_keys__username_key (cost=0.00..4.67 rows=2 width=0) (actual time=0.005..0.005 rows=1 loops=4,880)

  • Index Cond: ((username)::text = (u.username)::text)
Planning time : 0.289 ms
Execution time : 5,352.773 ms