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 searchmatch, geometry FROM postcode
> WHERE (replace(lower(pcode), ' '::text, ''::text)) LIKE
> (replace((lower($1)::text),' '::text,''::text)||'%'::text)
I assume that pcode is of type text.
In that case you could create an index like
CREATE INDEX my_index ON table_name ((replace(lower(pcode), ' '::text, ''::text)) text_pattern_ops);
ANALYZE table_name;
Such an index can be used for queries with a LIKE, if you
have a constant on the right hand side that does not
start with a wildcard.
If you have PostgreSQL 9.2 or later, that might work out of
the box in a PL/pgSQL function.
In doubt, or if you have an older version, first compute
the right hand side and run the query with EXECUTE.
Yours,
Laurenz Albe