From: | John Lister <john(dot)lister-ps(at)kickstone(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | John Lister <john(dot)lister-ps(at)kickstone(dot)com>, pgsql-jdbc(at)postgresql(dot)org |
Subject: | Re: Very strange performance decrease when reusing a PreparedStatement |
Date: | 2009-05-03 17:50:45 |
Message-ID: | 49FDD975.1020002@kickstone.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-jdbc |
Tom Lane wrote:
> John Lister <john(dot)lister-ps(at)kickstone(dot)com> writes:
>
>> However it seems that other optimisations can't be made for example it
>> doesn't seem possible to tell the server that parameter 1 is always
>> going to be an int and therefore it should be using index A. The current
>> implementation may not use index A as it is unaware as to the type of
>> the supplied parameter.
>>
>
> I don't think this is true either. The wire protocol certainly provides
> the ability for the client to tell the server what data type a parameter
> has. I don't know whether the JDBC driver makes use of that, but if it
> does not, then something like
> variable = ?
> is going to be treated exactly like
> variable = 'unmarked literal'
> and in both cases the parser's default assumption is that the
> unknown-type value has the same data type as the thing it's being
> compared to. So if the variable is indexed this would always be
> seen as a indexable comparison.
>
The JDBC driver tries to mitigate this by delaying the parse until
execution time when all the query parameters are known.
> There are certainly cases where lack of parameter type information could
> lead to a poor plan, but they are corner cases.
>
>
I was guessing on the server implementation (wrongly probably) based on
a some other posts and comments in the code. My knowledge of the query
planner is limited but i would hope there are only a few cases where
knowing the types isn't sufficient to generate good plans without
knowing the values
JOHN
From | Date | Subject | |
---|---|---|---|
Next Message | John Lister | 2009-05-03 17:53:55 | Re: Unit test patches |
Previous Message | Kris Jurka | 2009-05-03 16:42:52 | Re: Unit test patches |