Re: Weird indices

Ian Lance Taylor wrote:
>
<snip>

> You're right.  The mechanism used to preserve multiple versions of
> heap tuples could be extended to index tuples as well.
>
> Based on the heap tuple implementation, this would require adding two
> transaction ID's and a few flags to each index tuple.  That's not
> insignificant.  In a B-tree, right now, I think there are 8 bytes plus
> the key for each item in the tree.  This would require adding another
> 10 bytes or so.  That's a lot.
>
OK now this is starting to make sense to me.  I think.  I guess I'll
really have to sift throught the code again to figure out the rest.

> Also, more work would be required for every update.  Right now an
> update requires a B-tree insert for each index.  With this change,
> every update would require an additional B-tree lookup and write for
> each index.  That would require on average a bit less than one
> additional block write per index.  That's a lot.
>
> In exchange, certain queries would become faster.  Specifically, any
> query which only needed the information found in an index would become
> faster.  Each such query would save on average a bit less than one
> additional block read per value found in the index.  But since the
> indices would be less efficient, some of the advantage would be lost
> due to extra block reads of the index.
>
> What you are suggesting seems possible, but it does not seem to be
> obviously better.

It may not be as obvious as it first seemed to me, but I bet there are
certain databases out there that have just the right pattern of data
that would benefit from this.  I suppose this is something that
compilers have tried to balance all along.  Maybe there could be a
different type of index that could be manually added by admins who
wanted to fiddle around with their database.
>
> If you feel strongly about this, the most reasonable thing would be
> for you to implement it, and test the results.  Since as far as I can
> see what you are suggesting is not clearly better, it's unlikely that
> anybody else is going to go to the considerable effort of implement it
> on your behalf.
>

<snip>
> Some things could, sure.  It's not obvious to me that many things
> could.  The planner can't spend a lot of time looking at an index to
> decide whether or not to use it.  If it's going to do that, it's
> better off to just decide to use the index in the first place.  Index
> examination is not free.  It requires disk reads just like everything
> else.
>
Not free, but possibly worth it if it saves a seq scan.

> > > I don't think there is any way to do that today.  It would be possible
> > > to implement something along the lines I suggest above.  I have no
> > > idea if the Postgres maintainers have any plans along these lines.
> > >
> > At the end of a transaction, when it sets the bit that this tuple isn't
> > valid, couldn't it at the same time also remove it if was no longer
> > visible to any transaction?  It wouldn't remove the need for vacuum
> > because there may be another transaction that prevents it from being
> > removed right then and there.
>
> Yes, this could be done.  It wouldn't speed things up, though.  In
> fact, it would slow them down.  The only advantage would be that
> VACUUM would be required less often--an advantage which is not
> insignificant.
>
> I would guess that in the average multi-user database less than half
> of the tuples could be deleted at that point.  It would be easy to
> instrument Postgres to test this--why don't you try that?
>

I just might.  I've been thinking of hacking postgres, but for adding
xml support to postgres. That seems to be mostly a matter of parsing.



--
Joseph Shraibman
jks@selectacast.net
Increase signal to noise ratio.  http://www.targabot.com