Re: Faster inserts with mostly-monotonically increasing values

On 6 March 2018 at 04:40, Pavan Deolasee <pavan(dot)deolasee(at)gmail(dot)com> wrote:
>
>
> 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.

Even if the optimization is only valid with a single backend, its
still very useful - think COPY, but other similar cases.

> 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.

No, I meant that you were testing whether the value was higher (> 0),
whereas it should be lower (< 0) on DESC indexes.

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