From: Tomas Vondra <tv@fuzzy.cz>
To: Scott Marlowe <scott.marlowe@gmail.com>
Cc: Tomas Vondra <tv@fuzzy.cz>
Cc: Tory M Blue <tmblue@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance question 83 GB Table 150 million rows, distinct select
Date: Thu, 17 Nov 2011 03:27:35 +0100
Message-ID: <57c79e7822e93c2325245ab4f5606acc.squirrel@sq.gransy.com> (raw)
In-Reply-To: <CAOR=d=2ZHAf=jubZXXOFOHyrwGqf-UUGwZ6TO0sHjbTzDN6_-Q@mail.gmail.com>
References: <CAEaSS0b7D4eyDi4kt-Dt8u-yGq+akD=5PZwMk_TUHP-zujAnCQ@mail.gmail.com>
<e9ec6f004095262038f7a2b3d9e303e0.squirrel@sq.gransy.com>
<CAOR=d=2ZHAf=jubZXXOFOHyrwGqf-UUGwZ6TO0sHjbTzDN6_-Q@mail.gmail.com>
On 17 Listopad 2011, 2:57, Scott Marlowe wrote:
> On Wed, Nov 16, 2011 at 4:59 PM, Tomas Vondra <tv@fuzzy.cz> 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 actually
>> 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 my
previous post) would probably help him even more (less data, less CPU).
Tomas
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tv@fuzzy.cz, scott.marlowe@gmail.com, tmblue@gmail.com
Subject: Re: Performance question 83 GB Table 150 million rows, distinct select
In-Reply-To: <57c79e7822e93c2325245ab4f5606acc.squirrel@sq.gransy.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox