explain.depesz.com

PostgreSQL's explain analyze made readable

Result: EqYL

Settings
# exclusive inclusive rows x rows loops node
1. 475.408 18,684.617 ↓ 1.0 6,675 1

Sort (cost=1,340,704.97..1,340,721.08 rows=6,444 width=66) (actual time=18,643.341..18,684.617 rows=6,675 loops=1)

  • Sort Key: users.username
  • Sort Method: external merge Disk: 5,280kB
2. 380.284 18,209.209 ↓ 1.0 6,675 1

Seq Scan on users (cost=0.00..1,340,297.27 rows=6,444 width=66) (actual time=0.108..18,209.209 rows=6,675 loops=1)

3.          

SubPlan (for Seq Scan)

4. 340.425 720.900 ↑ 2.0 1 6,675

Sort (cost=12.34..12.34 rows=2 width=794) (actual time=0.106..0.108 rows=1 loops=6,675)

  • Sort Key: ssh_keys.key
  • Sort Method: quicksort Memory: 26kB
5. 71.883 380.475 ↑ 2.0 1 6,675

Result (cost=4.67..12.33 rows=2 width=794) (actual time=0.049..0.057 rows=1 loops=6,675)

  • One-Time Filter: users.is_active
6. 38.574 308.592 ↑ 2.0 1 4,286

Bitmap Heap Scan on ssh_keys (cost=4.67..12.33 rows=2 width=794) (actual time=0.071..0.072 rows=1 loops=4,286)

  • Recheck Cond: ((username)::text = (users.username)::text)
  • Heap Blocks: exact=3,610
7. 270.018 270.018 ↑ 2.0 1 4,286

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

  • Index Cond: ((username)::text = (users.username)::text)
8. 307.050 17,108.025 ↑ 22.2 9 6,675

Unique (cost=194.42..195.62 rows=200 width=274) (actual time=2.509..2.563 rows=9 loops=6,675)

9.          

CTE all_groups

10. 510.666 4,465.575 ↑ 53.4 9 6,675

Recursive Union (cost=4.50..110.44 rows=481 width=23) (actual time=0.098..0.669 rows=9 loops=6,675)

11. 234.127 1,061.325 ↑ 1.4 8 6,675

Result (cost=4.50..43.91 rows=11 width=18) (actual time=0.068..0.159 rows=8 loops=6,675)

  • One-Time Filter: users.is_active
12. 484.318 827.198 ↓ 1.1 12 4,286

Bitmap Heap Scan on group_memberships (cost=4.50..43.91 rows=11 width=18) (actual time=0.104..0.193 rows=12 loops=4,286)

  • Recheck Cond: ((username)::text = (users.username)::text)
  • Heap Blocks: exact=43,566
13. 342.880 342.880 ↓ 1.2 13 4,286

Bitmap Index Scan on group_memberships_pkey (cost=0.00..4.50 rows=11 width=0) (actual time=0.080..0.080 rows=13 loops=4,286)

  • Index Cond: ((username)::text = (users.username)::text)
14. 1,429.712 2,893.584 ↑ 47.0 1 7,863

Hash Join (cost=3.58..5.69 rows=47 width=23) (actual time=0.343..0.368 rows=1 loops=7,863)

  • Hash Cond: ((gd.groupname)::text = (all_groups.parent_groupname)::text)
15. 897.736 897.736 ↓ 1.3 62 5,474

Seq Scan on group_delegations gd (cost=0.00..1.47 rows=47 width=45) (actual time=0.006..0.164 rows=62 loops=5,474)

16. 306.657 566.136 ↑ 13.8 8 7,863

Hash (cost=2.20..2.20 rows=110 width=274) (actual time=0.072..0.072 rows=8 loops=7,863)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
17. 259.479 259.479 ↑ 13.8 8 7,863

WorkTable Scan on all_groups (cost=0.00..2.20 rows=110 width=274) (actual time=0.009..0.033 rows=8 loops=7,863)

18. 1,395.075 16,800.975 ↑ 26.7 9 6,675

Sort (cost=83.98..84.58 rows=240 width=274) (actual time=2.507..2.517 rows=9 loops=6,675)

  • Sort Key: all_groups_1.parent_groupname
  • Sort Method: quicksort Memory: 25kB
19. 645.845 15,405.900 ↑ 26.7 9 6,675

Hash Anti Join (cost=60.62..74.49 rows=240 width=274) (actual time=1.625..2.308 rows=9 loops=6,675)

  • Hash Cond: ((all_groups_1.parent_groupname)::text = (disabled_memberships.groupname)::text)
20. 4,806.000 4,806.000 ↑ 53.4 9 6,675

CTE Scan on all_groups all_groups_1 (cost=0.00..9.62 rows=481 width=274) (actual time=0.108..0.720 rows=9 loops=6,675)

21. 68.560 9,954.055 ↓ 0.0 0 4,285

Hash (cost=60.60..60.60 rows=2 width=20) (actual time=2.323..2.323 rows=0 loops=4,285)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
22. 9,885.495 9,885.495 ↓ 0.0 0 4,285

Seq Scan on disabled_memberships (cost=0.00..60.60 rows=2 width=20) (actual time=1.953..2.307 rows=0 loops=4,285)

  • Filter: ((username)::text = (users.username)::text)
  • Rows Removed by Filter: 1,640
Planning time : 6.106 ms
Execution time : 18,705.110 ms