explain.depesz.com

PostgreSQL's explain analyze made readable

Result: NKrP

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 107.438 ↑ 21.0 1 1

Limit (cost=15,258.46..15,353.22 rows=21 width=577) (actual time=107.437..107.438 rows=1 loops=1)

2.          

CTE filter_0

3. 31.094 31.094 ↑ 1,000.0 1 1

CTE Scan on rs (cost=2,515.06..2,535.06 rows=1,000 width=388) (actual time=26.636..31.094 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.295 26.299 ↑ 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.298..26.299 rows=1 loops=1)

8.          

Initplan (forFunction Scan)

9. 0.004 0.004 ↑ 1.0 1 1

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

10.          

CTE r_ids_for_pivot

11. 0.305 26.605 ↑ 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.604..26.605 rows=1 loops=1)

12.          

Initplan (forFunction Scan)

13. 26.300 26.300 ↑ 1,000.0 1 1

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

14.          

CTE rs

15. 0.541 31.091 ↑ 1,000.0 1 1

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

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

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

17. 0.002 26.608 ↑ 1,000.0 1 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
18. 26.606 26.606 ↑ 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.605..26.606 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.075..0.075 rows=1 loops=1)

23.          

CTE turbot_metadata_level

24. 0.283 0.373 ↑ 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.372..0.373 rows=5 loops=1)

25.          

Initplan (forFunction Scan)

26. 0.090 0.090 ↑ 1.0 1 1

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

27.          

CTE mh_pivot_ids

28. 25.128 25.130 ↑ 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.129..25.130 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.335 25.466 ↑ 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.465..25.466 rows=1 loops=1)

33.          

Initplan (forFunction Scan)

34. 25.131 25.131 ↑ 1,000.0 1 1

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

35.          

CTE active_grants

36. 0.008 26.193 ↑ 350.0 2 1

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

  • Hash Cond: (mh_pivots.id = mh.id)
37. 25.467 25.467 ↑ 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.466..25.467 rows=1 loops=1)

38. 0.018 0.718 ↑ 2.5 71 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
39. 0.021 0.700 ↑ 2.5 71 1

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

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

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

41. 0.036 0.232 ↓ 2.5 71 1

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

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

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

43. 0.010 0.179 ↓ 2.2 74 1

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

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

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

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

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

46. 0.001 0.078 ↑ 1.0 1 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
47. 0.077 0.077 ↑ 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.077 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.377 ↑ 200.0 5 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
50. 0.375 0.375 ↑ 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.373..0.375 rows=5 loops=1)

51.          

CTE permitted_resources

52. 49.854 49.855 ↑ 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=49.855..49.855 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.242 26.437 ↓ 0.0 0 1

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

57.          

Initplan (forFunction Scan)

58. 26.195 26.195 ↑ 350.0 2 1

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

59. 0.020 107.436 ↑ 750.0 1 1

Result (cost=12,486.73..15,871.10 rows=750 width=577) (actual time=107.436..107.436 rows=1 loops=1)

60. 0.013 107.416 ↑ 750.0 1 1

Sort (cost=12,486.73..12,488.60 rows=750 width=545) (actual time=107.416..107.416 rows=1 loops=1)

  • Sort Key: resources.title, resources.id DESC
  • Sort Method: quicksort Memory: 25kB
61. 0.004 107.403 ↑ 750.0 1 1

Nested Loop (cost=0.29..12,466.50 rows=750 width=545) (actual time=102.943..107.403 rows=1 loops=1)

62. 31.096 31.096 ↑ 1,000.0 1 1

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

63. 0.008 76.303 ↑ 1.0 1 1

Index Scan using resources_pkey on resources (cost=0.29..12.45 rows=1 width=545) (actual time=76.303..76.303 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))
64.          

SubPlan (forIndex Scan)

65. 26.437 26.437 ↓ 0.0 0 1

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

  • Filter: (id = resources.id)
66. 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)

67. 49.858 49.858 ↑ 5.0 1 1

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

  • Filter: (id = resources.id)
68. 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)

69.          

SubPlan (forResult)

70. 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)
71. 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.144 ms
Execution time : 108.304 ms