Re: expression index not used within function

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