Re: [PERFORM] index not being used when variable is sent

[Jim Nasby]

On Aug 17, 2011, at 1:49 AM, Eyal Wilde wrote:
 1. is there any more elegant solution?

Very possibly, but I'm having a heck of a time trying to figure out what your 
current code is actually doing.

What's the actual problem you're trying to solve here?
--
Jim C. Nasby, Database Architect   j...@nasby.net
512.569.9461 (cell) http://jim.nasby.net



-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] index not being used when variable is sent

[Tom Lane]

Eyal Wilde e...@impactsoft.co.il writes:
 CREATE OR REPLACE FUNCTION test_func(STR text)
 ...
 perform t1.val FROM t1 WHERE
 (COALESCE(rpad(t1.val, 100),'') ) like COALESCE(STR || '%','')
 order by COALESCE(rpad(t1.val, 100), '') using ~~ LIMIT 5;

[ doesn't use index ]

No, it doesn't.  The LIKE index optimization requires the LIKE pattern
to be a constant at plan time, so that the planner can extract the
pattern's fixed prefix.  An expression depending on a function parameter
is certainly not constant.

If you really need this to work, you could use EXECUTE USING so that
the query is re-planned for each execution.

regards, tom lane

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance