From: | Bharath Rupireddy <bharath(dot)rupireddyforpostgres(at)gmail(dot)com> |
---|---|
To: | PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Cc: | pguo(at)pivotal(dot)io, simon(at)2ndquadrant(dot)com |
Subject: | Multi Inserts in CREATE TABLE AS - revived patch |
Date: | 2020-11-03 11:24:16 |
Message-ID: | CALj2ACUr8Vnu3dMkiU47v-dh55tnY2Lr8m2xoSaRZeiCaNeVqQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hi,
I would like to propose an updated patch on multi/bulk inserts in CTAS [1]
that tries to address the review comments that came up in [1]. One of the
main review comments was to calculate/estimate the tuple size to decide on
when to flush. I tried to solve this point with a new function
GetTupleSize()(see the patch for implementation).
I did some testing with custom configuration[2].
Use case 1- 100mn tuples, 2 integer columns, exec time in sec:
HEAD: *131.507* when the select part is not parallel, 128.832 when the
select part is parallel
Patch: 98.925 when the select part is not parallel, *52.901* when the
select part is parallel
Use case 2- 10mn tuples, 4 integer and 6 text columns, exec time in sec:
HEAD: *76.801* when the select part is not parallel, 66.074 when the select
part is parallel
Patch: 74.083 when the select part is not parallel, *57.739* when the
select part is parallel
Thoughts?
If the approach followed in the patch looks okay, I can work on a separate
patch for multi inserts in refresh materialized view cases.
I thank Simon Riggs for the offlist discussion.
PS: I chose to start a new thread as the previous thread [1] was closed in
the CF. I hope that's not a problem.
[1] -
https://www.postgresql.org/message-id/CAEET0ZHRWxbRUgwzUK_tOFDWx7VE2-P%3DxMBT6-N%2BgAa9WQ%3DxxA%40mail.gmail.com
[2] - The postgresql.conf used:
shared_buffers = 40GB
synchronous_commit = off
checkpoint_timeout = 1d
max_wal_size = 24GB
min_wal_size = 15GB
autovacuum = off
With Regards,
Bharath Rupireddy.
EnterpriseDB: http://www.enterprisedb.com
Attachment | Content-Type | Size |
---|---|---|
v1-0001-Multi-Inserts-in-Create-Table-As.patch | application/octet-stream | 12.4 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Daniel Gustafsson | 2020-11-03 12:00:00 | Re: Move OpenSSL random under USE_OPENSSL_RANDOM |
Previous Message | Magnus Hagander | 2020-11-03 11:11:54 | Re: contrib/sslinfo cleanup and OpenSSL errorhandling |