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
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
