Re: So, is COUNT(*) fast now?

On Mon, Oct 24, 2011 at 2:15 PM, Kevin Grittner
<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
>> It doesn't look to me like the mean is above 2 (unless you have
>> many fewer toast tables than I suspect), so trying to optimize
>> many-column cases isn't going to help.
>
> The mean is 2.4 (give or take a little depending on whether you
> include system tables).  I have no idea where the optimization
> becomes worthwhile, but the assertion that most indexes probably
> have a single column worried me.  I'm sure there are databases where
> that is true (especially for those who insist on adding a
> meaningless surrogate key column to every table), but there are many
> where it isn't true.  I would guess that our average of 2.4 is
> higher than most, though.

Keep in mind that the existence of index-only scans is going to
encourage people to try to create covering indexes on columns that
aren't indexed today. It's not clear how much of a win that will be;
for certainly workloads, HOT might make it backfire spectacularly.

But even though Tom's statement that most indexes are one column might
be a slight exaggeration, I suspect it probably is true that the
optimizations he's talking about for large numbers of columns won't
produce any material benefit even for a 3 or 4 column index. Which
makes me think maybe we should focus our efforts elsewhere.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company