Re: Evaluation of secondary sort key.

On 2011-04-09 18:54, Tom Lane wrote:
> I think that would be a positive disimprovement. The current design
> guarantees that volatile sort expressions are evaluated exactly once,
> in the order the rows are read from the data source. There would be no
> guarantees at all, either as to the number of evaluations or the order
> in which they happen, if we tried to do evaluation only during the
> actual sort.
>
> Another small problem is that any such thing would require carrying
> along some kind of closure (ie, the expression and all its input
> values), not just the final sort key value, in tuples being sorted.
> The ensuing complexity, speed penalty, and increase in data volume
> to be sorted would be paid by everybody, making this probably a net
> performance loss when considered across all applications.
The current approach gives that:

select id from test1 where <some clause that matches say 10% random by
another index>
order by sortfunc1(id),sortfunc(2) limit 20;

on a table with 100.000 elements will also end up applying
both sortfunc1(id) and sortfunc2(id) to all 10.000 elements
even though sortfunc2(id) might only brings value to a very few amount
of tuples (the ones needed as secondary sortkeys for top20 within
the dataset).

It might be worth noting in the manual, that if at all possible you should
stuff the sortfunc2(id) into the table as a column (perhaps computed by
a before
trigger), since it might actully be evaluated way more often than
you anticipated.

Thanks a lot for the insight.

Jesper
--
Jesper