2017-06-11 18:34 GMT+02:00 Steven Grimm <sgr...@thesegovia.com>: > On Sun, Jun 11, 2017 at 8:21 AM, Tom Lane <t...@sss.pgh.pa.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 >
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 > >
