explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Imcv

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

CTE Scan on vl (cost=11,312,146.68..11,312,146.70 rows=1 width=842) (actual rows= loops=)

2.          

CTE split

3. 0.000 0.000 ↓ 0.0

Seq Scan on tc (cost=0.00..222.16 rows=396 width=40) (actual rows= loops=)

  • Filter: (((title)::text ~~* '%мбит/с%'::text) OR ((title)::text ~~* '%кбит/с%'::text) OR ((descr)::text ~~* '%мбит/с%'::text) OR ((descr)::text ~~* '%кбит/с%'::text))
4.          

CTE speed

5. 0.000 0.000 ↓ 0.0

CTE Scan on split s (cost=0.00..23.76 rows=396 width=40) (actual rows= loops=)

6.          

CTE tc_price

7. 0.000 0.000 ↓ 0.0

Seq Scan on tc_price price (cost=0.00..6,865.93 rows=2,287 width=8) (actual rows= loops=)

  • Filter: ((started_at = (SubPlan 3)) OR (finished_at IS NULL))
8.          

SubPlan (forSeq Scan)

9. 0.000 0.000 ↓ 0.0

Aggregate (cost=2.50..2.51 rows=1 width=8) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Index Scan using tc_id_idx on tc_price pr (cost=0.28..2.50 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (tc_id = price.tc_id)
11.          

CTE ntk_lc_discount

12. 0.000 0.000 ↓ 0.0

Seq Scan on ntk_lc_discount dis (cost=0.00..8,885.89 rows=18,922 width=8) (actual rows= loops=)

  • Filter: ((active_end IS NULL) OR (active_end > now()))
13.          

CTE dobr

14. 0.000 0.000 ↓ 0.0

Seq Scan on ntk_user_block nub (cost=0.00..17,978.86 rows=41,329 width=4) (actual rows= loops=)

  • Filter: ((closing_date IS NULL) AND (started_at < now()))
15.          

CTE acs

16. 0.000 0.000 ↓ 0.0

Seq Scan on account_state acs (cost=0.00..10,647,327.67 rows=84,267 width=14) (actual rows= loops=)

  • Filter: (created_at = (SubPlan 8))
17.          

SubPlan (forSeq Scan)

18. 0.000 0.000 ↓ 0.0

Result (cost=0.60..0.61 rows=1 width=8) (actual rows= loops=)

19.          

Initplan (forResult)

20. 0.000 0.000 ↓ 0.0

Limit (cost=0.56..0.60 rows=1 width=8) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Index Only Scan Backward using account_state_account_id_created_at_idx on account_state ac (cost=0.56..4.82 rows=103 width=8) (actual rows= loops=)

  • Index Cond: ((account_id = acs.account_id) AND (created_at IS NOT NULL))
22.          

CTE iptv_pack

23. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=62,375.44..62,375.46 rows=1 width=36) (actual rows= loops=)

  • Group Key: ip.set_se_id
24. 0.000 0.000 ↓ 0.0

Sort (cost=62,375.44..62,375.44 rows=1 width=47) (actual rows= loops=)

  • Sort Key: ip.set_se_id
25. 0.000 0.000 ↓ 0.0

Nested Loop (cost=10,191.23..62,375.43 rows=1 width=47) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on se ip (cost=10,190.95..62,372.93 rows=1 width=8) (actual rows= loops=)

  • Recheck Cond: ((svc_id = ANY ('{666,667}'::integer[])) AND (set_se_id IS NOT NULL))
  • Filter: ((activated IS NOT NULL) AND (NOT deleted))
27. 0.000 0.000 ↓ 0.0

BitmapAnd (cost=10,190.95..10,190.95 rows=57,152 width=0) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on se_svc_id_idx (cost=0.00..4,974.49 rows=372,458 width=0) (actual rows= loops=)

  • Index Cond: (svc_id = ANY ('{666,667}'::integer[]))
29. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on se_set_se_id_idx (cost=0.00..5,216.20 rows=372,183 width=0) (actual rows= loops=)

  • Index Cond: (set_se_id IS NOT NULL)
30. 0.000 0.000 ↓ 0.0

