LPlateAndy wrote:
Just wondering what kind of execute statement (within a function) i should
use to force the planner to use the index for the following?:
You cannot force anything.
The best you can do is to provide an index that *can* be used
and keep your statistics accurate.
SELECT pcode
Hi,
Just wondering what kind of execute statement (within a function) i should
use to force the planner to use the index for the following?:
SELECT pcode searchmatch, geometry FROM postcode
WHERE (replace(lower(pcode), ' '::text, ''::text)) LIKE
(replace((lower($1)::text),'
Hi David,
Thanks, i can see the logic there. To place a constant in front, i tried
putting 'pc'||pcode in to the index and 'pc'||$1 in to the WHERE clause. It
had no effect - does the planner see this as a cheat and i need to actually
prefix the data in the tables?
Andy
--
View this message
Thanks Tom. I'll try the EXECUTE method as well but my dev environment is
9.2 and the planner doesn't seem to be including the index so following are
the fairly basic table/index/function details. Thanks, Andy:
TABLE (circa 300,000 rows):
Hi,
I have created an index 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
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
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
David Johnston pol...@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
