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

Copy/paste problems -- the first set includes the system tables
except for toast.  User tables would be the difference between the
results below.  Sorry.
-Kevin
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> wrote:
> Tom Lane <tgl@sss.pgh.pa.us> wrote:
>  
>> Yeah, TOAST indexes are 2-column.  It would be best to exclude
>> those from your counts, since it seems pretty unlikely that
>> anyone will care how fast nodeIndexonlyscan.c is for scans on
>> toast tables.
>  
> User indexes (excluding toast):
>  
>  indnatts | count 
> ----------+-------
>         1 |   200
>         2 |   222
>         3 |   155
>         4 |    76
>         5 |    43
>         6 |    13
>         7 |     2
>         9 |     1
> (8 rows)
>  
> System indexes (excluding toast):
>  
>  indnatts | count 
> ----------+-------
>         1 |    46
>         2 |    24
>         3 |     9
>         4 |     5
> (4 rows)
>  
>> 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.
>  
> -Kevin