> So I really doubt that anyone would have any enthusiasm for saddling btree
> with a similar mechanism. It's complicated (and has been the cause of
> multiple bugs); it's hard to figure out when is the optimal time to flush
> the pending insertions; and it slows down searches in favor of making
> inserts cheap, which is generally not the way to bet --- if that's the
> tradeoff you want, why not drop the index altogether?
I'm not sure you're right about that. MySQL has a feature called
secondary index buffering:
https://dev.mysql.com/doc/refman/5.0/en/innodb-insert-buffering.html
Now that might not be exactly what we want to do for one reason or
another, but I think it would be silly to think that they implemented
that for any reason other than performance, so there may be some
performance to be gained there.
Consider that on a table with multiple indexes, we've got to insert
into all of them. If it turns out that the first leaf page we need
isn't in shared buffers, we'll wait for it to be read in. We won't
start the second index insertion until we've completed the first one,
and so on. So the whole thing is serial. In the system MySQL has
implemented, the foreground process would proceed unimpeded and any
indexes whose pages were not in the buffer pool would get updated in
the background.
Ignoring for the moment the complexities of whether they've got the
right design and how to implement it, that's sort of cool.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company