Richard- The prepare/execute problem would make sense to me, except that all of the parameters in the query are integers and are being appended to the actual query string (for various reasons) prior to calling prepareStatement() rather than being set as parameters. The planner should have everything right from the start. Does this seem to you to be a java caveat, or is it an area where the query planner could be improved?
Thanks- Bjorn -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Peterson, Bjorn Sent: Wednesday, March 30, 2005 8:25 AM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] Zombie processes 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 **************************************************************************** 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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
