Re: expression index not used within function

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 within a function (using RETURNS
TABLE) the query takes significantly longer - as though the index is
ignored. e.g. 2500ms instead of 12ms

Is there something fundamental i'm missing about the use of the indexed
expression?

Thanks!

Andy

--
View this message in context: http://postgresql.1045698.n5.nabble.com/expression-index-not-used-within-function-tp5778236.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

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 and indexed a new column to eliminate the expression and
the same happens

Andy

Hi,

I have created an index on an expression 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 within a function (using RETURNS
TABLE) the query takes significantly longer - as though the index is
ignored. e.g. 2500ms instead of 12ms

--
View this message in context: http://postgresql.1045698.n5.nabble.com/expression-index-not-used-within-function-tp5778236p5778241.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

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 something like "%mid" which
would resolve to "LIKE (%mid%)" which is a mid-string search which the index
will not help with.

If you place a constant at the front of the like pattern it can use the
index to get into the region with the matching prefix.

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/expression-index-not-used-within-function-tp5778236p5778242.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

David Johnston <polobo(at)yahoo(dot)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
> planner can guarantee the value of $1 isn't something like "%mid" which
> would resolve to "LIKE (%mid%)" which is a mid-string search which the index
> will not help with.

> If you place a constant at the front of the like pattern it can use the
> index to get into the region with the matching prefix.

Also, if you use 9.2 or later, the planner should be able to get the
desired result by re-planning the statement each time (so that it can
treat the current value of $1 as a constant). If this is 9.2+, and
that doesn't seem to be happening, it would be worth presenting a
complete example so that we can diagnose why not.

(Pre-9.2, the traditional advice for forcing a custom plan each time
is to use EXECUTE. That's not the optimal way anymore, though.)

regards, tom lane

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 in context: http://postgresql.1045698.n5.nabble.com/expression-index-not-used-within-function-tp5778236p5778319.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

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):
================================

CREATE TABLE postcode
(
gid serial NOT NULL,
pcode text,
e integer,
n integer,
geometry geometry(Geometry,27700),
CONSTRAINT postcode_pkey PRIMARY KEY (gid)
)
WITH (
OIDS=FALSE
);
ALTER TABLE postcode
OWNER TO postgres;

================================
TABLE INDEX:
================================

CREATE INDEX idx_postcode_lc_pcode
ON postcode
USING btree
(replace(lower(pcode), ' '::text, ''::text) COLLATE pg_catalog."default"
text_pattern_ops);

================================
SELECT FUNCTION:
================================

CREATE OR REPLACE FUNCTION _search_pcode(IN text)
RETURNS TABLE(searchmatch text, geometry geometry) AS
$BODY$
SELECT pcode searchmatch, geometry FROM postcode
WHERE (replace(lower(pcode), ' '::text, ''::text)) LIKE
(replace((lower($1)::text),' '::text,''::text)||'%'::text)
LIMIT 20;

$BODY$
LANGUAGE sql IMMUTABLE SECURITY DEFINER
COST 100
ROWS 1000;
ALTER FUNCTION _search_pcode(text)
OWNER TO postgres;
GRANT EXECUTE ON FUNCTION _search_pcode(text) TO public;
GRANT EXECUTE ON FUNCTION _search_pcode(text) TO postgres;

--
View this message in context: http://postgresql.1045698.n5.nabble.com/expression-index-not-used-within-function-tp5778236p5778321.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

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),' '::text,''::text)||'%'::text)

Thanks

Andy

--
View this message in context: http://postgresql.1045698.n5.nabble.com/expression-index-not-used-within-function-tp5778236p5778927.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

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