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(at)selectacast(dot)net
Increase signal to noise ratio. http://www.targabot.com