I am running Postgres 8.0.1 on a Windows 2000 server as a service, and the query returns appropriate values after about 30 seconds when executed from a psql console. However, when running this query from a Java application on the same machine through the postgres JDBC driver, the Java app hangs on st.executeQuery(). The query shows up in pg_stat_activity for about 3 minutes then goes away, but the process referenced by the pg_stat_activity remains active and consumes 50% of the CPU resources indefinitely until I kill it off. I let it run for over an hour yesterday. I can reproduce this every time I run this query.
Two things you should start with:
1. Turn query-logging on in your postgresql.conf so we can see exactly what is happening
2. See if you can simulate this using PREPARE/EXECUTE for the query.
I'm guessing that the Java side of things is producing a prepared query and substituting parameters in. That might produce a different plan.
First thing is to turn query logging on though, that way you can check exactly what is being executed.
--
Richard Huxton
Archonet Ltd
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
