explain.depesz.com

PostgreSQL's explain analyze made readable

Result: h7cj

Settings
# exclusive inclusive rows x rows loops node
1. 0.023 1,975.728 ↑ 7.0 1 1

GroupAggregate (cost=146,584.53..604,045.64 rows=7 width=146) (actual time=1,975.728..1,975.728 rows=1 loops=1)

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

CTE sys_role_code

3. 193.597 1,041.447 ↓ 1.0 2,872,992 1

Append (cost=1.18..112,947.98 rows=2,872,865 width=226) (actual time=0.031..1,041.447 rows=2,872,992 loops=1)

4. 479.586 666.468 ↓ 1.0 2,679,807 1

Hash Join (cost=1.18..54,627.81 rows=2,679,697 width=226) (actual time=0.030..666.468 rows=2,679,807 loops=1)

  • Hash Cond: (asr.system_role_id = sr.system_role_id)
5. 186.871 186.871 ↓ 1.0 2,679,807 1

Seq Scan on account_system_role asr (cost=0.00..43,865.97 rows=2,679,697 width=8) (actual time=0.010..186.871 rows=2,679,807 loops=1)

6. 0.007 0.011 ↑ 1.0 8 1

Hash (cost=1.08..1.08 rows=8 width=222) (actual time=0.011..0.011 rows=8 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
7. 0.004 0.004 ↑ 1.0 8 1

Seq Scan on system_role sr (cost=0.00..1.08 rows=8 width=222) (actual time=0.003..0.004 rows=8 loops=1)

8. 31.336 181.382 ↓ 1.0 193,185 1

Hash Join (cost=2.10..15,227.20 rows=193,168 width=226) (actual time=0.103..181.382 rows=193,185 loops=1)

  • Hash Cond: (o_1.system_role_id = sr_1.system_role_id)
9. 54.720 150.031 ↓ 1.0 193,185 1

Merge Join (cost=0.92..14,450.33 rows=193,168 width=8) (actual time=0.072..150.031 rows=193,185 loops=1)

  • Merge Cond: (oa.organization_id = o_1.organization_id)
10. 53.474 53.474 ↓ 1.0 193,185 1

Index Only Scan using x_uq_organization_account on organization_account oa (cost=0.42..4,660.94 rows=193,168 width=8) (actual time=0.043..53.474 rows=193,185 loops=1)

  • Heap Fetches: 193,185
11. 41.837 41.837 ↑ 1.0 191,098 1

Index Scan using organization_pkey on organization o_1 (cost=0.42..6,897.02 rows=191,307 width=8) (actual time=0.024..41.837 rows=191,098 loops=1)

12. 0.009 0.015 ↑ 1.0 8 1

Hash (cost=1.08..1.08 rows=8 width=222) (actual time=0.015..0.015 rows=8 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
13. 0.006 0.006 ↑ 1.0 8 1

Seq Scan on system_role sr_1 (cost=0.00..1.08 rows=8 width=222) (actual time=0.005..0.006 rows=8 loops=1)

14.          

CTE org_data

15. 26.594 201.238 ↑ 1.0 199,106 1

Unique (cost=32,067.71..33,635.56 rows=209,046 width=8) (actual time=156.591..201.238 rows=199,106 loops=1)

16. 77.565 174.644 ↑ 1.0 199,107 1

Sort (cost=32,067.71..32,590.33 rows=209,046 width=8) (actual time=156.590..174.644 rows=199,107 loops=1)

  • Sort Key: oa_1.organization_id, oa_1.account_id
  • Sort Method: external merge Disk: 3,528kB
17. 13.341 97.079 ↑ 1.0 199,107 1

Append (cost=0.00..11,960.88 rows=209,046 width=8) (actual time=0.008..97.079 rows=199,107 loops=1)

18. 19.851 19.851 ↓ 1.0 193,185 1

Seq Scan on organization_account oa_1 (cost=0.00..3,162.68 rows=193,168 width=8) (actual time=0.007..19.851 rows=193,185 loops=1)

19. 11.322 63.887 ↑ 2.7 5,922 1

Merge Join (cost=1.32..5,662.51 rows=15,878 width=8) (actual time=0.096..63.887 rows=5,922 loops=1)

  • Merge Cond: (o_2.organization_parent_id = oa_2.organization_id)
20. 3.752 3.752 ↑ 20.2 9,493 1

Index Scan using ix_organization_organization_parent_id on organization o_2 (cost=0.42..6,898.02 rows=191,307 width=8) (actual time=0.042..3.752 rows=9,493 loops=1)

21. 48.813 48.813 ↓ 1.0 195,887 1

Index Only Scan using x_uq_organization_account on organization_account oa_2 (cost=0.42..4,660.94 rows=193,168 width=8) (actual time=0.035..48.813 rows=195,887 loops=1)

  • Heap Fetches: 195,887
22. 0.003 232.965 ↑ 1,045.0 1 1

Nested Loop Left Join (cost=0.99..4,723.85 rows=1,045 width=58) (actual time=232.951..232.965 rows=1 loops=1)

  • Join Filter: (o.account_id = a.account_id)
23. 0.002 0.060 ↑ 1.0 1 1

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

  • Join Filter: (a.account_id = aa.account_id)
24. 0.004 0.044 ↑ 1.0 1 1

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

  • Join Filter: (a.account_status_id = acs.account_status_id)
  • Rows Removed by Join Filter: 6
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.008..0.012 rows=7 loops=1)

26. 0.006 0.028 ↑ 1.0 1 7

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

27. 0.022 0.022 ↑ 1.0 1 1

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

  • Index Cond: (account_id = 488,143)
28. 0.014 0.014 ↑ 1.0 1 1

Index Scan using ix_artist_account_id on artist aa (cost=0.43..1.45 rows=1 width=8) (actual time=0.011..0.014 rows=1 loops=1)

  • Index Cond: (account_id = 488,143)
29. 232.902 232.902 ↓ 0.0 0 1

CTE Scan on org_data o (cost=0.00..4,703.53 rows=1,045 width=8) (actual time=232.902..232.902 rows=0 loops=1)

  • Filter: (account_id = 488,143)
  • Rows Removed by Filter: 199,106
30.          

SubPlan (for GroupAggregate)

31. 0.025 1,742.740 ↑ 1.0 1 1

Aggregate (cost=64,675.38..64,675.39 rows=1 width=32) (actual time=1,742.740..1,742.740 rows=1 loops=1)

32. 1,742.715 1,742.715 ↑ 14,364.0 1 1

CTE Scan on sys_role_code sr_2 (cost=0.00..64,639.46 rows=14,364 width=222) (actual time=463.403..1,742.715 rows=1 loops=1)

  • Filter: (account_id = a.account_id)
  • Rows Removed by Filter: 2,872,991
Planning time : 1.225 ms
Execution time : 1,987.985 ms