Re: expression index not used within function

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.

David J.




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/expression-index-not-used-within-function-tp5778236p5778242.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.