Index Scan using tc_tc_id_pkey on tc ipt (cost=0.28..2.50 rows=1 width=47) (actual rows= loops=)

  • Index Cond: (tc_id = ip.tc_id)
31.          

CTE ktv

32. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=81,137.35..81,137.38 rows=1 width=68) (actual rows= loops=)

  • Group Key: tv.lc_num
33. 0.000 0.000 ↓ 0.0

Sort (cost=81,137.35..81,137.35 rows=1 width=95) (actual rows= loops=)

  • Sort Key: tv.lc_num
34. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.43..81,137.34 rows=1 width=95) (actual rows= loops=)

  • Join Filter: (tdis.se_id = tv.se_id)
35. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.43..80,522.37 rows=1 width=67) (actual rows= loops=)

  • Join Filter: (tvt.tc_id = tap.tc_id)
36. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.43..80,448.04 rows=1 width=59) (actual rows= loops=)

  • Join Filter: (tv.tc_id = tvt.tc_id)
37. 0.000 0.000 ↓ 0.0

Index Scan using se_svc_id_idx on se tv (cost=0.43..80,233.83 rows=1 width=12) (actual rows= loops=)

  • Index Cond: (svc_id = 516)
  • Filter: ((activated IS NOT NULL) AND (NOT deleted))
38. 0.000 0.000 ↓ 0.0

Seq Scan on tc tvt (cost=0.00..184.65 rows=2,365 width=47) (actual rows= loops=)

39. 0.000 0.000 ↓ 0.0

CTE Scan on tc_price tap (cost=0.00..45.74 rows=2,287 width=20) (actual rows= loops=)

40. 0.000 0.000 ↓ 0.0

CTE Scan on ntk_lc_discount tdis (cost=0.00..378.44 rows=18,922 width=36) (actual rows= loops=)

41.          

CTE av

42. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=62,008.74..62,008.77 rows=1 width=68) (actual rows= loops=)

  • Group Key: av.lc_num
43. 0.000 0.000 ↓ 0.0

Sort (cost=62,008.74..62,008.75 rows=1 width=95) (actual rows= loops=)

  • Sort Key: av.lc_num
44. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.43..62,008.73 rows=1 width=95) (actual rows= loops=)

  • Join Filter: (avdis.se_id = av.se_id)
45. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.43..61,393.77 rows=1 width=67) (actual rows= loops=)

  • Join Filter: (avt.tc_id = avap.tc_id)
46. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.43..61,319.44 rows=1 width=59) (actual rows= loops=)

  • Join Filter: (av.tc_id = avt.tc_id)
47. 0.000 0.000 ↓ 0.0

Index Scan using se_svc_id_idx on se av (cost=0.43..61,105.23 rows=1 width=12) (actual rows= loops=)

  • Index Cond: (svc_id = 514)
  • Filter: ((activated IS NOT NULL) AND (NOT deleted) AND (set_se_id IS NULL))
48. 0.000 0.000 ↓ 0.0

Seq Scan on tc avt (cost=0.00..184.65 rows=2,365 width=47) (actual rows= loops=)

49. 0.000 0.000 ↓ 0.0

CTE Scan on tc_price avap (cost=0.00..45.74 rows=2,287 width=20) (actual rows= loops=)

50. 0.000 0.000 ↓ 0.0

CTE Scan on ntk_lc_discount avdis (cost=0.00..378.44 rows=18,922 width=36) (actual rows= loops=)

51.          

CTE iptv

52. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=147,802.84..147,802.87 rows=1 width=68) (actual rows= loops=)

  • Group Key: ip_1.lc_num
53. 0.000 0.000 ↓ 0.0

Sort (cost=147,802.84..147,802.84 rows=1 width=95) (actual rows= loops=)

  • Sort Key: ip_1.lc_num
54. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1,000.71..147,802.83 rows=1 width=95) (actual rows= loops=)

  • Join Filter: (ipdis.se_id = ip_1.se_id)
55. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1,000.71..147,187.86 rows=1 width=67) (actual rows= loops=)

  • Join Filter: (ip_1.tc_id = ipt_1.tc_id)
56. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1,000.43..147,186.67 rows=3 width=32) (actual rows= loops=)

  • Join Filter: (ip_1.tc_id = ipap.tc_id)
