Re: Fast insertion indexes: why no developments

On Tue, Nov 5, 2013 at 12:25 AM, Leonardo Francalanci <m_lists(at)yahoo(dot)it>wrote:

> Andres Freund-3 wrote
> > On 2013-11-04 11:27:33 -0500, Robert Haas wrote:
> >> On Mon, Nov 4, 2013 at 11:24 AM, Claudio Freire &lt;
>
> > klaussfreire@
>
> > &gt; wrote:
> >> > Such a thing would help COPY, so maybe it's worth a look
> >>
> >> I have little doubt that a deferred insertion buffer of some kind
> >> could help performance on some workloads, though I suspect the buffer
> >> would have to be pretty big to make it worthwhile on a big COPY that
> >> generates mostly-random insertions.
> >
> > Even for random data presorting the to-be-inserted data appropriately
> > could result in much better io patterns.
>
> Mmh, I'm afraid that the buffer should be huge to get some real advantage.
> You have to buffer enough values to avoid "touching" entire pages, which is
> not that easy.

Some experiments I did a few years ago showed that applying sorts to the
data to be inserted could be helpful even when the sort batch size was as
small as one tuple per 5 pages of existing index. Maybe even less.

Cheers,

Jeff