Richard Huxton wrote:
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.
Thanks for your answer!
Actually I found the cause of the two results differing so much.
The query is executed using a set of values (IN (x1, x2, ..., xn))
I use a LIMIT..OFFSET query to get the value set to invoke the described
query on. With psql, I used always the same set, but with the JDBC
application the query was invoked on different sets, resulting in
different execution times.
When I repeated the test with psql using the correct data, I could
reproduce the difference.
But what bothers me is the overhead generated by the JDBC connection:
JDBC psql
data set A 1000 ms 300 ms
data set B 6000 ms 2000 ms
The JDBC execution times are determined using profiling in my application.
psql seems to be approx. 3 times faster than JDBC ...
Can I do something about this?
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.
Yes, I've tried that, but the verbose query plan was too complicated.
But you're right, I could at least use diff to compare them.
Thanks again!
-- Andreas
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@xxxxxxxxxxxxxx)