57. 0.000 0.000 ↓ 0.0

Gather (cost=1,000.43..147,112.34 rows=1 width=12) (actual rows= loops=)

  • Workers Planned: 3
58. 0.000 0.000 ↓ 0.0

Parallel Index Scan using se_svc_id_idx on se ip_1 (cost=0.43..146,112.24 rows=1 width=12) (actual rows= loops=)

  • Index Cond: (svc_id = ANY ('{666,667}'::integer[]))
  • Filter: ((activated IS NOT NULL) AND (NOT deleted) AND (set_se_id IS NULL))
59. 0.000 0.000 ↓ 0.0

CTE Scan on tc_price ipap (cost=0.00..45.74 rows=2,287 width=20) (actual rows= loops=)

60. 0.000 0.000 ↓ 0.0

Index Scan using tc_tc_id_pkey on tc ipt_1 (cost=0.28..0.39 rows=1 width=47) (actual rows= loops=)

  • Index Cond: (tc_id = ipap.tc_id)
61. 0.000 0.000 ↓ 0.0

CTE Scan on ntk_lc_discount ipdis (cost=0.00..378.44 rows=18,922 width=36) (actual rows= loops=)

62.          

CTE total

63. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=181,434.89..181,437.57 rows=1 width=36) (actual rows= loops=)

  • Group Key: ip_2.lc_num
64. 0.000 0.000 ↓ 0.0

Sort (cost=181,434.89..181,434.90 rows=1 width=22) (actual rows= loops=)

  • Sort Key: ip_2.lc_num
65. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1,000.98..181,434.88 rows=1 width=22) (actual rows= loops=)

66. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1,000.56..181,431.49 rows=1 width=22) (actual rows= loops=)

  • Join Filter: (ip_2.tc_id = ipt_2.tc_id)
67. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1,000.28..181,431.09 rows=1 width=30) (actual rows= loops=)

68. 0.000 0.000 ↓ 0.0

Gather (cost=1,000.00..181,426.07 rows=1 width=22) (actual rows= loops=)

  • Workers Planned: 5
69. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on se ip_2 (cost=0.00..180,425.97 rows=1 width=22) (actual rows= loops=)

  • Filter: ((activated IS NOT NULL) AND (NOT deleted))
70. 0.000 0.000 ↓ 0.0

Index Scan using tc_id_idx on tc_price ipap_1 (cost=0.28..5.01 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (tc_id = ip_2.tc_id)
  • Filter: ((started_at = (SubPlan 16)) OR (finished_at IS NULL))
71.          

SubPlan (forIndex Scan)

72. 0.000 0.000 ↓ 0.0

Aggregate (cost=2.50..2.51 rows=1 width=8) (actual rows= loops=)

73. 0.000 0.000 ↓ 0.0

Index Scan using tc_id_idx on tc_price pr_1 (cost=0.28..2.50 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (tc_id = ipap_1.tc_id)
74. 0.000 0.000 ↓ 0.0

Index Only Scan using tc_tc_id_pkey on tc ipt_2 (cost=0.28..0.39 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (tc_id = ipap_1.tc_id)
75. 0.000 0.000 ↓ 0.0

Index Scan using ntk_lc_discount_se_id_idx on ntk_lc_discount ipdis_1 (cost=0.42..3.38 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (se_id = ip_2.se_id)
  • Filter: ((active_end IS NULL) OR (active_end > now()))
76.          

SubPlan (forGroupAggregate)

77. 0.000 0.000 ↓ 0.0

Index Scan using se_se_id_pkey on se (cost=0.43..2.65 rows=1 width=0) (actual rows= loops=)

  • Index Cond: (se_id = ip_2.parent_se_id)
  • Filter: (set_se_id IS NOT NULL)
78. 0.000 0.000 ↓ 0.0

Index Scan using se_set_se_id_idx on se se_1 (cost=0.43..108,282.39 rows=372,183 width=4) (actual rows= loops=)

  • Index Cond: (set_se_id IS NOT NULL)
79.          

CTE vl

80. 0.000 0.000 ↓ 0.0

Unique (cost=96,080.32..96,080.37 rows=1 width=485) (actual rows= loops=)

81. 0.000 0.000 ↓ 0.0

Sort (cost=96,080.32..96,080.33 rows=1 width=485) (actual rows= loops=)

  • Sort Key: se_2.lc_contract_ident, (COALESCE(CASE WHEN (s_1.flag = 1) THEN (s_1.speed)::numeric(18,2) WHEN (s_1.flag = 0) THEN ((s_1.speed)::numeric(18,2) / '1024'::numeric) ELSE NULL::numeric END, ((tbw.value / 1024))::numeric)), pack.title, tc_1.title, ip_3.tarif, ((price_1.price - COALESCE(dis_1.money_amount, '0'::numeric))), se_2.se_id, ((pap.price - COALESCE(pdis.money_amount, '0'::numeric))), ktv.tarif, ktv.ap_tv, av_1.tarif, av_1.ap_av, iptv.tarif, iptv.ap_iptv, tot.ap_total, (CASE WHEN (d.lc_num IS NOT NULL) THEN 'Добровольная блокировка'::character varying ELSE ns.name END), lc.saldo
82. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1,003.85..96,080.31 rows=1 width=485) (actual rows= loops=)

  • Join Filter: (tot.lc_num = se_2.lc_num)
83. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1,003.85..96,080.25 rows=1 width=421) (actual rows= loops=)

  • Join Filter: (iptv.lc_num = se_2.lc_num)
84. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1,003.85..96,080.21 rows=1 width=357) (actual rows= loops=)

  • Join Filter: (av_1.lc_num = se_2.lc_num)
85. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1,003.85..96,080.18 rows=1 width=293) (actual rows= loops=)

  • Join Filter: (ktv.lc_num = se_2.lc_num)
86. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1,003.85..96,080.15 rows=1 width=229) (actual rows= loops=)

  • Join Filter: (ip_3.set_se_id = se_2.set_se_id)
87. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1,003.85..96,080.12 rows=1 width=201) (actual rows= loops=)

88. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1,003.43..96,076.73 rows=1 width=197) (actual rows= loops=)

89. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1,003.15..96,073.88 rows=1 width=205) (actual rows= loops=)

90. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1,002.87..96,073.58 rows=1 width=166) (actual rows= loops=)

91. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1,002.44..96,070.93 rows=1 width=162) (actual rows= loops=)

92. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1,002.16..96,067.92 rows=1 width=158) (actual rows= loops=)

  • Join Filter: (s_1.tc_id = se_2.tc_id)
93. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1,002.16..96,055.05 rows=1 width=122) (actual rows= loops=)

  • Join Filter: (d.lc_num = se_2.lc_num)
94. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1,002.16..94,711.85 rows=1 width=118) (actual rows= loops=)

95. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1,001.89..94,711.56 rows=1 width=88) (actual rows= loops=)

  • Join Filter: (acs_1.account_id = se_2.lc_num)
96. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1,001.89..91,972.88 rows=1 width=86) (actual rows= loops=)

97. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1,001.47..91,969.49 rows=1 width=82) (actual rows= loops=)

98. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1,001.19..91,966.65 rows=1 width=74) (actual rows= loops=)

99. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1,000.91..91,964.15 rows=1 width=27) (actual rows= loops=)

100. 0.000 0.000 ↓ 0.0

Gather Merge (cost=1,000.48..91,961.50 rows=1 width=22) (actual rows= loops=)

  • Workers Planned: 4
101. 0.000 0.000 ↓ 0.0

Parallel Index Scan using se_se_id_svc_id_20 on se se_2 (cost=0.42..90,961.33 rows=1 width=22) (actual rows= loops=)

  • Filter: ((NOT lc_company) AND (NOT deleted) AND (activated IS NOT NULL))
102. 0.000 0.000 ↓ 0.0

Index Scan using lc_lc_num_pkey on lc (cost=0.42..2.64 rows=1 width=9) (actual rows= loops=)

  • Index Cond: (lc_num = se_2.lc_num)
  • Filter: (commercial_type IS NULL)
103. 0.000 0.000 ↓ 0.0

Index Scan using tc_tc_id_pkey on tc tc_1 (cost=0.28..2.50 rows=1 width=47) (actual rows= loops=)

  • Index Cond: (tc_id = se_2.tc_id)
