Re: Query in function not using index...

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