Re: Performance problem in textanycat/anytextcat

Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com> writes:
> Tom Lane wrote:
>> So I think that labeling textanycat/anytextcat as immutable was a
>> thinko, and we instead ought to label them volatile so that the planner
>> can inline them no matter what the behavior of the underlying text cast
>> is.

> That feels backwards, having to label functions as more volatile than
> they really are, just to allow optimizations elsewhere.

Well, it's also bogus to label functions as less volatile than they
really are. An error in that direction is unsafe, while marking a
function as more volatile than it truly is cannot result in wrong
behavior.

> Marking textanycat as not immutable would forbid using it in
> expression indexes, too.

True. On the other hand, the current state of affairs allows one to
create an index on expressions that aren't really immutable, with
ensuing hilarity.

The basic problem here is that these functions are polymorphic and their
actual volatility can vary depending on the argument datatype. We don't
have any way to describe that in the present pg_proc definition. It
does seem like we ought to think about improving this, but that's
clearly a research project. In terms of what we could reasonably do
for 9.0, I think it's change the volatility marking or nothing ...
and changing the marking looks like the better bet to me.

>> ... The planner will not inline a function if the
>> resulting expression would be more volatile than the function claims
>> itself to be, because sometimes the point of such a function is to
>> hide the expression's volatility. ...

> Could we inline the function anyway, if it came from an operator?

No, that's just a crock with no semantic justification at all.

regards, tom lane