Message-ID: <4CB572CF.4080709@catalyst.net.nz>
Date: Wed, 13 Oct 2010 21:50:23 +1300
From: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.9.1.12) Gecko/20100915 Thunderbird/3.0.8
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: Slow count(*) again...
References: <201010091626.18636.neil.whelchel@gmail.com>
	<201010122347.20542.neil.whelchel@gmail.com>
	<4CB55D7E.8080902@catalyst.net.nz>
	<201010130138.39893.neil.whelchel@gmail.com>
In-Reply-To: <201010130138.39893.neil.whelchel@gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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