Does PostgreSQL do bind-peeking? Is `col like '%'` optimized-away by the planner?

Поиск
Список
Период
Сортировка
От Dominique Devienne
Тема Does PostgreSQL do bind-peeking? Is `col like '%'` optimized-away by the planner?
Дата
Msg-id CAFCRh-_3d5M=betpuTgU5JMtDTMbXVGZJYwh+S-k3cMQFqvNug@mail.gmail.com
обсуждение исходный текст
Ответы Re: Does PostgreSQL do bind-peeking? Is `col like '%'` optimized-away by the planner?
Re: Does PostgreSQL do bind-peeking? Is `col like '%'` optimized-away by the planner?
Список pgsql-general
Hi,

I just saw some code of ours that takes 4 strings are arguments,
and wants to do optional filtering on those, in a SELECT statement.
Something like:
```
void foo(string arg1, string arg2, ...) {
    ... = exec(
        conn, "SELECT * from tab where col1 like $1 and col2 like $2 and ...",
        arg1.empty()? "%": arg1, arg2.empty()? "%": arg2, ...
    );
}
```
where the exec() helper does proper binding of the argN strings.

Will the query planner be able to *peek* into the args, and turn the
`colN like $N`
into a no-op? Note that in this case, this is *not* a prepared
statement at the moment,
but it could be in the future.

So I guess my question can also be viewed as whether it's worth
preparing several statements
for the various cases of empty argN strings, or does the planner do
*bind-peeking*, and thus a single prepared statement would do the job,
and still have different plans used depending on the actual binds?

I'm assuming PostgreSQL does bind-peeking like Oracle, but I don't
know, and I've never read anything yet about that.

Thanks, --DD



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

Предыдущее
От: Adrian Klaver
Дата:
Сообщение: Re: Using a different column name in a foreign table
Следующее
От: "David G. Johnston"
Дата:
Сообщение: Re: Does PostgreSQL do bind-peeking? Is `col like '%'` optimized-away by the planner?