On Fri, Jun 5, 2015 at 10:59 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 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 have seen a case that a major fact table with up to 7 indices, every
15~60 mins with large amount of data loading, and there are
concurrrent seeks against indices at the same time. We can play with
partitioning, or sarcrifice some application semantics, to alleviate
the pressure but it is good to see if we can improve: sorting and
batching insert into btree is helpful for better IO and locking
behavior. So can we guard the case that hard to handle, e.g., the
indices enforcing some constraints (like uniqueness), and improve the
loading senario?
Hint bits update is also painful in above case, but it is out of the topic here.
Thanks,
Qingqing