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.