explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kESq

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=10,109,535.29..10,109,630.58 rows=1 width=201) (actual rows= loops=)

  • Join Filter: ((a.principal_vendor_gsl)::text = s.gsl)
2.          

CTE tiles_details

3. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on generic g_3 (cost=10.62..141.30 rows=1 width=193) (actual rows= loops=)

  • Recheck Cond: (type = 'S'::text)
  • Filter: (label_style = 'L1'::text)
4. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on generic_type_idx (cost=0.00..10.62 rows=312 width=0) (actual rows= loops=)

  • Index Cond: (type = 'S'::text)
5.          

CTE form_pages_split

6. 0.000 0.000 ↓ 0.0

HashAggregate (cost=216.90..256.02 rows=3,912 width=12) (actual rows= loops=)

  • Group Key: g_4.sort_order[1]
7. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=13.38..197.34 rows=3,912 width=12) (actual rows= loops=)

  • Hash Cond: (g_4.id = sp_3.generic_id)
8. 0.000 0.000 ↓ 0.0

Seq Scan on generic g_4 (cost=0.00..165.12 rows=3,912 width=58) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Hash (cost=8.17..8.17 rows=417 width=16) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Seq Scan on section_prototype sp_3 (cost=0.00..8.17 rows=417 width=16) (actual rows= loops=)

11.          

CTE forms_with_no_required_questions

12. 0.000 0.000 ↓ 0.0

HashSetOp Except (cost=0.00..444.51 rows=1 width=8) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Append (cost=0.00..438.80 rows=2,284 width=8) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 1 (cost=0.00..0.03 rows=1 width=8) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

CTE Scan on tiles_details (cost=0.00..0.02 rows=1 width=4) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 2 (cost=393.11..438.77 rows=2,283 width=8) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

HashAggregate (cost=393.11..415.94 rows=2,283 width=4) (actual rows= loops=)

  • Group Key: g_5.sort_order[1]
18. 0.000 0.000 ↓ 0.0

Hash Join (cost=184.79..387.41 rows=2,283 width=4) (actual rows= loops=)

  • Hash Cond: (g_5.id = q_3.generic_id)
19. 0.000 0.000 ↓ 0.0

Seq Scan on generic g_5 (cost=0.00..165.12 rows=3,912 width=58) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Hash (cost=156.25..156.25 rows=2,283 width=8) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Hash Join (cost=1.62..156.25 rows=2,283 width=8) (actual rows= loops=)

  • Hash Cond: (q_3.validation_rules_id = vr_1.id)
22. 0.000 0.000 ↓ 0.0

Seq Scan on question q_3 (cost=0.00..122.25 rows=3,425 width=16) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Hash (cost=1.38..1.38 rows=20 width=8) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Seq Scan on validation_rules vr_1 (cost=0.00..1.38 rows=20 width=8) (actual rows= loops=)

  • Filter: (validation_type = 'REQUIRED'::text)
25.          

CTE supplier_sites

26. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=24.04..24.64 rows=34 width=15) (actual rows= loops=)

  • Group Key: sa_1_2.gsl
27. 0.000 0.000 ↓ 0.0

Sort (cost=24.04..24.13 rows=34 width=44) (actual rows= loops=)

  • Sort Key: sa_1_2.gsl
28. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.85..23.18 rows=34 width=44) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.43..16.47 rows=1 width=8) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Index Scan using generic_hierarchy_idx on generic g_1_2 (cost=0.15..8.17 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (hierarchy_id <@ '1.1.2'::ltree)
31. 0.000 0.000 ↓ 0.0

Index Scan using question_generic_id_idx on question q_1_2 (cost=0.28..8.30 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (generic_id = g_1_2.id)
32. 0.000 0.000 ↓ 0.0

Index Scan using supplier_answer_question_id_idx on supplier_answer sa_1_2 (cost=0.42..6.07 rows=64 width=52) (actual rows= loops=)

  • Index Cond: (question_id = q_1_2.id)
33.          

CTE company_contacts

34. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=24.04..24.64 rows=34 width=15) (actual rows= loops=)

  • Group Key: sa_1_3.gsl
35. 0.000 0.000 ↓ 0.0

Sort (cost=24.04..24.13 rows=34 width=44) (actual rows= loops=)

  • Sort Key: sa_1_3.gsl
36. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.85..23.18 rows=34 width=44) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.43..16.47 rows=1 width=8) (actual rows= loops=)

38. 0.000 0.000 ↓ 0.0

