Re: Including Snapshot Info with Indexes

Gokulakannan Somasundaram wrote:
> As explained, if we are going to include the snapshot with indexes, Vacuum
> will be done on the index independent of the table, so Vacuum will not
> depend on immutability. We need to goto the index from the table, when we
> want to update the snapshot info. The problem on hand is that some of the
> userdefined functions are mutable, whereas the user might mark it immutable.
>
> So my idea is to have a mapping index, with tupleid as the first column and
> the function's values as subsequent columns. I have a somewhat detailed
> design in mind. So there will be a over head of extra 3 I/Os for
> update/delete on indices based on User-defined functions. But this setup
> will speed-up lot of queries where the tables are partitioned and there will
> be more inserts and selects and dropping partitions at periodic intervals.
> Updates become costly by 3 I/Os per Index with snapshot. So if someone has
> more selects than updates+deletes then this index might come handy (ofcourse
> not with user-defined functional indices).

I think you need to explain why that is better than using the Dead Space
Map. We're going to want the DSM anyway, to speed up VACUUMs; enabling
index-only-scans just came as an afterthought. While DSM designed just
for speeding up vacuums might look slightly different than one used for
index-only scans, the infrastructure is roughly the same.

What you're proposing sounds a lot more complex, less space-efficient,
and slower to update. It requires extra action from the DBA, and it
covers exactly the same use case (more selects than updates+deletes, to
use your words). It would require changes to all index access methods,
while the DSM would automatically work with all of them. In particular,
including visibility information in a bitmap index, should we have
bitmap indexes in the future, is impossible, while the DSM approach
would just work.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com