Thread: 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@yahoo.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