explain.depesz.com

PostgreSQL's explain analyze made readable

Result: F7PV

Settings
# exclusive inclusive rows x rows loops node
1. 209.705 5,663.472 ↑ 1.0 6,675 1

Sort (cost=18,747,730.57..18,747,747.26 rows=6,675 width=66) (actual time=5,655.305..5,663.472 rows=6,675 loops=1)

  • Sort Key: users.username
  • Sort Method: external merge Disk: 5,280kB
2. 253.942 5,453.767 ↑ 1.0 6,675 1

Seq Scan on users (cost=0.00..18,747,306.56 rows=6,675 width=66) (actual time=0.039..5,453.767 rows=6,675 loops=1)

3.          

SubPlan (for Seq Scan)

4. 53.542 173.550 ↑ 2.0 1 6,675

Result (cost=0.66..8.69 rows=2 width=794) (actual time=0.020..0.026 rows=1 loops=6,675)

  • One-Time Filter: users.is_active
5. 120.008 120.008 ↑ 2.0 1 4,286

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

  • Index Cond: (username = (users.username)::text)
  • Heap Fetches: 90
6. 801.000 5,026.275 ↑ 11.1 9 6,675

Sort (cost=2,799.61..2,799.86 rows=100 width=274) (actual time=0.744..0.753 rows=9 loops=6,675)

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

CTE all_groups

8. 405.918 2,042.550 ↑ 70.0 9 6,675

Recursive Union (cost=0.41..78.42 rows=630 width=23) (actual time=0.035..0.306 rows=9 loops=6,675)

9. 128.514 347.100 ↑ 1.2 8 6,675

Result (cost=0.41..8.59 rows=10 width=18) (actual time=0.019..0.052 rows=8 loops=6,675)

  • One-Time Filter: users.is_active
10. 218.586 218.586 ↓ 1.2 12 4,286

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

  • Index Cond: (username = (users.username)::text)
  • Heap Fetches: 2,376
11. 586.659 1,289.532 ↑ 62.0 1 7,863

Hash Join (cost=3.25..5.72 rows=62 width=23) (actual time=0.156..0.164 rows=1 loops=7,863)

  • Hash Cond: ((gd.groupname)::text = (all_groups.parent_groupname)::text)
12. 443.394 443.394 ↑ 1.0 62 5,474

Seq Scan on group_delegations gd (cost=0.00..1.62 rows=62 width=43) (actual time=0.014..0.081 rows=62 loops=5,474)

13. 204.438 259.479 ↑ 12.5 8 7,863

Hash (cost=2.00..2.00 rows=100 width=274) (actual time=0.033..0.033 rows=8 loops=7,863)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
14. 55.041 55.041 ↑ 12.5 8 7,863

WorkTable Scan on all_groups (cost=0.00..2.00 rows=100 width=274) (actual time=0.000..0.007 rows=8 loops=7,863)

15. 233.625 4,225.275 ↑ 11.1 9 6,675

HashAggregate (cost=2,716.87..2,717.87 rows=100 width=274) (actual time=0.619..0.633 rows=9 loops=6,675)

  • Group Key: all_groups_1.parent_groupname
16. 2,627.106 3,991.650 ↑ 35.0 9 6,675

CTE Scan on all_groups all_groups_1 (cost=0.00..2,716.09 rows=315 width=274) (actual time=0.054..0.598 rows=9 loops=6,675)

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

SubPlan (for CTE Scan)

18. 1,364.544 1,364.544 ↓ 0.0 0 59,328

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=59,328)

  • Index Cond: ((groupname = (all_groups_1.parent_groupname)::text) AND (username = (users.username)::text))
  • Heap Fetches: 741
Planning time : 0.532 ms
Execution time : 5,667.585 ms