Re: Including Snapshot Info with Indexes

On 10/9/07, Gokulakannan Somasundaram <gokul007(at)gmail(dot)com> wrote:
>
>
>
> On 10/8/07, Heikki Linnakangas <heikki(at)enterprisedb(dot)com> wrote:
> >
> > Gokulakannan Somasundaram wrote:
> > > I am always slightly late in understanding things. Let me
> > try
> > > to understand the use of DSM. It is a bitmap index on whether all the
> > tuples
> > > in a particular block is visible to all the backends, whether a
> > particular
> > > block contains tuples which are invisible to everyone. But i think
> > this will
> > > get subjected to the same limitations of Bitmap index. Even Oracle
> > suggests
> > > the use of Bitmap index for only data warehousing tables, where the
> > Bitmap
> > > indexes will be dropped and recreated after every bulk load. This is
> > not a
> > > viable alternative for OLTP transactions.
> >
> > Well, it's not quite the same as a bitmap index, though both use a
> > bitmap. You didn't quite get into details on what the limitations are
> > and why it wouldn't be suitable for OLTP, but I don't see any
> > significant problems.
> >
> > > But i think i am late in the game
> > > as i haven't participated in those discussions
> >
> > Better late than never :).
> >
> > > One Bitmap index block usually maps to lot of blocks in the
> > heap.
> > > So locking of one page to update the DSM for update/delete/insert
> > would hit
> > > the concurrency. But again all these are my observation w.r.t oracle
> > bitmap
> > > indexes. May be i am missing something in DSM.
> >
> > Yeah, the DSM page could become a contention bottleneck. My current
> > thinking is that we'd have a flag in the heap page header, that would be
> >
> > set together with the bit in the DSM. When the flag in the page header
> > is set, you don't need to lock and update the DSM because you know the
> > bit is already set. Vacuum would have to clear both the DSM bit and the
> > flag.
>
>
> It matters to us, where the index scan will goto. If the Index Scan is
> going to touch DSM for understanding visibility(This might degrade the
> performance of some of the index scans, if they have to wait to acquire the
> share lock, and learn that they have to goto the heap to understand their
> visibility requirements.) In the mean while, if the vacuum,
> inserts/updates/deletes are holding the BUFFER_EXCLUSIVE lock on that, this
> would hurt the Select transactions. Since there is only one bit per block in
> the DSM(best case), there might be one DSM block per 8000 table blocks. All
> the transactions which are accessing the 8000 blocks will be waiting on this
> one DSM block. If we are going to update the Heap page header and asking
> the Indexscan to refer to that, then there is no reduction in random I/Os.
> Can't we say that if the snapshot info is embedded with index, we can avoid
> all these difficulties? Most importantly it won't affect the performance of
> current postgres in any way.
>
> > Let's take up Retail Vacuuming again. The User defined
> > function
> > > which would return different values at different time can be
> > classified as
> > > non-deterministic functions. We can say that this index cannot be
> > created
> > > on a non-deterministic function. This is the way it is implemented in
> > > Oracle. What they have done is they have classified certain built-in
> > > operators and functions as deterministic. Similarly they have
> > classified a
> > > few as non-deterministic operators and functions. Can we follow a
> > similar
> > > approach?
> >
> > We already do. A function must be marked as IMMUTABLE in order to use it
> > in an index expression. But we can't enforce that the user defined
> > function really behaves like an immutable function should. If someone
> > creates a user-defined function in C that calls the C random() function,
> > we can't stop it.
>
>
> A function is said to be deterministic, if it returns the same value,
> irrespective of how many times, it is invoked. I think this definition
> clearly puts the random function under the non-deterministic category. If we
> have such a classification, do you think we can resolve this issue?
>

If we frame a set of guidelines/test procedure, do you think it might solve
the issue? Even, if we don't allow this type of indexing to anything other
than built-in deterministic functions, i feel it would serve most of the
indexing requirements.

As I said earlier, using an index like that will of course lead to bogus
> > results. But it won't currently cause any server crashes or more serious
> >
> > corruption.
>
>
>
> One more final word on unique indexes. Whenever we are doing an update,
> there will be insertions into the unique indexes which will trigger table
> lookups. Ofcourse there is more probability, that the table block would be
> in memory(un-pinned). Still contention for a shared resource is avoided, if
> the snapshot info is stored with the indexes.
>
> Let me get one more clarification, what would be type of performance
> results with this implementation, that would encourage the hackers community
> to accept the extra maintenance overhead.
>
> Thanks,
> Gokul.
>
>