Re: Unexpected planner behavior with *_pattern_ops index matching

Fabio Ugo Venchiarutti <fabio(at)vuole(dot)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