104. 0.000 0.000 ↓ 0.0

Index Scan using tc_id_idx on tc_price price_1 (cost=0.28..2.84 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (tc_id = tc_1.tc_id)
  • Filter: ((started_at = (SubPlan 18)) OR (finished_at IS NULL))
105.          

SubPlan (forIndex Scan)

106. 0.000 0.000 ↓ 0.0

Aggregate (cost=2.50..2.51 rows=1 width=8) (actual rows= loops=)

107. 0.000 0.000 ↓ 0.0

Index Scan using tc_id_idx on tc_price pr_2 (cost=0.28..2.50 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (tc_id = price_1.tc_id)
108. 0.000 0.000 ↓ 0.0

Index Scan using ntk_lc_discount_se_id_idx on ntk_lc_discount dis_1 (cost=0.42..3.38 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (se_id = se_2.se_id)
  • Filter: ((active_end IS NULL) OR (active_end > now()))
109. 0.000 0.000 ↓ 0.0

CTE Scan on acs acs_1 (cost=0.00..1,685.34 rows=84,267 width=6) (actual rows= loops=)

110. 0.000 0.000 ↓ 0.0

Index Scan using ntk_states_pkey on ntk_states ns (cost=0.28..0.29 rows=1 width=36) (actual rows= loops=)

  • Index Cond: (state_id = acs_1.state_id)
111. 0.000 0.000 ↓ 0.0

CTE Scan on dobr d (cost=0.00..826.58 rows=41,329 width=4) (actual rows= loops=)

112. 0.000 0.000 ↓ 0.0

CTE Scan on speed s_1 (cost=0.00..7.92 rows=396 width=40) (actual rows= loops=)

113. 0.000 0.000 ↓ 0.0

Index Scan using tc_bw_tc_id_is_holiday_zone_id_idx on tc_bw tbw (cost=0.28..3.00 rows=2 width=8) (actual rows= loops=)

  • Index Cond: ((tc_id = se_2.tc_id) AND (is_holiday = 0))
114. 0.000 0.000 ↓ 0.0

Index Scan using se_se_id_pkey on se pa (cost=0.43..2.65 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (se_id = se_2.set_se_id)
115. 0.000 0.000 ↓ 0.0

Index Scan using tc_tc_id_pkey on tc pack (cost=0.28..0.30 rows=1 width=47) (actual rows= loops=)

  • Index Cond: (tc_id = pa.tc_id)
116. 0.000 0.000 ↓ 0.0

Index Scan using tc_id_idx on tc_price pap (cost=0.28..2.84 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (tc_id = tc_1.tc_id)
  • Filter: ((started_at = (SubPlan 19)) OR (finished_at IS NULL))
117.          

SubPlan (forIndex Scan)

118. 0.000 0.000 ↓ 0.0

Aggregate (cost=2.50..2.51 rows=1 width=8) (actual rows= loops=)

119. 0.000 0.000 ↓ 0.0

Index Scan using tc_id_idx on tc_price pr_3 (cost=0.28..2.50 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (tc_id = pap.tc_id)
120. 0.000 0.000 ↓ 0.0

Index Scan using ntk_lc_discount_se_id_idx on ntk_lc_discount pdis (cost=0.42..3.38 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (se_id = se_2.se_id)
  • Filter: ((active_end IS NULL) OR (active_end > now()))
121. 0.000 0.000 ↓ 0.0

CTE Scan on iptv_pack ip_3 (cost=0.00..0.02 rows=1 width=36) (actual rows= loops=)

122. 0.000 0.000 ↓ 0.0

CTE Scan on ktv (cost=0.00..0.02 rows=1 width=68) (actual rows= loops=)

123. 0.000 0.000 ↓ 0.0

CTE Scan on av av_1 (cost=0.00..0.02 rows=1 width=68) (actual rows= loops=)

124. 0.000 0.000 ↓ 0.0

CTE Scan on iptv (cost=0.00..0.02 rows=1 width=68) (actual rows= loops=)

125. 0.000 0.000 ↓ 0.0

CTE Scan on total tot (cost=0.00..0.02 rows=1 width=36) (actual rows= loops=)