explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CjUp : Optimization for: Optimization for: plan #Pm14; plan #VS7o

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.368 6.835 ↑ 1.0 1 1

Aggregate (cost=1,359.57..1,359.59 rows=1 width=96) (actual time=6.835..6.835 rows=1 loops=1)

2.          

CTE rows

3. 0.003 6.263 ↑ 1.0 21 1

Limit (cost=0.70..1,357.77 rows=21 width=260) (actual time=0.366..6.263 rows=21 loops=1)

4. 0.087 6.260 ↑ 427.4 21 1

Nested Loop (cost=0.70..580,048.32 rows=8,976 width=260) (actual time=0.366..6.260 rows=21 loops=1)

5. 0.041 0.041 ↑ 427.4 21 1

Index Scan using account_employee__site_id_cdate_uuid on account_employee e (cost=0.41..10,710.33 rows=8,976 width=224) (actual time=0.010..0.041 rows=21 loops=1)

  • Index Cond: (site_id = 6)
  • Filter: (date_delete IS NULL)
  • Rows Removed by Filter: 2
6. 0.063 0.063 ↑ 1.0 1 21

Index Scan using account_user_pkey on account_user u (cost=0.29..1.02 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=21)

  • Index Cond: (id = e.user_id)
7.          

SubPlan (forNested Loop)

8. 0.126 0.441 ↑ 1.0 1 21

Aggregate (cost=41.83..41.84 rows=1 width=32) (actual time=0.021..0.021 rows=1 loops=21)

9.          

CTE regrefs

10. 0.063 0.252 ↑ 1.3 3 21

Sort (cost=41.72..41.73 rows=4 width=29) (actual time=0.012..0.012 rows=3 loops=21)

  • Sort Key: account_registrationreference.id
  • Sort Method: quicksort Memory: 25kB
11. 0.070 0.189 ↑ 1.3 3 21

Nested Loop (cost=0.70..41.68 rows=4 width=29) (actual time=0.005..0.009 rows=3 loops=21)

12. 0.063 0.063 ↑ 1.3 3 21

Index Scan using account_registrationreference_user_id on account_registrationreference (cost=0.42..8.49 rows=4 width=24) (actual time=0.003..0.003 rows=3 loops=21)

  • Index Cond: (e.user_id = employee_id)
13. 0.056 0.056 ↑ 1.0 1 56

Index Scan using enterprise_organization_pkey on enterprise_organization (cost=0.28..8.30 rows=1 width=13) (actual time=0.001..0.001 rows=1 loops=56)

  • Index Cond: (id = account_registrationreference.organization_id)
14. 0.315 0.315 ↑ 1.3 3 21

CTE Scan on regrefs (cost=0.00..0.08 rows=4 width=24) (actual time=0.014..0.015 rows=3 loops=21)

15. 0.840 5.628 ↑ 1.0 1 21

Aggregate (cost=20.51..20.52 rows=1 width=32) (actual time=0.267..0.268 rows=1 loops=21)

16. 0.210 4.788 ↑ 1.1 32 21

Subquery Scan on cf (cost=20.00..20.42 rows=34 width=34) (actual time=0.217..0.228 rows=32 loops=21)

17. 0.546 4.578 ↑ 1.1 32 21

Sort (cost=20.00..20.08 rows=34 width=42) (actual time=0.216..0.218 rows=32 loops=21)

  • Sort Key: dynattr.code
  • Sort Method: quicksort Memory: 27kB
18. 4.032 4.032 ↑ 1.1 32 21

Seq Scan on role_dynamicattribute dynattr (cost=0.00..19.13 rows=34 width=42) (actual time=0.004..0.192 rows=32 loops=21)

  • Filter: ((site_id = e.site_id) AND (resources = 'employees'::rh2_resources_type))
  • Rows Removed by Filter: 136
19.          

CTE has_next

20. 0.001 0.313 ↑ 1.0 1 1

Limit (cost=0.40..0.42 rows=1 width=1) (actual time=0.313..0.313 rows=1 loops=1)

21. 0.312 0.312 ↑ 1.0 21 1

CTE Scan on rows (cost=0.00..0.42 rows=21 width=1) (actual time=0.002..0.312 rows=21 loops=1)

22.          

CTE ordered_rows

23. 0.004 6.011 ↑ 1.0 20 1

Limit (cost=0.00..0.40 rows=20 width=2,905) (actual time=0.369..6.011 rows=20 loops=1)

24. 6.007 6.007 ↑ 1.1 20 1

CTE Scan on rows rows_1 (cost=0.00..0.42 rows=21 width=2,905) (actual time=0.368..6.007 rows=20 loops=1)

25.          

CTE first

26. 0.001 0.006 ↑ 1.0 1 1

Limit (cost=0.00..0.02 rows=1 width=32) (actual time=0.006..0.006 rows=1 loops=1)

27. 0.005 0.005 ↑ 20.0 1 1

CTE Scan on ordered_rows ordered_rows_1 (cost=0.00..0.45 rows=20 width=32) (actual time=0.005..0.005 rows=1 loops=1)

28.          

CTE last

29. 0.000 0.358 ↑ 1.0 1 1

Limit (cost=0.45..0.47 rows=1 width=32) (actual time=0.358..0.358 rows=1 loops=1)

30.          

Initplan (forLimit)

31. 0.314 0.314 ↑ 1.0 1 1

CTE Scan on has_next (cost=0.00..0.02 rows=1 width=1) (actual time=0.314..0.314 rows=1 loops=1)

32. 0.348 0.357 ↑ 1.0 20 1

Result (cost=0.00..0.45 rows=20 width=32) (actual time=0.319..0.357 rows=20 loops=1)

  • One-Time Filter: $10
33. 0.009 0.009 ↑ 1.0 20 1

CTE Scan on ordered_rows ordered_rows_2 (cost=0.00..0.45 rows=20 width=32) (actual time=0.000..0.009 rows=20 loops=1)

34.          

Initplan (forAggregate)

35. 0.007 0.007 ↑ 1.0 1 1

CTE Scan on first (cost=0.00..0.02 rows=1 width=32) (actual time=0.006..0.007 rows=1 loops=1)

36. 0.359 0.359 ↑ 1.0 1 1

CTE Scan on last (cost=0.00..0.02 rows=1 width=32) (actual time=0.359..0.359 rows=1 loops=1)

37. 6.101 6.101 ↑ 1.0 20 1

CTE Scan on ordered_rows (cost=0.00..0.40 rows=20 width=24) (actual time=0.393..6.101 rows=20 loops=1)

Planning time : 1.236 ms
Execution time : 6.945 ms