Joe Love <joe@primoweb.com> writes:
> I'm wondering what type of index would work for this as it is a volatile
> function. Not knowing how PGs optimizer runs, I'm at a loss as to why this
> wouldn't be possible or worth doing. It seems to me that all functions in
> the "select" part of the statement could be calculated at the end of the
> query after the results have been gathered, and even after the sorting had
> been done as long as the column wasn't part of the order by (or perhaps
> group by).
The short answer is that doing so directly contradicts the computational
model defined by the SQL standard, and will break applications that rely
on the current behavior. Since there's already a clear way to write the
query in a way that specifies evaluating the functions after the
sort/limit steps (ie, put the order by/limit in a sub-select), IMHO that's
what you should do, not lobby to make the optimizer reinterpret what you
wrote.
regards, tom lane