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

On Fri, Jan 21, 2022 at 5:47 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Dominique Devienne <ddevienne(at)gmail(dot)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