On Sat, May 15, 2010 at 9:51 PM, Tom Lane <t...@sss.pgh.pa.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
