Search Postgresql Archives

Re: expression index not used within function

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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





[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux