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
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 |