Re: So, is COUNT(*) fast now? - Mailing list pgsql-hackers

From Robert Haas
Subject Re: So, is COUNT(*) fast now?
Date
Msg-id CA+TgmobCY_uU5BC5rpgO8nO-EiKUy3SeSSe6nwEwdtHX1r3sWw@mail.gmail.com
Whole thread Raw
In response to Re: So, is COUNT(*) fast now?  ("Kevin Grittner" <Kevin.Grittner@wicourts.gov>)
Responses Re: So, is COUNT(*) fast now?
List pgsql-hackers
On Mon, Oct 24, 2011 at 2:15 PM, Kevin Grittner
<Kevin.Grittner@wicourts.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


pgsql-hackers by date:

Previous
From: "Kevin Grittner"
Date:
Subject: Re: So, is COUNT(*) fast now?
Next
From: Tom Lane
Date:
Subject: Re: So, is COUNT(*) fast now?