Message-ID: <4FCD6024.10507@atlassian.com>
Date: Tue, 05 Jun 2012 11:25:56 +1000
From: Trevor Campbell <tcampbell@atlassian.com>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:12.0) Gecko/20120430 Thunderbird/12.0.1
MIME-Version: 1.0
To: Maciek Sakrejda <m.sakrejda@gmail.com>
CC: Vitalii Tymchyshyn <tivv00@gmail.com>,
	Craig James <cjames@emolecules.com>, pgsql-performance@postgresql.org
Subject: Re: Trouble with plan statistics for behaviour for query.
References: <4FC7F0DB.40600@atlassian.com>
	<CAFwQ8rcEyUyhyUHrhQRzJk=DRVYhZT9-mJmvKAPoD8+8_NWG5g@mail.gmail.com>
	<4FC7F844.8070205@atlassian.com>
	<CAFwQ8rf89H+BCvua_EnjtPPK1onrxUA_mMDA8nr9hd5VgQvtcA@mail.gmail.com>
	<4FC80000.2000900@atlassian.com> <4FC8862B.70103@gmail.com>
	<CAOtHd0Bh+LxwQy70wfr9khrKGbz+umZBQDXYOnLLee4ZJ9e9Pw@mail.gmail.com>
In-Reply-To: 
 <CAOtHd0Bh+LxwQy70wfr9khrKGbz+umZBQDXYOnLLee4ZJ9e9Pw@mail.gmail.com>
Content-Type: multipart/alternative;
	boundary="------------070405020307020701090808"

This is a multi-part message in MIME format.
--------------070405020307020701090808
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Thanks for all your help so far.  I have been away for a couple of days so my apologies for not replying earlier.

We are using a third party library to run our SQL via JDBC (not one of the common ones like Hibernate etc), but I have 
been able to dig out the exact statements run in the scenario we are experiencing.

We always run a prepare and then execute a query as follows, we never reuse the prepared statement:

_ps = _connection.prepareStatement(sql, resultSetType, resultSetConcurrency);

// The values here are:
// sql = SELECT CG.ID, CG.issueid, CG.AUTHOR, CG.CREATED, CI.ID, CI.groupid, CI.FIELDTYPE, CI.FIELD, CI.OLDVALUE, 
CI.OLDSTRING, CI.NEWVALUE, CI.NEWSTRING
//                           FROM public.changegroup CG INNER JOIN public.changeitem CI ON CG.ID = CI.groupid
//                           WHERE CG.issueid=? ORDER BY CG.CREATED ASC, CI.ID ASC
// resultSetType = ResultSet.TYPE_FORWARD_ONLY
// resultSetConcurrency =  ResultSet.CONCUR_READ_ONLY

_ps.setLong(1, field);

_rs = _ps.executeQuery();

On 02/06/12 01:38, Maciek Sakrejda wrote:
>> If I am correct, JDBC uses named portal only on the 5th time you use
>> PreparedStatement (configurable). Before it uses unnamed thing that should
>> work as if you did embed the value.
> If this is due to the difference in parameter type information, this
> doesn't have anything to do with named portals.
>
> My guess is that the driver has one idea about parameter types (based
> on either the specific setTypeFoo call or the Java type of the
> parameter passed to setObject), and the server another (based on the
> type information of the CHANGEGROUP.ISSUEID column). Actually, I'm
> rather surprised to see 'real' there: if you're using setObject with a
> Long, I would imagine that turns into a bigint (which I believe the
> server knows how to coerce to numeric). Can you show us your JDBC
> code?

--------------070405020307020701090808
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<html>
  <head>
    <meta content="text/html; charset=ISO-8859-1"
      http-equiv="Content-Type">
  </head>
  <body bgcolor="#FFFFFF" text="#000000">
    <font face="DejaVu Serif">Thanks for all your help so far.&nbsp; I have
      been away for a couple of days so my apologies for not replying
      earlier.</font><br>
    <br>
    We are using a third party library to run our SQL via JDBC (not one
    of the common ones like Hibernate etc), but I have been able to dig
    out the exact statements run in the scenario we are experiencing.<br>
    <br>
    We always run a prepare and then execute a query as follows, we
    never reuse the prepared statement:<br>
    <br>
    _ps = _connection.prepareStatement(sql, resultSetType,
    resultSetConcurrency);&nbsp; <br>
    <br>
    // The values here are:<br>
    // sql = SELECT CG.ID, CG.issueid, CG.AUTHOR, CG.CREATED, CI.ID,
    CI.groupid, CI.FIELDTYPE, CI.FIELD, CI.OLDVALUE, CI.OLDSTRING,
    CI.NEWVALUE, CI.NEWSTRING&nbsp; <br>
    // &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FROM public.changegroup CG INNER JOIN
    public.changeitem CI ON CG.ID = CI.groupid <br>
    // &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; WHERE CG.issueid=? ORDER BY CG.CREATED
    ASC, CI.ID ASC<br>
    // resultSetType = ResultSet.TYPE_FORWARD_ONLY <br>
    // resultSetConcurrency =&nbsp; ResultSet.CONCUR_READ_ONLY<br>
    <br>
    _ps.setLong(1, field);&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <br>
    <br>
    _rs = _ps.executeQuery();<br>
    <br>
    On 02/06/12 01:38, Maciek Sakrejda wrote:
    <blockquote
cite="mid:CAOtHd0Bh+LxwQy70wfr9khrKGbz+umZBQDXYOnLLee4ZJ9e9Pw@mail.gmail.com"
      type="cite">
      <blockquote type="cite">
        <pre wrap="">If I am correct, JDBC uses named portal only on the 5th time you use
PreparedStatement (configurable). Before it uses unnamed thing that should
work as if you did embed the value.
</pre>
      </blockquote>
      <pre wrap="">
If this is due to the difference in parameter type information, this
doesn't have anything to do with named portals.

My guess is that the driver has one idea about parameter types (based
on either the specific setTypeFoo call or the Java type of the
parameter passed to setObject), and the server another (based on the
type information of the CHANGEGROUP.ISSUEID column). Actually, I'm
rather surprised to see 'real' there: if you're using setObject with a
Long, I would imagine that turns into a bigint (which I believe the
server knows how to coerce to numeric). Can you show us your JDBC
code?
</pre>
    </blockquote>
  </body>
</html>

--------------070405020307020701090808--