Re: index not being used when variable is sent

Поиск
Список
Период
Сортировка
От Tom Lane
Тема Re: index not being used when variable is sent
Дата
Msg-id 25601.1313502058@sss.pgh.pa.us
обсуждение исходный текст
Ответ на index not being used when variable is sent  (Eyal Wilde <eyal@impactsoft.co.il>)
Ответы Re: index not being used when variable is sent  (Eyal Wilde <eyal@impactsoft.co.il>)
Список 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

В списке pgsql-performance по дате отправления:

Предыдущее
От: Eyal Wilde
Дата:
Сообщение: index not being used when variable is sent
Следующее
От: Pin007
Дата:
Сообщение: Re: Streaming replication performance