Index Scan using generic_hierarchy_idx on generic g_1_3 (cost=0.15..8.17 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (hierarchy_id <@ '1.3'::ltree)
39. 0.000 0.000 ↓ 0.0

Index Scan using question_generic_id_idx on question q_1_3 (cost=0.28..8.30 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (generic_id = g_1_3.id)
40. 0.000 0.000 ↓ 0.0

Index Scan using supplier_answer_question_id_idx on supplier_answer sa_1_3 (cost=0.42..6.07 rows=64 width=52) (actual rows= loops=)

  • Index Cond: (question_id = q_1_3.id)
41. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=10,108,595.47..10,108,690.55 rows=1 width=150) (actual rows= loops=)

  • Hash Cond: ((s_1.gsl = s.gsl) AND ((g.sort_order[1]) = td.form_id))
  • Filter: (((CASE WHEN (COALESCE(vr.validation_type, ''::text) = 'REQUIRED'::text) THEN 1 WHEN ((vr.validation_type IS NULL) AND (g.sort_order[1] = fwnrq.form_id)) THEN 1 ELSE 0 END) = 1) OR ((CASE WHEN (COALESCE(vr.validation_type, ''::text) = 'REQUIRED'::text) THEN 1 WHEN ((vr.validation_type IS NULL) AND (g.sort_order[1] = fwnrq.form_id)) THEN 1 ELSE 0 END) IS NULL))
42. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=10,108,556.33..10,108,633.50 rows=2,367 width=39) (actual rows= loops=)

  • Merge Cond: (s_1.gsl = ss.gsl)
43. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=10,108,555.55..10,108,579.47 rows=2,367 width=71) (actual rows= loops=)

  • Merge Cond: ((s_1.gsl = sa_1.gsl) AND ((g.sort_order[1]) = fps_1.form_id))
  • Join Filter: ((CASE WHEN (COALESCE(vr.validation_type, ''::text) = 'REQUIRED'::text) THEN 1 WHEN ((vr.validation_type IS NULL) AND (g.sort_order[1] = fwnrq.form_id)) THEN 1 ELSE 0 END) = 1)
44. 0.000 0.000 ↓ 0.0

Sort (cost=9,598,853.50..9,598,859.42 rows=2,367 width=87) (actual rows= loops=)

  • Sort Key: s_1.gsl, (g.sort_order[1])
45. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=9,598,579.76..9,598,720.85 rows=2,367 width=87) (actual rows= loops=)

  • Merge Cond: ((g.sort_order[1]) = (g_1.sort_order[1]))
  • Join Filter: (s_1_1.gsl = s_1.gsl)
46. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=9,030,711.98..9,030,782.99 rows=2,367 width=24) (actual rows= loops=)

  • Group Key: (g.sort_order[1]), s_1.gsl, (CASE WHEN (COALESCE(vr.validation_type, ''::text) = 'REQUIRED'::text) THEN 1 WHEN ((vr.validation_type IS NULL) AND (g.sort_order[1] = fwnrq.form_id)) THEN 1 ELSE 0 END), (CASE WHEN (fwnrq.form_id IS NOT NULL) THEN true ELSE false END)
47. 0.000 0.000 ↓ 0.0

Sort (cost=9,030,711.98..9,030,717.90 rows=2,367 width=24) (actual rows= loops=)

  • Sort Key: (g.sort_order[1]), (CASE WHEN (COALESCE(vr.validation_type, ''::text) = 'REQUIRED'::text) THEN 1 WHEN ((vr.validation_type IS NULL) AND (g.sort_order[1] = fwnrq.form_id)) THEN 1 ELSE 0 END), (CASE WHEN (fwnrq.form_id IS NOT NULL) THEN true ELSE false END)
48. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=25.29..9,030,579.33 rows=2,367 width=24) (actual rows= loops=)

  • Hash Cond: (g.sort_order[1] = fwnrq.form_id)
49. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=25.25..9,030,558.46 rows=2,367 width=73) (actual rows= loops=)

50. 0.000 0.000 ↓ 0.0

Merge Right Join (cost=25.12..9,030,159.62 rows=2,367 width=73) (actual rows= loops=)

  • Merge Cond: (q.generic_id = g.id)
  • Filter: (((sp.generic_id = g.id) AND (g.type = ANY ('{S,S_WQ}'::text[]))) OR ((sp.generic_id = g.parent_id) AND (g.type = ANY ('{Q,Q_WQ}'::text[]))) OR (q.is_nested_question AND (g.type = 'Q'::text) AND (sp.generic_id = (SubPlan 7))))
51. 0.000 0.000 ↓ 0.0

Index Scan using question_generic_id_idx on question q (cost=0.28..207.07 rows=3,425 width=25) (actual rows= loops=)

52. 0.000 0.000 ↓ 0.0

