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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
