explain.depesz.com

PostgreSQL's explain analyze made readable

Result: AFNP : Optimization for: Optimization for: plan #qVU1U; plan #4Grr

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.002 1.416 ↑ 1.0 1 1

Aggregate (cost=2,860.34..2,860.35 rows=1 width=8) (actual time=1.416..1.416 rows=1 loops=1)

2. 0.019 1.414 ↓ 0.0 0 1

Sort (cost=2,860.06..2,860.10 rows=19 width=8) (actual time=1.414..1.414 rows=0 loops=1)

  • Sort Key: x2.created DESC
  • Sort Method: quicksort Memory: 25kB
3. 0.010 1.395 ↓ 0.0 0 1

Hash Join (cost=2,630.84..2,859.65 rows=19 width=8) (actual time=1.395..1.395 rows=0 loops=1)

  • Hash Cond: (x2.id = partner_user_roles.user_profile_id)
4. 0.010 1.129 ↓ 0.0 0 1

Hash Join (cost=2,572.24..2,800.26 rows=97 width=16) (actual time=1.129..1.129 rows=0 loops=1)

  • Hash Cond: (x2.aggregate_id = profile_ancestor_view.child_profile_id)
5. 0.002 0.002 ↑ 2,040.0 1 1

Seq Scan on partner_user_profiles x2 (cost=0.00..219.40 rows=2,040 width=32) (actual time=0.002..0.002 rows=1 loops=1)

6. 0.000 1.117 ↓ 0.0 0 1

Hash (cost=2,571.02..2,571.02 rows=97 width=16) (actual time=1.117..1.117 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
7. 0.000 1.117 ↓ 0.0 0 1

Subquery Scan on profile_ancestor_view (cost=2,569.56..2,571.02 rows=97 width=16) (actual time=1.117..1.117 rows=0 loops=1)

8. 0.000 1.117 ↓ 0.0 0 1

Unique (cost=2,569.56..2,570.05 rows=97 width=32) (actual time=1.117..1.117 rows=0 loops=1)

9.          

CTE profile_hierarchical_children

10. 0.243 0.937 ↑ 26.4 748 1

Recursive Union (cost=0.00..2,122.33 rows=19,731 width=36) (actual time=0.006..0.937 rows=748 loops=1)

11. 0.034 0.034 ↑ 1.3 266 1

Seq Scan on partner_user_relationships (cost=0.00..7.41 rows=341 width=36) (actual time=0.005..0.034 rows=266 loops=1)

12. 0.293 0.660 ↑ 44.1 44 11

Hash Join (cost=90.94..172.03 rows=1,939 width=36) (actual time=0.043..0.060 rows=44 loops=11)

  • Hash Cond: (pur.parent_profile_id = c.child_profile_id)
13. 0.180 0.180 ↑ 1.3 266 10

Seq Scan on partner_user_relationships pur (cost=0.00..7.41 rows=341 width=32) (actual time=0.001..0.018 rows=266 loops=10)

14. 0.099 0.187 ↑ 17.2 66 11

Hash (cost=76.72..76.72 rows=1,137 width=36) (actual time=0.017..0.017 rows=66 loops=11)

  • Buckets: 2,048 Batches: 1 Memory Usage: 16kB
15. 0.088 0.088 ↑ 17.2 66 11

WorkTable Scan on profile_hierarchical_children c (cost=0.00..76.72 rows=1,137 width=36) (actual time=0.000..0.008 rows=66 loops=11)

  • Filter: (depth < 10)
  • Rows Removed by Filter: 2
16. 0.012 1.117 ↓ 0.0 0 1

Sort (cost=447.23..447.48 rows=99 width=32) (actual time=1.117..1.117 rows=0 loops=1)

  • Sort Key: profile_hierarchical_children.child_profile_id
  • Sort Method: quicksort Memory: 25kB
17. 1.105 1.105 ↓ 0.0 0 1

CTE Scan on profile_hierarchical_children (cost=0.00..443.95 rows=99 width=32) (actual time=1.104..1.105 rows=0 loops=1)

  • Filter: (parent_profile_id = 1aea7152-1fd3-45c1-b4f6-03639a6acc73::uuid)
  • Rows Removed by Filter: 748
18. 0.051 0.256 ↓ 1.0 396 1

Hash (cost=53.73..53.73 rows=390 width=8) (actual time=0.256..0.256 rows=396 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 24kB
19. 0.205 0.205 ↓ 1.0 396 1

Seq Scan on partner_user_roles (cost=0.00..53.73 rows=390 width=8) (actual time=0.007..0.205 rows=396 loops=1)

  • Filter: (role = AGENT::text)
  • Rows Removed by Filter: 1,684
Planning time : 0.728 ms
Execution time : 1.957 ms