Re: Improving count(*)

mark(at)mark(dot)mielke(dot)cc wrote:
> Probably obvious, and already mentioned, count(*) isn't the only query
> that would benefit from visibility information in the index. It's
> rather unfortunate that MVCC requires table lookups, when all values
> queried or matched are found in the index key itself. The idea of an
> all index table is appealing to me for some applications (Oracle
> supports this, I believe?). In effect, a sorted, and searchable table,
> that doesn't double in size, just because it is indexed.
>

I've been thinking about that lately also. It seems like it would be
useful to have the entire table in a Btree in some situations, but there
are some drawbacks:
(1) probably hard to implement
(2) only works with one key
(3) since tuples would not be at a fixed location on disk, you can't
just use a noraml secondary index. The secondary index would have to
point to the key of the tuple in the Btree table, and then do another
lookup in the actual table.
(4) of course, insert performance goes down due to btree maintenence

Range queries (or queries on equality when there are many duplicates)
might benefit a lot. But I would think that in many situations, the fact
that you could only have one key indexed on the table would counteract
those benefits.

I haven't noticed any recent comments by the hackers on this subject.
Maybe they have some more details? I think MS SQL has something similar
to that also.

Regards,
Jeff Davis