Listmail <lists@peufeu.com> writes:
>> Any idea why using a variable v_streetName instead of a string 'wynn%'
>> behaves differently?
> Yeah.
> 'wynn%' doesn't start with % so LIKE uses index.
> But Postgres doesn't know that you know that the contents of this
> variable never starts with '%'...
Even more to the point: the transformation of LIKE 'pattern' into an
index range is a plan-time optimization, so if the planner hasn't got a
constant pattern to work with, it'll never happen. (Yeah, this should
be improved someday.)
If you need to depend on LIKE indexing with non-constant patterns,
you'll have to use EXECUTE to force the query to be replanned each time.
regards, tom lane