explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.004 19,814.223 ↑ 1.0 1 1

Aggregate (cost=43,828.38..43,828.39 rows=1 width=8) (actual time=19,814.223..19,814.223 rows=1 loops=1)

2. 0.010 19,814.219 ↓ 0.0 0 1

Hash Join (cost=35,353.02..43,828.37 rows=4 width=0) (actual time=19,814.219..19,814.219 rows=0 loops=1)

  • Hash Cond: (profile_hierarchical_children.child_profile_id = x2.aggregate_id)
3. 19,771.431 19,771.431 ↓ 0.0 0 1

CTE Scan on profile_hierarchical_children (cost=35,250.74..43,700.23 rows=1,878 width=32) (actual time=19,771.431..19,771.431 rows=0 loops=1)

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

CTE profile_hierarchical_children

5. 10,210.890 15,038.121 ↓ 19.8 7,423,093 1

Recursive Union (cost=0.00..35,250.74 rows=375,533 width=36) (actual time=0.022..15,038.121 rows=7,423,093 loops=1)

6. 1.215 1.215 ↑ 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.215 rows=5,403 loops=1)

7. 3,059.253 4,826.016 ↓ 42.8 1,583,508 6

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

  • Hash Cond: (c.child_profile_id = pur.parent_profile_id)
8. 1,763.034 1,763.034 ↓ 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=216.503..293.839 rows=248,727 loops=6)

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

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

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

11. 0.027 42.778 ↑ 1.0 6 1

Hash (cost=102.20..102.20 rows=6 width=16) (actual time=42.778..42.778 rows=6 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
12. 0.049 42.751 ↑ 1.0 6 1

Nested Loop (cost=0.28..102.20 rows=6 width=16) (actual time=2.435..42.751 rows=6 loops=1)

13. 39.198 39.198 ↑ 1.0 6 1

Seq Scan on partner_user_roles (cost=0.00..56.41 rows=6 width=8) (actual time=1.203..39.198 rows=6 loops=1)

  • Filter: (role = AGENT::text)
  • Rows Removed by Filter: 2,667
14. 3.504 3.504 ↑ 1.0 1 6

Index Scan using partner_user_profiles_pkey on partner_user_profiles x2 (cost=0.28..7.63 rows=1 width=24) (actual time=0.583..0.584 rows=1 loops=6)

  • Index Cond: (id = partner_user_roles.user_profile_id)