explain.depesz.com

PostgreSQL's explain analyze made readable

Result: inB6

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.004 83,453.624 ↑ 1.0 1 1

Aggregate (cost=43,937.14..43,937.15 rows=1 width=8) (actual time=83,453.624..83,453.624 rows=1 loops=1)

2. 0.012 83,453.620 ↓ 0.0 0 1

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

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

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

  • Merge Cond: (profile_hierarchical_children.child_profile_id = x2.aggregate_id)
4. 0.000 83,453.606 ↓ 0.0 0 1

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

5.          

CTE profile_hierarchical_children

6. 41,288.179 67,436.181 ↓ 59.1 22,211,515 1

Recursive Union (cost=0.00..35,250.74 rows=375,533 width=36) (actual time=0.028..67,436.181 rows=22,211,515 loops=1)

7. 1.376 1.376 ↑ 1.0 5,403 1

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

8. 18,446.804 26,146.626 ↓ 110.5 4,090,199 11

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

  • Hash Cond: (c.child_profile_id = pur.parent_profile_id)
9. 7,697.019 7,697.019 ↓ 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.868..699.729 rows=1,930,533 loops=11)

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

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

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

12. 0.028 83,453.606 ↓ 0.0 0 1

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

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

CTE Scan on profile_hierarchical_children (cost=0.00..8,449.49 rows=1,878 width=32) (actual time=83,453.578..83,453.578 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)