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

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

Dominique Devienne <ddevienne@gmail.com> writes:
> Will the query planner be able to *peek* into the args, and turn the
> `colN like $N`
> into a no-op?

No.  It would not do that even if the pattern were constant '%';
it doesn't know that much about that particular function.

There is a notion of "custom plans" in which parameter values are
inserted as constants, precisely to allow simplifications based on
known constant values.  But this particular case isn't implemented.

I am entirely unfamiliar with the terminology "bind-peeking", so
I can't say whether that's effectively the same thing as our
custom plans.

            regards, tom lane

On Fri, Jan 21, 2022 at 5:47 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Dominique Devienne <ddevienne@gmail.com> writes:
> > Will the query planner be able to *peek* into the args, and turn `colN like $N` into a no-op?

Thanks for the replies, David and Tom.

> No.  It would not do that even if the pattern were constant '%';
> it doesn't know that much about that particular function.

Interesting. Thanks.

> There is a notion of "custom plans" in which parameter values are
> inserted as constants, precisely to allow simplifications based on
> known constant values.  But this particular case isn't implemented.

Where can I read more about this? And is it something the client has
any influence on?

> I am entirely unfamiliar with the terminology "bind-peeking"

I thought it was "standard speak" in the SQL world :)
I don't recall where I picked that up, to be honest.

Here's what seems like an official use in the Oracle PL/SQL doc:
https://oracle.readthedocs.io/en/latest/plsql/bind/bind-peeking.html

But that link does imply that what I thought could happen in Oracle,
i.e. that it could keep several plans for the same prepared statement,
that it would select at runtime based on the actual binds, is NOT
happening in Oracle either...

Thanks, --DD

Dominique Devienne <ddevienne@gmail.com> writes:
> On Fri, Jan 21, 2022 at 5:47 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> There is a notion of "custom plans" in which parameter values are
>> inserted as constants, precisely to allow simplifications based on
>> known constant values.  But this particular case isn't implemented.

> Where can I read more about this? And is it something the client has
> any influence on?

Start here:

https://www.postgresql.org/docs/current/sql-prepare.html

https://www.postgresql.org/docs/current/plpgsql-implementation.html#PLPGSQL-PLAN-CACHING

            regards, tom lane