Re: Different execution time from psql and JDBC

Andreas Hartmann wrote:
> Dear postgresql community,
>
> I have a quite complex statement. When I execute it directly via
> psql, the execution time is approx. 2000 ms.
>
> When I execute it via JDBC (Apache Cocoon), the execution time
> is either 600..1000 ms or approx. 10.000 ms, based on a certain
> value in a table.
>
>
> An interesting point is that the value has a big impact on the
> JDBC execution time, but the psql execution time is not affected
> at all.

This suggests to me the problem is with a parametered query. If in psql
I have two queries:
SELECT * FROM people WHERE surname='Huxton';
SELECT * FROM people WHERE surname='Smith';
If the statistics suggest there are many Smiths, then I might get two
different plans.
With a parameterised query:
SELECT * FROM people WHERE surname=?
The planner has to come up with one plan that will suit all cases.

You can simulate this with PREPARE ... EXECUTE from psql - see if that
does it.

> Is there a way to output the query plan (like EXPLAIN ANALYZE)
> in the log files? How can I trace down the problem?

You can turn on DEBUG_PRINT_PARSE - see the runtime configuration for
details. This doesn't exactly produce an EXPLAIN but it will let you
compare the two plans.

--
Richard Huxton
Archonet Ltd