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(dot)Grittner(at)wicourts(dot)gov> wrote:
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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