explain.depesz.com

PostgreSQL's explain analyze made readable

Result: T6vy

Settings
# exclusive inclusive rows x rows loops node
1. 2.735 307,820.893 ↓ 1.0 16,188 1

Subquery Scan on a (cost=467,180.27..672,984.47 rows=15,960 width=32) (actual time=306,577.232..307,820.893 rows=16,188 loops=1)

2. 1,122.878 307,818.158 ↓ 1.0 16,188 1

GroupAggregate (cost=467,180.27..672,824.87 rows=15,960 width=68) (actual time=306,577.231..307,818.158 rows=16,188 loops=1)

  • Group Key: r.questionnaire_key, r.shorter_section_path
3.          

CTE raw_data

4. 1,020.084 284,129.681 ↓ 258.8 258,837 1

WindowAgg (cost=9,085.76..9,406.40 rows=1,000 width=100) (actual time=262,738.382..284,129.681 rows=258,837 loops=1)

5. 312.845 278,968.205 ↓ 258.8 258,837 1

Nested Loop (cost=9,085.76..9,113.90 rows=1,000 width=144) (actual time=262,738.147..278,968.205 rows=258,837 loops=1)

6. 331.699 265,195.576 ↓ 258,842.0 258,842 1

Nested Loop (cost=9,085.51..9,093.65 rows=1 width=1,980) (actual time=262,734.223..265,195.576 rows=258,842 loops=1)

7. 152.667 263,310.825 ↓ 258,842.0 258,842 1

Nested Loop Left Join (cost=9,084.94..9,085.05 rows=1 width=320) (actual time=262,734.174..263,310.825 rows=258,842 loops=1)

  • Join Filter: (i_2.grp_key = tg_1.grp_key)
8.          

CTE tmp_functions

9. 0.015 0.015 ↑ 6.2 16 1

Function Scan on unnest a_1 (cost=0.00..1.75 rows=100 width=44) (actual time=0.013..0.015 rows=16 loops=1)

10.          

CTE tmp_reporting_names

11. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=0.00..0.01 rows=1 width=59) (never executed)

  • Group Key: questionnaire_key, reporting_name, report_val_type, report_val_type_arg, idx
12. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..0.00 rows=0 width=59) (never executed)

  • One-Time Filter: false
13.          

CTE tmp_template_response_map

14. 0.014 0.148 ↑ 6.3 16 1

HashAggregate (cost=867.85..868.86 rows=101 width=560) (actual time=0.145..0.148 rows=16 loops=1)

  • Group Key: response_key, reporting_name, idx, report_val_type, report_val_type_arg
15. 0.002 0.134 ↑ 6.3 16 1

Append (cost=0.00..866.59 rows=101 width=560) (actual time=0.130..0.134 rows=16 loops=1)

16. 0.001 0.001 ↓ 0.0 0 1

HashAggregate (cost=0.00..0.01 rows=1 width=59) (actual time=0.001..0.001 rows=0 loops=1)

  • Group Key: response_key, reporting_name, idx, report_val_type, report_val_type_arg
17. 0.000 0.000 ↓ 0.0 0 1

Result (cost=0.00..0.00 rows=0 width=59) (actual time=0.000..0.000 rows=0 loops=1)

  • One-Time Filter: false
18. 0.003 0.131 ↑ 6.2 16 1

Unique (cost=864.07..865.57 rows=100 width=59) (actual time=0.128..0.131 rows=16 loops=1)

19. 0.037 0.128 ↑ 6.2 16 1

Sort (cost=864.07..864.32 rows=100 width=59) (actual time=0.127..0.128 rows=16 loops=1)

  • Sort Key: a_2.template_response_key, r_1.reporting_name, a_2.idx, a_2.report_val_type, a_2.report_val_type_arg
  • Sort Method: quicksort Memory: 25kB
20. 0.007 0.091 ↑ 6.2 16 1

Nested Loop (cost=0.57..860.75 rows=100 width=59) (actual time=0.030..0.091 rows=16 loops=1)

21. 0.020 0.020 ↑ 6.2 16 1

CTE Scan on tmp_functions a_2 (cost=0.00..2.00 rows=100 width=44) (actual time=0.014..0.020 rows=16 loops=1)

