Message-ID: <4CB61F7B.4030404@catalyst.net.nz>
Date: Thu, 14 Oct 2010 10:07:07 +1300
From: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.1.12) Gecko/20100915 Lightning/1.0b1 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>
	<201010130138.39893.neil.whelchel@gmail.com>
	<4CB572CF.4080709@catalyst.net.nz>
	<201010130316.12922.neil.whelchel@gmail.com>
In-Reply-To: <201010130316.12922.neil.whelchel@gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 13/10/10 23:16, Neil Whelchel wrote:
>    
> The good ol' bruit force approach! I knew I'd see this one sooner or later.
> Though I was not sure if I was going to see the 16TB of RAM suggestion first.
> Seriously though, as the title of this thread suggests, everything is
> relative. Sure count(*) and everything else will work faster with more system
> power. It just seems to me that count(*) is slower than it could be given a
> set of conditions....
>
> Since the thread has started, I have had people ask about different system
> configurations, especially the filesystem (XFS, ext4...). I have never tested
> ext4, and since we are all involved here, I thought that I could do so and
> share my results for others, that is why I got into time testing stuff.
> Time testing count(*) in my later postings is really not the point as count is
> simply dragging the entire table off of the RAID through RAM, I can use any
> other function like max()... No that can narrow down its scan with an INDEX...
> Ok, sum(), there we go!
>
>
>    

Well in some (quite common) use cases, the queries cannot be known in 
advance, and the tables are considerably bigger than ram... this makes 
the fast IO a good option - sometimes better (and in the end cheaper) 
than trying to maintain every conceivable covering index.

Of course it would be great if Postgres could use the indexes alone to 
execute certain queries - we may see some of that capability in the next 
few release (keep and eye on messages concerning the 'Visibility Map').

regards

Mark