[Spacewalk-list] Query Continuously Running

[Wojtak, Greg (Superfly)]

When I run an explain and analyze, I get:
spaceschema=# EXPLAIN DELETE FROM rhnPackageChangeLogData WHERE id NOT IN ( SELECT changelog_data_id FROM rhnPackageChangeLogRec );
                                          QUERY PLAN                                           
-----------------------------------------------------------------------------------------------
 Seq Scan on rhnpackagechangelogdata  (cost=126110.35..9410672444.95 rows=113191 width=6)
   Filter: (NOT (SubPlan 1))
   SubPlan 1
     ->  Materialize  (cost=126110.35..196580.21 rows=5067486 width=8)
           ->  Seq Scan on rhnpackagechangelogrec  (cost=0.00..101247.86 rows=5067486 width=8)
(5 rows)

spaceschema=# ANALYZE rhnPackageChangeLogData;
ANALYZE
spaceschema=# ANALYZE rhnPackageChangeLogRec;
ANALYZE

I noticed about a week ago that I haven't been seeing these except every once in a while after I deleted our Solaris systems, channels, patch clusters and disabled Solaris support.  I'm wondering if that was related at all.

-----Original Message-----
From: spacewalk-list-bounces at redhat.com [mailto:spacewalk-list-bounces at redhat.com] On Behalf Of Jan Pazdziora
Sent: Monday, January 16, 2012 2:17 PM
To: spacewalk-list at redhat.com
Subject: Re: [Spacewalk-list] Query Continuously Running

On Thu, Jan 12, 2012 at 03:41:39PM +0000, Wojtak, Greg (Superfly) wrote:
> Spacewalk 1.5 (PostgreSQL) on Cent 6
> 
> I've been seeing a process keep popping up that uses as much cpu as it can get.  It appears to be a delete transaction.  I've run the following query in psql:
> 
>  select current_query from pg_stat_activity where current_query <> '<IDLE>' and current_query not like '%pg_stat_activity%';
> 
> It appears that the query that is hanging is:
> 
>  DELETE FROM rhnPackageChangeLogData
>          WHERE id NOT IN ( SELECT changelog_data_id FROM rhnPackageChangeLogRec )
> 
> Any insight into this?

What does EXPLAIN say about the query?

Do things change if you ANALYZE the tables?

-- 
Jan Pazdziora
Principal Software Engineer, Satellite Engineering, Red Hat

_______________________________________________
Spacewalk-list mailing list
Spacewalk-list at redhat.com
https://www.redhat.com/mailman/listinfo/spacewalk-list