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

Re: Very strange performance decrease when reusing a PreparedStatement

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 (view raw or flat)
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

In response to

pgsql-jdbc by date

Next:From: John ListerDate: 2009-05-03 17:53:55
Subject: Re: Unit test patches
Previous:From: Kris JurkaDate: 2009-05-03 16:42:52
Subject: Re: Unit test patches

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