22. 0.064 0.064 ↑ 1.0 1 16

Index Scan using response_pkey on response r_1 (cost=0.57..8.59 rows=1 width=19) (actual time=0.004..0.004 rows=1 loops=16)

  • Index Cond: (response_key = a_2.template_response_key)
23.          

CTE tmp_template_response_keys

24. 0.008 0.160 ↑ 7.2 14 1

HashAggregate (cost=2.27..3.28 rows=101 width=4) (actual time=0.158..0.160 rows=14 loops=1)

  • Group Key: m.template_response_key
25. 0.152 0.152 ↑ 6.3 16 1

CTE Scan on tmp_template_response_map m (cost=0.00..2.02 rows=101 width=4) (actual time=0.146..0.152 rows=16 loops=1)

26.          

CTE tmp_questionnaire_list

27. 19.564 75.466 ↓ 197.0 197 1

Nested Loop (cost=1,281.77..1,311.25 rows=1 width=211) (actual time=52.118..75.466 rows=197 loops=1)

28.          

Initplan (forNested Loop)

29. 0.005 0.195 ↑ 1.0 1 1

Aggregate (cost=869.61..869.62 rows=1 width=4) (actual time=0.195..0.195 rows=1 loops=1)

30. 0.000 0.190 ↑ 7.2 14 1

Nested Loop (cost=0.57..869.36 rows=101 width=4) (actual time=0.164..0.190 rows=14 loops=1)

31. 0.163 0.163 ↑ 7.2 14 1

CTE Scan on tmp_template_response_keys a_3 (cost=0.00..2.02 rows=101 width=4) (actual time=0.159..0.163 rows=14 loops=1)

32. 0.028 0.028 ↑ 1.0 1 14

Index Scan using response_pkey on response r_2 (cost=0.57..8.59 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=14)

  • Index Cond: (response_key = a_3.template_response_key)
33. 0.253 55.313 ↓ 197.0 197 1

Nested Loop (cost=411.87..434.12 rows=1 width=94) (actual time=50.418..55.313 rows=197 loops=1)

34. 0.569 50.934 ↓ 2,063.0 2,063 1

Hash Join (cost=411.45..425.21 rows=1 width=36) (actual time=50.199..50.934 rows=2,063 loops=1)

  • Hash Cond: (p.questionnaire_key = q.questionnaire_key)
35.          

CTE standards

36. 0.010 0.125 ↓ 2.5 33 1

Recursive Union (cost=0.29..330.86 rows=13 width=36) (actual time=0.019..0.125 rows=33 loops=1)

37. 0.037 0.037 ↓ 3.0 9 1

Index Scan using standard_organisation_key_index on standard s (cost=0.29..29.13 rows=3 width=36) (actual time=0.018..0.037 rows=9 loops=1)

  • Index Cond: (organisation_key = 1036)
  • Filter: (parent_standard_key IS NULL)
  • Rows Removed by Filter: 24
38. 0.040 0.078 ↓ 12.0 12 2

Hash Join (cost=1.26..30.15 rows=1 width=36) (actual time=0.024..0.039 rows=12 loops=2)

  • Hash Cond: (s_1.parent_standard_key = sub.standard_key)
39. 0.028 0.028 ↓ 3.7 33 2

Index Scan using standard_organisation_key_index on standard s_1 (cost=0.29..29.13 rows=9 width=8) (actual time=0.003..0.014 rows=33 loops=2)

  • Index Cond: (organisation_key = 1036)
40. 0.006 0.010 ↑ 1.9 16 2

Hash (cost=0.60..0.60 rows=30 width=36) (actual time=0.005..0.005 rows=16 loops=2)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
41. 0.004 0.004 ↑ 1.9 16 2

WorkTable Scan on standards sub (cost=0.00..0.60 rows=30 width=36) (actual time=0.001..0.002 rows=16 loops=2)

42. 48.950 48.950 ↓ 2.3 2,340 1

Function Scan on f_compliance_questionnaire p (cost=0.25..10.25 rows=1,000 width=4) (actual time=48.774..48.950 rows=2,340 loops=1)

43. 0.276 1.415 ↓ 2,370.0 2,370 1

