Re: Window functions patch v04 for the September commit fest

>> Also, current implementation has only a type of plan which uses sort
>> operation. It should be optimized by re-position the windows and/or
>> using hashtable.
>
> I would like to see some performance test results also. It would be good
> to know whether they are fast/slow etc.. It will definitely help the
> case for inclusion if they are faster than alternative multi-statement
> approaches to solving the basic data access problems.
>

Just for the report, I attach the result I have tested today. You see
the result says the current window function is faster than
sort-operated self-join and slower than hashagg-operated self-join.

This test is on the Redhat Linux ES3 Xeon 2.13GHz with 100,000 rows 2
column integers. I wrote simple perl script using psql invoking the
shell so it may contain the invocation overhead overall.

test0 test1 test2 test3 test4 test5
------------------------------------------------------------
689.502 416.633 257.970 1195.294 954.318 1204.292
687.254 447.676 256.629 1075.342 949.711 1154.754
700.602 421.818 260.742 1105.680 926.462 1203.012
736.594 476.388 334.310 1157.818 978.861 1199.944
676.572 418.782 270.270 1060.900 909.474 1175.079
687.260 428.564 257.032 1069.013 1045.387 1275.988
700.252 429.289 263.216 1074.749 1018.968 1273.965
719.478 445.218 258.464 1087.932 1015.744 1273.637
694.865 453.737 261.286 1065.229 1039.941 1262.208
685.756 430.169 258.017 1124.795 1102.055 1297.603
------------------------------------------------------------
697.81 436.83 267.79 1101.68 994.09 1232.05

test0 SELECT sum(amount) OVER (PARTITION BY sector) FROM bench1;
test1 SELECT amount FROM bench1 ORDER BY sector;
test2 SELECT sum(amount) FROM bench1 GROUP BY sector;
test3 SELECT id, amount - avg(amount) OVER (PARTITION BY sector) FROM bench1;
test4 SELECT id, amount - avg FROM bench1 INNER JOIN(SELECT sector,
avg(amount) FROM bench1 GROUP BY sector)t USING(sector)
test5 SET enable_hashagg TO off; SELECT id, amount - avg FROM bench1
INNER JOIN(SELECT sector, avg(amount) FROM bench1 GROUP BY sector)t
USING(sector)

I'll include this test in my docs later.

Regards,

--
Hitoshi Harada