explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vEcT : Optimization for: plan #inB6

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.004 79,801.835 ↑ 1.0 1 1

Aggregate (cost=43,937.05..43,937.06 rows=1 width=8) (actual time=79,801.835..79,801.835 rows=1 loops=1)

2. 0.000 79,801.831 ↓ 0.0 0 1

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

  • Merge Cond: (profile_hierarchical_children.child_profile_id = x2.aggregate_id)
3. 0.002 79,801.831 ↓ 0.0 0 1

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

4.          

CTE profile_hierarchical_children

5. 40,152.280 65,109.506 ↓ 59.1 22,211,515 1

Recursive Union (cost=0.00..35,250.74 rows=375,533 width=36) (actual time=0.018..65,109.506 rows=22,211,515 loops=1)

6. 1.218 1.218 ↑ 1.0 5,403 1

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

7. 18,365.682 24,956.008 ↓ 110.5 4,090,199 11

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

  • Hash Cond: (c.child_profile_id = pur.parent_profile_id)
8. 6,586.778 6,586.778 ↓ 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.272..598.798 rows=1,930,533 loops=11)

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

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

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

11. 0.009 79,801.829 ↓ 0.0 0 1

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

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

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

  • Filter: (parent_profile_id = 1aea7152-1fd3-45c1-b4f6-03639a6acc73::uuid)
  • Rows Removed by Filter: 22,211,515
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)