From: Trevor Campbell <tcampbell@atlassian.com>
To: Maciek Sakrejda <m.sakrejda@gmail.com>
Cc: Vitalii Tymchyshyn <tivv00@gmail.com>
Cc: Craig James <cjames@emolecules.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Trouble with plan statistics for behaviour for query.
Date: Tue, 05 Jun 2012 11:25:56 +1000
Message-ID: <4FCD6024.10507@atlassian.com> (raw)
In-Reply-To: <CAOtHd0Bh+LxwQy70wfr9khrKGbz+umZBQDXYOnLLee4ZJ9e9Pw@mail.gmail.com>
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>
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?
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tcampbell@atlassian.com, m.sakrejda@gmail.com, tivv00@gmail.com, cjames@emolecules.com
Subject: Re: Trouble with plan statistics for behaviour for query.
In-Reply-To: <4FCD6024.10507@atlassian.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox