Re: LIMIT/SORT optimization

"Simon Riggs" <simon@2ndquadrant.com> writes:

> On Wed, 2007-02-07 at 10:49 +0000, Gregory Stark wrote:
> > The two open issues (which are arguably the same issue) is how to get
> > the information down to the sort node and how to cost the plan.
> > Currently it's a bit of a hack: the Limit node peeks at its child and
> > if it's a sort it calls a special function to provide the limit.
>
> We can't lose the LIMIT node altogether, in case we have a paramterised
> limit or a limit expression, so it does need to be in the executor.

Right. The LIMIT node also implements offset and handles tricky border cases
such as cursors that move past the edges. It would be pointless to duplicate
the logic in tuplesort.c. The idea is to advise tuplesort.c when it can save
work by not sorting more work than necessary, not duplicate the work of Limit.

> Exploiting knowledge about adjacent plan types is already used in the
> executor. If this seemed like it might be a generic trick, then I'd say
> implement a generic function, but I don't see that it is.
>
> We still want to push LIMIT/Sort down through an APPEND, but this won't
> help us here - we'd need to do that in the planner.

Hm, that's exactly the type of situation I was afraid of needing to have the
information to propagate farther than an immediate child node and with more
sophisticated rules. However as you point out that can be handled by doing
optimizations that modify the plan tree. That keeps the scope of the
optimization to a minimum: sort nodes directly under limit nodes. That's
probably a better approach.

--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com