Re: expression index not used within function

David Johnston <polobo@yahoo.com> writes:
> LPlateAndy wrote
>> When i call SELECT * FROM _function(IN TEXT) it is very slow on my WHERE
>> clause using LIKE($1||'%') but (run in error), very fast when LIKE('some
>> text'||'%')

> The index cannot be used for LIKE ($1 || '%') because there is no way the
> planner can guarantee the value of $1 isn't something like "%mid" which
> would resolve to "LIKE (%mid%)" which is a mid-string search which the index
> will not help with.

> If you place a constant at the front of the like pattern it can use the
> index to get into the region with the matching prefix.

Also, if you use 9.2 or later, the planner should be able to get the
desired result by re-planning the statement each time (so that it can
treat the current value of $1 as a constant).  If this is 9.2+, and
that doesn't seem to be happening, it would be worth presenting a
complete example so that we can diagnose why not.

(Pre-9.2, the traditional advice for forcing a custom plan each time
is to use EXECUTE.  That's not the optimal way anymore, though.)

            regards, tom lane