Materialize (cost=24.84..19,895.57 rows=1,235,755 width=85) (actual rows= loops=)

53. 0.000 0.000 ↓ 0.0

Nested Loop (cost=24.84..16,806.18 rows=1,235,755 width=85) (actual rows= loops=)

54. 0.000 0.000 ↓ 0.0

Index Scan using generic_pkey on generic g (cost=0.28..256.64 rows=3,481 width=70) (actual rows= loops=)

  • Filter: (type = ANY ('{Q,Q_WQ}'::text[]))
55. 0.000 0.000 ↓ 0.0

Materialize (cost=24.56..1,103.49 rows=355 width=15) (actual rows= loops=)

56. 0.000 0.000 ↓ 0.0

Hash Join (cost=24.56..1,101.72 rows=355 width=15) (actual rows= loops=)

  • Hash Cond: (s_1.section_prototype_id = sp.id)
57. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on section s_1 (cost=11.17..1,083.54 rows=355 width=15) (actual rows= loops=)

  • Recheck Cond: (gsl = 'N16615'::text)
58. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on section_gsl_idx (cost=0.00..11.08 rows=355 width=0) (actual rows= loops=)

  • Index Cond: (gsl = 'N16615'::text)
59. 0.000 0.000 ↓ 0.0

Hash (cost=8.17..8.17 rows=417 width=16) (actual rows= loops=)

60. 0.000 0.000 ↓ 0.0

Seq Scan on section_prototype sp (cost=0.00..8.17 rows=417 width=16) (actual rows= loops=)

61.          

SubPlan (for Merge Right Join)

62. 0.000 0.000 ↓ 0.0

Limit (cost=0.28..8.30 rows=1 width=8) (actual rows= loops=)

63. 0.000 0.000 ↓ 0.0

Index Scan using generic_pkey on generic g2 (cost=0.28..8.30 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (id = g.parent_id)
  • Filter: (type = 'Q'::text)
64. 0.000 0.000 ↓ 0.0

Index Scan using validation_rules_pkey on validation_rules vr (cost=0.14..0.16 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (id = q.validation_rules_id)
65. 0.000 0.000 ↓ 0.0

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

66. 0.000 0.000 ↓ 0.0

CTE Scan on forms_with_no_required_questions fwnrq (cost=0.00..0.02 rows=1 width=4) (actual rows= loops=)

67. 0.000 0.000 ↓ 0.0

Materialize (cost=567,867.78..567,880.85 rows=134 width=75) (actual rows= loops=)

68. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=567,867.78..567,879.17 rows=134 width=75) (actual rows= loops=)

  • Group Key: s_1_1.gsl, (g_1.sort_order[1])
69. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=567,867.78..567,874.48 rows=134 width=106) (actual rows= loops=)

  • Group Key: (g_1.sort_order[1]), g_1.hierarchy_id, s_1_1.gsl, ss_1.num_of_sites, cc.num_of_company_contacts
70. 0.000 0.000 ↓ 0.0

Sort (cost=567,867.78..567,868.12 rows=134 width=156) (actual rows= loops=)

  • Sort Key: (g_1.sort_order[1]), g_1.hierarchy_id, ss_1.num_of_sites, cc.num_of_company_contacts
71. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=309.95..567,863.05 rows=134 width=156) (actual rows= loops=)

  • Hash Cond: (s_1_1.gsl = cc.gsl)
72. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=309.18..567,860.43 rows=134 width=144) (actual rows= loops=)

  • Hash Cond: (s_1_1.gsl = ss_1.gsl)
73. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=308.40..567,857.81 rows=134 width=136) (actual rows= loops=)

  • Join Filter: (sa.gsl = s_1_1.gsl)
74. 0.000 0.000 ↓ 0.0

Hash Join (cost=307.98..567,021.75 rows=134 width=128) (actual rows= loops=)

  • Hash Cond: (s_1_1.section_prototype_id = sp_1.id)
  • Join Filter: (((sp_1.generic_id = g_1.id) AND (g_1.type = ANY ('{S,S_WQ}'::text[]))) OR ((sp_1.generic_id = g_1.parent_id) AND (g_1.type = ANY ('{Q,Q_WQ}'::text[]))) OR (q_1.is_nested_question AND (sp_1.generic_id = (SubPlan 9))))
75. 0.000 0.000 ↓ 0.0

Nested Loop (cost=294.60..2,382.61 rows=69,438 width=157) (actual rows= loops=)

76. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on section s_1_1 (cost=11.17..1,083.54 rows=355 width=15) (actual rows= loops=)

  • Recheck Cond: (gsl = 'N16615'::text)
77. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on section_gsl_idx (cost=0.00..11.08 rows=355 width=0) (actual rows= loops=)

  • Index Cond: (gsl = 'N16615'::text)
78. 0.000 0.000 ↓ 0.0

Materialize (cost=283.42..429.81 rows=196 width=142) (actual rows= loops=)

79. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=283.42..428.83 rows=196 width=142) (actual rows= loops=)

  • Hash Cond: (q_1.validation_rules_id = v.id)
  • Filter: CASE WHEN (g_1.sort_order[1] = ANY ('{1,2,3}'::integer[])) THEN (v.validation_type = 'REQUIRED'::text) ELSE true END
80. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=281.75..420.26 rows=391 width=150) (actual rows= loops=)

  • Hash Cond: (q_1.generic_id = g_1.id)
81. 0.000 0.000 ↓ 0.0

Seq Scan on question q_1 (cost=0.00..122.25 rows=3,425 width=25) (actual rows= loops=)

82. 0.000 0.000 ↓ 0.0

Hash (cost=276.86..276.86 rows=391 width=133) (actual rows= loops=)

83. 0.000 0.000 ↓ 0.0

Hash Join (cost=88.27..276.86 rows=391 width=133) (actual rows= loops=)

  • Hash Cond: (g_1.sort_order[1] = fps.form_id)
84. 0.000 0.000 ↓ 0.0

Seq Scan on generic g_1 (cost=0.00..165.12 rows=3,912 width=133) (actual rows= loops=)

85. 0.000 0.000 ↓ 0.0

Hash (cost=88.02..88.02 rows=20 width=4) (actual rows= loops=)

86. 0.000 0.000 ↓ 0.0

CTE Scan on form_pages_split fps (cost=0.00..88.02 rows=20 width=4) (actual rows= loops=)

  • Filter: (total_pages = 1)
87. 0.000 0.000 ↓ 0.0

Hash (cost=1.30..1.30 rows=30 width=16) (actual rows= loops=)

88. 0.000 0.000 ↓ 0.0

Seq Scan on validation_rules v (cost=0.00..1.30 rows=30 width=16) (actual rows= loops=)

89. 0.000 0.000 ↓ 0.0

Hash (cost=8.17..8.17 rows=417 width=16) (actual rows= loops=)

90. 0.000 0.000 ↓ 0.0

Seq Scan on section_prototype sp_1 (cost=0.00..8.17 rows=417 width=16) (actual rows= loops=)

91.          

SubPlan (for Hash Join)

92. 0.000 0.000 ↓ 0.0

Limit (cost=0.28..8.30 rows=1 width=8) (actual rows= loops=)

93. 0.000 0.000 ↓ 0.0

Index Scan using generic_pkey on generic g2_2 (cost=0.28..8.30 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (id = g_1.parent_id)
  • Filter: (type = 'Q'::text)
94. 0.000 0.000 ↓ 0.0

Index Scan using supplier_answer_question_id_idx on supplier_answer sa (cost=0.42..6.23 rows=1 width=23) (actual rows= loops=)

  • Index Cond: (question_id = q_1.id)
  • Filter: (gsl = 'N16615'::text)
95. 0.000 0.000 ↓ 0.0

Hash (cost=0.77..0.77 rows=1 width=40) (actual rows= loops=)

96. 0.000 0.000 ↓ 0.0

CTE Scan on supplier_sites ss_1 (cost=0.00..0.77 rows=1 width=40) (actual rows= loops=)

  • Filter: (gsl = 'N16615'::text)
97. 0.000 0.000 ↓ 0.0

Hash (cost=0.77..0.77 rows=1 width=40) (actual rows= loops=)

98. 0.000 0.000 ↓ 0.0

CTE Scan on company_contacts cc (cost=0.00..0.77 rows=1 width=40) (actual rows= loops=)

  • Filter: (gsl = 'N16615'::text)
99. 0.000 0.000 ↓ 0.0

Materialize (cost=509,702.05..509,702.05 rows=1 width=27) (actual rows= loops=)

100. 0.000 0.000 ↓ 0.0

Sort (cost=509,702.05..509,702.05 rows=1 width=27) (actual rows= loops=)

  • Sort Key: sa_1.gsl, fps_1.form_id
101. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=509,701.97..509,702.03 rows=1 width=27) (actual rows= loops=)

  • Group Key: sa_1.gsl, fps_1.form_id
102. 0.000 0.000 ↓ 0.0

Unique (cost=509,701.97..509,701.99 rows=1 width=91) (actual rows= loops=)

103. 0.000 0.000 ↓ 0.0

