explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xRrn

Settings
# exclusive inclusive rows x rows loops node
1. 7,933.248 7,933.248 ↓ 226,466.0 226,466 1

CTE Scan on tmp_responses_all_possible (cost=3,283.75..3,283.77 rows=1 width=17) (actual time=75.982..7,933.248 rows=226,466 loops=1)

2.          

CTE tmp_template_response_keys

3. 0.013 0.013 ↓ 14.0 14 1

Index Scan using report_template_response_index on report_template_response (cost=0.42..45.71 rows=1 width=14) (actual time=0.010..0.013 rows=14 loops=1)

  • Index Cond: (report_template_key = 4231803)
  • Filter: (organisation_key = 1036)
4.          

CTE tmp_questionnaire_list

5. 17.239 69.261 ↓ 197.0 197 1

Nested Loop (cost=420.77..450.25 rows=1 width=211) (actual time=47.604..69.261 rows=197 loops=1)

6.          

Initplan (forNested Loop)

7. 0.003 0.077 ↑ 1.0 1 1

Aggregate (cost=8.61..8.62 rows=1 width=4) (actual time=0.077..0.077 rows=1 loops=1)

8. 0.001 0.074 ↓ 14.0 14 1

Nested Loop (cost=0.57..8.61 rows=1 width=4) (actual time=0.023..0.074 rows=14 loops=1)

9. 0.017 0.017 ↓ 14.0 14 1

CTE Scan on tmp_template_response_keys a (cost=0.00..0.02 rows=1 width=4) (actual time=0.011..0.017 rows=14 loops=1)

10. 0.056 0.056 ↑ 1.0 1 14

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

  • Index Cond: (response_key = a.template_response_key)
11. 0.446 51.748 ↓ 197.0 197 1

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

12. 0.579 47.174 ↓ 2,064.0 2,064 1

Hash Join (cost=411.45..425.21 rows=1 width=36) (actual time=46.431..47.174 rows=2,064 loops=1)

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

CTE standards

14. 0.015 0.127 ↓ 2.5 33 1

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

15. 0.040 0.040 ↓ 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.021..0.040 rows=9 loops=1)

  • Index Cond: (organisation_key = 1036)
  • Filter: (parent_standard_key IS NULL)
  • Rows Removed by Filter: 24
16. 0.034 0.072 ↓ 12.0 12 2

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

  • Hash Cond: (s_1.parent_standard_key = sub.standard_key)
17. 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)
18. 0.004 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
19. 0.006 0.006 ↑ 1.9 16 2

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

20. 45.081 45.081 ↓ 2.3 2,341 1

Function Scan on f_compliance_questionnaire p (cost=0.25..10.25 rows=1,000 width=4) (actual time=44.910..45.081 rows=2,341 loops=1)

21. 0.296 1.514 ↓ 2,371.0 2,371 1

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

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 183kB
22. 0.273 1.218 ↓ 2,371.0 2,371 1

Nested Loop (cost=0.42..80.32 rows=1 width=36) (actual time=0.057..1.218 rows=2,371 loops=1)

23. 0.145 0.145 ↓ 8.0 8 1

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

  • Filter: (160377 = ANY (path))
  • Rows Removed by Filter: 25
24. 0.800 0.800 ↓ 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.100 rows=296 loops=8)

  • Index Cond: (standard_key = s_2.standard_key)
  • Filter: (organisation_key = 1036)
25. 4.128 4.128 ↓ 0.0 0 2,064

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,064)

  • Index Cond: (questionnaire_key = q.questionnaire_key)
  • Filter: (template_questionnaire_key = $2)
  • Rows Removed by Filter: 1
26. 0.197 0.197 ↑ 1.0 1 197

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

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

CTE tmp_groups

28. 0.000 22.979 ↓ 0.0 0 1

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

29.          

CTE grouphierachy

30. 0.001 1.071 ↑ 189.0 1 1

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

31. 0.059 1.070 ↑ 39.0 1 1

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

  • Merge Cond: (e.child_grp_key = g.grp_key)
32. 0.890 0.890 ↑ 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.890 rows=910 loops=1)

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

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

  • Sort Key: g.grp_key
  • Sort Method: quicksort Memory: 36kB
