From: lakkireddy <srinivas.lakkireddy@emc.com>
To: pgsql-admin@postgresql.org
Subject: Re: Query is stuck
Date: Wed, 22 May 2013 03:04:55 -0700 (PDT)
Message-ID: <1369217095354-5756437.post@n5.nabble.com> (raw)
In-Reply-To: <4BC70633020000250003095B@gw.wicourts.gov>
References: <3A8C969225424C4D8E6BEE65ED8552DA0119F188@XMB-BGL-41C.cisco.com>
<4BC724A7.5070301@setonhome.org>
<4BC70633020000250003095B@gw.wicourts.gov>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
I have a similar problem where my query is getting stuck for where long time.
Some times it is getting completed in 30-35 mins and some times it is
running for 5-6 days. This is happening from my application which is running
on tomcat-5-5-33. If I try the same query from psql console this query gets
completed in 30 sec to 1 minute.
OS: Linux 5.8 - 64 bit
Postgresql version: 8.4.7
The query is:
SELECT encode(audit_history.history_id,'hex') from audit_history join
(select config_state_id, max(compliance_audit_time)
as enforcement_time from audit_history group by config_state_id)A on
(audit_history.compliance_audit_time = A.enforcement_time AND
audit_history.config_state_id = A.config_state_id) left join
enforcement_trail on
(audit_history.history_id = enforcement_trail.history_id) where
enforcement_trail.history_id is null
LIMIT 500;
The query plan is:
QUERY PLAN
---------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------
Limit (cost=663730.68..765365.22 rows=1 width=17)
-> Nested Loop Anti Join (cost=663730.68..765365.22 rows=1 width=17)
-> Hash Join (cost=663730.68..765357.10 rows=1 width=17)
Hash Cond: ((a.enforcement_time =
mydb.audit_history.compliance_audit_time)
AND (a.config_state_id = mydb.audit_history.config_state_id))
-> Subquery Scan a (cost=456217.60..471694.21 rows=28395
width=40)
-> GroupAggregate (cost=456217.60..471410.26
rows=28395 width=25)
-> Sort (cost=456217.60..461163.51 rows=1978363
width=25)
Sort Key:
mydb.audit_history.config_state_id
-> Seq Scan on audit_history
(cost=0.00..161415.63 rows=1978363 width=25)
-> Hash (cost=161415.63..161415.63 rows=1978363 width=42)
-> Seq Scan on audit_history (cost=0.00..161415.63
rows=1978363 width=42
)
-> Index Scan using cm_rpt_enforcment_trail_fkindex1 on
enforcement_trail (cost=0.00..8.10 rows=1 width=17)
Index Cond: (mydb.audit_history.history_id =
enforcement_trail.history_id)
This issue is happening only one customer setup and we don't see this issue
in other linux setups.
In general this query gets completed in seconds whether it is from tomcat or
from psql console.
The 'historyid' column is a bytearray which we are encoding with hex.
I have tried reindxing both the tables used in the query but no luck.
stract_output.txt
<http://postgresql.1045698.n5.nabble.com/file/n5756437/stract_output.txt;
Please let me know what is the issue here.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Query-is-stuck-tp2093817p5756437.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: srinivas.lakkireddy@emc.com
Subject: Re: Query is stuck
In-Reply-To: <1369217095354-5756437.post@n5.nabble.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox