explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ReBg

Settings
# exclusive inclusive rows x rows loops node
1. 0.022 108.755 ↑ 750.0 1 1

Nested Loop (cost=2,772.02..18,613.24 rows=750 width=577) (actual time=103.793..108.755 rows=1 loops=1)

2.          

CTE filter_0

3. 31.566 31.566 ↑ 1,000.0 1 1

CTE Scan on rs (cost=2,515.06..2,535.06 rows=1,000 width=388) (actual time=26.606..31.566 rows=1 loops=1)

4.          

CTE options

5. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=1)

6.          

CTE r_pivot_ids

7. 26.220 26.223 ↑ 1,000.0 1 1

Function Scan on resource_ids_for_fuzzy_akas (cost=0.27..10.27 rows=1,000 width=8) (actual time=26.223..26.223 rows=1 loops=1)

8.          

Initplan (forFunction Scan)

9. 0.003 0.003 ↑ 1.0 1 1

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

10.          

CTE r_ids_for_pivot

11. 0.349 26.573 ↑ 1,000.0 1 1

Function Scan on resources_for_levels_self (cost=20.25..30.25 rows=1,000 width=56) (actual time=26.573..26.573 rows=1 loops=1)

12.          

Initplan (forFunction Scan)

13. 26.224 26.224 ↑ 1,000.0 1 1

CTE Scan on r_pivot_ids (cost=0.00..20.00 rows=1,000 width=8) (actual time=26.224..26.224 rows=1 loops=1)

14.          

CTE rs

15. 0.539 31.564 ↑ 1,000.0 1 1

Hash Join (cost=32.50..2,474.53 rows=1,000 width=636) (actual time=26.604..31.564 rows=1 loops=1)

  • Hash Cond: (r.id = r_ids_for_pivot.id)
16. 4.448 4.448 ↑ 1.0 8,502 1

Seq Scan on resources r (cost=0.00..1,348.02 rows=8,502 width=636) (actual time=0.007..4.448 rows=8,502 loops=1)

17. 0.002 26.577 ↑ 1,000.0 1 1

Hash (cost=20.00..20.00 rows=1,000 width=8) (actual time=26.577..26.577 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
18. 26.575 26.575 ↑ 1,000.0 1 1

CTE Scan on r_ids_for_pivot (cost=0.00..20.00 rows=1,000 width=8) (actual time=26.574..26.575 rows=1 loops=1)

19.          

CTE identity_ids

20. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=1)

21.          

CTE turbot_permission_type

22. 0.075 0.075 ↑ 1.0 1 1

Result (cost=0.00..0.26 rows=1 width=8) (actual time=0.074..0.075 rows=1 loops=1)

23.          

CTE turbot_metadata_level

24. 0.289 0.384 ↑ 200.0 5 1

Function Scan on permission_levels_for_levels_self_descendant (cost=0.51..10.51 rows=1,000 width=288) (actual time=0.383..0.384 rows=5 loops=1)

25.          

Initplan (forFunction Scan)

26. 0.095 0.095 ↑ 1.0 1 1

Result (cost=0.00..0.26 rows=1 width=8) (actual time=0.095..0.095 rows=1 loops=1)

27.          

CTE mh_pivot_ids

28. 25.522 25.524 ↑ 1,000.0 1 1

Function Scan on resource_ids_for_fuzzy_akas resource_ids_for_fuzzy_akas_1 (cost=0.27..10.27 rows=1,000 width=8) (actual time=25.524..25.524 rows=1 loops=1)

29.          

Initplan (forFunction Scan)

30. 0.002 0.002 ↑ 1.0 1 1

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

31.          

CTE membership_hierarchy_ids_for_pivot

32. 0.365 25.891 ↑ 1,000.0 1 1

Function Scan on membership_hierarchy_for_resources_levels_self_descendant (cost=20.25..30.25 rows=1,000 width=8) (actual time=25.890..25.891 rows=1 loops=1)

33.          

Initplan (forFunction Scan)

34. 25.526 25.526 ↑ 1,000.0 1 1

CTE Scan on mh_pivot_ids (cost=0.00..20.00 rows=1,000 width=8) (actual time=25.525..25.526 rows=1 loops=1)

35.          

CTE active_grants

36. 0.006 26.614 ↑ 350.0 2 1

Hash Join (cost=106.13..136.88 rows=700 width=191) (actual time=26.613..26.614 rows=2 loops=1)

  • Hash Cond: (mh_pivots.id = mh.id)
37. 25.892 25.892 ↑ 1,000.0 1 1

CTE Scan on membership_hierarchy_ids_for_pivot mh_pivots (cost=0.00..20.00 rows=1,000 width=8) (actual time=25.891..25.892 rows=1 loops=1)

38. 0.018 0.716 ↑ 2.5 71 1

Hash (cost=103.94..103.94 rows=175 width=199) (actual time=0.716..0.716 rows=71 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
39. 0.022 0.698 ↑ 2.5 71 1

Hash Join (cost=77.31..103.94 rows=175 width=199) (actual time=0.577..0.698 rows=71 loops=1)

  • Hash Cond: (g.permission_level_id = t_metadata.id)
40. 0.000 0.288 ↓ 2.0 71 1

Nested Loop (cost=44.81..65.23 rows=35 width=207) (actual time=0.182..0.288 rows=71 loops=1)

41. 0.032 0.217 ↓ 2.5 71 1

Hash Join (cost=44.67..50.52 rows=28 width=207) (actual time=0.176..0.217 rows=71 loops=1)

  • Hash Cond: (ag.grant_id = g.id)
42. 0.018 0.018 ↓ 1.0 115 1

Seq Scan on active_grants ag (cost=0.00..5.14 rows=114 width=191) (actual time=0.005..0.018 rows=115 loops=1)

43. 0.010 0.167 ↓ 2.2 74 1

Hash (cost=44.24..44.24 rows=34 width=24) (actual time=0.167..0.167 rows=74 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
44. 0.038 0.157 ↓ 2.2 74 1

Hash Join (cost=0.03..44.24 rows=34 width=24) (actual time=0.084..0.157 rows=74 loops=1)

  • Hash Cond: (g.permission_type_id = t_pt.id)
45. 0.042 0.042 ↓ 1.0 141 1

Seq Scan on grants g (cost=0.00..43.36 rows=136 width=32) (actual time=0.003..0.042 rows=141 loops=1)

46. 0.001 0.077 ↑ 1.0 1 1

Hash (cost=0.02..0.02 rows=1 width=8) (actual time=0.077..0.077 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
47. 0.076 0.076 ↑ 1.0 1 1

CTE Scan on turbot_permission_type t_pt (cost=0.00..0.02 rows=1 width=8) (actual time=0.076..0.076 rows=1 loops=1)

48. 0.071 0.071 ↑ 1.0 1 71

Index Scan using membership_hierarchy_profile_id_idx on membership_hierarchy mh (cost=0.14..0.52 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=71)

  • Index Cond: (profile_id = g.profile_id)
49. 0.002 0.388 ↑ 200.0 5 1

Hash (cost=20.00..20.00 rows=1,000 width=8) (actual time=0.388..0.388 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
50. 0.386 0.386 ↑ 200.0 5 1

CTE Scan on turbot_metadata_level t_metadata (cost=0.00..20.00 rows=1,000 width=8) (actual time=0.384..0.386 rows=5 loops=1)

51.          

CTE permitted_resources

52. 50.287 50.288 ↑ 1,000.0 1 1

Function Scan on resources_for_levels_self_descendant (cost=14.25..24.25 rows=1,000 width=16) (actual time=50.288..50.288 rows=1 loops=1)

53.          

Initplan (forFunction Scan)

54. 0.001 0.001 ↑ 350.0 2 1

CTE Scan on active_grants (cost=0.00..14.00 rows=700 width=8) (actual time=0.001..0.001 rows=2 loops=1)

55.          

CTE not_permitted_resources

56. 0.247 26.864 ↓ 0.0 0 1

Function Scan on resources_for_levels_ancestor (cost=14.25..24.25 rows=1,000 width=16) (actual time=26.864..26.864 rows=0 loops=1)

57.          

Initplan (forFunction Scan)

58. 26.617 26.617 ↑ 350.0 2 1

CTE Scan on active_grants active_grants_1 (cost=0.00..14.00 rows=700 width=8) (actual time=26.615..26.617 rows=2 loops=1)

59. 31.568 31.568 ↑ 1,000.0 1 1

CTE Scan on filter_0 (cost=0.00..20.00 rows=1,000 width=8) (actual time=26.608..31.568 rows=1 loops=1)

60. 0.009 77.165 ↑ 1.0 1 1

Index Scan using resources_pkey on resources (cost=0.29..12.45 rows=1 width=545) (actual time=77.165..77.165 rows=1 loops=1)

  • Index Cond: (id = filter_0.id)
  • Filter: ((alternatives: SubPlan 23 or hashed SubPlan 24) OR (alternatives: SubPlan 25 or hashed SubPlan 26))
61.          

SubPlan (forIndex Scan)

62. 26.865 26.865 ↓ 0.0 0 1

CTE Scan on not_permitted_resources p_2 (cost=0.00..22.50 rows=5 width=0) (actual time=26.865..26.865 rows=0 loops=1)

  • Filter: (id = resources.id)
63. 0.000 0.000 ↓ 0.0 0

CTE Scan on not_permitted_resources p_3 (cost=0.00..20.00 rows=1,000 width=8) (never executed)

64. 50.291 50.291 ↑ 5.0 1 1

CTE Scan on permitted_resources p_4 (cost=0.00..22.50 rows=5 width=0) (actual time=50.291..50.291 rows=1 loops=1)

  • Filter: (id = resources.id)
65. 0.000 0.000 ↓ 0.0 0

CTE Scan on permitted_resources p_5 (cost=0.00..20.00 rows=1,000 width=8) (never executed)

66.          

SubPlan (forNested Loop)

67. 0.000 0.000 ↓ 0.0 0

CTE Scan on not_permitted_resources p (cost=0.00..22.50 rows=5 width=0) (never executed)

  • Filter: (id = resources.id)
68. 0.000 0.000 ↓ 0.0 0 1

CTE Scan on not_permitted_resources p_1 (cost=0.00..20.00 rows=1,000 width=8) (actual time=0.000..0.000 rows=0 loops=1)

Planning time : 1.139 ms
Execution time : 109.644 ms