Re: difference on execution time between prepared statement in pgAdmin and through JDBC Stack - Mailing list pgsql-jdbc

Try an EXPLAIN ANALYZE, as Dave suggested. For JDBC, just do it for
the full statement. For the psql PREPARE / EXECUTE, you can only
explain analyze the execute, but that should tell us if the plans are
different.
---
Maciek Sakrejda | System Architect | Truviso

1065 E. Hillsdale Blvd., Suite 215
Foster City, CA 94404
(650) 242-3500 Main
www.truviso.com



On Fri, Feb 25, 2011 at 1:51 AM, Lars Feistner
<feistner@uni-heidelberg.de> wrote:
> Hello Maciek,
>
> okay, thanx for the clarifcation.
> Here are the server side logs for the pgAdmin case:
> 2011-02-25 10:42:08 CET LOG:  duration: 98.226 ms  statement: execute t2
> (1,7429);
> 2011-02-25 10:42:08 CET DETAIL:  prepare: prepare t2 (int4, int4) AS
>         SELECT DISTINCT vec.*, s.statistic_difficulty as
> examination_difficulty,
>         s.statistic_discriminatorypower as examination_discriminatorypower,
>         vimc.mediacount,vis.difficulty, vis.discriminatorypower,
> vis.statistic_counter, virc.reviewcount, u.user_surname
>         FROM ims_vexaminationcontent vec
>         LEFT OUTER JOIN ims_statistic s ON vec.item_id = s.object_id AND
> s.examination_id = vec.examination_id
>         LEFT OUTER JOIN ims_vitemmediacount vimc ON (vimc.item_id =
> vec.item_id)
>         LEFT OUTER JOIN ims_vitemstatistic vis ON (vis.item_id =
> vec.item_id)
>         INNER JOIN ims_user u on (author_id = u.user_id)
>         LEFT OUTER JOIN ims_vitemreviewcount virc ON virc.item_id =
> vec.item_id
>         WHERE ((vec.examinationcontentversion_version=$1 AND
> vec.examination_entityid=$2))
>         ORDER BY vec.examinationcontentversion_sequenceno ASC NULLS LAST
>
> Is there a chance to get the execution plan of the statement, when the
> statement is executed through JDBC stack?
>
> I have the same feeling about the edge case here, that is exactly why i am
> writing to this list;-)
> So still the question is how can i find out what is going on?
>
> Yesterday i also looked at the debug level logs of the jdbc driver but i
> found nothinng unusal.
>
> Any more hints?
>
> Thanx in advance
> Lars
>
> On 02/24/2011 07:38 PM, Maciek Sakrejda wrote:
>>
>> A named prepared statement (which you seem to be using through
>> pgAdmin) is *not* the same thing as a PreparedStatement in JDBC. Most
>> pertinently, JDBC is using an unnamed portal to execute the query,
>> which delays planning until the parameters are provided. I don't think
>> this ever happens with a named prepared statement (it would help if
>> you provided server-side logs for the pgAdmin execution as well). The
>> deferred planning behavior typically works to your advantage, but you
>> may have run into some edge case here.
>>
>> Alternately, because the unnamed portal execution actually also does
>> the planning, I presume it's counted in the time of the statement
>> execution, whereas in the pgAdmin case, it looks like you're only
>> timing the execute, which is going against an already-planned (at
>> prepare time) query, so you may be comparing apples to oranges.
>>
>> ---
>> Maciek Sakrejda | System Architect | Truviso
>>
>> 1065 E. Hillsdale Blvd., Suite 215
>> Foster City, CA 94404
>> (650) 242-3500 Main
>> www.truviso.com
>>
>
> --
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Lars Feistner
>
> Kompetenzzentrum für Prüfungen in der Medizin
> Medizinische Fakultät Heidelberg,
> Im Neuenheimer Feld 346, Raum 013
> 69120 Heidelberg
>
> E-Mail: feistner@uni-heidelberg.de
> Fon:   +49-6221-56-8269
> Fax:   +49-6221-56-7175
>
> WWW:   http://www.ims-m.de
>       http://www.kompmed.de
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc
>