My coworkers and I studied the EXPLAIN and EXPLAIN ANALYZE output for this query and determined that the query planner seemed to be causing problems with this query. By calling "set enable_nestloop = off" before executing our query we were able to get the execution time down to under 2 minutes, which is acceptable for our purposes.
Thanks for your response- Bjorn -----Original Message----- From: Richard Huxton [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 30, 2005 1:31 AM To: Peterson, Bjorn Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Zombie processes Peterson, Bjorn wrote: > > 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 **************************************************************************** This email may contain confidential material. If you were not an intended recipient, Please notify the sender and delete all copies. We may monitor email to and from our network. **************************************************************************** ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
