In investigating the planner changes necessary for the append node planning I
described in my other email I noticed something else I find strange.
The min/max optimization which builds an "ORDER BY ... LIMIT 1" type of plan
for min or max works by explicitly building an index path to scan a plain
relation. It has comments saying it's not possible to do this optimization for
most joins and let alone more complex things like subqueries, grouped queries,
or set operations.
I don't understand why it wouldn't work to do it for any arbitrary path for
any query at all as long as it has the correct ordering.
I would have expected the way to do this would be to detect that the min/max
optimization might be applicable early on, in which case we teach
pathkeys_useful_for_ordering about the ordering which would be necessary for
it.
Then when we're finding useful indexes we should automatically accumulate
paths to produce that order. And when we come to produce the final plan we
check if we have an already-ordered path which will produce the required
column and has a startup cost less than the total cost of the cheapest path.
-- Gregory Stark EnterpriseDB http://www.enterprisedb.com