To: Josh Kupershmidt <schmiddy@gmail.com>
cc: pgsql-docs <pgsql-docs@postgresql.org>
Subject: Re: docs update for count(*) and index-only scans 
In-reply-to: 
 <CAK3UJRGTvO_z1Q6GJW=9LKQFBUXQTdJt7JP5RSBKP_UE+QznJQ@mail.gmail.com>
References: 
 <CAK3UJRGh3itWqYDPhogBav=334sEZcy2224eo1e4fwKc+cJhuQ@mail.gmail.com>
	<26833.1320187862@sss.pgh.pa.us>
	<CAK3UJRGTvO_z1Q6GJW=9LKQFBUXQTdJt7JP5RSBKP_UE+QznJQ@mail.gmail.com>
Comments: In-reply-to Josh Kupershmidt <schmiddy@gmail.com>
	message dated "Tue, 01 Nov 2011 19:16:48 -0400"
Date: Tue, 01 Nov 2011 20:07:31 -0400
Message-ID: <370.1320192451@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Josh Kupershmidt <schmiddy@gmail.com> writes:
> On Tue, Nov 1, 2011 at 6:51 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Well, it might need adjustment, but I don't think we should remove it
>> outright.  The people who complain that COUNT(*) is not O(1) are still
>> going to be complaining.  On tables that are not read-mostly, there's
>> no reason to expect that index-only scans will even provide a material
>> speed boost, let alone be close to free.

> But the tone of the existing note suggests that users "may be
> surprised" that our COUNT(*) is slower than other RDBMSs. So I guess
> I'm wondering, are we really still that much slower than our
> competitors for COUNT(*)? Ignoring MyISAM and similar lobotomized
> engines, does any competitor have a much-faster way?

It's the "lobotomized engines" that are the problem, IMO --- people
coming from databases like mysql tend to think count(*) just means
reading a table size counter that the engine has anyway.

I'm not really sure how our index-only scans stack up against the likes
of Oracle.  For a read-mostly table they probably have to do a
comparable amount of work, but I'm not real sure how occasional heap
visits (for us) will compare to occasional rollback segement visits (for
them).  And they may be able to scan their indexes in physical order,
which would help a fair amount.  And the whole code path is probably a
good bit more mature and better optimized on that side of the fence,
too.

			regards, tom lane