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
On Fri, Jan 21, 2022 at 9:36 AM Dominique Devienne <ddevienne@gmail.com>
wrote:
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.
A prepared statement either generates a custom plan and, as a side-effect,
does bind-peeking, or it uses the single prepared plan it has established
and executes that. It doesn't use bind-peeking to decide among multiple
saved prepared plans. There is more to it than that, like a 5 custom plan
threshold before abandoning bind-peeking (I think I got that right...).
David J.
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