Re: Very strange performance decrease when reusing a PreparedStatement

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.

There are certainly cases where lack of parameter type information could
lead to a poor plan, but they are corner cases.

regards, tom lane