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

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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~