explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fMdV

Settings
# exclusive inclusive rows x rows loops node
1. 28.405 5,367.289 ↑ 3,086.0 77 1

Result (cost=1,601,105.35..2,083,471.92 rows=237,619 width=2,895) (actual time=5,339.783..5,367.289 rows=77 loops=1)

2.          

CTE section_map_o

3. 0.020 0.020 ↑ 1.0 1 1

Index Scan using section_pkey on section s (cost=0.44..2.46 rows=1 width=20) (actual time=0.020..0.020 rows=1 loops=1)

  • Index Cond: (section_key = 1460803760)
4.          

CTE section_ranked

5. 0.003 0.085 ↑ 26.2 5 1

Recursive Union (cost=0.44..4,612.84 rows=131 width=8) (actual time=0.013..0.085 rows=5 loops=1)

6. 0.001 0.013 ↑ 1.0 1 1

Nested Loop (cost=0.44..2.48 rows=1 width=8) (actual time=0.012..0.013 rows=1 loops=1)

7. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on section_map_o (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.001 rows=1 loops=1)

8. 0.011 0.011 ↑ 1.0 1 1

Index Scan using section_pkey on section s_1 (cost=0.44..2.46 rows=1 width=8) (actual time=0.011..0.011 rows=1 loops=1)

  • Index Cond: (section_key = section_map_o.section_key)
9. 0.000 0.069 ↑ 13.0 1 3

Subquery Scan on *SELECT* 2 (cost=460.48..460.90 rows=13 width=8) (actual time=0.022..0.023 rows=1 loops=3)

10. 0.012 0.069 ↑ 13.0 1 3

WindowAgg (cost=460.48..460.77 rows=13 width=16) (actual time=0.022..0.023 rows=1 loops=3)

11. 0.009 0.057 ↑ 13.0 1 3

Sort (cost=460.48..460.51 rows=13 width=12) (actual time=0.019..0.019 rows=1 loops=3)

  • Sort Key: s_2.parent_section_key, s_2.order_
  • Sort Method: quicksort Memory: 25kB
12. 0.008 0.048 ↑ 13.0 1 3

Nested Loop (cost=0.43..460.24 rows=13 width=12) (actual time=0.013..0.016 rows=1 loops=3)

13. 0.000 0.000 ↑ 5.0 2 3

WorkTable Scan on section_ranked sp (cost=0.00..0.20 rows=10 width=4) (actual time=0.000..0.000 rows=2 loops=3)

14. 0.016 0.040 ↑ 1.0 1 5

Index Scan using section_parent_section_key_index on section s_2 (cost=0.43..45.99 rows=1 width=12) (actual time=0.006..0.008 rows=1 loops=5)

  • Index Cond: (parent_section_key = sp.section_key)
  • Filter: ((template_section_key IS NULL) OR (order_ = (SubPlan 3)))
15.          

SubPlan (for Index Scan)

16. 0.000 0.024 ↑ 1.0 1 4

Result (cost=2.59..2.60 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=4)

17.          

Initplan (for Result)

18. 0.004 0.024 ↑ 1.0 1 4

Limit (cost=0.56..2.59 rows=1 width=4) (actual time=0.005..0.006 rows=1 loops=4)

19. 0.020 0.020 ↑ 1.0 1 4

Index Scan using section_order_unique on section sf (cost=0.56..2.59 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=4)

  • Index Cond: ((questionnaire_key = s_2.questionnaire_key) AND (parent_section_key = s_2.parent_section_key) AND (order_ IS NOT NULL))
  • Filter: (template_section_key = s_2.template_section_key)
  • Rows Removed by Filter: 0
20.          

CTE section_map

21. 0.007 0.623 ↑ 2.2 5 1

Recursive Union (cost=0.56..1,016.82 rows=11 width=40) (actual time=0.051..0.623 rows=5 loops=1)

22. 0.004 0.052 ↑ 1.0 1 1

Nested Loop (cost=0.56..3.71 rows=1 width=40) (actual time=0.051..0.052 rows=1 loops=1)

23. 0.022 0.022 ↑ 1.0 1 1

CTE Scan on section_map_o so (cost=0.00..0.02 rows=1 width=20) (actual time=0.021..0.022 rows=1 loops=1)

24. 0.026 0.026 ↑ 1.0 1 1