Sort (cost=509,701.97..509,701.97 rows=1 width=91) (actual rows= loops=)

  • Sort Key: fps_1.form_id, (CASE WHEN ((COALESCE(vr_childs.validation_type, ''::text)) = 'REQUIRED'::text) THEN 1 WHEN (((COALESCE(vr_childs.validation_type, ''::text)) <> 'REQUIRED'::text) AND (gq.form_id IS NOT NULL)) THEN 1 ELSE 0 END), (CASE WHEN (((COALESCE(vr_childs.validation_type, ''::text)) <> 'REQUIRED'::text) AND (gq.form_id IS NOT NULL)) THEN (gq.questions * COALESCE(ss_1_1.num_of_sites, '1'::bigint)) ELSE (count(DISTINCT q_childs.id) * COALESCE(ss_1_1.num_of_sites, '1'::bigint)) END), (CASE WHEN (((COALESCE(vr_childs.validation_type, ''::text)) <> 'REQUIRED'::text) AND (gq.form_id IS NOT NULL)) THEN gq.answers ELSE count(DISTINCT sa_childs.id) END), ss_1_1.num_of_sites
104. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=509,701.91..509,701.96 rows=1 width=91) (actual rows= loops=)

  • Group Key: sa_1.gsl, fps_1.form_id, ss_1_1.num_of_sites, (COALESCE(vr_childs.validation_type, ''::text)), gq.form_id, gq.questions, gq.answers
105. 0.000 0.000 ↓ 0.0

Sort (cost=509,701.91..509,701.91 rows=1 width=95) (actual rows= loops=)

  • Sort Key: fps_1.form_id, ss_1_1.num_of_sites, (COALESCE(vr_childs.validation_type, ''::text)), gq.form_id, gq.questions, gq.answers
106. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=508,173.75..509,701.90 rows=1 width=95) (actual rows= loops=)

  • Join Filter: (ss_1_1.gsl = sa_1.gsl)
107. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=508,173.75..509,701.12 rows=1 width=55) (actual rows= loops=)

  • Join Filter: (sa_childs.gsl = sa_1.gsl)
108. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=508,173.33..509,694.88 rows=1 width=47) (actual rows= loops=)

  • Filter: ((vr_childs.validation_type = 'REQUIRED'::text) OR ((vr_childs.validation_type IS NULL) AND (gq.form_id IS NOT NULL)))
109. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=508,173.19..509,694.54 rows=2 width=47) (actual rows= loops=)

110. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=508,172.91..509,693.69 rows=2 width=39) (actual rows= loops=)

  • Join Filter: ((gq.gsl = sa_1.gsl) AND (gq.form_id = fps_1.form_id))
111. 0.000 0.000 ↓ 0.0

Nested Loop (cost=134.27..1,645.67 rows=2 width=19) (actual rows= loops=)

  • Join Filter: (g_2.sort_order[1] = fps_1.form_id)
112. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=134.27..1,541.35 rows=1 width=65) (actual rows= loops=)

  • Filter: ((nlevel(g_childs.hierarchy_id) - CASE WHEN (g_parent_ancestor.type = 'S'::text) THEN 3 ELSE 2 END) = nlevel(g_2.hierarchy_id))
113. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=133.99..1,518.70 rows=49 width=199) (actual rows= loops=)

114. 0.000 0.000 ↓ 0.0

Nested Loop (cost=133.71..1,495.81 rows=49 width=199) (actual rows= loops=)

  • Join Filter: (g_childs.hierarchy_id <@ g_2.hierarchy_id)
115. 0.000 0.000 ↓ 0.0

Index Scan using generic_pkey on generic g_childs (cost=0.28..256.64 rows=3,481 width=79) (actual rows= loops=)

  • Filter: (type = ANY ('{Q,Q_WQ}'::text[]))
116. 0.000 0.000 ↓ 0.0

Materialize (cost=133.43..508.20 rows=14 width=120) (actual rows= loops=)

117. 0.000 0.000 ↓ 0.0

Nested Loop (cost=133.43..508.13 rows=14 width=120) (actual rows= loops=)

118. 0.000 0.000 ↓ 0.0

Hash Join (cost=133.15..486.71 rows=14 width=15) (actual rows= loops=)

  • Hash Cond: (sa_1.question_id = q_2.id)
119. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on supplier_answer sa_1 (cost=5.21..358.25 rows=102 width=15) (actual rows= loops=)

  • Recheck Cond: (gsl = 'N16615'::text)
120. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on supplier_answer_gsl_idx (cost=0.00..5.18 rows=102 width=0) (actual rows= loops=)

  • Index Cond: (gsl = 'N16615'::text)
121. 0.000 0.000 ↓ 0.0

Hash (cost=122.25..122.25 rows=455 width=16) (actual rows= loops=)

