Re: Evaluation of secondary sort key.

On 2011-04-18 11:00, Greg Stark wrote:
> On Mon, Apr 18, 2011 at 6:25 AM, Jesper Krogh<jesper@krogh.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

How come that expression be relevant? There is only one sortkey and no
limit, so no matter what it should clearly get the full resultset in all 
cases.

>> 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.

Yes, as with all other cases it would be hard to get the optimum, but
there is also cases where it is straightforward, say when the secondary
sort column has an ndistinct of -1 (or similar close to). The current 
standard
assumption is that 2 columns are unrelated, that would also work here. 
(As good as is
does similar places in PG).

Jesper

-- 
Jesper