On Tue, May 25, 2021 at 1:10 PM tsunakawa.ta...@fujitsu.com <tsunakawa.ta...@fujitsu.com> wrote: > > Although this should be a controversial and may be crazy idea, the following > change brought 4-11% speedup. This is because I thought parallel workers > might contend for WAL flush as a result of them using the limited ring buffer > and flushing dirty buffers when the ring buffer is filled. Can we take > advantage of this? > > [GetBulkInsertState] > /* bistate->strategy = GetAccessStrategy(BAS_BULKWRITE);*/ > bistate->strategy = NULL;
You are right. If ring buffer(16MB) is not used and shared buffers(1GB) are used instead, in your case since the table size is 335MB and it can fit in the shared buffers, there will not be any or will be very minimal dirty buffer flushing, so there will be more some more speedup. Otherwise, the similar speed up can be observed when the BAS_BULKWRITE is increased a bit from the current 16MB to some other reasonable value. I earlier tried these experiments. Otherwise, as I said in [1], we can also increase the number of extra blocks added at a time, say Min(1024, lockWaiters * 128/256/512) than currently extraBlocks = Min(512, lockWaiters * 20);. This will also give some speedup and we don't see any regression with parallel inserts in CTAS patches. But, I'm not so sure that the hackers will agree any of the above as a practical solution to the "relation extension" problem. [1] https://www.postgresql.org/message-id/CALj2ACVdcrjwHXwvJqT-Fa32vnJEOjteep_3L24X8MK50E7M8w%40mail.gmail.com With Regards, Bharath Rupireddy. EnterpriseDB: http://www.enterprisedb.com