34. 0.072 0.085 ↑ 1.0 186 1

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

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

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

  • Index Cond: (organisation_key = 1036)
36. 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)
37. 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)
38. 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)

39. 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
40. 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)
41. 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)
42. 0.000 0.000 ↓ 0.0 0

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

43. 0.000 0.000 ↓ 0.0 0

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

44. 0.011 22.979 ↓ 0.0 0 1

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

  • Sort Key: gh.grp_key, gh.grp, gh.key, gh.hdescription
  • Sort Method: quicksort Memory: 25kB
45. 0.003 22.968 ↓ 0.0 0 1

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

  • Hash Cond: (gh.grp_key = qqq.grp_key)
46. 1.073 1.073 ↑ 189.0 1 1

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

47. 0.031 21.892 ↓ 0.0 0 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
48. 21.861 21.861 ↓ 197.0 197 1

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

49.          

CTE tmp_responses_all_possible_sub

50. 0.764 49.126 ↓ 2,758.0 2,758 1

Nested Loop (cost=0.00..0.05 rows=1 width=135) (actual time=47.609..49.126 rows=2,758 loops=1)

51. 47.771 47.771 ↓ 197.0 197 1

CTE Scan on tmp_questionnaire_list i_1 (cost=0.00..0.02 rows=1 width=127) (actual time=47.607..47.771 rows=197 loops=1)

52. 0.591 0.591 ↓ 14.0 14 197

CTE Scan on tmp_template_response_keys a_1 (cost=0.00..0.02 rows=1 width=8) (actual time=0.000..0.003 rows=14 loops=197)

53.          

CTE tmp_responses_all_possible

54. 70.198 7,850.699 ↓ 226,466.0 226,466 1

Nested Loop Left Join (cost=42.67..46.78 rows=1 width=17) (actual time=75.980..7,850.699 rows=226,466 loops=1)

  • Join Filter: (i_2.grp_key = tg.grp_key)
55. 0.000 7,780.501 ↓ 226,466.0 226,466 1

Nested Loop (cost=42.67..46.75 rows=1 width=21) (actual time=52.999..7,780.501 rows=226,466 loops=1)

  • Join Filter: (i_2.template_section_key = s_3.template_section_key)
56. 38.102 7,350.461 ↓ 226,466.0 226,466 1

Nested Loop (cost=42.11..46.15 rows=1 width=25) (actual time=52.985..7,350.461 rows=226,466 loops=1)

57. 50.545 50.545 ↓ 2,758.0 2,758 1

CTE Scan on tmp_responses_all_possible_sub i_2 (cost=0.00..0.02 rows=1 width=17) (actual time=47.610..50.545 rows=2,758 loops=1)

58. 308.896 7,261.814 ↓ 82.0 82 2,758

Bitmap Heap Scan on response r_1 (cost=42.11..46.12 rows=1 width=16) (actual time=2.576..2.633 rows=82 loops=2,758)

  • Recheck Cond: ((template_ultimate_response_key = i_2.template_response_key) AND (questionnaire_key = i_2.questionnaire_key))
  • Heap Blocks: exact=195700
59. 1,712.718 6,952.918 ↓ 0.0 0 2,758

BitmapAnd (cost=42.11..42.11 rows=1 width=0) (actual time=2.521..2.521 rows=0 loops=2,758)

60. 4,936.820 4,936.820 ↓ 88.9 17,167 2,758

Bitmap Index Scan on response_template_ultimate_response_key_index (cost=0.00..10.02 rows=193 width=0) (actual time=1.790..1.790 rows=17,167 loops=2,758)

  • Index Cond: (template_ultimate_response_key = i_2.template_response_key)
61. 303.380 303.380 ↓ 2.3 2,222 2,758

Bitmap Index Scan on response_questionnaire_key_index (cost=0.00..31.84 rows=969 width=0) (actual time=0.110..0.110 rows=2,222 loops=2,758)

  • Index Cond: (questionnaire_key = i_2.questionnaire_key)
62. 452.932 452.932 ↑ 1.0 1 226,466

Index Scan using section_pkey on section s_3 (cost=0.56..0.59 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=226,466)

  • Index Cond: (section_key = r_1.section_key)
63. 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)