Re: Caching index AM working data across aminsert calls

On Tue, Feb 7, 2017 at 6:04 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> It's always been possible for index AMs to cache data across successive
> amgettuple calls within a single SQL command: the IndexScanDesc.opaque
> field is meant for precisely that. However, no comparable facility
> exists for amortizing setup work across successive aminsert calls.
> The attached proposed patch adds such a feature and teaches gin,
> gist, and brin to use it. (The other standard index AMs keep everything
> they need in the relcache, so there's little to improve there.)
>
> The improvement I see from this is fairly modest in a normal build.
> In an example similar to the gin regression test's main insert query,
>
> insert into gin_test_tbl select array[1, 2, g] from generate_series(1, 1000000) g;
>
> the overall insertion speed increases perhaps 10%, which is nice but
> not great. gist and brin are less, maybe 5% or so.

I think that's more than nice. I think it's great. It's not that
easy to squeeze 5-10% out of common operations.

(I have not reviewed the patch.)

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company