Hash (cost=80.32..80.32 rows=1 width=36) (actual time=1.415..1.415 rows=2,370 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 183kB
44. 0.184 1.139 ↓ 2,370.0 2,370 1

Nested Loop (cost=0.42..80.32 rows=1 width=36) (actual time=0.056..1.139 rows=2,370 loops=1)

45. 0.147 0.147 ↓ 8.0 8 1

CTE Scan on standards s_2 (cost=0.00..0.42 rows=1 width=36) (actual time=0.044..0.147 rows=8 loops=1)

  • Filter: (160377 = ANY (path))
  • Rows Removed by Filter: 25
46. 0.808 0.808 ↓ 296.0 296 8

Index Scan using questionnaire_standard_key_index on questionnaire q (cost=0.42..79.89 rows=1 width=8) (actual time=0.007..0.101 rows=296 loops=8)

  • Index Cond: (standard_key = s_2.standard_key)
  • Filter: (organisation_key = 1036)
47. 4.126 4.126 ↓ 0.0 0 2,063

Index Scan using questionnaire_pkey on questionnaire qf (cost=0.42..8.44 rows=1 width=62) (actual time=0.002..0.002 rows=0 loops=2,063)

  • Index Cond: (questionnaire_key = q.questionnaire_key)
  • Filter: (template_questionnaire_key = $13)
  • Rows Removed by Filter: 1
48. 0.394 0.394 ↑ 1.0 1 197

Index Scan using standard_pkey on standard i (cost=0.29..7.01 rows=1 width=36) (actual time=0.002..0.002 rows=1 loops=197)

  • Index Cond: (standard_key = qf.standard_key)
49.          

CTE tmp_groups

50. 0.000 24.748 ↓ 0.0 0 1

Unique (cost=2,740.94..2,740.96 rows=1 width=186) (actual time=24.748..24.748 rows=0 loops=1)

51.          

CTE grouphierachy

52. 0.001 1.147 ↑ 189.0 1 1

Recursive Union (cost=104.35..2,736.40 rows=189 width=118) (actual time=1.147..1.147 rows=1 loops=1)

53. 0.064 1.146 ↑ 39.0 1 1

Merge Join (cost=104.35..224.51 rows=39 width=118) (actual time=1.146..1.146 rows=1 loops=1)

  • Merge Cond: (e.child_grp_key = g.grp_key)
54. 0.950 0.950 ↑ 1.9 910 1

Index Scan using grp_entity_child_grp_key_index on grp_entity e (cost=0.28..495.47 rows=1,740 width=8) (actual time=0.009..0.950 rows=910 loops=1)

  • Filter: (grp_key IS NULL)
  • Rows Removed by Filter: 3086
55. 0.036 0.132 ↑ 186.0 1 1

Sort (cost=104.06..104.53 rows=186 width=18) (actual time=0.132..0.132 rows=1 loops=1)

  • Sort Key: g.grp_key
  • Sort Method: quicksort Memory: 36kB
56. 0.080 0.096 ↑ 1.0 186 1

Bitmap Heap Scan on grp g (cost=5.73..97.05 rows=186 width=18) (actual time=0.022..0.096 rows=186 loops=1)

  • Recheck Cond: (organisation_key = 1036)
  • Heap Blocks: exact=27
57. 0.016 0.016 ↑ 1.0 186 1

Bitmap Index Scan on grp_organisation_key_index (cost=0.00..5.68 rows=186 width=0) (actual time=0.016..0.016 rows=186 loops=1)

  • Index Cond: (organisation_key = 1036)
58. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=117.02..250.81 rows=15 width=118) (never executed)

  • Hash Cond: (e_1.grp_key = sub_1.grp_key)
59. 0.000 0.000 ↓ 0.0 0

Merge Join (cost=104.35..228.74 rows=179 width=22) (never executed)

  • Merge Cond: (e_1.child_grp_key = g_1.grp_key)
60. 0.000 0.000 ↓ 0.0 0

Index Scan using grp_entity_child_grp_key_index on grp_entity e_1 (cost=0.28..495.47 rows=8,072 width=8) (never executed)

61. 0.000 0.000 ↓ 0.0 0

Sort (cost=104.06..104.53 rows=186 width=18) (never executed)

  • Sort Key: g_1.grp_key
62. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on grp g_1 (cost=5.73..97.05 rows=186 width=18) (never executed)

  • Recheck Cond: (organisation_key = 1036)
63. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on grp_organisation_key_index (cost=0.00..5.68 rows=186 width=0) (never executed)

  • Index Cond: (organisation_key = 1036)
64. 0.000 0.000 ↓ 0.0 0

Hash (cost=7.80..7.80 rows=390 width=68) (never executed)

65. 0.000 0.000 ↓ 0.0 0

WorkTable Scan on grouphierachy sub_1 (cost=0.00..7.80 rows=390 width=68) (never executed)

66. 0.006 24.748 ↓ 0.0 0 1

Sort (cost=4.54..4.55 rows=1 width=186) (actual time=24.748..24.748 rows=0 loops=1)

  • Sort Key: gh.grp_key, gh.grp, gh.key, gh.hdescription
  • Sort Method: quicksort Memory: 25kB
67. 0.004 24.742 ↓ 0.0 0 1

Hash Join (cost=0.03..4.53 rows=1 width=186) (actual time=24.742..24.742 rows=0 loops=1)

  • Hash Cond: (gh.grp_key = qqq.grp_key)
68. 1.148 1.148 ↑ 189.0 1 1

CTE Scan on grouphierachy gh (cost=0.00..3.78 rows=189 width=186) (actual time=1.148..1.148 rows=1 loops=1)

69. 0.030 23.590 ↓ 0.0 0 1