122. 0.000 0.000 ↓ 0.0

Seq Scan on question q_2 (cost=0.00..122.25 rows=455 width=16) (actual rows= loops=)

  • Filter: (site_context IS FALSE)
123. 0.000 0.000 ↓ 0.0

Index Scan using generic_pkey on generic g_2 (cost=0.28..1.52 rows=1 width=121) (actual rows= loops=)

  • Index Cond: (id = q_2.generic_id)
124. 0.000 0.000 ↓ 0.0

Index Scan using generic_pkey on generic g_parent (cost=0.28..0.46 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (id = g_childs.parent_id)
125. 0.000 0.000 ↓ 0.0

Index Scan using generic_pkey on generic g_parent_ancestor (cost=0.28..0.44 rows=1 width=12) (actual rows= loops=)

  • Index Cond: (id = g_parent.parent_id)
126. 0.000 0.000 ↓ 0.0

CTE Scan on form_pages_split fps_1 (cost=0.00..88.02 rows=1,304 width=4) (actual rows= loops=)

  • Filter: (total_pages > 1)
127. 0.000 0.000 ↓ 0.0

Materialize (cost=508,038.64..508,043.67 rows=134 width=27) (actual rows= loops=)

128. 0.000 0.000 ↓ 0.0

Subquery Scan on gq (cost=508,038.64..508,043.00 rows=134 width=27) (actual rows= loops=)

129. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=508,038.64..508,041.66 rows=134 width=27) (actual rows= loops=)

  • Group Key: s_1_2.gsl, (g_1_1.sort_order[1])
130. 0.000 0.000 ↓ 0.0

Sort (cost=508,038.64..508,038.98 rows=134 width=27) (actual rows= loops=)

  • Sort Key: (g_1_1.sort_order[1])
131. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=25.54..508,033.91 rows=134 width=27) (actual rows= loops=)

  • Join Filter: (sa_1_1.gsl = s_1_2.gsl)
132. 0.000 0.000 ↓ 0.0

Merge Right Join (cost=25.12..507,197.85 rows=134 width=65) (actual rows= loops=)

  • Merge Cond: (q_1_1.generic_id = g_1_1.id)
  • Filter: (((sp_2.generic_id = g_1_1.id) AND (g_1_1.type = ANY ('{S,S_WQ}'::text[]))) OR ((sp_2.generic_id = g_1_1.parent_id) AND (g_1_1.type = ANY ('{Q,Q_WQ}'::text[]))) OR (q_1_1.is_nested_question AND (sp_2.generic_id = (SubPlan 8))))
133. 0.000 0.000 ↓ 0.0

Index Scan using question_generic_id_idx on question q_1_1 (cost=0.28..207.07 rows=3,425 width=17) (actual rows= loops=)

134. 0.000 0.000 ↓ 0.0

Materialize (cost=24.84..2,407.40 rows=69,225 width=85) (actual rows= loops=)

135. 0.000 0.000 ↓ 0.0

Nested Loop (cost=24.84..2,234.34 rows=69,225 width=85) (actual rows= loops=)

136. 0.000 0.000 ↓ 0.0

Index Scan using generic_pkey on generic g_1_1 (cost=0.28..266.42 rows=195 width=70) (actual rows= loops=)

  • Filter: ((hierarchy_id <@ '4.0'::ltree) OR (hierarchy_id <@ '12.0'::ltree))
137. 0.000 0.000 ↓ 0.0

Materialize (cost=24.56..1,103.49 rows=355 width=15) (actual rows= loops=)

138. 0.000 0.000 ↓ 0.0

Hash Join (cost=24.56..1,101.72 rows=355 width=15) (actual rows= loops=)

  • Hash Cond: (s_1_2.section_prototype_id = sp_2.id)
139. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on section s_1_2 (cost=11.17..1,083.54 rows=355 width=15) (actual rows= loops=)

  • Recheck Cond: (gsl = 'N16615'::text)
140. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on section_gsl_idx (cost=0.00..11.08 rows=355 width=0) (actual rows= loops=)

  • Index Cond: (gsl = 'N16615'::text)
141. 0.000 0.000 ↓ 0.0

Hash (cost=8.17..8.17 rows=417 width=16) (actual rows= loops=)

142. 0.000 0.000 ↓ 0.0

Seq Scan on section_prototype sp_2 (cost=0.00..8.17 rows=417 width=16) (actual rows= loops=)

143.          

SubPlan (for Merge Right Join)

144. 0.000 0.000 ↓ 0.0

Limit (cost=0.28..8.30 rows=1 width=8) (actual rows= loops=)

145. 0.000 0.000 ↓ 0.0

