From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | Steven Grimm <sgrimm(at)thesegovia(dot)com> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Inconsistent performance with LIKE and bind variable on long-lived connection |
Date: | 2017-06-11 17:18:48 |
Message-ID: | CAFj8pRBHJBdkk_c3nLvOCKrj5+JQ-BcD=Vu-yZAUSqH=ThNQzQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
2017-06-11 18:34 GMT+02:00 Steven Grimm <sgrimm(at)thesegovia(dot)com>:
> 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
>
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
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Bruno Wolff III | 2017-06-11 19:15:55 | Re: Limiting DB access by role after initial connection? |
Previous Message | Andre Mikulec | 2017-06-11 16:57:10 | trying to program in PostgreSQL C a statistics function |