explain.depesz.com

PostgreSQL's explain analyze made readable

Result: upJl : Optimization for: Optimization for: plan #inB6; plan #vEcT

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.004 23,138.598 ↑ 1.0 1 1

Aggregate (cost=43,937.05..43,937.06 rows=1 width=8) (actual time=23,138.597..23,138.598 rows=1 loops=1)

2. 0.002 23,138.594 ↓ 0.0 0 1

Merge Join (cost=43,904.63..43,937.04 rows=4 width=0) (actual time=23,138.594..23,138.594 rows=0 loops=1)

  • Merge Cond: (profile_hierarchical_children.child_profile_id = x2.aggregate_id)
3. 0.000 23,138.592 ↓ 0.0 0 1

Unique (cost=43,802.35..43,811.74 rows=1,836 width=32) (actual time=23,138.592..23,138.592 rows=0 loops=1)

4.          

CTE profile_hierarchical_children

5. 11,768.413 16,801.878 ↓ 19.8 7,423,093 1

Recursive Union (cost=0.00..35,250.74 rows=375,533 width=36) (actual time=0.036..16,801.878 rows=7,423,093 loops=1)

6. 4.607 4.607 ↑ 1.0 5,403 1

Seq Scan on partner_user_relationships (cost=0.00..116.03 rows=5,403 width=36) (actual time=0.032..4.607 rows=5,403 loops=1)

7. 3,175.402 5,028.858 ↓ 42.8 1,583,508 6

Hash Join (cost=183.57..2,762.41 rows=37,013 width=36) (actual time=219.564..838.143 rows=1,583,508 loops=6)

  • Hash Cond: (c.child_profile_id = pur.parent_profile_id)
8. 1,848.954 1,848.954 ↓ 13.8 248,727 6

WorkTable Scan on profile_hierarchical_children c (cost=0.00..1,215.67 rows=18,010 width=36) (actual time=218.799..308.159 rows=248,727 loops=6)

  • Filter: (depth < 5)
  • Rows Removed by Filter: 988,455
9. 3.161 4.502 ↑ 1.0 5,403 1

Hash (cost=116.03..116.03 rows=5,403 width=32) (actual time=4.502..4.502 rows=5,403 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 402kB
10. 1.341 1.341 ↑ 1.0 5,403 1

Seq Scan on partner_user_relationships pur (cost=0.00..116.03 rows=5,403 width=32) (actual time=0.019..1.341 rows=5,403 loops=1)

11. 0.568 23,138.592 ↓ 0.0 0 1

Sort (cost=8,551.61..8,556.30 rows=1,878 width=32) (actual time=23,138.592..23,138.592 rows=0 loops=1)

  • Sort Key: profile_hierarchical_children.child_profile_id
  • Sort Method: quicksort Memory: 25kB
12. 23,138.024 23,138.024 ↓ 0.0 0 1

CTE Scan on profile_hierarchical_children (cost=0.00..8,449.49 rows=1,878 width=32) (actual time=23,138.024..23,138.024 rows=0 loops=1)

  • Filter: (parent_profile_id = 1aea7152-1fd3-45c1-b4f6-03639a6acc73::uuid)
  • Rows Removed by Filter: 7,423,093
13. 0.000 0.000 ↓ 0.0 0

Sort (cost=102.28..102.30 rows=6 width=16) (never executed)

  • Sort Key: x2.aggregate_id
14. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.28..102.20 rows=6 width=16) (never executed)

15. 0.000 0.000 ↓ 0.0 0

Seq Scan on partner_user_roles (cost=0.00..56.41 rows=6 width=8) (never executed)

  • Filter: (role = AGENT::text)
16. 0.000 0.000 ↓ 0.0 0

Index Scan using partner_user_profiles_pkey on partner_user_profiles x2 (cost=0.28..7.63 rows=1 width=24) (never executed)

  • Index Cond: (id = partner_user_roles.user_profile_id)