Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





2017-06-11 18:34 GMT+02:00 Steven Grimm <sgrimm@xxxxxxxxxxxxxx>:
On Sun, Jun 11, 2017 at 8:21 AM, Tom Lane <tgl@xxxxxxxxxxxxx> 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

yes - this is client side prepared statement -  prepareThreshold https://jdbc.postgresql.org/documentation/head/connect.html

Regards

Pavel

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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux