API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Jul 12th, 2019
258
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 76.29 KB | None | 0 0
raw download clone embed print report
  1. tucha=> \out plans
  2. tucha=> EXPLAIN ( ANALYZE, COSTS, VERBOSE, BUFFERS, format text )
  3. tucha-> SELECT
  4. tucha-> --next_ots.group_cost AS next_cost,
  5. tucha-> (SELECT next_ots FROM order_total_suma( next_range ) next_ots
  6. tucha(> WHERE next_ots.order_id = ots.order_id AND next_ots.consumed_period @> (ots.o).billed_to
  7. tucha(> ) AS next_suma,
  8. tucha-> ots.* FROM (
  9. tucha(> SELECT
  10. tucha(> tstzrange(
  11. tucha(> NULLIF( (ots.o).billed_to, 'infinity' ),
  12. tucha(> NULLIF( (ots.o).billed_to +p.interval, 'infinity' )
  13. tucha(> ) as next_range,
  14. tucha(> ots.*
  15. tucha(> FROM order_total_suma() ots
  16. tucha(> LEFT JOIN period p ON p.id = (ots.o).period_id
  17. tucha(> ) ots
  18. tucha-> --LEFT JOIN order_total_suma( next_range ) next_ots ON next_ots.order_id = 6154 --<< this is fine
  19. tucha-> -- AND next_ots.consumed_period @> (ots.o).billed_to
  20. tucha-> --LEFT JOIN order_total_suma( next_range ) next_ots ON next_ots.order_id = ots.order_id --<< this takes 11500ms
  21. tucha-> -- AND next_ots.consumed_period @> (ots.o).billed_to
  22. tucha-> WHERE ots.order_id IN ( 6154, 10805 )
  23. tucha-> ;
  24. tucha=> EXPLAIN ( ANALYZE, COSTS, VERBOSE, BUFFERS, format text )
  25. tucha-> SELECT
  26. to
  27. --) AS next_suma,
  28. ots.* FROM (
  29. SELECT
  30. tstzrange(
  31. NULLIF( (ots.o).billed_to, 'infinity' ),
  32. NULLIF( (ots.tucha-> --next_ots.group_cost AS next_cost,
  33. tucha-> --(SELECT next_ots FROM order_total_suma( next_range ) next_ots
  34. tucha-> --WHERE next_ots.order_id = ots.order_id AND next_ots.consumed_period @> (ots.o).billed_to
  35. tucha-> --) AS next_suma,
  36. tucha-> ots.* FROM (
  37. tucha(> SELECT
  38. tucha(> tstzrange(
  39. tucha(> NULLIF( (ots.o).billed_to, 'infinity' ),
  40. tucha(> NULLIF( (ots.o).billed_to +p.interval, 'infinity' )
  41. tucha(> ) as next_range,
  42. tucha(> ots.*
  43. tucha(> FROM order_total_suma() ots
  44. tucha(> LEFT JOIN period p ON p.id = (ots.o).period_id
  45. tucha(> ) ots
  46. tucha-> --LEFT JOIN order_total_suma( next_range ) next_ots ON next_ots.order_id = 6154 --<< this is fine
  47. tucha-> -- AND next_ots.consumed_period @> (ots.o).billed_to
  48. tucha-> LEFT JOIN order_total_suma( next_range ) next_ots ON next_ots.order_id = ots.order_id --<< this takes 11500ms
  49. tucha-> AND next_ots.consumed_period @> (ots.o).billed_to
  50. tucha-> WHERE ots.order_id IN ( 6154, 10805 )
  51. tucha-> ;
  52.  
  53.  
  54.  
  55.  
  56. QUERY PLAN
  57. --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  58. Hash Left Join (cost=369.35..1639.06 rows=6 width=292) (actual time=121.898..135.833 rows=2 loops=1)
  59. Output: (SubPlan 4), tstzrange(NULLIF((t.o).billed_to, 'infinity'::timestamp with time zone), NULLIF(((t.o).billed_to + p."interval"), 'infinity'::timestamp with time zone)), (sum(t.group_suma) OVER (?)), t.group_cost, t.group_suma, t.consumed, t.consumed_period, t.o, t.order_id
  60. Inner Unique: true
  61. Hash Cond: ((t.o).period_id = p.id)
  62. Buffers: shared hit=3999
  63. -> WindowAgg (cost=368.21..368.32 rows=6 width=228) (actual time=31.797..31.802 rows=2 loops=1)
  64. Output: sum(t.group_suma) OVER (?), t.group_cost, t.group_suma, t.consumed, t.consumed_period, t.o, t.order_id
  65. Buffers: shared hit=759
  66. -> Sort (cost=368.21..368.23 rows=6 width=220) (actual time=31.790..31.791 rows=2 loops=1)
  67. Output: t.order_id, t.group_suma, t.group_cost, t.consumed, t.consumed_period, t.o
  68. Sort Key: t.order_id
  69. Sort Method: quicksort Memory: 25kB
  70. Buffers: shared hit=759
  71. -> Subquery Scan on t (cost=350.52..368.14 rows=6 width=220) (actual time=31.768..31.776 rows=2 loops=1)
  72. Output: t.order_id, t.group_suma, t.group_cost, t.consumed, t.consumed_period, t.o
  73. Buffers: shared hit=759
  74. -> GroupAggregate (cost=350.52..368.08 rows=6 width=220) (actual time=31.766..31.774 rows=2 loops=1)
  75. Output: sum(cis.item_cost), sum(cis.item_suma), max(ic.consumed), ic.consumed_period, o.*, o.id
  76. Group Key: o.*, o.id, ic.consumed_period
  77. Buffers: shared hit=759
  78. -> Sort (cost=350.52..353.02 rows=1000 width=220) (actual time=31.717..31.718 rows=10 loops=1)
  79. Output: ic.consumed_period, o.*, o.id, cis.item_cost, cis.item_suma, ic.consumed
  80. Sort Key: o.*, o.id, ic.consumed_period
  81. Sort Method: quicksort Memory: 27kB
  82. Buffers: shared hit=750
  83. -> Nested Loop Left Join (cost=43.38..300.69 rows=1000 width=220) (actual time=6.397..31.619 rows=10 loops=1)
  84. Output: ic.consumed_period, o.*, o.id, cis.item_cost, cis.item_suma, ic.consumed
  85. Buffers: shared hit=743
  86. -> Nested Loop Left Join (cost=43.13..280.44 rows=1 width=421) (actual time=5.986..16.317 rows=10 loops=1)
  87. Output: o.*, o.id, od.*, sp.*, ic.consumed, ic.consumed_period, ic.*
  88. Buffers: shared hit=263
  89. -> Nested Loop Left Join (cost=42.05..271.19 rows=1 width=392) (actual time=5.622..15.414 rows=10 loops=1)
  90. Output: o.*, o.id, o.app_period, split_period.split_period, od.app_period, od.*, sp.*
  91. Join Filter: (od.service_type_id = sp.service_type_id)
  92. Rows Removed by Join Filter: 643
  93. Buffers: shared hit=234
  94. -> Nested Loop (cost=41.80..258.38 rows=1 width=372) (actual time=2.258..5.545 rows=10 loops=1)
  95. Output: o.*, o.id, o.service_level_id, o.app_period, o.period_id, split_period.split_period, od.app_period, od.*, od.service_type_id
  96. Join Filter: ((od.app_period * o.app_period) && split_period.split_period)
  97. Rows Removed by Join Filter: 3
  98. Buffers: shared hit=87
  99. -> Nested Loop (cost=41.52..241.65 rows=1 width=226) (actual time=2.203..5.048 rows=2 loops=1)
  100. Output: o.*, o.id, o.service_level_id, o.app_period, o.period_id, split_period.split_period
  101. Buffers: shared hit=72
  102. -> Bitmap Heap Scan on public.order_bt o (cost=8.61..29.24 rows=6 width=194) (actual time=0.139..0.156 rows=6 loops=1)
  103. Output: o.*, o.id, o.service_level_id, o.app_period, o.period_id
  104. Recheck Cond: ((o.id = ANY ('{6154,10805}'::integer[])) AND (o.sys_period @> sys_time()))
  105. Heap Blocks: exact=2
  106. Buffers: shared hit=6
  107. -> Bitmap Index Scan on order_id_sys_period_app_period_excl (cost=0.00..8.61 rows=6 width=0) (actual time=0.114..0.114 rows=6 loops=1)
  108. Index Cond: ((o.id = ANY ('{6154,10805}'::integer[])) AND (o.sys_period @> sys_time()))
  109. Buffers: shared hit=4
  110. -> Subquery Scan on split_period (cost=32.91..35.39 rows=1 width=32) (actual time=0.811..0.813 rows=0 loops=6)
  111. Output: split_period.split_period
  112. Filter: ((o.app_period * app_period()) && split_period.split_period)
  113. Rows Removed by Filter: 1
  114. Buffers: shared hit=66
  115. -> Subquery Scan on x (cost=32.91..33.27 rows=8 width=32) (actual time=0.759..0.763 rows=1 loops=6)
  116. Output: x.app_period
  117. Filter: (x.boundary IS NOT NULL)
  118. Rows Removed by Filter: 1
  119. Buffers: shared hit=66
  120. CTE ranges
  121. -> HashAggregate (cost=32.37..32.41 rows=4 width=32) (actual time=0.732..0.732 rows=1 loops=6)
  122. Output: ((o_3.app_period * app_period()))
  123. Group Key: ((o_3.app_period * app_period()))
  124. Buffers: shared hit=63
  125. -> Append (cost=0.65..32.36 rows=4 width=32) (actual time=0.152..0.721 rows=6 loops=6)
  126. Buffers: shared hit=63
  127. -> Index Only Scan using order_id_sys_period_app_period_excl on public.order_bt o_3 (cost=0.65..13.20 rows=2 width=32) (actual time=0.079..0.080 rows=1 loops=6)
  128. Output: (o_3.app_period * app_period())
  129. Index Cond: ((o_3.id = $18) AND (o_3.sys_period @> sys_time()) AND (o_3.app_period && app_period()))
  130. Heap Fetches: 6
  131. Buffers: shared hit=18
  132. -> Index Scan using order_detail_idx_order_id on public.order_detail_bt od_3 (cost=0.29..19.13 rows=2 width=32) (actual time=0.172..0.564 rows=5 loops=6)
  133. Output: (od_3.app_period * app_period())
  134. Index Cond: (od_3.order_id = $18)
  135. Filter: ((od_3.sys_period @> sys_time()) AND (od_3.app_period && app_period()))
  136. Rows Removed by Filter: 10
  137. Buffers: shared hit=45
  138. CTE points
  139. -> Sort (cost=0.48..0.50 rows=8 width=8) (actual time=0.752..0.752 rows=2 loops=6)
  140. Output: (lower(ranges_2.app_period))
  141. Sort Key: (lower(ranges_2.app_period))
  142. Sort Method: quicksort Memory: 25kB
  143. Buffers: shared hit=66
  144. -> HashAggregate (cost=0.28..0.36 rows=8 width=8) (actual time=0.740..0.741 rows=2 loops=6)
  145. Output: (lower(ranges_2.app_period))
  146. Group Key: (lower(ranges_2.app_period))
  147. Buffers: shared hit=63
  148. -> Append (cost=0.00..0.26 rows=8 width=8) (actual time=0.735..0.738 rows=2 loops=6)
  149. Buffers: shared hit=63
  150. -> CTE Scan on ranges ranges_2 (cost=0.00..0.09 rows=4 width=8) (actual time=0.735..0.735 rows=1 loops=6)
  151. Output: lower(ranges_2.app_period)
  152. Buffers: shared hit=63
  153. -> CTE Scan on ranges ranges_3 (cost=0.00..0.09 rows=4 width=8) (actual time=0.001..0.001 rows=1 loops=6)
  154. Output: upper(ranges_3.app_period)
  155. -> WindowAgg (cost=0.00..0.28 rows=8 width=40) (actual time=0.758..0.761 rows=2 loops=6)
  156. Output: tstzrange(points.point, lead(points.point) OVER (?)), lead(points.point) OVER (?)
  157. Buffers: shared hit=66
  158. -> CTE Scan on points (cost=0.00..0.16 rows=8 width=8) (actual time=0.753..0.755 rows=2 loops=6)
  159. Output: points.point
  160. Buffers: shared hit=66
  161. -> Index Scan using order_detail_idx_order_id on public.order_detail_bt od (cost=0.29..16.69 rows=2 width=150) (actual time=0.115..0.233 rows=6 loops=2)
  162. Output: od.app_period, od.*, od.order_id, od.service_type_id
  163. Index Cond: (od.order_id = o.id)
  164. Filter: (od.sys_period @> sys_time())
  165. Rows Removed by Filter: 8
  166. Buffers: shared hit=15
  167. -> Function Scan on public.service_level_price sp (cost=0.25..12.75 rows=5 width=40) (actual time=0.938..0.975 rows=65 loops=10)
  168. Output: sp.*, sp.service_type_id, sp.period_id
  169. Function Call: service_level_price(o.service_level_id)
  170. Filter: (o.period_id = sp.period_id)
  171. Rows Removed by Filter: 38
  172. Buffers: shared hit=147
  173. -> Subquery Scan on ic (cost=1.07..9.24 rows=1 width=104) (actual time=0.087..0.088 rows=1 loops=10)
  174. Output: ic.consumed, ic.consumed_period, ic.*
  175. Buffers: shared hit=29
  176. -> Nested Loop Left Join (cost=1.07..9.23 rows=1 width=72) (actual time=0.085..0.085 rows=1 loops=10)
  177. 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), $22), COALESCE(((od.app_period * o.app_period) * split_period.split_period), ((od.app_period * o.app_period) * split_period.split_period))
  178. Buffers: shared hit=29
  179. InitPlan 7 (returns $22)
  180. -> Seq Scan on public.period period_1 (cost=0.00..1.07 rows=1 width=16) (actual time=0.004..0.005 rows=1 loops=10)
  181. Output: period_1."interval"
  182. Filter: (period_1.id = ($21).period_id)
  183. Rows Removed by Filter: 5
  184. Buffers: shared hit=10
  185. -> Result (cost=0.00..0.01 rows=1 width=4) (actual time=0.000..0.001 rows=1 loops=10)
  186. Output: NULL::integer
  187. -> Seq Scan on public.currency_rate_bt cr (cost=0.00..7.86 rows=1 width=56) (actual time=0.045..0.045 rows=0 loops=10)
  188. Output: cr.id, cr.rate, cr.docdate, cr.from_currency_id, cr.to_currency_id, cr.sid, cr.app_period, cr.sys_period
  189. 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()))
  190. Rows Removed by Filter: 13
  191. Buffers: shared hit=10
  192. -> Function Scan on public.calc_item_suma cis (cost=0.25..10.25 rows=1000 width=16) (actual time=1.527..1.527 rows=1 loops=10)
  193. Output: cis.item_qty, cis.item_price, cis.item_cost, cis.item_suma
  194. Function Call: calc_item_suma(o.*, od.*, sp.*, ic.*)
  195. Buffers: shared hit=480
  196. -> Hash (cost=1.06..1.06 rows=6 width=20) (actual time=0.009..0.009 rows=6 loops=1)
  197. Output: p."interval", p.id
  198. Buckets: 1024 Batches: 1 Memory Usage: 9kB
  199. Buffers: shared hit=1
  200. -> Seq Scan on public.period p (cost=0.00..1.06 rows=6 width=20) (actual time=0.005..0.006 rows=6 loops=1)
  201. Output: p."interval", p.id
  202. Buffers: shared hit=1
  203. SubPlan 4
  204. -> Subquery Scan on next_ots (cost=193.93..211.57 rows=1 width=244) (actual time=51.997..51.998 rows=1 loops=2)
  205. Output: next_ots.*
  206. Filter: (next_ots.consumed_period @> (t.o).billed_to)
  207. Rows Removed by Filter: 2
  208. Buffers: shared hit=3239
  209. -> WindowAgg (cost=193.93..211.53 rows=3 width=228) (actual time=51.989..51.991 rows=4 loops=2)
  210. Output: sum((sum(cis_1.item_suma))) OVER (?), (sum(cis_1.item_cost)), (sum(cis_1.item_suma)), (max(ic_1.consumed)), ic_1.consumed_period, o_2.*, o_2.id
  211. Buffers: shared hit=3239
  212. -> GroupAggregate (cost=193.93..211.46 rows=3 width=220) (actual time=51.956..51.977 rows=4 loops=2)
  213. Output: sum(cis_1.item_cost), sum(cis_1.item_suma), max(ic_1.consumed), ic_1.consumed_period, o_2.*, o_2.id
  214. Group Key: o_2.*, o_2.id, ic_1.consumed_period
  215. Buffers: shared hit=3239
  216. -> Sort (cost=193.93..196.43 rows=1000 width=220) (actual time=51.942..51.944 rows=18 loops=2)
  217. Output: ic_1.consumed_period, o_2.*, o_2.id, cis_1.item_cost, cis_1.item_suma, ic_1.consumed
  218. Sort Key: o_2.*, ic_1.consumed_period
  219. Sort Method: quicksort Memory: 27kB
  220. Buffers: shared hit=3239
  221. -> Nested Loop Left Join (cost=28.89..144.10 rows=1000 width=220) (actual time=5.566..51.884 rows=18 loops=2)
  222. Output: ic_1.consumed_period, o_2.*, o_2.id, cis_1.item_cost, cis_1.item_suma, ic_1.consumed
  223. Buffers: shared hit=3239
  224. -> Nested Loop Left Join (cost=28.64..123.85 rows=1 width=421) (actual time=5.509..19.876 rows=18 loops=2)
  225. Output: o_2.*, o_2.id, od_2.*, sp_1.*, ic_1.consumed, ic_1.consumed_period, ic_1.*
  226. Buffers: shared hit=1186
  227. -> Nested Loop Left Join (cost=27.57..114.60 rows=1 width=392) (actual time=5.113..17.561 rows=18 loops=2)
  228. Output: o_2.*, o_2.id, o_2.app_period, split_period_1.split_period, od_2.app_period, od_2.*, sp_1.*
  229. Join Filter: (od_2.service_type_id = sp_1.service_type_id)
  230. Rows Removed by Join Filter: 1123
  231. Buffers: shared hit=999
  232. -> Nested Loop (cost=27.32..101.79 rows=1 width=372) (actual time=4.347..6.992 rows=18 loops=2)
  233. Output: o_2.*, o_2.id, o_2.service_level_id, o_2.app_period, o_2.period_id, split_period_1.split_period, od_2.app_period, od_2.*, od_2.service_type_id
  234. Join Filter: ((od_2.app_period * o_2.app_period) && split_period_1.split_period)
  235. Rows Removed by Join Filter: 8
  236. Buffers: shared hit=859
  237. -> Nested Loop (cost=27.03..84.15 rows=1 width=226) (actual time=4.274..6.384 rows=4 loops=2)
  238. Output: o_2.*, o_2.id, o_2.service_level_id, o_2.app_period, o_2.period_id, split_period_1.split_period
  239. Buffers: shared hit=802
  240. -> Bitmap Heap Scan on public.order_bt o_2 (cost=4.43..15.47 rows=3 width=194) (actual time=0.044..0.050 rows=3 loops=2)
  241. Output: o_2.*, o_2.id, o_2.service_level_id, o_2.app_period, o_2.period_id
  242. Recheck Cond: ((o_2.id = t.order_id) AND (o_2.sys_period @> sys_time()))
  243. Heap Blocks: exact=3
  244. Buffers: shared hit=7
  245. -> Bitmap Index Scan on order_id_sys_period_app_period_excl (cost=0.00..4.43 rows=3 width=0) (actual time=0.015..0.015 rows=3 loops=2)
  246. Index Cond: ((o_2.id = t.order_id) AND (o_2.sys_period @> sys_time()))
  247. Buffers: shared hit=4
  248. -> Subquery Scan on split_period_1 (cost=22.60..22.88 rows=1 width=32) (actual time=2.096..2.107 rows=1 loops=6)
  249. Output: split_period_1.split_period
  250. Filter: ((o_2.app_period * tstzrange(NULLIF((t.o).billed_to, 'infinity'::timestamp with time zone), NULLIF(((t.o).billed_to + p."interval"), 'infinity'::timestamp with time zone))) && split_period_1.split_period)
  251. Rows Removed by Filter: 2
  252. Buffers: shared hit=795
  253. -> Subquery Scan on x_1 (cost=22.60..22.78 rows=4 width=32) (actual time=2.088..2.098 rows=4 loops=6)
  254. Output: x_1.app_period
  255. Filter: (x_1.boundary IS NOT NULL)
  256. Rows Removed by Filter: 1
  257. Buffers: shared hit=795
  258. CTE ranges
  259. -> Unique (cost=22.36..22.37 rows=2 width=32) (actual time=2.059..2.062 rows=5 loops=6)
  260. Output: ((o_1.app_period * tstzrange(NULLIF((t.o).billed_to, 'infinity'::timestamp with time zone), NULLIF(((t.o).billed_to + p."interval"), 'infinity'::timestamp with time zone))))
  261. Buffers: shared hit=795
  262. -> Sort (cost=22.36..22.37 rows=2 width=32) (actual time=2.058..2.058 rows=8 loops=6)
  263. Output: ((o_1.app_period * tstzrange(NULLIF((t.o).billed_to, 'infinity'::timestamp with time zone), NULLIF(((t.o).billed_to + p."interval"), 'infinity'::timestamp with time zone))))
  264. Sort Key: ((o_1.app_period * tstzrange(NULLIF((t.o).billed_to, 'infinity'::timestamp with time zone), NULLIF(((t.o).billed_to + p."interval"), 'infinity'::timestamp with time zone))))
  265. Sort Method: quicksort Memory: 25kB
  266. Buffers: shared hit=795
  267. -> Append (cost=0.41..22.35 rows=2 width=32) (actual time=0.039..2.049 rows=8 loops=6)
  268. Buffers: shared hit=795
  269. -> Index Only Scan using order_id_sys_period_app_period_excl on public.order_bt o_1 (cost=0.41..8.44 rows=1 width=32) (actual time=0.019..0.021 rows=2 loops=6)
  270. Output: (o_1.app_period * tstzrange(NULLIF((t.o).billed_to, 'infinity'::timestamp with time zone), NULLIF(((t.o).billed_to + p."interval"), 'infinity'::timestamp with time zone)))
  271. Index Cond: ((o_1.id = $3) AND (o_1.sys_period @> sys_time()) AND (o_1.app_period && tstzrange(NULLIF((t.o).billed_to, 'infinity'::timestamp with time zone), NULLIF(((t.o).billed_to + p."interval"), 'infinity'::timestamp with time zone))))
  272. Heap Fetches: 12
  273. Buffers: shared hit=21
  274. -> Bitmap Heap Scan on public.order_detail_bt od_1 (cost=9.60..13.89 rows=1 width=32) (actual time=1.993..2.003 rows=6 loops=6)
  275. Output: (od_1.app_period * tstzrange(NULLIF((t.o).billed_to, 'infinity'::timestamp with time zone), NULLIF(((t.o).billed_to + p."interval"), 'infinity'::timestamp with time zone)))
  276. Recheck Cond: ((od_1.order_id = $3) AND (od_1.sys_period @> sys_time()) AND (od_1.app_period && tstzrange(NULLIF((t.o).billed_to, 'infinity'::timestamp with time zone), NULLIF(((t.o).billed_to + p."interval"), 'infinity'::timestamp with time zone))))
  277. Heap Blocks: exact=24
  278. Buffers: shared hit=774
  279. -> BitmapAnd (cost=9.60..9.60 rows=1 width=0) (actual time=1.972..1.972 rows=0 loops=6)
  280. Buffers: shared hit=750
  281. -> Bitmap Index Scan on order_detail_idx_order_id (cost=0.00..4.32 rows=4 width=0) (actual time=0.007..0.007 rows=14 loops=6)
  282. Index Cond: (od_1.order_id = $3)
  283. Buffers: shared hit=12
  284. -> Bitmap Index Scan on order_detail_bt_id_sys_period_app_period_excl (cost=0.00..5.04 rows=50 width=0) (actual time=1.955..1.955 rows=4114 loops=6)
  285. Index Cond: ((od_1.sys_period @> sys_time()) AND (od_1.app_period && tstzrange(NULLIF((t.o).billed_to, 'infinity'::timestamp with time zone), NULLIF(((t.o).billed_to + p."interval"), 'infinity'::timestamp with time zone))))
  286. Buffers: shared hit=738
  287. CTE points
  288. -> Sort (cost=0.22..0.23 rows=4 width=8) (actual time=2.081..2.082 rows=4 loops=6)
  289. Output: (lower(ranges.app_period))
  290. Sort Key: (lower(ranges.app_period))
  291. Sort Method: quicksort Memory: 25kB
  292. Buffers: shared hit=795
  293. -> HashAggregate (cost=0.14..0.18 rows=4 width=8) (actual time=2.075..2.076 rows=4 loops=6)
  294. Output: (lower(ranges.app_period))
  295. Group Key: (lower(ranges.app_period))
  296. Buffers: shared hit=795
  297. -> Append (cost=0.00..0.13 rows=4 width=8) (actual time=2.061..2.069 rows=10 loops=6)
  298. Buffers: shared hit=795
  299. -> CTE Scan on ranges (cost=0.00..0.04 rows=2 width=8) (actual time=2.061..2.065 rows=5 loops=6)
  300. Output: lower(ranges.app_period)
  301. Buffers: shared hit=795
  302. -> CTE Scan on ranges ranges_1 (cost=0.00..0.04 rows=2 width=8) (actual time=0.001..0.002 rows=5 loops=6)
  303. Output: upper(ranges_1.app_period)
  304. -> WindowAgg (cost=0.00..0.14 rows=4 width=40) (actual time=2.087..2.095 rows=4 loops=6)
  305. Output: tstzrange(points_1.point, lead(points_1.point) OVER (?)), lead(points_1.point) OVER (?)
  306. Buffers: shared hit=795
  307. -> CTE Scan on points points_1 (cost=0.00..0.08 rows=4 width=8) (actual time=2.083..2.086 rows=4 loops=6)
  308. Output: points_1.point
  309. Buffers: shared hit=795
  310. -> Index Scan using order_detail_idx_order_id on public.order_detail_bt od_2 (cost=0.29..17.61 rows=2 width=150) (actual time=0.050..0.162 rows=7 loops=7)
  311. Output: od_2.app_period, od_2.*, od_2.order_id, od_2.service_type_id
  312. Index Cond: (od_2.order_id = t.order_id)
  313. Filter: (od_2.sys_period @> sys_time())
  314. Rows Removed by Filter: 6
  315. Buffers: shared hit=57
  316. -> Function Scan on public.service_level_price sp_1 (cost=0.25..12.75 rows=5 width=40) (actual time=0.561..0.594 rows=65 loops=35)
  317. Output: sp_1.*, sp_1.service_type_id, sp_1.period_id
  318. Function Call: service_level_price(o_2.service_level_id)
  319. Filter: (o_2.period_id = sp_1.period_id)
  320. Rows Removed by Filter: 38
  321. Buffers: shared hit=140
  322. -> Subquery Scan on ic_1 (cost=1.07..9.24 rows=1 width=104) (actual time=0.128..0.130 rows=1 loops=35)
  323. Output: ic_1.consumed, ic_1.consumed_period, ic_1.*
  324. Buffers: shared hit=187
  325. -> Nested Loop Left Join (cost=1.07..9.23 rows=1 width=72) (actual time=0.126..0.128 rows=1 loops=35)
  326. Output: ROW(cr_1.id, cr_1.rate, cr_1.docdate, cr_1.from_currency_id, cr_1.to_currency_id, cr_1.app_period)::currency_rate, interval_length(((od_2.app_period * o_2.app_period) * split_period_1.split_period), $7), COALESCE(((od_2.app_period * o_2.app_period) * split_period_1.split_period), ((od_2.app_period * o_2.app_period) * split_period_1.split_period))
  327. Buffers: shared hit=187
  328. InitPlan 3 (returns $7)
  329. -> Seq Scan on public.period (cost=0.00..1.07 rows=1 width=16) (actual time=0.003..0.004 rows=1 loops=35)
  330. Output: period."interval"
  331. Filter: (period.id = ($6).period_id)
  332. Rows Removed by Filter: 5
  333. Buffers: shared hit=35
  334. -> Result (cost=0.00..0.01 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=35)
  335. Output: NULL::integer
  336. -> Seq Scan on public.currency_rate_bt cr_1 (cost=0.00..7.86 rows=1 width=56) (actual time=0.053..0.055 rows=1 loops=35)
  337. Output: cr_1.id, cr_1.rate, cr_1.docdate, cr_1.from_currency_id, cr_1.to_currency_id, cr_1.sid, cr_1.app_period, cr_1.sys_period
  338. Filter: ((cr_1.from_currency_id = (sp_1.*).currency_id) AND (cr_1.app_period @> lower(((od_2.app_period * o_2.app_period) * split_period_1.split_period))) AND (cr_1.sys_period @> sys_time()) AND (cr_1.to_currency_id = app_currency()))
  339. Rows Removed by Filter: 12
  340. Buffers: shared hit=35
  341. -> Function Scan on public.calc_item_suma cis_1 (cost=0.25..10.25 rows=1000 width=16) (actual time=1.827..1.827 rows=1 loops=35)
  342. Output: cis_1.item_qty, cis_1.item_price, cis_1.item_cost, cis_1.item_suma
  343. Function Call: calc_item_suma(o_2.*, od_2.*, sp_1.*, ic_1.*)
  344. Buffers: shared hit=2053
  345. Planning time: 7.571 ms
  346. Execution time: 137.647 ms
  347. (289 rows)
  348.  
  349. QUERY PLAN
  350. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  351. Nested Loop Left Join (cost=64607.17..386057.18 rows=6 width=260) (actual time=11007.771..22086.976 rows=2 loops=1)
  352. Output: tstzrange(NULLIF((t.o).billed_to, 'infinity'::timestamp with time zone), NULLIF(((t.o).billed_to + p."interval"), 'infinity'::timestamp with time zone)), (sum(t.group_suma) OVER (?)), t.group_cost, t.group_suma, t.consumed, t.consumed_period, t.o, t.order_id
  353. Buffers: shared hit=842575
  354. -> Nested Loop Left Join (cost=368.21..369.93 rows=6 width=244) (actual time=29.214..29.232 rows=2 loops=1)
  355. Output: t.o, (sum(t.group_suma) OVER (?)), t.group_cost, t.group_suma, t.consumed, t.consumed_period, t.order_id, p."interval"
  356. Inner Unique: true
  357. Join Filter: (p.id = (t.o).period_id)
  358. Buffers: shared hit=582
  359. -> WindowAgg (cost=368.21..368.32 rows=6 width=228) (actual time=29.203..29.217 rows=2 loops=1)
  360. Output: sum(t.group_suma) OVER (?), t.group_cost, t.group_suma, t.consumed, t.consumed_period, t.o, t.order_id
  361. Buffers: shared hit=581
  362. -> Sort (cost=368.21..368.23 rows=6 width=220) (actual time=29.198..29.198 rows=2 loops=1)
  363. Output: t.order_id, t.group_suma, t.group_cost, t.consumed, t.consumed_period, t.o
  364. Sort Key: t.order_id
  365. Sort Method: quicksort Memory: 25kB
  366. Buffers: shared hit=581
  367. -> Subquery Scan on t (cost=350.52..368.14 rows=6 width=220) (actual time=29.182..29.190 rows=2 loops=1)
  368. Output: t.order_id, t.group_suma, t.group_cost, t.consumed, t.consumed_period, t.o
  369. Buffers: shared hit=581
  370. -> GroupAggregate (cost=350.52..368.08 rows=6 width=220) (actual time=29.180..29.188 rows=2 loops=1)
  371. Output: sum(cis.item_cost), sum(cis.item_suma), max(ic.consumed), ic.consumed_period, o.*, o.id
  372. Group Key: o.*, o.id, ic.consumed_period
  373. Buffers: shared hit=581
  374. -> Sort (cost=350.52..353.02 rows=1000 width=220) (actual time=29.165..29.166 rows=10 loops=1)
  375. Output: ic.consumed_period, o.*, o.id, cis.item_cost, cis.item_suma, ic.consumed
  376. Sort Key: o.*, o.id, ic.consumed_period
  377. Sort Method: quicksort Memory: 27kB
  378. Buffers: shared hit=581
  379. -> Nested Loop Left Join (cost=43.38..300.69 rows=1000 width=220) (actual time=3.893..29.121 rows=10 loops=1)
  380. Output: ic.consumed_period, o.*, o.id, cis.item_cost, cis.item_suma, ic.consumed
  381. Buffers: shared hit=581
  382. -> Nested Loop Left Join (cost=43.13..280.44 rows=1 width=421) (actual time=3.768..14.541 rows=10 loops=1)
  383. Output: o.*, o.id, od.*, sp.*, ic.consumed, ic.consumed_period, ic.*
  384. Buffers: shared hit=144
  385. -> Nested Loop Left Join (cost=42.05..271.19 rows=1 width=392) (actual time=3.579..13.671 rows=10 loops=1)
  386. Output: o.*, o.id, o.app_period, split_period.split_period, od.app_period, od.*, sp.*
  387. Join Filter: (od.service_type_id = sp.service_type_id)
  388. Rows Removed by Join Filter: 643
  389. Buffers: shared hit=124
  390. -> Nested Loop (cost=41.80..258.38 rows=1 width=372) (actual time=1.924..4.514 rows=10 loops=1)
  391. Output: o.*, o.id, o.service_level_id, o.app_period, o.period_id, split_period.split_period, od.app_period, od.*, od.service_type_id
  392. Join Filter: ((od.app_period * o.app_period) && split_period.split_period)
  393. Rows Removed by Join Filter: 3
  394. Buffers: shared hit=84
  395. -> Nested Loop (cost=41.52..241.65 rows=1 width=226) (actual time=1.877..4.057 rows=2 loops=1)
  396. Output: o.*, o.id, o.service_level_id, o.app_period, o.period_id, split_period.split_period
  397. Buffers: shared hit=69
  398. -> Bitmap Heap Scan on public.order_bt o (cost=8.61..29.24 rows=6 width=194) (actual time=0.087..0.102 rows=6 loops=1)
  399. Output: o.*, o.id, o.service_level_id, o.app_period, o.period_id
  400. Recheck Cond: ((o.id = ANY ('{6154,10805}'::integer[])) AND (o.sys_period @> sys_time()))
  401. Heap Blocks: exact=2
  402. Buffers: shared hit=6
  403. -> Bitmap Index Scan on order_id_sys_period_app_period_excl (cost=0.00..8.61 rows=6 width=0) (actual time=0.074..0.074 rows=6 loops=1)
  404. Index Cond: ((o.id = ANY ('{6154,10805}'::integer[])) AND (o.sys_period @> sys_time()))
  405. Buffers: shared hit=4
  406. -> Subquery Scan on split_period (cost=32.91..35.39 rows=1 width=32) (actual time=0.656..0.658 rows=0 loops=6)
  407. Output: split_period.split_period
  408. Filter: ((o.app_period * app_period()) && split_period.split_period)
  409. Rows Removed by Filter: 1
  410. Buffers: shared hit=63
  411. -> Subquery Scan on x (cost=32.91..33.27 rows=8 width=32) (actual time=0.618..0.621 rows=1 loops=6)
  412. Output: x.app_period
  413. Filter: (x.boundary IS NOT NULL)
  414. Rows Removed by Filter: 1
  415. Buffers: shared hit=63
  416. CTE ranges
  417. -> HashAggregate (cost=32.37..32.41 rows=4 width=32) (actual time=0.599..0.600 rows=1 loops=6)
  418. Output: ((o_3.app_period * app_period()))
  419. Group Key: ((o_3.app_period * app_period()))
  420. Buffers: shared hit=63
  421. -> Append (cost=0.65..32.36 rows=4 width=32) (actual time=0.101..0.590 rows=6 loops=6)
  422. Buffers: shared hit=63
  423. -> Index Only Scan using order_id_sys_period_app_period_excl on public.order_bt o_3 (cost=0.65..13.20 rows=2 width=32) (actual time=0.052..0.052 rows=1 loops=6)
  424. Output: (o_3.app_period * app_period())
  425. Index Cond: ((o_3.id = $10) AND (o_3.sys_period @> sys_time()) AND (o_3.app_period && app_period()))
  426. Heap Fetches: 6
  427. Buffers: shared hit=18
  428. -> Index Scan using order_detail_idx_order_id on public.order_detail_bt od_3 (cost=0.29..19.13 rows=2 width=32) (actual time=0.123..0.486 rows=5 loops=6)
  429. Output: (od_3.app_period * app_period())
  430. Index Cond: (od_3.order_id = $10)
  431. Filter: ((od_3.sys_period @> sys_time()) AND (od_3.app_period && app_period()))
  432. Rows Removed by Filter: 10
  433. Buffers: shared hit=45
  434. CTE points
  435. -> Sort (cost=0.48..0.50 rows=8 width=8) (actual time=0.612..0.612 rows=2 loops=6)
  436. Output: (lower(ranges_2.app_period))
  437. Sort Key: (lower(ranges_2.app_period))
  438. Sort Method: quicksort Memory: 25kB
  439. Buffers: shared hit=63
  440. -> HashAggregate (cost=0.28..0.36 rows=8 width=8) (actual time=0.606..0.606 rows=2 loops=6)
  441. Output: (lower(ranges_2.app_period))
  442. Group Key: (lower(ranges_2.app_period))
  443. Buffers: shared hit=63
  444. -> Append (cost=0.00..0.26 rows=8 width=8) (actual time=0.602..0.604 rows=2 loops=6)
  445. Buffers: shared hit=63
  446. -> CTE Scan on ranges ranges_2 (cost=0.00..0.09 rows=4 width=8) (actual time=0.601..0.602 rows=1 loops=6)
  447. Output: lower(ranges_2.app_period)
  448. Buffers: shared hit=63
  449. -> CTE Scan on ranges ranges_3 (cost=0.00..0.09 rows=4 width=8) (actual time=0.001..0.001 rows=1 loops=6)
  450. Output: upper(ranges_3.app_period)
  451. -> WindowAgg (cost=0.00..0.28 rows=8 width=40) (actual time=0.617..0.620 rows=2 loops=6)
  452. Output: tstzrange(points.point, lead(points.point) OVER (?)), lead(points.point) OVER (?)
  453. Buffers: shared hit=63
  454. -> CTE Scan on points (cost=0.00..0.16 rows=8 width=8) (actual time=0.613..0.614 rows=2 loops=6)
  455. Output: points.point
  456. Buffers: shared hit=63
  457. -> Index Scan using order_detail_idx_order_id on public.order_detail_bt od (cost=0.29..16.69 rows=2 width=150) (actual time=0.068..0.214 rows=6 loops=2)
  458. Output: od.app_period, od.*, od.order_id, od.service_type_id
  459. Index Cond: (od.order_id = o.id)
  460. Filter: (od.sys_period @> sys_time())
  461. Rows Removed by Filter: 8
  462. Buffers: shared hit=15
  463. -> Function Scan on public.service_level_price sp (cost=0.25..12.75 rows=5 width=40) (actual time=0.854..0.902 rows=65 loops=10)
  464. Output: sp.*, sp.service_type_id, sp.period_id
  465. Function Call: service_level_price(o.service_level_id)
  466. Filter: (o.period_id = sp.period_id)
  467. Rows Removed by Filter: 38
  468. Buffers: shared hit=40
  469. -> Subquery Scan on ic (cost=1.07..9.24 rows=1 width=104) (actual time=0.084..0.084 rows=1 loops=10)
  470. Output: ic.consumed, ic.consumed_period, ic.*
  471. Buffers: shared hit=20
  472. -> Nested Loop Left Join (cost=1.07..9.23 rows=1 width=72) (actual time=0.081..0.082 rows=1 loops=10)
  473. 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), $14), COALESCE(((od.app_period * o.app_period) * split_period.split_period), ((od.app_period * o.app_period) * split_period.split_period))
  474. Buffers: shared hit=20
  475. InitPlan 6 (returns $14)
  476. -> Seq Scan on public.period period_1 (cost=0.00..1.07 rows=1 width=16) (actual time=0.004..0.005 rows=1 loops=10)
  477. Output: period_1."interval"
  478. Filter: (period_1.id = ($13).period_id)
  479. Rows Removed by Filter: 5
  480. Buffers: shared hit=10
  481. -> Result (cost=0.00..0.01 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=10)
  482. Output: NULL::integer
  483. -> Seq Scan on public.currency_rate_bt cr (cost=0.00..7.86 rows=1 width=56) (actual time=0.056..0.056 rows=0 loops=10)
  484. Output: cr.id, cr.rate, cr.docdate, cr.from_currency_id, cr.to_currency_id, cr.sid, cr.app_period, cr.sys_period
  485. 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()))
  486. Rows Removed by Filter: 13
  487. Buffers: shared hit=10
  488. -> Function Scan on public.calc_item_suma cis (cost=0.25..10.25 rows=1000 width=16) (actual time=1.455..1.455 rows=1 loops=10)
  489. Output: cis.item_qty, cis.item_price, cis.item_cost, cis.item_suma
  490. Function Call: calc_item_suma(o.*, od.*, sp.*, ic.*)
  491. Buffers: shared hit=437
  492. -> Materialize (cost=0.00..1.09 rows=6 width=20) (actual time=0.004..0.004 rows=1 loops=2)
  493. Output: p."interval", p.id
  494. Buffers: shared hit=1
  495. -> Seq Scan on public.period p (cost=0.00..1.06 rows=6 width=20) (actual time=0.005..0.005 rows=1 loops=1)
  496. Output: p."interval", p.id
  497. Buffers: shared hit=1
  498. -> Subquery Scan on next_ots (cost=64238.95..64281.19 rows=1 width=36) (actual time=11025.949..11028.858 rows=1 loops=2)
  499. Output: next_ots.total_suma, next_ots.group_cost, next_ots.group_suma, next_ots.consumed, next_ots.consumed_period, next_ots.o, next_ots.order_id
  500. Filter: ((next_ots.consumed_period @> (t.o).billed_to) AND (next_ots.order_id = t.order_id))
  501. Rows Removed by Filter: 1722
  502. Buffers: shared hit=841993
  503. -> Subquery Scan on t_1 (cost=64238.95..64267.37 rows=921 width=100) (actual time=11020.538..11028.340 rows=1722 loops=2)
  504. Output: NULL::double precision, NULL::double precision, NULL::double precision, NULL::double precision, t_1.consumed_period, NULL::order_bt, t_1.order_id
  505. Buffers: shared hit=841993
  506. -> GroupAggregate (cost=64238.95..64258.16 rows=921 width=220) (actual time=11020.536..11027.952 rows=1722 loops=2)
  507. Output: NULL::double precision, NULL::double precision, NULL::double precision, ic_1.consumed_period, o_1.*, o_1.id
  508. Group Key: o_1.*, o_1.id, ic_1.consumed_period
  509. Buffers: shared hit=841993
  510. -> Sort (cost=64238.95..64241.45 rows=1000 width=196) (actual time=11020.481..11020.916 rows=5654 loops=2)
  511. Output: ic_1.consumed_period, o_1.*, o_1.id
  512. Sort Key: o_1.*, o_1.id, ic_1.consumed_period
  513. Sort Method: quicksort Memory: 1730kB
  514. Buffers: shared hit=841993
  515. -> Nested Loop Left Join (cost=24.86..64189.12 rows=1000 width=196) (actual time=3.107..10972.879 rows=5654 loops=2)
  516. Output: ic_1.consumed_period, o_1.*, o_1.id
  517. Buffers: shared hit=841993
  518. -> Nested Loop Left Join (cost=24.61..64168.87 rows=1 width=405) (actual time=3.045..10211.572 rows=5654 loops=2)
  519. Output: o_1.*, o_1.id, od_1.*, sp_1.*, ic_1.consumed_period, ic_1.*
  520. Buffers: shared hit=808763
  521. -> Nested Loop Left Join (cost=23.54..64159.63 rows=1 width=392) (actual time=2.955..9624.922 rows=5654 loops=2)
  522. Output: o_1.*, o_1.id, o_1.app_period, split_period_1.split_period, od_1.app_period, od_1.*, sp_1.*
  523. Join Filter: (od_1.service_type_id = sp_1.service_type_id)
  524. Rows Removed by Join Filter: 321286
  525. Buffers: shared hit=786149
  526. -> Nested Loop (cost=23.29..64146.81 rows=1 width=372) (actual time=2.306..6160.228 rows=5652 loops=2)
  527. Output: o_1.*, o_1.id, o_1.service_level_id, o_1.app_period, o_1.period_id, split_period_1.split_period, od_1.app_period, od_1.*, od_1.service_type_id
  528. Join Filter: ((od_1.app_period * o_1.app_period) && split_period_1.split_period)
  529. Rows Removed by Join Filter: 1620
  530. Buffers: shared hit=741197
  531. -> Nested Loop (cost=23.00..64055.95 rows=56 width=226) (actual time=2.241..5958.286 rows=1926 loops=2)
  532. Output: o_1.*, o_1.id, o_1.service_level_id, o_1.app_period, o_1.period_id, split_period_1.split_period
  533. Buffers: shared hit=719239
  534. -> Index Scan using order_id_sys_period_app_period_excl on public.order_bt o_1 (cost=0.40..504.98 rows=2776 width=194) (actual time=0.034..8.911 rows=2775 loops=2)
  535. Output: o_1.*, o_1.id, o_1.service_level_id, o_1.app_period, o_1.period_id
  536. Index Cond: (o_1.sys_period @> sys_time())
  537. Buffers: shared hit=1636
  538. -> Subquery Scan on split_period_1 (cost=22.60..22.88 rows=1 width=32) (actual time=2.138..2.142 rows=1 loops=5550)
  539. Output: split_period_1.split_period
  540. Filter: ((o_1.app_period * tstzrange(NULLIF((t.o).billed_to, 'infinity'::timestamp with time zone), NULLIF(((t.o).billed_to + p."interval"), 'infinity'::timestamp with time zone))) && split_period_1.split_period)
  541. Rows Removed by Filter: 1
  542. Buffers: shared hit=717603
  543. -> Subquery Scan on x_1 (cost=22.60..22.78 rows=4 width=32) (actual time=2.133..2.137 rows=2 loops=5550)
  544. Output: x_1.app_period
  545. Filter: (x_1.boundary IS NOT NULL)
  546. Rows Removed by Filter: 1
  547. Buffers: shared hit=717603
  548. CTE ranges
  549. -> Unique (cost=22.36..22.37 rows=2 width=32) (actual time=2.110..2.112 rows=3 loops=5550)
  550. Output: ((o_2.app_period * tstzrange(NULLIF(($0).billed_to, 'infinity'::timestamp with time zone), NULLIF((($0).billed_to + $1), 'infinity'::timestamp with time zone))))
  551. Buffers: shared hit=717603
  552. -> Sort (cost=22.36..22.37 rows=2 width=32) (actual time=2.109..2.110 rows=5 loops=5550)
  553. Output: ((o_2.app_period * tstzrange(NULLIF(($0).billed_to, 'infinity'::timestamp with time zone), NULLIF((($0).billed_to + $1), 'infinity'::timestamp with time zone))))
  554. Sort Key: ((o_2.app_period * tstzrange(NULLIF(($0).billed_to, 'infinity'::timestamp with time zone), NULLIF((($0).billed_to + $1), 'infinity'::timestamp with time zone))))
  555. Sort Method: quicksort Memory: 25kB
  556. Buffers: shared hit=717603
  557. -> Append (cost=0.41..22.35 rows=2 width=32) (actual time=0.035..2.103 rows=5 loops=5550)
  558. Buffers: shared hit=717603
  559. -> Index Only Scan using order_id_sys_period_app_period_excl on public.order_bt o_2 (cost=0.41..8.44 rows=1 width=32) (actual time=0.017..0.019 rows=2 loops=5550)
  560. Output: (o_2.app_period * tstzrange(NULLIF(($0).billed_to, 'infinity'::timestamp with time zone), NULLIF((($0).billed_to + $1), 'infinity'::timestamp with time zone)))
  561. Index Cond: ((o_2.id = $2) AND (o_2.sys_period @> sys_time()) AND (o_2.app_period && tstzrange(NULLIF(($0).billed_to, 'infinity'::timestamp with time zone), NULLIF((($0).billed_to + $1), 'infinity'::timestamp with time zone))))
  562. Heap Fetches: 11100
  563. Buffers: shared hit=17016
  564. -> Bitmap Heap Scan on public.order_detail_bt od_2 (cost=9.60..13.89 rows=1 width=32) (actual time=2.055..2.061 rows=3 loops=5550)
  565. Output: (od_2.app_period * tstzrange(NULLIF(($0).billed_to, 'infinity'::timestamp with time zone), NULLIF((($0).billed_to + $1), 'infinity'::timestamp with time zone)))
  566. Recheck Cond: ((od_2.order_id = $2) AND (od_2.sys_period @> sys_time()) AND (od_2.app_period && tstzrange(NULLIF(($0).billed_to, 'infinity'::timestamp with time zone), NULLIF((($0).billed_to + $1), 'infinity'::timestamp with time zone))))
  567. Heap Blocks: exact=14103
  568. Buffers: shared hit=700587
  569. -> BitmapAnd (cost=9.60..9.60 rows=1 width=0) (actual time=2.031..2.031 rows=0 loops=5550)
  570. Buffers: shared hit=686484
  571. -> Bitmap Index Scan on order_detail_idx_order_id (cost=0.00..4.32 rows=4 width=0) (actual time=0.006..0.006 rows=7 loops=5550)
  572. Index Cond: (od_2.order_id = $2)
  573. Buffers: shared hit=11214
  574. -> Bitmap Index Scan on order_detail_bt_id_sys_period_app_period_excl (cost=0.00..5.04 rows=50 width=0) (actual time=2.044..2.044 rows=4114 loops=5490)
  575. Index Cond: ((od_2.sys_period @> sys_time()) AND (od_2.app_period && tstzrange(NULLIF(($0).billed_to, 'infinity'::timestamp with time zone), NULLIF((($0).billed_to + $1), 'infinity'::timestamp with time zone))))
  576. Buffers: shared hit=675270
  577. CTE points
  578. -> Sort (cost=0.22..0.23 rows=4 width=8) (actual time=2.127..2.128 rows=3 loops=5550)
  579. Output: (lower(ranges.app_period))
  580. Sort Key: (lower(ranges.app_period))
  581. Sort Method: quicksort Memory: 25kB
  582. Buffers: shared hit=717603
  583. -> HashAggregate (cost=0.14..0.18 rows=4 width=8) (actual time=2.122..2.122 rows=3 loops=5550)
  584. Output: (lower(ranges.app_period))
  585. Group Key: (lower(ranges.app_period))
  586. Buffers: shared hit=717603
  587. -> Append (cost=0.00..0.13 rows=4 width=8) (actual time=2.112..2.117 rows=6 loops=5550)
  588. Buffers: shared hit=717603
  589. -> CTE Scan on ranges (cost=0.00..0.04 rows=2 width=8) (actual time=2.112..2.115 rows=3 loops=5550)
  590. Output: lower(ranges.app_period)
  591. Buffers: shared hit=717603
  592. -> CTE Scan on ranges ranges_1 (cost=0.00..0.04 rows=2 width=8) (actual time=0.001..0.001 rows=3 loops=5550)
  593. Output: upper(ranges_1.app_period)
  594. -> WindowAgg (cost=0.00..0.14 rows=4 width=40) (actual time=2.132..2.136 rows=3 loops=5550)
  595. Output: tstzrange(points_1.point, lead(points_1.point) OVER (?)), lead(points_1.point) OVER (?)
  596. Buffers: shared hit=717603
  597. -> CTE Scan on points points_1 (cost=0.00..0.08 rows=4 width=8) (actual time=2.128..2.130 rows=3 loops=5550)
  598. Output: points_1.point
  599. Buffers: shared hit=717603
  600. -> Index Scan using order_detail_idx_order_id on public.order_detail_bt od_1 (cost=0.29..1.59 rows=2 width=150) (actual time=0.033..0.098 rows=4 loops=3853)
  601. Output: od_1.app_period, od_1.*, od_1.order_id, od_1.service_type_id
  602. Index Cond: (od_1.order_id = o_1.id)
  603. Filter: (od_1.sys_period @> sys_time())
  604. Rows Removed by Filter: 3
  605. Buffers: shared hit=21958
  606. -> Function Scan on public.service_level_price sp_1 (cost=0.25..12.75 rows=5 width=40) (actual time=0.573..0.604 rows=58 loops=11304)
  607. Output: sp_1.*, sp_1.service_type_id, sp_1.period_id
  608. Function Call: service_level_price(o_1.service_level_id)
  609. Filter: (o_1.period_id = sp_1.period_id)
  610. Rows Removed by Filter: 43
  611. Buffers: shared hit=44952
  612. -> Subquery Scan on ic_1 (cost=1.07..9.24 rows=1 width=88) (actual time=0.099..0.102 rows=1 loops=11307)
  613. Output: ic_1.consumed_period, ic_1.*
  614. Buffers: shared hit=22614
  615. -> Nested Loop Left Join (cost=1.07..9.23 rows=1 width=72) (actual time=0.098..0.100 rows=1 loops=11307)
  616. Output: ROW(cr_1.id, cr_1.rate, cr_1.docdate, cr_1.from_currency_id, cr_1.to_currency_id, cr_1.app_period)::currency_rate, interval_length(((od_1.app_period * o_1.app_period) * split_period_1.split_period), $6), COALESCE(((od_1.app_period * o_1.app_period) * split_period_1.split_period), ((od_1.app_period * o_1.app_period) * split_period_1.split_period))
  617. Buffers: shared hit=22614
  618. InitPlan 3 (returns $6)
  619. -> Seq Scan on public.period (cost=0.00..1.07 rows=1 width=16) (actual time=0.003..0.004 rows=1 loops=11307)
  620. Output: period."interval"
  621. Filter: (period.id = ($5).period_id)
  622. Rows Removed by Filter: 5
  623. Buffers: shared hit=11307
  624. -> Result (cost=0.00..0.01 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=11307)
  625. Output: NULL::integer
  626. -> Seq Scan on public.currency_rate_bt cr_1 (cost=0.00..7.86 rows=1 width=56) (actual time=0.045..0.047 rows=1 loops=11307)
  627. Output: cr_1.id, cr_1.rate, cr_1.docdate, cr_1.from_currency_id, cr_1.to_currency_id, cr_1.sid, cr_1.app_period, cr_1.sys_period
  628. Filter: ((cr_1.from_currency_id = (sp_1.*).currency_id) AND (cr_1.app_period @> lower(((od_1.app_period * o_1.app_period) * split_period_1.split_period))) AND (cr_1.sys_period @> sys_time()) AND (cr_1.to_currency_id = app_currency()))
  629. Rows Removed by Filter: 12
  630. Buffers: shared hit=11307
  631. -> Function Scan on public.calc_item_suma cis_1 (cost=0.25..10.25 rows=1000 width=0) (actual time=0.133..0.133 rows=1 loops=11307)
  632. Output: cis_1.item_qty, cis_1.item_price, cis_1.item_cost, cis_1.item_suma
  633. Function Call: calc_item_suma(o_1.*, od_1.*, sp_1.*, ic_1.*)
  634. Buffers: shared hit=33230
  635. Planning time: 6.787 ms
  636. Execution time: 22087.594 ms
  637. (286 rows)
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!