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:dev@archonet.com]
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.
****************************************************************************