Re: Performance problem in textanycat/anytextcat

On Sat, May 15, 2010 at 9:51 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> I noticed by accident that there are some cases where the planner fails
> to inline the SQL functions that underlie the || operator for text vs
> non-text cases.  The reason is that these functions are marked
> immutable, but their expansion involves a coercion to text that might
> not be immutable.  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.  In this case, however, we don't want to
> hide the true nature of the expression, and we definitely don't want
> to pay the performance price of calling a SQL function.  That price
> is pretty significant, eg on a rather slow machine I get
>
> regression=# select count(localtimestamp || i::text) from generate_series(1,100000) i;
>  count
> --------
>  100000
> (1 row)
>
> Time: 12512.624 ms
> regression=# update pg_proc set provolatile = 'v' where oid = 2004;
> UPDATE 1
> Time: 7.104 ms
> regression=# select count(localtimestamp || i::text) from generate_series(1,100000) i;
>  count
> --------
>  100000
> (1 row)
>
> Time: 4967.086 ms
>
> so the added overhead more than doubles the cost of this case.
>
> There's also a possibility of an outright wrong behavior, since the
> immutable marking will allow the concatenation of two constants to
> be folded to a constant in contexts where perhaps it shouldn't be.
> Continuing the above example, 'now'::timestamp || 'foo' will be folded
> to a constant on sight, which is wrong because the coercion to text
> depends on DateStyle and ought to react to a later change in DateStyle.
>
> 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.

Couldn't you apply this argument to any built-in immutable function whatsoever?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company