Index Scan using section_order_unique on section sn (cost=0.56..3.68 rows=1 width=20) (actual time=0.026..0.026 rows=1 loops=1)

  • Index Cond: ((questionnaire_key = so.questionnaire_key) AND (order_ = so.order_))
  • Filter: ((NOT (so.template_section_key IS DISTINCT FROM template_section_key)) AND (NOT (so.parent_section_key IS DISTINCT FROM parent_section_key)))
  • Rows Removed by Filter: 2
25. 0.017 0.564 ↑ 1.0 1 3

Nested Loop (cost=5.13..101.29 rows=1 width=40) (actual time=0.097..0.188 rows=1 loops=3)

  • Join Filter: ((NOT (sn_1.template_section_key IS DISTINCT FROM so_1.template_section_key)) AND (sm.o_section_key = so_1.parent_section_key))
  • Rows Removed by Join Filter: 1
26. 0.023 0.147 ↑ 2.0 3 3

Hash Join (cost=4.69..50.64 rows=6 width=52) (actual time=0.044..0.049 rows=3 loops=3)

  • Hash Cond: (sn_1.order_ = sr.rank)
27. 0.005 0.033 ↑ 156.0 1 3

Nested Loop (cost=0.43..45.74 rows=156 width=48) (actual time=0.008..0.011 rows=1 loops=3)

28. 0.003 0.003 ↑ 5.0 2 3

WorkTable Scan on section_map sm (cost=0.00..0.20 rows=10 width=40) (actual time=0.000..0.001 rows=2 loops=3)

29. 0.025 0.025 ↑ 16.0 1 5

Index Scan using section_parent_section_key_index on section sn_1 (cost=0.43..4.39 rows=16 width=16) (actual time=0.004..0.005 rows=1 loops=5)

  • Index Cond: (parent_section_key = sm.n_section_key)
30. 0.004 0.091 ↑ 26.2 5 1

