From: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
To: pgsql-performance@postgresql.org
Subject: Re: Slow count(*) again...
Date: Wed, 13 Oct 2010 21:50:23 +1300
Message-ID: <4CB572CF.4080709@catalyst.net.nz> (raw)
In-Reply-To: <201010130138.39893.neil.whelchel@gmail.com>
References: <201010091626.18636.neil.whelchel@gmail.com>
<201010122347.20542.neil.whelchel@gmail.com>
<4CB55D7E.8080902@catalyst.net.nz>
<201010130138.39893.neil.whelchel@gmail.com>
On 13/10/10 21:38, Neil Whelchel wrote:
>
> So with our conclusion pile so far we can deduce that if we were to keep all
> of our data in two column tables (one to link them together, and the other to
> store one column of data), we stand a much better chance of making the entire
> table to be counted fit in RAM, so we simply apply the WHERE clause to a
> specific table as opposed to a column within a wider table... This seems to
> defeat the entire goal of the relational database...
>
>
That is a bit excessive I think - a more reasonable conclusion to draw
is that tables bigger than ram will drop to IO max speed to scan, rather
than DIMM max speed...
There are things you can do to radically improve IO throughput - e.g a
pair of AMC or ARECA 12 slot RAID cards setup RAID 10 and tuned properly
should give you a max sequential throughput of something like 12*100
MB/s = 1.2 GB/s. So your example table (estimated at 2GB) so be able to
be counted by Postgres in about 3-4 seconds...
This assumes a more capable machine than you are testing on I suspect.
Cheers
Mark
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: mark.kirkwood@catalyst.net.nz
Subject: Re: Slow count(*) again...
In-Reply-To: <4CB572CF.4080709@catalyst.net.nz>
* 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