explain.depesz.com

PostgreSQL's explain analyze made readable

Result: sHXz

Settings
# exclusive inclusive rows x rows loops node
1. 241.916 21,728.281 ↓ 1.0 6,677 1

Sort (cost=122,889,829.01..122,889,845.70 rows=6,675 width=66) (actual time=21,712.636..21,728.281 rows=6,677 loops=1)

  • Sort Key: users.username
  • Sort Method: external merge Disk: 5,328kB
2. 243.613 21,486.365 ↓ 1.0 6,677 1

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

3.          

SubPlan (for Seq Scan)

4. 368.768 368.768 ↑ 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.081..0.086 rows=1 loops=4,288)

  • Index Cond: (username = (users.username)::text)
  • Heap Fetches: 165
5. 1,260.672 20,873.984 ↑ 7.1 14 4,288

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

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

CTE all_groups

7. 585.241 14,767.872 ↑ 300.0 14 4,288

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

8. 587.456 587.456 ↓ 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.056..0.137 rows=12 loops=4,288)

  • Index Cond: (username = (users.username)::text)
  • Heap Fetches: 31,633
9. 6,603.799 13,595.175 ↑ 209.5 2 5,493

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

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

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

11. 159.297 329.580 ↑ 9.1 11 5,493

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

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

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

13. 621.760 19,613.312 ↑ 7.1 14 4,288

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

  • Group Key: all_groups_1.parent_groupname
14. 15,510.220 18,991.552 ↑ 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.227..4.429 rows=14 loops=4,288)

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

SubPlan (for CTE Scan)

16. 3,481.332 3,481.332 ↓ 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.057..0.057 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 : 0.526 ms
Execution time : 21,736.551 ms