Fabio Ugo Venchiarutti <fabio@vuole.me> writes:
>>> Conversion of the pattern to an index qualification requires that the
>>> pattern be a plan-time constant. STABLE functions, by definition,
>>> are not that.
> I guess it is correct to assume that the same applies to regular
> expressions stored in pl/pgsql variables/arguments then, as they're
> inspected after the plan is cached?
Not necessarily. Recent PG versions will generate custom plans (ie,
plans for the query with parameter values substituted as constants)
if that consistently offers a significant win over the generic plan.
Which it would as long as the parameter value always reduces to a
reasonably long left-anchored pattern.
regards, tom lane