Re: Inconsistent performance with LIKE and bind variable on long-lived connection

On Sun, Jun 11, 2017 at 8:21 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Yeah, I've been watching this thread and trying to figure out how to
> explain that part; I suspected a cause of this form but couldn't
> make that theory match the 9-iterations observation. (I still can't.)
>

I walked through the Java code in a debugger just now and have an
explanation for the 5 vs. 9 discrepancy. The JDBC driver keeps a cache of
queries that have been passed to a connection's prepareStatement() method,
and inlines the bind values the first 4 times it sees a query in the hopes
of reducing overhead on one-off queries. So I believe the sequence ends up
being:

1-4: JDBC driver inlines the values, server sees no bind variables
5: JDBC driver prepares the statement, server sees bind variables and tries
generic plan
6+: JDBC driver reuses the existing prepared statement from iteration 5
10: Server has seen the query 5 times before and switches to the custom plan

As for the broader problem, at the risk of being hopelessly naive about how
all this works internally: Could the discrepancy between the estimated and
actual row counts be tracked and fed back into the planner somehow?

-Steve