From: Kevin Kempter <kevin@kevinkempterllc.com>
To: pgsql-performance@postgresql.org
Subject: long-running query - needs tuning
Date: Thu, 23 Aug 2007 11:16:57 -0600
User-Agent: KMail/1.9.7
MIME-Version: 1.0
Content-Disposition: inline
Content-Type: text/plain;
  charset="us-ascii"
Content-Transfer-Encoding: 7bit
Message-Id: <200708231116.58109.kevin@kevinkempterllc.com>

Hi List;

I've just started working with a new client and they have amoung other issues 
with their databases a particular update that basically locks out users. 

The below query was running for over 6 hours this morning and the CPU load had 
climbed to a point where new connections simply hung waiting to connect. We 
had to kill the query to allow business users to connect to the applications 
that connect to the database, thus I could not post an explain analyze.

In any case the query looks like this:

update dat_customer_mailbox_counts
set total_contacts = contacts.ct,
total_contact_users = contacts.dct
from
( select customer_id, count(*) as ct,
count( distinct con.user_id ) as dct
from dat_user_contacts con
group by customer_id        )
contacts where contacts.customer_id = dat_customer_mailbox_counts.customer_id

Here's the latest counts from the system catalogs:

dat_customer_mailbox_counts:       423
dat_user_contacts                           59,469,476


And here's an explain plan:

                                             QUERY PLAN
----------------------------------------------------------------------------------------------------
 Merge Join  (cost=17118858.51..17727442.30 rows=155 width=90)
   Merge Cond: ("outer".customer_id = "inner".customer_id)
   ->  GroupAggregate  (cost=17118772.93..17727347.34 rows=155 width=8)
         ->  Sort  (cost=17118772.93..17270915.95 rows=60857208 width=8)
               Sort Key: con.customer_id
               ->  Seq Scan on dat_user_contacts con  (cost=0.00..7332483.08 
rows=60857208 width=8)
   ->  Sort  (cost=85.57..88.14 rows=1026 width=74)
         Sort Key: dat_customer_mailbox_counts.customer_id
         ->  Seq Scan on dat_customer_mailbox_counts  (cost=0.00..34.26 
rows=1026 width=74)
(9 rows)


Any thoughts, comments, Ideas for debugging, etc would be way helpful...

Thanks in advance.

/Kevin