Re: TODO items for window functions

"David Rowley" <dgrowley(at)gmail(dot)com> writes:
> Unsure how difficult it is, maybe another one for a TODO, 8.4 or 8.5 I'm not
> sure:
> * Minimise sorts in a query such as:

I'm not tremendously excited about improving that situation. As the
code stands, the user can control what happens by ordering the WINDOW
clause appropriately, so it's not really a show-stopper. Ideally we'd
do better automatically, but it's not easy in the current planner
structure --- we can only ask query_planner for one target sort order
and there's no good way to determine beforehand which of the possible
targets might be the best choice. So at best this is a "maybe TODO" for
8.5 or later.

I do think the patch has probably left some low-hanging fruit on the
simpler end of the difficulty spectrum, namely when the window stuff
requires only one ordering that could be done either explicitly or
by an indexscan. That choice should ideally be done with a proper
cost comparison taking any LIMIT into account. I think right now
the LIMIT might not be accounted for, or might be considered even
when it shouldn't be because another sort is needed anyway.

But in any case, the tuplestore internal issues are probably the
more significant performance problems for the short term.

regards, tom lane