Index Scan using generic_pkey on generic g2_1 (cost=0.28..8.30 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (id = g_1_1.parent_id)
  • Filter: (type = 'Q'::text)
146. 0.000 0.000 ↓ 0.0

Index Scan using supplier_answer_question_id_idx on supplier_answer sa_1_1 (cost=0.42..6.23 rows=1 width=23) (actual rows= loops=)

  • Index Cond: (question_id = q_1_1.id)
  • Filter: (gsl = 'N16615'::text)
147. 0.000 0.000 ↓ 0.0

Index Scan using question_generic_id_idx on question q_childs (cost=0.28..0.41 rows=1 width=24) (actual rows= loops=)

  • Index Cond: (generic_id = g_childs.id)
148. 0.000 0.000 ↓ 0.0

Index Scan using validation_rules_pkey on validation_rules vr_childs (cost=0.14..0.16 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (id = q_childs.validation_rules_id)
149. 0.000 0.000 ↓ 0.0

Index Scan using supplier_answer_question_id_idx on supplier_answer sa_childs (cost=0.42..6.23 rows=1 width=23) (actual rows= loops=)

  • Index Cond: (question_id = q_childs.id)
  • Filter: (gsl = 'N16615'::text)
150. 0.000 0.000 ↓ 0.0

CTE Scan on supplier_sites ss_1_1 (cost=0.00..0.77 rows=1 width=40) (actual rows= loops=)

  • Filter: (gsl = 'N16615'::text)
151. 0.000 0.000 ↓ 0.0

Materialize (cost=0.78..0.78 rows=1 width=40) (actual rows= loops=)

152. 0.000 0.000 ↓ 0.0

Sort (cost=0.78..0.78 rows=1 width=40) (actual rows= loops=)

  • Sort Key: ss.gsl
153. 0.000 0.000 ↓ 0.0

CTE Scan on supplier_sites ss (cost=0.00..0.77 rows=1 width=40) (actual rows= loops=)

  • Filter: (gsl = 'N16615'::text)
154. 0.000 0.000 ↓ 0.0

Hash (cost=39.13..39.13 rows=1 width=115) (actual rows= loops=)

155. 0.000 0.000 ↓ 0.0

Nested Loop (cost=35.08..39.13 rows=1 width=115) (actual rows= loops=)

156. 0.000 0.000 ↓ 0.0

CTE Scan on tiles_details td (cost=0.00..0.02 rows=1 width=108) (actual rows= loops=)

157. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on section s (cost=35.08..39.10 rows=1 width=15) (actual rows= loops=)

  • Recheck Cond: ((gsl = 'N16615'::text) AND (generic_id = td.id))
158. 0.000 0.000 ↓ 0.0

BitmapAnd (cost=35.08..35.08 rows=1 width=0) (actual rows= loops=)

159. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on section_gsl_idx (cost=0.00..11.08 rows=355 width=0) (actual rows= loops=)

  • Index Cond: (gsl = 'N16615'::text)
160. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on section_generic_id_idx (cost=0.00..23.66 rows=965 width=0) (actual rows= loops=)

  • Index Cond: (generic_id = td.id)
161. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=48.71..48.85 rows=1 width=39) (actual rows= loops=)

  • Group Key: a.principal_vendor_gsl
162. 0.000 0.000 ↓ 0.0

Unique (cost=48.71..48.81 rows=1 width=725) (actual rows= loops=)

163.          

CTE sup_session

164. 0.000 0.000 ↓ 0.0

HashAggregate (cost=6.65..7.16 rows=51 width=38) (actual rows= loops=)

  • Group Key: supplier_sessions.princpl_supplier_gsl, supplier_sessions.princpl_supplier_name, supplier_sessions.region_idn, supplier_sessions.purchase_family_idn
165. 0.000 0.000 ↓ 0.0

Seq Scan on supplier_sessions (cost=0.00..5.95 rows=56 width=38) (actual rows= loops=)

  • Filter: (purchase_family_idn <> 0)
166. 0.000 0.000 ↓ 0.0

Sort (cost=41.55..41.56 rows=1 width=725) (actual rows= loops=)

  • Sort Key: a.principal_vendor_name, a.region, a.sites, (COALESCE(a.sites, '-99999999'::integer)), a.purchase_family, a.year_spend, a.ytd_spend, a.ssrp_class, a.ssrp_class_order, a.qscor, (COALESCE(a.qscor, '-99999999'::integer)), a.risk_fhr, (COALESCE(a.risk_fhr, '-99999999'::integer)), a.cotd, a.dscore_otd, (COALESCE(a.dscore_otd, '-99999999'::numeric)), a.dscore_avg_days_late_cnbd_pf, a.dscore_avg_days_late_nbd_pf, a.dscore_awlt_competiv_pf, a.dscore_awlt_days_pf, a.cash_awt, a.mpi, (COALESCE(a.mpi, '-99999999'::numeric)), a.session_s, a.ssrp_category_user_rating, a.risk_fhr_user_rating, a.cash_awt_user_rating, a.org_alignment, a.responsiveness, a.innovation, a.last_update, (COALESCE(a.last_update, '1970-01-01 01:00:00+01'::timestamp with time zone)), a.updated_by, a.ssrp_rating, a.qscor_comnt, a.rolng_3mth_recvd_qty, a.rolng_3mth_defct_qty, a.rolng_3mth_dppm
167. 0.000 0.000 ↓ 0.0

Subquery Scan on a (cost=41.38..41.54 rows=1 width=725) (actual rows= loops=)

168. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=41.38..41.53 rows=1 width=661) (actual rows= loops=)

  • Group Key: spcm.principal_vendor_name, spcm.principal_vendor_gsl, pf.purchase_family, spcm.region
