Re: Very strange performance decrease when reusing a PreparedStatement

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