Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 4EDBF804C6B for ; Wed, 16 Nov 2011 22:27:57 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 83287-04 for ; Thu, 17 Nov 2011 02:27:51 +0000 (UTC) Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id 27CB1804C69 for ; Wed, 16 Nov 2011 22:27:50 -0400 (AST) Received: from ns2.gransy.com ([89.187.132.100] helo=elizabeth.gransy.com) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1RQrhe-0006Aa-8F for pgsql-performance@postgresql.org; Thu, 17 Nov 2011 02:27:50 +0000 Received: from sq.gransy.com (localhost [127.0.0.1]) by elizabeth.gransy.com (Postfix) with ESMTP id 9429115E42BA; Thu, 17 Nov 2011 03:27:35 +0100 (CET) Received: from 78.45.133.255 (SquirrelMail authenticated user tv@fuzzy.cz) by sq.gransy.com with HTTP; Thu, 17 Nov 2011 03:27:35 +0100 Message-ID: <57c79e7822e93c2325245ab4f5606acc.squirrel@sq.gransy.com> In-Reply-To: References: Date: Thu, 17 Nov 2011 03:27:35 +0100 Subject: Re: Performance question 83 GB Table 150 million rows, distinct select From: "Tomas Vondra" To: "Scott Marlowe" Cc: "Tomas Vondra" , "Tory M Blue" , pgsql-performance@postgresql.org User-Agent: SquirrelMail/1.4.21 MIME-Version: 1.0 Content-Type: text/plain;charset=utf-8 X-Priority: 3 (Normal) Importance: Normal Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9 X-Spam-Level: X-Archive-Number: 201111/208 X-Sequence-Number: 45719 On 17 Listopad 2011, 2:57, Scott Marlowe wrote: > On Wed, Nov 16, 2011 at 4:59 PM, Tomas Vondra wrote: > >> But you're right - you're not bound by I/O (although I don't know what >> are >> those 15% - iowait, util or what?). The COUNT(DISTINCT) has to actuall= y >> keep all the distinct values to determine which are actually distinct. > > Actually I meant to comment on this, he is IO bound. Look at % Util, > it's at 99 or 100. > > Also, if you have 16 cores and look at something like vmstat you'll > see 6% wait state. That 6% represents one CPU core waiting for IO, > the other cores will add up the rest to 100%. Aaaah, I keep forgetting about this and I somehow ignored the iostat results too. Yes, he's obviously IO bound. But this actually means the pre-aggregating the data (as I described in m= y previous post) would probably help him even more (less data, less CPU). Tomas