explain.depesz.com

PostgreSQL's explain analyze made readable

Result: doxt

Settings
# exclusive inclusive rows x rows loops node
1. 287.244 10,820.874 ↓ 1.0 6,677 1

Sort (cost=122,889,829.01..122,889,845.70 rows=6,675 width=66) (actual time=10,792.359..10,820.874 rows=6,677 loops=1)

  • Sort Key: users.username
  • Sort Method: external merge Disk: 5,328kB
2. 165.246 10,533.630 ↓ 1.0 6,677 1

Seq Scan on users (cost=0.00..122,889,405.00 rows=6,675 width=66) (actual time=0.670..10,533.630 rows=6,677 loops=1)

3.          

SubPlan (for Seq Scan)

4. 167.232 167.232 ↑ 2.0 1 4,288

Index Only Scan using u__ssh_keys__username_key on ssh_keys (cost=0.66..8.69 rows=2 width=794) (actual time=0.036..0.039 rows=1 loops=4,288)

  • Index Cond: (username = (users.username)::text)
  • Heap Fetches: 165
5. 531.712 10,201.152 ↑ 7.1 14 4,288

Sort (cost=18,401.42..18,401.67 rows=100 width=274) (actual time=2.362..2.379 rows=14 loops=4,288)

  • Sort Key: all_groups_1.parent_groupname
  • Sort Method: quicksort Memory: 25kB
6.          

CTE all_groups

7. 342.801 7,448.256 ↑ 300.0 14 4,288

Recursive Union (cost=0.41..284.60 rows=4,200 width=25) (actual time=0.038..1.737 rows=14 loops=4,288)

8. 321.600 321.600 ↓ 1.2 12 4,288

Index Only Scan using group_memberships_pkey on group_memberships (cost=0.41..8.59 rows=10 width=18) (actual time=0.028..0.075 rows=12 loops=4,288)

  • Index Cond: (username = (users.username)::text)
  • Heap Fetches: 31,633
9. 3,658.878 6,783.855 ↑ 209.5 2 5,493

Hash Join (cost=3.25..19.20 rows=419 width=25) (actual time=1.025..1.235 rows=2 loops=5,493)

  • Hash Cond: ((gd.groupname)::text = (all_groups.parent_groupname)::text)
10. 2,965.680 2,965.680 ↓ 1.1 441 5,492

Seq Scan on group_delegations gd (cost=0.00..10.19 rows=419 width=41) (actual time=0.010..0.540 rows=441 loops=5,492)

11. 126.339 159.297 ↑ 9.1 11 5,493

Hash (cost=2.00..2.00 rows=100 width=274) (actual time=0.029..0.029 rows=11 loops=5,493)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
12. 32.958 32.958 ↑ 9.1 11 5,493

WorkTable Scan on all_groups (cost=0.00..2.00 rows=100 width=274) (actual time=0.000..0.006 rows=11 loops=5,493)

13. 334.464 9,669.440 ↑ 7.1 14 4,288

HashAggregate (cost=18,112.50..18,113.50 rows=100 width=274) (actual time=2.242..2.255 rows=14 loops=4,288)

  • Group Key: all_groups_1.parent_groupname
14. 7,930.228 9,334.976 ↑ 150.0 14 4,288

CTE Scan on all_groups all_groups_1 (cost=0.00..18,107.25 rows=2,100 width=274) (actual time=0.097..2.177 rows=14 loops=4,288)

  • Filter: (NOT (SubPlan 3))
  • Rows Removed by Filter: 0
15.          

SubPlan (for CTE Scan)

16. 1,404.748 1,404.748 ↓ 0.0 0 61,076

Index Only Scan using disabled_memberships_pkey on disabled_memberships dm (cost=0.28..8.30 rows=1 width=20) (actual time=0.023..0.023 rows=0 loops=61,076)

  • Index Cond: ((groupname = (all_groups_1.parent_groupname)::text) AND (username = (users.username)::text))
  • Heap Fetches: 1,464
Planning time : 1.552 ms
Execution time : 10,833.344 ms