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

From: Dominique Devienne <ddevienne(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Does PostgreSQL do bind-peeking? Is `col like '%'` optimized-away by the planner?
Date: 2022-01-21 16:38:43
Message-ID: CAFCRh-_3d5M=betpuTgU5JMtDTMbXVGZJYwh+S-k3cMQFqvNug@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David G. Johnston 2022-01-21 16:44:30 Re: Does PostgreSQL do bind-peeking? Is `col like '%'` optimized-away by the planner?
Previous Message Adrian Klaver 2022-01-21 16:33:06 Re: Using a different column name in a foreign table