> BTW I am still not satisfied by the performance improvement for window
> functions for frames, that was only 40%. I will study the code to look
> for more optimization.
So I come up with more optimization for window functions working on
frames (i.e. first_value, last_value and nth_value). Attached v14
patch does it.
There are 3 major functions used here.
1) window_gettupleslot (get a row)
2) row_is_in_frame (check whether row is in frame or not)
3) ExecEvalExpr (evaluate arg on the row)
In v12 (and v13), we eliminate #3 in some cases but the saving was
only 40%. In v14, I found some cases where we don't need to call
#1. row_is_in_frame requires a row ("tuple" argument), which is
provided by #1. However row_is_in_frame actually uses the row argument
only when frame clause is "RANGE" or "GROUPS" and frame end is
"CURRENT ROW". In other cases it does not use "tuple" argument at
all. So I check the frame clause and the frame end, and if they are
not the case, I can omit #1. Plus if the not null cache for the row
has been already created, we can omit #3 as well. The optimization
contributes to the performance. I observe 2.7x (1k rows case) to 5.2x
(3k rows case) speed up when I compare the performance of v13 patch
and v14 patch using the same script (see attached).
v13:
rows duration (msec)
1000 34.740
2000 91.169
3000 205.847
4000 356.142
5000 557.063
v14:
rows duration (msec)
1000 12.807
2000 21.782
3000 39.248
4000 69.123
5000 101.220
I am not sure how the case where frame clause is "RANGE" or "GROUPS"
and frame end is "CURRENT ROW" is majority of window function use
cases. If it's majority, the optimization in v14 does not help much
because v14 does not optimize the case. However if it's not, the v14
patch is close to commitable form, I think. Comments are welcome.
Best regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp
for i in 1000 2000 3000 4000 5000
do
echo "$i rows: "
pos=`expr $i / 2`
psql -a test <<EOF
\timing
explain analyze
SELECT
x,
nth_value(x,$pos) IGNORE NULLS OVER w
FROM generate_series(1,$i) g(x)
WINDOW w AS (ORDER BY x ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING);
EOF
done | egrep "rows:|Time:" | egrep -v "Planning|Execution"|
sed -e 's/rows: *//' -e 's/Time: //' -e 's/ms//'
