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(at)enterprisedb(dot)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
>