Re: Evaluation of secondary sort key.

On Mon, Apr 18, 2011 at 6:25 AM, Jesper Krogh <jesper(at)krogh(dot)cc> wrote:
> Getting the value for the first sortkey and carrying on a closure
> for the rest would mostly (very often) be "optimal" ?

Well that might depend. The input data to the function might be much
larger than the output. Consider the, quite common, idiom of:

order by case when (complex expresssion) 1 when (complex expression) 2 else 3

> It would also enable a select that has to sortkeys to utilize an
> index that only contains the primary sortkey, which is a huge
> negative effect of what's being done today.

This is a separate problem entirely. It would be nice to have a
strategy for ordering that can take advantage of partially ordered
results. It's not hard to see how to do the executor side -- it could
keep a tuplesort for each group and truncate it when the group
changes. As usual the hard part is having the planner figure out
*when* to use it. We have a hard enough time calculating ndistinct for
individual columns -- this would require having an idea of how many
values are present for each major key column.

--
greg