On 2010-10-23 18:42, Tom Lane wrote:
Jesper Krogh<jes...@krogh.cc> writes:
I have been puzzled about the evaluation order when using window
functions and limit.
It's basically FROM -> WHERE -> window functions -> LIMIT.
I expected it to either count to 3 or blow up and tell me that count(*)
wasn't a window function.
Any aggregate function can be used as a window function. It just
aggregates over the whole partition (which in this case is all 8
rows that satisfy the WHERE).
Thank you for clarifying.
Testing more.. I can see that it does that in all situations, so
it is not that "confusing" anymore. I still think it is hugely
counter intuitive and attached is a documentation patch
that should clarify it a bit.
I would just assume the reverse behaviour would be way closer to
useful for everyone. (say if you want the window function to operate
over the full set, then you wouldn't specify the limit). But that
doesn't help anyone if the SQL-spec specifies it otherwise.
As a sidenote.. the queryplan for some of them seems quite "unoptimal".
# explain select id,last_value(id) over () from testtable order by id
asc limit 3;
QUERY PLAN
-------------------------------------------------------------------------------------------------------
Limit (cost=0.00..1.30 rows=3 width=4)
-> WindowAgg (cost=0.00..6117917.93 rows=14165769 width=4)
-> Index Scan using testtable_pkey on testtable
(cost=0.00..5940845.82 rows=14165769 width=4)
The output is essentially the 3 smallest ids and the largest one in the
table which
all can be found by both a forward and reverse scan on the primary key
index so above
is absolutely not the cheapest way to find the result.
--
Jesper
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 183b72c..7bcbe25 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -10684,6 +10684,30 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;
clause follows the call; otherwise they act as regular aggregates.
</para>
+ <para>
+ The evaluation order may be tricky for Window Functions when used in conjunction
+ with a <literal>LIMIT</> clause. The <literal>WHERE</> clause is evaluated
+ first then the Window Function and last the <literal>LIMIT</> is applied.
+ An example:
+ </para>
+
+<screen>
+# select * from testtable;
+ id | value
+----+-------
+ 1 | 1
+ 2 | 2
+ 3 | 3
+(3 rows)
+
+# select id,max(id) over () from testtable order by id asc limit 2;
+ id | max
+----+-----
+ 1 | 3
+ 2 | 3
+(2 rows)
+</screen>
+
+
<table id="functions-window-table">
<title>General-Purpose Window Functions</title>
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
