Re: Including Snapshot Info with Indexes

Hi All,
         Last mail was sent by mistake without completion. I apologize for that. i am continuing on that.

So i think we are clear now, that it is possible to have an index with snapshot info.  Let me try to enumerate the uses of having the Index with snapshot info, in comparison to the Dead Space Map.

a) Dead Space, if it is successfull in its implementation of what it claims, will have the means to point out that all the tuples of certain chunks are frozen for registered relations and registered chunks. There would be lot of blocks which won't fall under this category.
              i) For example, if the records are newly inserted, that block can't be marked as containing all frozen tuples.
             ii) Imagine the case where there is a batch job / Huge select query running in a enterprise for more than 6hrs. All the blocks which have got inserted into the tables, during this period might not be able to get the advantage of DSM

On 10/11/07, Heikki Linnakangas < heikki@enterprisedb.com> wrote:

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