Re: Faster inserts with mostly-monotonically increasing values

On Tue, Mar 6, 2018 at 7:29 AM, Peter Geoghegan <pg(at)bowt(dot)ie> wrote:

> On Mon, Mar 5, 2018 at 5:48 PM, Claudio Freire <klaussfreire(at)gmail(dot)com>
> wrote:
>
> > I believe PKs are a prime candidate for this optimization, and
> > expecting it to apply only when no concurrency is involved is severely
> > dumbing down the optimization.
>
> Pavan justified the patch using a benchmark that only involved a
> single client -- hardly typical for a patch that changes the B-Tree
> code. If the benefits with many clients can be shown to matter, that
> will make this much more interesting to me.

Ok. I will repeat those tests with more number of clients and report back.

Regarding your suggestion about using page LSN to detect intermediate
activity, my concern is that unless we store that value in shared memory,
concurrent backends, even if inserting values in an order, will make
backend-local cached page LSN invalid and the optimisation will not
kick-in.

I am yet to digest the entire conversation between you and Claudio; you
guys clearly understand b-tree internals better than me. It seems while
you're worried about missing out on something, Claudio feels that we can
find a safe way just looking at the information available in the current
page. I feel the same way, but will need to re-read the discussion
carefully again.

Simon had raised concerns about DESC indexes and whether we need to do the
checks for leftmost page in that case. I haven't yet figured out if DESC
indexes are actually stored in the reverse order. I am gonna look at that
too.

Thanks,
Pavan

--
Pavan Deolasee http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services