Re: Query is stuck

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.