Hash (cost=0.02..0.02 rows=1 width=4) (actual time=23.590..23.590 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
70. 23.560 23.560 ↓ 197.0 197 1

CTE Scan on tmp_questionnaire_list qqq (cost=0.00..0.02 rows=1 width=4) (actual time=0.003..23.560 rows=197 loops=1)

71.          

CTE tmp_responses_all_possible

72. 64.638 444.294 ↓ 226,466.0 226,466 1

Nested Loop Left Join (cost=3.85..1,514.74 rows=1 width=17) (actual time=76.932..444.294 rows=226,466 loops=1)

  • Join Filter: (i_1.grp_key = tg.grp_key)
73. 49.677 379.656 ↓ 226,466.0 226,466 1

Hash Join (cost=3.85..1,514.71 rows=1 width=21) (actual time=52.182..379.656 rows=226,466 loops=1)

  • Hash Cond: (r_3.template_ultimate_response_key = a_4.template_response_key)
74. 112.886 329.975 ↓ 452.2 437,746 1

Nested Loop (cost=0.57..1,507.79 rows=968 width=21) (actual time=52.143..329.975 rows=437,746 loops=1)

75. 52.200 52.200 ↓ 197.0 197 1

CTE Scan on tmp_questionnaire_list i_1 (cost=0.00..0.02 rows=1 width=9) (actual time=52.120..52.200 rows=197 loops=1)

76. 164.889 164.889 ↓ 2.3 2,222 197

Index Scan using response_questionnaire_key_index on response r_3 (cost=0.57..1,498.09 rows=968 width=16) (actual time=0.010..0.837 rows=2,222 loops=197)

  • Index Cond: (questionnaire_key = i_1.questionnaire_key)
77. 0.002 0.004 ↑ 7.2 14 1

Hash (cost=2.02..2.02 rows=101 width=4) (actual time=0.004..0.004 rows=14 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
78. 0.002 0.002 ↑ 7.2 14 1

CTE Scan on tmp_template_response_keys a_4 (cost=0.00..2.02 rows=101 width=4) (actual time=0.001..0.002 rows=14 loops=1)

79. 0.000 0.000 ↓ 0.0 0 226,466

CTE Scan on tmp_groups tg (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=226,466)

80.          

CTE tmp_responses_with_basic_permission

81. 51.747 262,288.391 ↓ 45,293.2 226,466 1

Hash Join (cost=0.35..14.15 rows=5 width=17) (actual time=262,177.621..262,288.391 rows=226,466 loops=1)

  • Hash Cond: (p_1.response_key = r_4.response_key)
82.          

Initplan (forHash Join)

83. 0.032 0.062 ↑ 1.0 1 1

Aggregate (cost=0.02..0.03 rows=1 width=32) (actual time=0.062..0.062 rows=1 loops=1)

84. 0.030 0.030 ↓ 197.0 197 1

CTE Scan on tmp_questionnaire_list qqq_1 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.030 rows=197 loops=1)

85. 20.638 39.192 ↑ 1.0 1 1

Aggregate (cost=0.02..0.03 rows=1 width=32) (actual time=39.192..39.192 rows=1 loops=1)

86. 18.554 18.554 ↓ 226,466.0 226,466 1

CTE Scan on tmp_responses_all_possible rrr (cost=0.00..0.02 rows=1 width=4) (actual time=0.018..18.554 rows=226,466 loops=1)

87. 261,629.656 261,629.656 ↓ 226.5 226,466 1

Function Scan on f_compliance_permission_response_view p_1 (cost=0.25..10.25 rows=1,000 width=4) (actual time=261,609.869..261,629.656 rows=226,466 loops=1)

88. 40.998 567.734 ↓ 226,466.0 226,466 1

Hash (cost=0.02..0.02 rows=1 width=17) (actual time=567.734..567.734 rows=226,466 loops=1)

  • Buckets: 262144 (originally 1024) Batches: 1 (originally 1) Memory Usage: 13549kB
89. 526.736 526.736 ↓ 226,466.0 226,466 1

CTE Scan on tmp_responses_all_possible r_4 (cost=0.00..0.02 rows=1 width=17) (actual time=76.934..526.736 rows=226,466 loops=1)

90.          

CTE tmp_all_sections

91. 54.886 262,414.670 ↓ 3,237.6 16,188 1

HashAggregate (cost=0.12..0.17 rows=5 width=5) (actual time=262,404.297..262,414.670 rows=16,188 loops=1)

  • Group Key: p_2.section_key, p_2.full_control
92. 262,359.784 262,359.784 ↓ 45,293.2 226,466 1

CTE Scan on tmp_responses_with_basic_permission p_2 (cost=0.00..0.10 rows=5 width=5) (actual time=262,177.623..262,359.784 rows=226,466 loops=1)

93.          

CTE sections_permissions

94. 2.180 262,428.871 ↓ 5,396.0 16,188 1

Append (cost=0.00..9.05 rows=3 width=4) (actual time=262,404.299..262,428.871 rows=16,188 loops=1)

95. 262,424.534 262,424.534 ↓ 8,094.0 16,188 1

CTE Scan on tmp_all_sections s_3 (cost=0.00..0.10 rows=2 width=4) (actual time=262,404.298..262,424.534 rows=16,188 loops=1)

  • Filter: full_control
96. 0.001 2.157 ↓ 0.0 0 1

Nested Loop (cost=0.29..8.92 rows=1 width=4) (actual time=2.157..2.157 rows=0 loops=1)

  • Join Filter: user_section_filter_sub(s_4.section_key, 22162, (p_3.email)::text)
97. 0.009 0.009 ↑ 1.0 1 1

Index Scan using person_key_pkey on person p_3 (cost=0.29..8.30 rows=1 width=27) (actual time=0.008..0.009 rows=1 loops=1)

  • Index Cond: (person_key = 22162)
98. 2.147 2.147 ↓ 0.0 0 1

CTE Scan on tmp_all_sections s_4 (cost=0.00..0.10 rows=2 width=4) (actual time=2.147..2.147 rows=0 loops=1)

  • Filter: (NOT full_control)
  • Rows Removed by Filter: 16188
99.          

CTE section_paths

100. 262,441.599 262,587.291 ↓ 5,396.0 16,188 1

CTE Scan on sections_permissions s_5 (cost=0.00..2,617.91 rows=3 width=36) (actual time=262,404.351..262,587.291 rows=16,188 loops=1)

101.          

SubPlan (forCTE Scan)

102. 145.692 145.692 ↑ 1.0 1 16,188

CTE Scan on sections a_5 (cost=870.60..872.62 rows=1 width=32) (actual time=0.007..0.009 rows=1 loops=16,188)

  • Filter: (parent_section_key IS NULL)
103.          

CTE sections

104. 16.188 129.504 ↑ 101.0 1 16,188

Recursive Union (cost=0.56..870.60 rows=101 width=36) (actual time=0.006..0.008 rows=1 loops=16,188)

105. 97.128 97.128 ↑ 1.0 1 16,188

Index Scan using section_pkey on section ss (cost=0.56..8.58 rows=1 width=36) (actual time=0.006..0.006 rows=1 loops=16,188)

  • Index Cond: (section_key = s_5.section_key)
106. 16.188 16.188 ↓ 0.0 0 16,188

Nested Loop (cost=0.56..86.00 rows=10 width=36) (actual time=0.001..0.001 rows=0 loops=16,188)

107. 0.000 0.000 ↑ 10.0 1 16,188

WorkTable Scan on sections sss (cost=0.00..0.20 rows=10 width=36) (actual time=0.000..0.000 rows=1 loops=16,188)

108. 0.000 0.000 ↓ 0.0 0 16,188

Index Scan using section_pkey on section ss_1 (cost=0.56..8.58 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=16,188)

  • Index Cond: (section_key = sss.parent_section_key)
109.          

CTE output_sub

110. 47.376 113.294 ↓ 51,768.4 258,842 1

Hash Join (cost=0.16..2.61 rows=5 width=568) (actual time=65.916..113.294 rows=258,842 loops=1)

  • Hash Cond: (a_6.template_response_key = rp.template_response_key)
111. 0.011 0.011 ↑ 6.3 16 1

CTE Scan on tmp_template_response_map a_6 (cost=0.00..2.02 rows=101 width=560) (actual time=0.001..0.011 rows=16 loops=1)

112. 32.046 65.907 ↓ 45,293.2 226,466 1

Hash (cost=0.10..0.10 rows=5 width=16) (actual time=65.907..65.907 rows=226,466 loops=1)

  • Buckets: 262144 (originally 1024) Batches: 1 (originally 1) Memory Usage: 12664kB
113. 33.861 33.861 ↓ 45,293.2 226,466 1

CTE Scan on tmp_responses_with_basic_permission rp (cost=0.00..0.10 rows=5 width=16) (actual time=0.011..33.861 rows=226,466 loops=1)

114. 232.805 263,158.158 ↓ 258,842.0 258,842 1

Hash Join (cost=0.17..0.26 rows=1 width=644) (actual time=262,734.171..263,158.158 rows=258,842 loops=1)

  • Hash Cond: (sp.section_key = rp_1.section_key)
115. 262,595.717 262,595.717 ↓ 5,396.0 16,188 1

CTE Scan on section_paths sp (cost=0.00..0.06 rows=3 width=36) (actual time=262,404.352..262,595.717 rows=16,188 loops=1)

116. 72.017 329.636 ↓ 258,842.0 258,842 1

Hash (cost=0.16..0.16 rows=1 width=612) (actual time=329.636..329.636 rows=258,842 loops=1)

  • Buckets: 131072 (originally 1024) Batches: 2 (originally 1) Memory Usage: 16259kB
117. 68.521 257.619 ↓ 258,842.0 258,842 1

Hash Join (cost=0.03..0.16 rows=1 width=612) (actual time=66.014..257.619 rows=258,842 loops=1)

  • Hash Cond: (rp_1.questionnaire_key = i_2.questionnaire_key)
118. 189.014 189.014 ↓ 51,768.4 258,842 1

CTE Scan on output_sub rp_1 (cost=0.00..0.10 rows=5 width=52) (actual time=65.918..189.014 rows=258,842 loops=1)

119. 0.031 0.084 ↓ 197.0 197 1

Hash (cost=0.02..0.02 rows=1 width=564) (actual time=0.084..0.084 rows=197 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 28kB
120. 0.053 0.053 ↓ 197.0 197 1

CTE Scan on tmp_questionnaire_list i_2 (cost=0.00..0.02 rows=1 width=564) (actual time=0.005..0.053 rows=197 loops=1)

121. 0.000 0.000 ↓ 0.0 0 258,842

CTE Scan on tmp_groups tg_1 (cost=0.00..0.02 rows=1 width=186) (actual time=0.000..0.000 rows=0 loops=258,842)

122. 1,553.052 1,553.052 ↑ 1.0 1 258,842

Index Scan using response_pkey on response r_5 (cost=0.57..8.59 rows=1 width=1,728) (actual time=0.006..0.006 rows=1 loops=258,842)

  • Index Cond: (response_key = rp_1.response_key)
123. 13,459.784 13,459.784 ↑ 1,000.0 1 258,842

Function Scan on f_report_value b (cost=0.25..10.25 rows=1,000 width=96) (actual time=0.052..0.052 rows=1 loops=258,842)

124.          

SubPlan (forWindowAgg)

125. 4,141.392 4,141.392 ↑ 1.0 1 258,837

Result (cost=0.00..0.26 rows=1 width=32) (actual time=0.016..0.016 rows=1 loops=258,837)

126.          

CTE tmp_fltr_raw_sub1

127. 373.429 1,941.035 ↓ 14.2 566,152 1

GroupAggregate (cost=167,529.85..185,529.85 rows=40,000 width=108) (actual time=1,219.891..1,941.035 rows=566,152 loops=1)

  • Group Key: p_4.questionnaire_key, p_4.idx, p_4.section_path, (p_4.section_path[1:path_idx.path_idx]), path_idx.path_idx
128. 1,119.650 1,567.606 ↑ 1.8 566,152 1

Sort (cost=167,529.85..170,029.85 rows=1,000,000 width=84) (actual time=1,219.879..1,567.606 rows=566,152 loops=1)

  • Sort Key: p_4.questionnaire_key, p_4.idx, p_4.section_path, (p_4.section_path[1:path_idx.path_idx]), path_idx.path_idx
  • Sort Method: external merge Disk: 50624kB
129. 141.863 447.956 ↑ 1.8 566,152 1

Nested Loop (cost=0.01..20,020.01 rows=1,000,000 width=84) (actual time=0.029..447.956 rows=566,152 loops=1)

130. 47.256 47.256 ↓ 258.8 258,837 1

CTE Scan on raw_data p_4 (cost=0.00..20.00 rows=1,000 width=48) (actual time=0.015..47.256 rows=258,837 loops=1)

131. 258.837 258.837 ↑ 500.0 2 258,837

Function Scan on generate_series path_idx (cost=0.01..10.01 rows=1,000 width=4) (actual time=0.001..0.001 rows=2 loops=258,837)

132.          

CTE tmp_fltr_raw_sub2

133. 7,874.414 10,156.101 ↓ 76.8 307,343 1

HashAggregate (cost=11,300.00..11,340.00 rows=4,000 width=76) (actual time=9,981.685..10,156.101 rows=307,343 loops=1)

  • Group Key: p_5.questionnaire_key, p_5.idx, p_5.short_section_path
  • Filter: (count(*) = 1)
  • Rows Removed by Filter: 3118
134. 2,281.687 2,281.687 ↓ 14.2 566,152 1

CTE Scan on tmp_fltr_raw_sub1 p_5 (cost=0.00..800.00 rows=40,000 width=76) (actual time=1,219.894..2,281.687 rows=566,152 loops=1)

135.          

CTE tmp_fltr_all_sub1

136. 389.391 873.128 ↓ 1.0 80,942 1

HashAggregate (cost=110,080.01..110,880.01 rows=80,000 width=68) (actual time=851.811..873.128 rows=80,942 loops=1)

  • Group Key: p_6.questionnaire_key, p_6.short_section_path, p_6.short_section_path[1:path_idx2.path_idx2]
137. 137.531 483.737 ↑ 6.0 663,136 1

Nested Loop (cost=0.01..80,080.01 rows=4,000,000 width=68) (actual time=0.034..483.737 rows=663,136 loops=1)

138. 38.863 38.863 ↓ 76.8 307,343 1

CTE Scan on tmp_fltr_raw_sub2 p_6 (cost=0.00..80.00 rows=4,000 width=36) (actual time=0.018..38.863 rows=307,343 loops=1)

139. 307.343 307.343 ↑ 500.0 2 307,343

Function Scan on generate_series path_idx2 (cost=0.01..10.01 rows=1,000 width=4) (actual time=0.001..0.001 rows=2 loops=307,343)

140.          

CTE tmp_fltr_all_sub2

141. 40.476 941.009 ↓ 2.0 16,188 1

HashAggregate (cost=2,200.00..2,280.00 rows=8,000 width=36) (actual time=934.989..941.009 rows=16,188 loops=1)

  • Group Key: p_7.questionnaire_key, p_7.shorter_section_path
  • Filter: (count(*) = 1)
  • Rows Removed by Filter: 16385
142. 900.533 900.533 ↓ 1.0 80,942 1

CTE Scan on tmp_fltr_all_sub1 p_7 (cost=0.00..1,600.00 rows=80,000 width=36) (actual time=851.813..900.533 rows=80,942 loops=1)

143.          

CTE tmp_all_cells

144. 147.536 20,614.161 ↓ 1.9 307,343 1

ProjectSet (cost=997.95..5,027.92 rows=159,600 width=44) (actual time=11,477.368..20,614.161 rows=307,343 loops=1)

145. 7,888.073 20,466.625 ↓ 192.6 307,343 1

Merge Join (cost=997.95..4,217.95 rows=1,596 width=68) (actual time=11,477.362..20,466.625 rows=307,343 loops=1)

  • Merge Cond: (t.questionnaire_key = r_6.questionnaire_key)
  • Join Filter: ((t.short_section_path = r_6.shorter_section_path) OR (t.short_section_path <@ r_6.shorter_section_path))
  • Rows Removed by Join Filter: 25198800
146. 279.177 10,580.464 ↓ 76.8 307,343 1

Sort (cost=319.32..329.32 rows=4,000 width=68) (actual time=10,529.473..10,580.464 rows=307,343 loops=1)

  • Sort Key: t.questionnaire_key
  • Sort Method: external merge Disk: 22760kB
147. 10,301.287 10,301.287 ↓ 76.8 307,343 1

CTE Scan on tmp_fltr_raw_sub2 t (cost=0.00..80.00 rows=4,000 width=68) (actual time=9,981.688..10,301.287 rows=307,343 loops=1)

148. 1,052.956 1,998.088 ↓ 3,188.1 25,504,567 1

Sort (cost=678.63..698.63 rows=8,000 width=36) (actual time=947.840..1,998.088 rows=25,504,567 loops=1)

  • Sort Key: r_6.questionnaire_key
  • Sort Method: quicksort Memory: 1649kB
149. 945.132 945.132 ↓ 2.0 16,188 1

CTE Scan on tmp_fltr_all_sub2 r_6 (cost=0.00..160.00 rows=8,000 width=36) (actual time=934.991..945.132 rows=16,188 loops=1)

150. 534.236 306,695.280 ↑ 2.6 307,343 1

Sort (cost=142,716.10..144,711.10 rows=798,000 width=76) (actual time=306,576.789..306,695.280 rows=307,343 loops=1)

  • Sort Key: r.questionnaire_key, r.shorter_section_path
  • Sort Method: external merge Disk: 35632kB
151. 92.199 306,161.044 ↑ 2.6 307,343 1

Merge Join (cost=17,054.54..29,029.54 rows=798,000 width=76) (actual time=305,987.041..306,161.044 rows=307,343 loops=1)

  • Merge Cond: (d.cell_id = r.cell_id)
152. 407.419 285,093.207 ↓ 258.8 258,837 1

Sort (cost=69.83..72.33 rows=1,000 width=48) (actual time=285,051.874..285,093.207 rows=258,837 loops=1)

  • Sort Key: d.cell_id
  • Sort Method: external sort Disk: 24528kB
153. 284,685.788 284,685.788 ↓ 258.8 258,837 1

CTE Scan on raw_data d (cost=0.00..20.00 rows=1,000 width=48) (actual time=262,738.387..284,685.788 rows=258,837 loops=1)

154. 258.355 20,975.638 ↓ 1.9 307,343 1

Sort (cost=16,984.71..17,383.71 rows=159,600 width=44) (actual time=20,935.161..20,975.638 rows=307,343 loops=1)

  • Sort Key: r.cell_id
  • Sort Method: external sort Disk: 18656kB
155. 20,717.283 20,717.283 ↓ 1.9 307,343 1

CTE Scan on tmp_all_cells r (cost=0.00..3,192.00 rows=159,600 width=44) (actual time=11,477.371..20,717.283 rows=307,343 loops=1)

Planning time : 15.216 ms
Execution time : 307,853.040 ms