Re: expression index not used within function

Hi,

I have further found that it is only when passing the string in to the
function that the slow response occurs.

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'||'%')

I have also created and indexed a new column to eliminate the expression and
the same happens

Andy

Hi,

I have created an index on an expression as follows:

(replace(lower(my_column), ' '::text, ''::text)

which i use in a WHERE clause against LIKE 'string%'

By using text_pattern_ops i get the index used provided i more than one
character is used in the string.

However, with the same SELECT query running within a function (using RETURNS
TABLE) the query takes significantly longer - as though the index is
ignored. e.g. 2500ms instead of 12ms

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