Re: index not being used when variable is sent - Mailing list pgsql-performance

Eyal Wilde <eyal@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