From: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
To: pgsql-performance@postgresql.org
Subject: Re: Slow count(*) again...
Date: Thu, 14 Oct 2010 10:07:07 +1300
Message-ID: <4CB61F7B.4030404@catalyst.net.nz> (raw)
In-Reply-To: <201010130316.12922.neil.whelchel@gmail.com>
References: <201010091626.18636.neil.whelchel@gmail.com>
<201010130138.39893.neil.whelchel@gmail.com>
<4CB572CF.4080709@catalyst.net.nz>
<201010130316.12922.neil.whelchel@gmail.com>
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
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: <4CB61F7B.4030404@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