Re: Batch update of indexes

On Jan 21, 2016, at 5:14 AM, Simon Riggs wrote:

> On 20 January 2016 at 14:55, Konstantin Knizhnik <k(dot)knizhnik(at)postgrespro(dot)ru> wrote:
> Hi,
>
> Hi, I glad to see that you interested in that too.
> I think this is a good feature and I think it will be very useful to have.
> I have already mentioned some related problems and possible improvements in my presentation.
> http://www.slideshare.net/AnastasiaLubennikova/indexes-dont-mean-slow-inserts
> Last two slides concern to this thread. Briefly, I've suggested to think about insertion buffer. Something very similar to it is already implemented in BRIN. It does not index last data from heap, while the number of last pages is less than pages_per_block.
>
> Do you mean GIN-like usage of insertion buffer (here it is called "pending list")?
> So that we have to combine search in the main tree and in the insert buffer?
> Actually this is what I want to avoided (because at least in case of GIN pending list cause significant degrade of performance,
> while up-to-date state of full text index is rarely required).
>
> Degrade in performance is because scan of pending list is O(N).
>
> If we did the same thing for monotonic inserts into a btree, the performance of ruling out any contents in the pending list would be O(1), so it is more feasible than you say.

Sorry, didn't get it.
Certainly for B-Tree we can organize insert buffer (or pending list) as sorted array or also as a tree.
But in both case complexity of search in this buffer will be O(log(N)), not O(1).
O(1) is possible only if we will use hash table for pending list and are lucky with hash function.
But in this case it will be not possible to support range queries and proper order.

In any case, necessity to perform two searches instead of one and combining results will significantly complicate index implementation.
But definitely this solution is more convenient and transparent for users...

>
> --
> Simon Riggs http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services