Re: Text-any concatenation volatility acting as optimization barrier

On 02/07/2012 03:18 PM, Marti Raudsepp wrote:
> Hi list,
>
> Andrew Dunstan reported an awkward-seeming case on IRC where shifting
> around a concatenation expression in a view made the planner choose a
> good or a bad execution plan.
>
> Simplified, it boils down to this:
>
> db=# create table foo(i int);
> db=# explain verbose select i from (select i, i::text || 'x' as asd
> from foo) as subq;
> Seq Scan on public.foo (cost=0.00..34.00 rows=2400 width=4)
> Output: foo.i
>
> db=# explain verbose select i from (select i, i || 'x'::text as asd
> from foo) as subq;
> Subquery Scan on subq (cost=0.00..76.00 rows=2400 width=4)
> Output: subq.i
> -> Seq Scan on public.foo (cost=0.00..52.00 rows=2400 width=4)
> Output: foo.i, ((foo.i)::text || 'x'::text)
>
> Case #1 uses the normal textcat(text, text) operator by automatically
> coercing 'x' as text.
> However, case #2 uses the anytextcat(anynonarray, text), which is
> marked as volatile thus acts as an optimization barrier. Later, the
> anytextcat SQL function is inlined and the EXPLAIN VERBOSE output has
> no trace of what happened.
>
> Is this something we can, or want, to fix?
>
> One way would be doing preprocess_expression() before
> pull_up_subqueries() so function inlining happens earlier, but I can't
> imagine what unintended consequences that might have.
>
> Another option would be creating explicit immutable text || foo
> operators for common types, but that sounds pretty hacky.
>

It gets worse if you replace the expression with a call to a (non-sql)
function returning text, which was in fact the original use case. Then
you're pretty much hosed.

cheers

andrew