169. 0.000 0.000 ↓ 0.0

Sort (cost=41.38..41.39 rows=1 width=200) (actual rows= loops=)

  • Sort Key: spcm.principal_vendor_name, pf.purchase_family, spcm.region
170. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1.78..41.37 rows=1 width=200) (actual rows= loops=)

  • Join Filter: ((dscor.principal_vendor_gsl = (spcm.principal_vendor_gsl)::text) AND (dscor.principal_vendor_name = (spcm.principal_vendor_name)::text) AND ((dscor.region)::text = (spcm.region)::text))
171. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1.37..34.04 rows=1 width=181) (actual rows= loops=)

  • Join Filter: (((ss_2.princpl_supplier_gsl)::text = (spcm.principal_vendor_gsl)::text) AND ((ss_2.princpl_supplier_name)::text = (spcm.principal_vendor_name)::text) AND (ss_2.purchase_family_idn = spcm.purchase_family_idn) AND (ss_2.region_idn = spcm.region_idn))
172. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1.37..32.87 rows=1 width=186) (actual rows= loops=)

  • Join Filter: (((spcm.principal_vendor_gsl)::text = psur.principal_vendor_gsl) AND ((spcm.principal_vendor_name)::text = psur.principal_vendor_name) AND ((spcm.region)::text = psur.region))
173. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.96..25.04 rows=1 width=126) (actual rows= loops=)

174. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.69..16.74 rows=1 width=115) (actual rows= loops=)

  • Join Filter: (((spcm.principal_vendor_gsl)::text = qcp.principal_vendor_gsl) AND ((spcm.region)::text = qcp.region))
175. 0.000 0.000 ↓ 0.0

Index Scan using isuppscoremonthperreg on supplier_pf_score_monthly_per_reg spcm (cost=0.41..8.43 rows=1 width=107) (actual rows= loops=)

  • Index Cond: ((principal_vendor_gsl)::text = 'N16615'::text)
176. 0.000 0.000 ↓ 0.0

Index Scan using qscor_calc_perreg_pkey on qscor_calc_perreg qcp (cost=0.28..8.30 rows=1 width=20) (actual rows= loops=)

  • Index Cond: (principal_vendor_gsl = 'N16615'::text)
177. 0.000 0.000 ↓ 0.0

Index Scan using purchase_family_d_pkey on src_purchase_family_d pf (cost=0.28..8.29 rows=1 width=17) (actual rows= loops=)

  • Index Cond: (pf_idn = (spcm.purchase_family_idn)::numeric)
  • Filter: ((purchase_family)::text <> 'Unassigned'::text)
178. 0.000 0.000 ↓ 0.0

Index Scan using pfregion_scorecard_key on pfregion_scorecard_user_rating psur (cost=0.41..7.81 rows=1 width=107) (actual rows= loops=)

  • Index Cond: ((principal_vendor_gsl = 'N16615'::text) AND (COALESCE((pf.purchase_family)::text, ''::text) = purchase_family))
179. 0.000 0.000 ↓ 0.0

CTE Scan on sup_session ss_2 (cost=0.00..1.15 rows=1 width=672) (actual rows= loops=)

  • Filter: ((princpl_supplier_gsl)::text = 'N16615'::text)
180. 0.000 0.000 ↓ 0.0

Index Scan using dscore_details_pf_pkey on dscore_details_pf dscor (cost=0.41..7.32 rows=1 width=65) (actual rows= loops=)

  • Index Cond: ((principal_vendor_gsl = 'N16615'::text) AND ((purchase_family_description)::text = COALESCE((pf.purchase_family)::text, ''::text)))