Skip site navigation (1) Skip section navigation (2)

Re: Very strange performance decrease when reusing a PreparedStatement

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: John Lister <john(dot)lister-ps(at)kickstone(dot)com>
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: Very strange performance decrease when reusing a PreparedStatement
Date: 2009-05-03 14:41:36
Message-ID: 11395.1241361696@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-jdbc
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

In response to

Responses

pgsql-jdbc by date

Next:From: Péter KovácsDate: 2009-05-03 16:28:53
Subject: Re: Very strange performance decrease when reusing a PreparedStatement
Previous:From: John ListerDate: 2009-05-03 10:41:03
Subject: Re: getTiIme/Timestamp with TimeZone inconsistency

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group