Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id C35B89FA0FC for ; Thu, 23 Aug 2007 15:56:29 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 97218-02-8 for ; Thu, 23 Aug 2007 15:56:22 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 03DD49FA1CD for ; Thu, 23 Aug 2007 15:56:21 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.1/8.14.1) with ESMTP id l7NIuJK6010906; Thu, 23 Aug 2007 14:56:19 -0400 (EDT) To: Kevin Kempter cc: pgsql-performance@postgresql.org Subject: Re: long-running query - needs tuning In-reply-to: <200708231116.58109.kevin@kevinkempterllc.com> References: <200708231116.58109.kevin@kevinkempterllc.com> Comments: In-reply-to Kevin Kempter message dated "Thu, 23 Aug 2007 11:16:57 -0600" Date: Thu, 23 Aug 2007 14:56:19 -0400 Message-ID: <10905.1187895379@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200708/273 X-Sequence-Number: 26403 Kevin Kempter writes: > 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) The planner, at least, thinks that all the time will go into the sort step. Sorting 60M rows is gonna take awhile :-(. What PG version is this? (8.2 has noticeably faster sort code than prior releases...) What have you got work_mem set to? Bad as the sort is, I suspect that the real problem is the count(distinct) operator, which is going to require *another* sort-and-uniq step for each customer_id group --- and judging by the rowcount estimates, at least some of those groups must be pretty large. (AFAIR this time is not counted in the planner estimates.) Again, work_mem would have an effect on how fast that goes. regards, tom lane