explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qVU1U

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.007 92,870.175 ↑ 1.0 1 1

Aggregate (cost=43,937.14..43,937.15 rows=1 width=8) (actual time=92,870.174..92,870.175 rows=1 loops=1)

2. 0.837 92,870.168 ↓ 0.0 0 1

Sort (cost=43,937.08..43,937.09 rows=4 width=8) (actual time=92,870.168..92,870.168 rows=0 loops=1)

  • Sort Key: x2.created DESC
  • Sort Method: quicksort Memory: 25kB
3. 0.002 92,869.331 ↓ 0.0 0 1

Merge Join (cost=43,904.63..43,937.04 rows=4 width=8) (actual time=92,869.331..92,869.331 rows=0 loops=1)

  • Merge Cond: (profile_hierarchical_children.child_profile_id = x2.aggregate_id)
4. 0.001 92,869.329 ↓ 0.0 0 1

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

5.          

CTE profile_hierarchical_children

6. 48,294.554 74,524.733 ↓ 59.1 22,211,515 1

Recursive Union (cost=0.00..35,250.74 rows=375,533 width=36) (actual time=0.074..74,524.733 rows=22,211,515 loops=1)

7. 4.727 4.727 ↑ 1.0 5,403 1

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

8. 19,203.327 26,225.452 ↓ 110.5 4,090,199 11

Hash Join (cost=183.57..2,762.41 rows=37,013 width=36) (actual time=19.332..2,384.132 rows=4,090,199 loops=11)

  • Hash Cond: (c.child_profile_id = pur.parent_profile_id)
9. 7,018.550 7,018.550 ↓ 107.2 1,930,533 11

WorkTable Scan on profile_hierarchical_children c (cost=0.00..1,215.67 rows=18,010 width=36) (actual time=18.940..638.050 rows=1,930,533 loops=11)

  • Filter: (depth < 10)
  • Rows Removed by Filter: 88,696
10. 2.430 3.575 ↑ 1.0 5,403 1

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

  • Buckets: 8,192 Batches: 1 Memory Usage: 402kB
11. 1.145 1.145 ↑ 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.014..1.145 rows=5,403 loops=1)

12. 0.016 92,869.328 ↓ 0.0 0 1

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

  • Sort Key: profile_hierarchical_children.child_profile_id
  • Sort Method: quicksort Memory: 25kB
13. 92,869.312 92,869.312 ↓ 0.0 0 1

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

  • Filter: (parent_profile_id = 1aea7152-1fd3-45c1-b4f6-03639a6acc73::uuid)
  • Rows Removed by Filter: 22,211,515
14. 0.000 0.000 ↓ 0.0 0

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

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

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

16. 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)
17. 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=32) (never executed)

  • Index Cond: (id = partner_user_roles.user_profile_id)