explain.depesz.com

PostgreSQL's explain analyze made readable

Result: uS1k

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 0.032 ↓ 0.0 0 1

GroupAggregate (cost=125,631.55..583,758.64 rows=7 width=146) (actual time=0.032..0.032 rows=0 loops=1)

  • Group Key: a.account_id, acs.code
2.          

CTE sys_role_code

3. 0.000 0.000 ↓ 0.0 0

Append (cost=1.18..110,250.60 rows=2,877,104 width=20) (never executed)

4. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=1.18..54,712.77 rows=2,683,904 width=20) (never executed)

  • Hash Cond: (asr.system_role_id = sr.system_role_id)
5. 0.000 0.000 ↓ 0.0 0

Seq Scan on account_system_role asr (cost=0.00..43,934.04 rows=2,683,904 width=8) (never executed)

6. 0.000 0.000 ↓ 0.0 0

Hash (cost=1.08..1.08 rows=8 width=16) (never executed)

7. 0.000 0.000 ↓ 0.0 0

Seq Scan on system_role sr (cost=0.00..1.08 rows=8 width=16) (never executed)

8. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=7,934.29..12,381.27 rows=193,200 width=20) (never executed)

  • Hash Cond: (o_1.system_role_id = sr_1.system_role_id)
9. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=7,933.11..11,604.27 rows=193,200 width=8) (never executed)

  • Hash Cond: (oa.organization_id = o_1.organization_id)
10. 0.000 0.000 ↓ 0.0 0

Seq Scan on organization_account oa (cost=0.00..3,164.00 rows=193,200 width=8) (never executed)

11. 0.000 0.000 ↓ 0.0 0

Hash (cost=5,543.05..5,543.05 rows=191,205 width=8) (never executed)

12. 0.000 0.000 ↓ 0.0 0

Seq Scan on organization o_1 (cost=0.00..5,543.05 rows=191,205 width=8) (never executed)

13. 0.000 0.000 ↓ 0.0 0

Hash (cost=1.08..1.08 rows=8 width=16) (never executed)

14. 0.000 0.000 ↓ 0.0 0

Seq Scan on system_role sr_1 (cost=0.00..1.08 rows=8 width=16) (never executed)

15.          

CTE org_data

16. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=13,290.41..15,379.96 rows=208,955 width=8) (never executed)

  • Group Key: oa_1.organization_id, oa_1.account_id
17. 0.000 0.000 ↓ 0.0 0

Append (cost=0.00..12,245.63 rows=208,955 width=8) (never executed)

18. 0.000 0.000 ↓ 0.0 0

Seq Scan on organization_account oa_1 (cost=0.00..3,164.00 rows=193,200 width=8) (never executed)

19. 0.000 0.000 ↓ 0.0 0

Merge Join (cost=3.72..5,947.31 rows=15,755 width=8) (never executed)

  • Merge Cond: (o_2.organization_parent_id = oa_2.organization_id)
20. 0.000 0.000 ↓ 0.0 0

Index Scan using ix_organization_organization_parent_id on organization o_2 (cost=0.42..7,244.49 rows=191,205 width=8) (never executed)

21. 0.000 0.000 ↓ 0.0 0

Index Only Scan using x_uq_organization_account on organization_account oa_2 (cost=0.42..4,926.42 rows=193,200 width=8) (never executed)

  • Heap Fetches: 0
22. 0.000 0.031 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.99..4,721.81 rows=1,045 width=58) (actual time=0.031..0.031 rows=0 loops=1)

  • Join Filter: (o.account_id = a.account_id)
23. 0.000 0.031 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.99..7.26 rows=1 width=54) (actual time=0.031..0.031 rows=0 loops=1)

  • Join Filter: (a.account_id = aa.account_id)
24. 0.005 0.031 ↓ 0.0 0 1

Nested Loop (cost=0.56..5.79 rows=1 width=50) (actual time=0.031..0.031 rows=0 loops=1)

  • Join Filter: (a.account_status_id = acs.account_status_id)
25. 0.012 0.012 ↑ 1.0 7 1

Index Scan using ix_uq_account_status_code on account_status acs (cost=0.13..3.24 rows=7 width=12) (actual time=0.011..0.012 rows=7 loops=1)

26. 0.002 0.014 ↓ 0.0 0 7

Materialize (cost=0.43..2.45 rows=1 width=46) (actual time=0.002..0.002 rows=0 loops=7)

27. 0.012 0.012 ↓ 0.0 0 1

Index Scan using account_pkey on account a (cost=0.43..2.45 rows=1 width=46) (actual time=0.012..0.012 rows=0 loops=1)

  • Index Cond: (account_id = 1)
28. 0.000 0.000 ↓ 0.0 0

Index Scan using ix_artist_account_id on artist aa (cost=0.43..1.45 rows=1 width=8) (never executed)

  • Index Cond: (account_id = 1)
29. 0.000 0.000 ↓ 0.0 0

CTE Scan on org_data o (cost=0.00..4,701.49 rows=1,045 width=8) (never executed)

  • Filter: (account_id = 1)
30.          

SubPlan (for GroupAggregate)

31. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=64,770.81..64,770.82 rows=1 width=32) (never executed)

32. 0.000 0.000 ↓ 0.0 0

CTE Scan on sys_role_code sr_2 (cost=0.00..64,734.84 rows=14,386 width=222) (never executed)

  • Filter: (account_id = a.account_id)
Planning time : 1.083 ms
Execution time : 2.863 ms