API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Jul 12th, 2019
239
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 34.59 KB | None | 0 0
raw download clone embed print report
  1. QUERY PLAN
  2. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  3. WindowAgg (cost=248.25..263.39 rows=4 width=228) (actual time=39.176..39.176 rows=1 loops=1)
  4. Output: sum(t.group_suma) OVER (?), t.group_cost, t.group_suma, t.consumed, t.consumed_period, t.o, ((t.o).id)
  5. Buffers: shared hit=1071
  6. -> Subquery Scan on t (cost=248.25..263.33 rows=4 width=220) (actual time=39.165..39.166 rows=1 loops=1)
  7. Output: (t.o).id, t.group_suma, t.group_cost, t.consumed, t.consumed_period, t.o
  8. Buffers: shared hit=1071
  9. -> GroupAggregate (cost=248.25..263.29 rows=4 width=216) (actual time=39.163..39.164 rows=1 loops=1)
  10. Output: sum(cis.item_cost), sum(cis.item_suma), max(ic.consumed), ic.consumed_period, o.*
  11. Group Key: o.*, ic.consumed_period
  12. Buffers: shared hit=1071
  13. -> Sort (cost=248.25..250.75 rows=1000 width=216) (actual time=39.100..39.101 rows=5 loops=1)
  14. Output: ic.consumed_period, o.*, cis.item_cost, cis.item_suma, ic.consumed
  15. Sort Key: o.*, ic.consumed_period
  16. Sort Method: quicksort Memory: 27kB
  17. Buffers: shared hit=1062
  18. -> Nested Loop Left Join (cost=36.56..198.42 rows=1000 width=216) (actual time=4.184..39.024 rows=5 loops=1)
  19. Output: ic.consumed_period, o.*, cis.item_cost, cis.item_suma, ic.consumed
  20. Buffers: shared hit=1055
  21. -> Nested Loop (cost=36.31..178.17 rows=1 width=417) (actual time=3.677..16.561 rows=5 loops=1)
  22. Output: o.*, od.*, sp.*, ic.consumed, ic.consumed_period, ic.*
  23. Buffers: shared hit=545
  24. -> Nested Loop Left Join (cost=35.22..168.92 rows=1 width=388) (actual time=2.758..14.964 rows=20 loops=1)
  25. Output: o.*, o.app_period, split_period.split_period, od.app_period, od.*, sp.*
  26. Join Filter: (od.service_type_id = sp.service_type_id)
  27. Rows Removed by Join Filter: 1280
  28. Buffers: shared hit=370
  29. -> Nested Loop (cost=34.97..156.11 rows=1 width=368) (actual time=0.808..1.374 rows=20 loops=1)
  30. Output: o.*, o.service_level_id, o.app_period, o.period_id, split_period.split_period, od.app_period, od.*, od.service_type_id
  31. Join Filter: ((od.app_period * o.app_period) && split_period.split_period)
  32. Rows Removed by Join Filter: 12
  33. Buffers: shared hit=79
  34. -> Nested Loop (cost=34.69..141.49 rows=1 width=226) (actual time=0.767..0.789 rows=4 loops=1)
  35. Output: o.*, o.id, o.service_level_id, o.app_period, o.period_id, split_period.split_period
  36. Buffers: shared hit=43
  37. -> Bitmap Heap Scan on public.order_bt o (cost=4.44..18.76 rows=4 width=194) (actual time=0.103..0.112 rows=3 loops=1)
  38. Output: o.*, o.id, o.service_level_id, o.app_period, o.period_id
  39. Recheck Cond: ((o.id = 6154) AND (o.sys_period @> sys_time()))
  40. Heap Blocks: exact=2
  41. Buffers: shared hit=4
  42. -> Bitmap Index Scan on order_id_sys_period_app_period_excl (cost=0.00..4.44 rows=4 width=0) (actual time=0.077..0.077 rows=3 loops=1)
  43. Index Cond: ((o.id = 6154) AND (o.sys_period @> sys_time()))
  44. Buffers: shared hit=2
  45. -> Subquery Scan on split_period (cost=30.25..30.67 rows=1 width=32) (actual time=0.217..0.223 rows=1 loops=3)
  46. Output: split_period.split_period
  47. Filter: ((o.app_period * '["2019-04-01 00:00:00+03","2019-05-01 00:00:00+03")'::tstzrange) && split_period.split_period)
  48. Rows Removed by Filter: 3
  49. Buffers: shared hit=39
  50. -> Subquery Scan on x (cost=30.25..30.55 rows=8 width=32) (actual time=0.210..0.219 rows=4 loops=3)
  51. Output: x.app_period
  52. Filter: (x.boundary IS NOT NULL)
  53. Rows Removed by Filter: 1
  54. Buffers: shared hit=39
  55. CTE ranges
  56. -> HashAggregate (cost=29.69..29.73 rows=4 width=32) (actual time=0.176..0.177 rows=5 loops=3)
  57. Output: ((o_1.app_period * '["2019-04-01 00:00:00+03","2019-05-01 00:00:00+03")'::tstzrange))
  58. Group Key: ((o_1.app_period * '["2019-04-01 00:00:00+03","2019-05-01 00:00:00+03")'::tstzrange))
  59. Buffers: shared hit=36
  60. -> Append (cost=4.42..29.68 rows=4 width=32) (actual time=0.043..0.166 rows=9 loops=3)
  61. Buffers: shared hit=36
  62. -> Bitmap Heap Scan on public.order_bt o_1 (cost=4.42..12.00 rows=2 width=32) (actual time=0.040..0.040 rows=1 loops=3)
  63. Output: (o_1.app_period * '["2019-04-01 00:00:00+03","2019-05-01 00:00:00+03")'::tstzrange)
  64. Recheck Cond: ((o_1.id = $0) AND (o_1.sys_period @> sys_time()) AND (o_1.app_period && '["2019-04-01 00:00:00+03","2019-05-01 00:00:00+03")'::tstzrange))
  65. Heap Blocks: exact=3
  66. Buffers: shared hit=9
  67. -> Bitmap Index Scan on order_id_sys_period_app_period_excl (cost=0.00..4.42 rows=2 width=0) (actual time=0.014..0.014 rows=1 loops=3)
  68. Index Cond: ((o_1.id = $0) AND (o_1.sys_period @> sys_time()) AND (o_1.app_period && '["2019-04-01 00:00:00+03","2019-05-01 00:00:00+03")'::tstzrange))
  69. Buffers: shared hit=6
  70. -> Index Scan using order_detail_idx_order_id on public.order_detail_bt od_1 (cost=0.29..17.63 rows=2 width=32) (actual time=0.036..0.120 rows=8 loops=3)
  71. Output: (od_1.app_period * '["2019-04-01 00:00:00+03","2019-05-01 00:00:00+03")'::tstzrange)
  72. Index Cond: (od_1.order_id = $0)
  73. Filter: ((od_1.app_period && '["2019-04-01 00:00:00+03","2019-05-01 00:00:00+03")'::tstzrange) AND (od_1.sys_period @> sys_time()))
  74. Rows Removed by Filter: 3
  75. Buffers: shared hit=27
  76. -> WindowAgg (cost=0.52..0.74 rows=8 width=40) (actual time=0.209..0.217 rows=5 loops=3)
  77. Output: tstzrange((lower(ranges.app_period)), lead((lower(ranges.app_period))) OVER (?)), lead((lower(ranges.app_period))) OVER (?)
  78. Buffers: shared hit=39
  79. -> Sort (cost=0.52..0.54 rows=8 width=8) (actual time=0.200..0.201 rows=5 loops=3)
  80. Output: (lower(ranges.app_period))
  81. Sort Key: (lower(ranges.app_period))
  82. Sort Method: quicksort Memory: 25kB
  83. Buffers: shared hit=39
  84. -> HashAggregate (cost=0.32..0.40 rows=8 width=8) (actual time=0.189..0.190 rows=5 loops=3)
  85. Output: (lower(ranges.app_period))
  86. Group Key: (lower(ranges.app_period))
  87. Buffers: shared hit=36
  88. -> Append (cost=0.00..0.30 rows=8 width=8) (actual time=0.179..0.185 rows=10 loops=3)
  89. Buffers: shared hit=36
  90. -> CTE Scan on ranges (cost=0.00..0.09 rows=4 width=8) (actual time=0.178..0.181 rows=5 loops=3)
  91. Output: lower(ranges.app_period)
  92. Buffers: shared hit=36
  93. -> CTE Scan on ranges ranges_1 (cost=0.00..0.09 rows=4 width=8) (actual time=0.001..0.002 rows=5 loops=3)
  94. Output: upper(ranges_1.app_period)
  95. -> Index Scan using order_detail_idx_order_id on public.order_detail_bt od (cost=0.29..14.59 rows=2 width=150) (actual time=0.026..0.136 rows=8 loops=4)
  96. Output: od.app_period, od.*, od.order_id, od.service_type_id
  97. Index Cond: (od.order_id = 6154)
  98. Filter: (od.sys_period @> sys_time())
  99. Rows Removed by Filter: 3
  100. Buffers: shared hit=36
  101. -> Function Scan on public.service_level_price sp (cost=0.25..12.75 rows=5 width=40) (actual time=0.636..0.669 rows=65 loops=20)
  102. Output: sp.*, sp.service_type_id, sp.period_id
  103. Function Call: service_level_price(o.service_level_id)
  104. Filter: (o.period_id = sp.period_id)
  105. Rows Removed by Filter: 38
  106. Buffers: shared hit=291
  107. -> Subquery Scan on ic (cost=1.09..9.24 rows=1 width=104) (actual time=0.078..0.079 rows=0 loops=20)
  108. Output: ic.consumed, ic.consumed_period, ic.*
  109. Buffers: shared hit=175
  110. -> Result (cost=1.09..9.23 rows=1 width=72) (actual time=0.077..0.077 rows=0 loops=20)
  111. Output: ROW(cr.id, cr.rate, cr.docdate, cr.from_currency_id, cr.to_currency_id, cr.app_period)::currency_rate, interval_length(((od.app_period * o.app_period) * split_period.split_period), $3), COALESCE(((od.app_period * o.app_period) * split_period.split_period), ((od.app_period * o.app_period) * split_period.split_period))
  112. One-Time Filter: (COALESCE(((od.app_period * o.app_period) * split_period.split_period), ((od.app_period * o.app_period) * split_period.split_period)) @> '2019-04-01 00:00:00+03'::timestamp with time zone)
  113. Buffers: shared hit=175
  114. InitPlan 2 (returns $3)
  115. -> Seq Scan on public.period (cost=0.00..1.07 rows=1 width=16) (actual time=0.005..0.007 rows=1 loops=5)
  116. Output: period."interval"
  117. Filter: (period.id = ($2).period_id)
  118. Rows Removed by Filter: 5
  119. Buffers: shared hit=5
  120. -> Nested Loop Left Join (cost=0.01..7.89 rows=1 width=56) (actual time=0.062..0.066 rows=1 loops=5)
  121. Output: cr.id, cr.rate, cr.docdate, cr.from_currency_id, cr.to_currency_id, cr.app_period
  122. Buffers: shared hit=5
  123. -> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=5)
  124. -> Seq Scan on public.currency_rate_bt cr (cost=0.00..7.86 rows=1 width=56) (actual time=0.059..0.062 rows=1 loops=5)
  125. Output: cr.id, cr.rate, cr.docdate, cr.from_currency_id, cr.to_currency_id, cr.sid, cr.app_period, cr.sys_period
  126. Filter: ((cr.from_currency_id = (sp.*).currency_id) AND (cr.app_period @> lower(((od.app_period * o.app_period) * split_period.split_period))) AND (cr.sys_period @> sys_time()) AND (cr.to_currency_id = app_currency()))
  127. Rows Removed by Filter: 12
  128. Buffers: shared hit=5
  129. -> Function Scan on public.calc_item_suma cis (cost=0.25..10.25 rows=1000 width=16) (actual time=4.489..4.489 rows=1 loops=5)
  130. Output: cis.item_qty, cis.item_price, cis.item_cost, cis.item_suma
  131. Function Call: calc_item_suma(o.*, od.*, sp.*, ic.*)
  132. Buffers: shared hit=510
  133. Planning Time: 10.940 ms
  134. Execution Time: 40.260 ms
  135. (132 rows)
  136.  
  137. QUERY PLAN
  138. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  139. Subquery Scan on ots (cost=86089.99..86096.49 rows=1 width=224) (actual time=2650.454..2651.194 rows=1 loops=1)
  140. Output: ots.total_suma, ots.group_cost, ots.group_suma, ots.consumed, ots.consumed_period, ots.o
  141. Filter: ((ots.consumed_period @> '2019-04-01 00:00:00+03'::timestamp with time zone) AND ((ots.o).id = 6154))
  142. Rows Removed by Filter: 877
  143. Buffers: shared hit=50838
  144. -> WindowAgg (cost=86089.99..86093.49 rows=200 width=228) (actual time=2650.185..2650.987 rows=878 loops=1)
  145. Output: sum(t.group_suma) OVER (?), t.group_cost, t.group_suma, t.consumed, t.consumed_period, t.o, ((t.o).id)
  146. Buffers: shared hit=50838
  147. -> Sort (cost=86089.99..86090.49 rows=200 width=220) (actual time=2650.174..2650.239 rows=878 loops=1)
  148. Output: ((t.o).id), t.group_suma, t.group_cost, t.consumed, t.consumed_period, t.o
  149. Sort Key: ((t.o).id)
  150. Sort Method: quicksort Memory: 272kB
  151. Buffers: shared hit=50838
  152. -> Subquery Scan on t (cost=86063.34..86082.34 rows=200 width=220) (actual time=2645.389..2649.952 rows=878 loops=1)
  153. Output: (t.o).id, t.group_suma, t.group_cost, t.consumed, t.consumed_period, t.o
  154. Buffers: shared hit=50838
  155. -> GroupAggregate (cost=86063.34..86080.34 rows=200 width=216) (actual time=2645.387..2649.751 rows=878 loops=1)
  156. Output: sum(cis.item_cost), sum(cis.item_suma), max(ic.consumed), ic.consumed_period, o.*
  157. Group Key: o.*, ic.consumed_period
  158. Buffers: shared hit=50838
  159. -> Sort (cost=86063.34..86065.84 rows=1000 width=216) (actual time=2645.353..2645.563 rows=2884 loops=1)
  160. Output: ic.consumed_period, o.*, cis.item_cost, cis.item_suma, ic.consumed
  161. Sort Key: o.*, ic.consumed_period
  162. Sort Method: quicksort Memory: 898kB
  163. Buffers: shared hit=50838
  164. -> Nested Loop Left Join (cost=32.51..86013.51 rows=1000 width=216) (actual time=1.756..2625.800 rows=2884 loops=1)
  165. Output: ic.consumed_period, o.*, cis.item_cost, cis.item_suma, ic.consumed
  166. Buffers: shared hit=50838
  167. -> Nested Loop Left Join (cost=32.26..85993.26 rows=1 width=417) (actual time=1.675..2330.464 rows=2884 loops=1)
  168. Output: o.*, od.*, sp.*, ic.consumed, ic.consumed_period, ic.*
  169. Buffers: shared hit=47880
  170. -> Nested Loop Left Join (cost=31.18..85984.03 rows=1 width=388) (actual time=1.520..2082.150 rows=2884 loops=1)
  171. Output: o.*, o.app_period, split_period.split_period, od.app_period, od.*, sp.*
  172. Join Filter: (od.service_type_id = sp.service_type_id)
  173. Rows Removed by Join Filter: 164206
  174. Buffers: shared hit=42112
  175. -> Nested Loop (cost=30.93..85971.21 rows=1 width=368) (actual time=0.354..363.420 rows=2882 loops=1)
  176. Output: o.*, o.service_level_id, o.app_period, o.period_id, split_period.split_period, od.app_period, od.*, od.service_type_id
  177. Join Filter: ((od.app_period * o.app_period) && split_period.split_period)
  178. Rows Removed by Join Filter: 970
  179. Buffers: shared hit=30650
  180. -> Nested Loop (cost=30.65..85791.13 rows=111 width=226) (actual time=0.308..283.564 rows=1005 loops=1)
  181. Output: o.*, o.id, o.service_level_id, o.app_period, o.period_id, split_period.split_period
  182. Buffers: shared hit=24861
  183. -> Index Scan using order_id_sys_period_app_period_excl on public.order_bt o (cost=0.40..497.05 rows=2780 width=194) (actual time=0.108..8.185 rows=2775 loops=1)
  184. Output: o.*, o.id, o.service_level_id, o.app_period, o.period_id
  185. Index Cond: (o.sys_period @> sys_time())
  186. Buffers: shared hit=807
  187. -> Subquery Scan on split_period (cost=30.25..30.67 rows=1 width=32) (actual time=0.097..0.098 rows=0 loops=2775)
  188. Output: split_period.split_period
  189. Filter: ((o.app_period * '["2019-04-01 00:00:00+03","2019-05-01 00:00:00+03")'::tstzrange) && split_period.split_period)
  190. Rows Removed by Filter: 1
  191. Buffers: shared hit=24054
  192. -> Subquery Scan on x (cost=30.25..30.55 rows=8 width=32) (actual time=0.095..0.097 rows=1 loops=2775)
  193. Output: x.app_period
  194. Filter: (x.boundary IS NOT NULL)
  195. Rows Removed by Filter: 1
  196. Buffers: shared hit=24054
  197. CTE ranges
  198. -> HashAggregate (cost=29.69..29.73 rows=4 width=32) (actual time=0.082..0.082 rows=1 loops=2775)
  199. Output: ((o_1.app_period * '["2019-04-01 00:00:00+03","2019-05-01 00:00:00+03")'::tstzrange))
  200. Group Key: ((o_1.app_period * '["2019-04-01 00:00:00+03","2019-05-01 00:00:00+03")'::tstzrange))
  201. Buffers: shared hit=24054
  202. -> Append (cost=4.42..29.68 rows=4 width=32) (actual time=0.030..0.077 rows=4 loops=2775)
  203. Buffers: shared hit=24054
  204. -> Bitmap Heap Scan on public.order_bt o_1 (cost=4.42..12.00 rows=2 width=32) (actual time=0.027..0.027 rows=1 loops=2775)
  205. Output: (o_1.app_period * '["2019-04-01 00:00:00+03","2019-05-01 00:00:00+03")'::tstzrange)
  206. Recheck Cond: ((o_1.id = $0) AND (o_1.sys_period @> sys_time()) AND (o_1.app_period && '["2019-04-01 00:00:00+03","2019-05-01 00:00:00+03")'::tstzrange))
  207. Heap Blocks: exact=2775
  208. Buffers: shared hit=8442
  209. -> Bitmap Index Scan on order_id_sys_period_app_period_excl (cost=0.00..4.42 rows=2 width=0) (actual time=0.012..0.012 rows=1 loops=2775)
  210. Index Cond: ((o_1.id = $0) AND (o_1.sys_period @> sys_time()) AND (o_1.app_period && '["2019-04-01 00:00:00+03","2019-05-01 00:00:00+03")'::tstzrange))
  211. Buffers: shared hit=5667
  212. -> Index Scan using order_detail_idx_order_id on public.order_detail_bt od_1 (cost=0.29..17.63 rows=2 width=32) (actual time=0.019..0.045 rows=3 loops=2775)
  213. Output: (od_1.app_period * '["2019-04-01 00:00:00+03","2019-05-01 00:00:00+03")'::tstzrange)
  214. Index Cond: (od_1.order_id = $0)
  215. Filter: ((od_1.app_period && '["2019-04-01 00:00:00+03","2019-05-01 00:00:00+03")'::tstzrange) AND (od_1.sys_period @> sys_time()))
  216. Rows Removed by Filter: 4
  217. Buffers: shared hit=15612
  218. -> WindowAgg (cost=0.52..0.74 rows=8 width=40) (actual time=0.094..0.096 rows=2 loops=2775)
  219. Output: tstzrange((lower(ranges.app_period)), lead((lower(ranges.app_period))) OVER (?)), lead((lower(ranges.app_period))) OVER (?)
  220. Buffers: shared hit=24054
  221. -> Sort (cost=0.52..0.54 rows=8 width=8) (actual time=0.091..0.091 rows=2 loops=2775)
  222. Output: (lower(ranges.app_period))
  223. Sort Key: (lower(ranges.app_period))
  224. Sort Method: quicksort Memory: 25kB
  225. Buffers: shared hit=24054
  226. -> HashAggregate (cost=0.32..0.40 rows=8 width=8) (actual time=0.087..0.087 rows=2 loops=2775)
  227. Output: (lower(ranges.app_period))
  228. Group Key: (lower(ranges.app_period))
  229. Buffers: shared hit=24054
  230. -> Append (cost=0.00..0.30 rows=8 width=8) (actual time=0.084..0.085 rows=2 loops=2775)
  231. Buffers: shared hit=24054
  232. -> CTE Scan on ranges (cost=0.00..0.09 rows=4 width=8) (actual time=0.083..0.084 rows=1 loops=2775)
  233. Output: lower(ranges.app_period)
  234. Buffers: shared hit=24054
  235. -> CTE Scan on ranges ranges_1 (cost=0.00..0.09 rows=4 width=8) (actual time=0.000..0.001 rows=1 loops=2775)
  236. Output: upper(ranges_1.app_period)
  237. -> Index Scan using order_detail_idx_order_id on public.order_detail_bt od (cost=0.29..1.59 rows=2 width=150) (actual time=0.021..0.073 rows=4 loops=1005)
  238. Output: od.app_period, od.*, od.order_id, od.service_type_id
  239. Index Cond: (od.order_id = o.id)
  240. Filter: (od.sys_period @> sys_time())
  241. Rows Removed by Filter: 4
  242. Buffers: shared hit=5789
  243. -> Function Scan on public.service_level_price sp (cost=0.25..12.75 rows=5 width=40) (actual time=0.556..0.587 rows=58 loops=2882)
  244. Output: sp.*, sp.service_type_id, sp.period_id
  245. Function Call: service_level_price(o.service_level_id)
  246. Filter: (o.period_id = sp.period_id)
  247. Rows Removed by Filter: 43
  248. Buffers: shared hit=11462
  249. -> Subquery Scan on ic (cost=1.07..9.23 rows=1 width=104) (actual time=0.083..0.085 rows=1 loops=2884)
  250. Output: ic.consumed, ic.consumed_period, ic.*
  251. Buffers: shared hit=5768
  252. -> Nested Loop Left Join (cost=1.07..9.22 rows=1 width=72) (actual time=0.081..0.083 rows=1 loops=2884)
  253. Output: ROW(cr.id, cr.rate, cr.docdate, cr.from_currency_id, cr.to_currency_id, cr.app_period)::currency_rate, interval_length(((od.app_period * o.app_period) * split_period.split_period), $3), COALESCE(((od.app_period * o.app_period) * split_period.split_period), ((od.app_period * o.app_period) * split_period.split_period))
  254. Buffers: shared hit=5768
  255. InitPlan 2 (returns $3)
  256. -> Seq Scan on public.period (cost=0.00..1.07 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=2884)
  257. Output: period."interval"
  258. Filter: (period.id = ($2).period_id)
  259. Rows Removed by Filter: 5
  260. Buffers: shared hit=2884
  261. -> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=2884)
  262. -> Seq Scan on public.currency_rate_bt cr (cost=0.00..7.86 rows=1 width=56) (actual time=0.036..0.037 rows=1 loops=2884)
  263. Output: cr.id, cr.rate, cr.docdate, cr.from_currency_id, cr.to_currency_id, cr.sid, cr.app_period, cr.sys_period
  264. Filter: ((cr.from_currency_id = (sp.*).currency_id) AND (cr.app_period @> lower(((od.app_period * o.app_period) * split_period.split_period))) AND (cr.sys_period @> sys_time()) AND (cr.to_currency_id = app_currency()))
  265. Rows Removed by Filter: 12
  266. Buffers: shared hit=2884
  267. -> Function Scan on public.calc_item_suma cis (cost=0.25..10.25 rows=1000 width=16) (actual time=0.100..0.100 rows=1 loops=2884)
  268. Output: cis.item_qty, cis.item_price, cis.item_cost, cis.item_suma
  269. Function Call: calc_item_suma(o.*, od.*, sp.*, ic.*)
  270. Buffers: shared hit=2958
  271. Planning Time: 4.262 ms
  272. Execution Time: 2651.775 ms
  273. (134 rows)
  274.  
  275.  
  276.  
  277. CREATE FUNCTION "order_cost_details" (in _target_range tstzrange default app_period())
  278. RETURNS table(
  279. item_qty double precision,
  280. item_price double precision,
  281. item_cost double precision,
  282. item_suma double precision,
  283. o order_bt, c order_detail_bt, p price, ic consume_info
  284. )
  285.  
  286. LANGUAGE sql
  287. STABLE
  288. AS $$
  289. SELECT cis.*, o, od, sp, ic
  290. FROM "order_bt" o
  291. LEFT JOIN LATERAL order_item_intervals( o.id, _target_range ) split_period ON TRUE
  292. INNER JOIN order_detail_bt od ON od.order_id = o.id
  293. AND od.sys_period @> sys_time() AND (od.app_period * o.app_period) && split_period
  294. LEFT JOIN LATERAL service_level_price( o.service_level_id ) sp
  295. USING( service_type_id, period_id )
  296. LEFT JOIN LATERAL consume_info( sp, od.app_period * o.app_period * split_period ) ic ON true
  297. LEFT JOIN LATERAL calc_item_suma( o, od, sp, ic ) cis on TRUE
  298. WHERE o.sys_period @> sys_time()
  299. AND (o.app_period * _target_range)
  300. && split_period
  301. $$
  302. ;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!