Hash (cost=2.62..2.62 rows=131 width=8) (actual time=0.091..0.091 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
31. 0.087 0.087 ↑ 26.2 5 1

CTE Scan on section_ranked sr (cost=0.00..2.62 rows=131 width=8) (actual time=0.013..0.087 rows=5 loops=1)

32. 0.032 0.400 ↑ 1.0 1 8

Index Scan using section_pkey on section so_1 (cost=0.44..8.43 rows=1 width=12) (actual time=0.050..0.050 rows=1 loops=8)

  • Index Cond: (section_key = sr.section_key)
  • Filter: ((template_section_key IS NULL) OR (order_ = (SubPlan 5)))
33.          

SubPlan (for Index Scan)

34. 0.016 0.368 ↑ 1.0 1 8

Aggregate (cost=5.96..5.97 rows=1 width=4) (actual time=0.045..0.046 rows=1 loops=8)

35. 0.128 0.352 ↑ 1.0 1 8

Bitmap Heap Scan on section sf_1 (cost=4.94..5.96 rows=1 width=4) (actual time=0.043..0.044 rows=1 loops=8)

  • Recheck Cond: ((template_section_key = so_1.template_section_key) AND (questionnaire_key = so_1.questionnaire_key))
  • Filter: (NOT (parent_section_key IS DISTINCT FROM so_1.parent_section_key))
  • Rows Removed by Filter: 22
  • Heap Blocks: exact=106
36. 0.040 0.224 ↓ 0.0 0 8

BitmapAnd (cost=4.94..4.94 rows=1 width=0) (actual time=0.028..0.028 rows=0 loops=8)

37. 0.096 0.096 ↑ 2.6 44 8

Bitmap Index Scan on section_template_section_key_index (cost=0.00..2.30 rows=115 width=0) (actual time=0.012..0.012 rows=44 loops=8)

  • Index Cond: (template_section_key = so_1.template_section_key)
38. 0.088 0.088 ↓ 1.1 117 8

Bitmap Index Scan on section_order_unique (cost=0.00..2.39 rows=110 width=0) (actual time=0.011..0.011 rows=117 loops=8)

  • Index Cond: (questionnaire_key = so_1.questionnaire_key)
39.          

CTE recurse_templates

40. 0.061 0.768 ↑ 3,101.5 77 1

Nested Loop (cost=0.43..134,545.25 rows=238,813 width=224) (actual time=0.066..0.768 rows=77 loops=1)

41. 0.627 0.627 ↑ 2.2 5 1

CTE Scan on section_map sm_1 (cost=0.00..0.22 rows=11 width=8) (actual time=0.052..0.627 rows=5 loops=1)

42. 0.080 0.080 ↑ 1,447.3 15 5

Index Scan using response_order_unique on response r_1 (cost=0.43..12,014.27 rows=21,710 width=196) (actual time=0.010..0.016 rows=15 loops=5)

  • Index Cond: (section_key = sm_1.o_section_key)
43.          

CTE response_sequences

44. 1.868 1.909 ↑ 1,194.0 1 1

Nested Loop (cost=5,434.60..6,666.44 rows=1,194 width=16) (actual time=1.908..1.909 rows=1 loops=1)

45. 0.015 0.015 ↑ 1.0 1 1

Index Scan using questionnaire_pkey on questionnaire q (cost=0.29..2.31 rows=1 width=4) (actual time=0.014..0.015 rows=1 loops=1)

  • Index Cond: (questionnaire_key = 45153161)
46. 0.013 0.026 ↑ 1,194.0 1 1

Sort (cost=5,434.32..5,437.30 rows=1,194 width=220) (actual time=0.026..0.026 rows=1 loops=1)

  • Sort Key: recurse_templates.n_section_key, recurse_templates.r_order
  • Sort Method: quicksort Memory: 25kB
47. 0.013 0.013 ↑ 1,194.0 1 1

CTE Scan on recurse_templates (cost=0.00..5,373.29 rows=1,194 width=220) (actual time=0.008..0.013 rows=1 loops=1)

  • Filter: ((response_type)::text = 'Sequence'::text)
  • Rows Removed by Filter: 76
48. 0.104 5,338.884 ↑ 3,086.0 77 1

Sort (cost=1,454,261.54..1,454,855.59 rows=237,619 width=6,295) (actual time=5,338.869..5,338.884 rows=77 loops=1)

  • Sort Key: rt.n_section_key, rt.r_order
  • Sort Method: quicksort Memory: 90kB
49. 0.087 5,338.780 ↑ 3,086.0 77 1

Nested Loop Left Join (cost=30,854.27..333,124.20 rows=237,619 width=6,295) (actual time=5,338.512..5,338.780 rows=77 loops=1)

50. 265.236 5,338.693 ↑ 3,086.0 77 1

Merge Join (cost=30,853.97..259,414.53 rows=237,619 width=6,251) (actual time=5,338.493..5,338.693 rows=77 loops=1)

  • Merge Cond: (rt.response_key = r.response_key)
51. 0.026 2.808 ↑ 3,086.0 77 1

Merge Left Join (cost=30,853.54..32,759.81 rows=237,619 width=48) (actual time=2.779..2.808 rows=77 loops=1)

  • Merge Cond: ((rt.response_key = rsq.response_key) AND (rt.n_section_key = rsq.n_section_key))
52. 0.038 0.867 ↑ 3,086.0 77 1

Sort (cost=30,768.64..31,362.69 rows=237,619 width=44) (actual time=0.862..0.867 rows=77 loops=1)

  • Sort Key: rt.response_key, rt.n_section_key
  • Sort Method: quicksort Memory: 31kB
53. 0.829 0.829 ↑ 3,086.0 77 1

CTE Scan on recurse_templates rt (cost=0.00..5,373.29 rows=237,619 width=44) (actual time=0.071..0.829 rows=77 loops=1)

  • Filter: ((response_type)::text <> 'Create Task Status'::text)
54. 0.004 1.915 ↑ 1,194.0 1 1

Sort (cost=84.90..87.89 rows=1,194 width=16) (actual time=1.915..1.915 rows=1 loops=1)

  • Sort Key: rsq.response_key, rsq.n_section_key
  • Sort Method: quicksort Memory: 25kB
55. 1.911 1.911 ↑ 1,194.0 1 1

CTE Scan on response_sequences rsq (cost=0.00..23.88 rows=1,194 width=16) (actual time=1.909..1.911 rows=1 loops=1)

56. 5,070.649 5,070.649 ↑ 1.0 4,339,673 1

Index Scan using response_pkey on response r (cost=0.43..212,829.33 rows=4,342,060 width=6,211) (actual time=0.039..5,070.649 rows=4,339,673 loops=1)

57. 0.000 0.000 ↓ 0.0 0 77

Index Scan using list_pkey on list ln (cost=0.29..0.31 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=77)

  • Index Cond: (r.list_key = list_key)
Planning time : 3.346 ms
Execution time : 5,369.213 ms