Re: New Table Access Methods for Multi and Single Inserts

Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>

From: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>

To: Masahiko Sawada <sawada.mshk@gmail.com>

Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>, Andres Freund <andres@anarazel.de>, Dilip Kumar <dilipbalaut@gmail.com>, Luc Vlaming <luc@swarm64.com>, Justin Pryzby <pryzby@telsasoft.com>, Jeff Davis <pgsql@j-davis.com>, Michael Paquier <michael@paquier.xyz>, Matthias van de Meent <boekewurm+postgres@gmail.com>

Date: 2024-03-21T07:40:16Z

Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

libpq: Fix some issues in TAP tests for service files
- 2c7bd2ba507e 18.0 cited
Multiple revisions to the GROUP BY reordering tests
- 874d817baa16 17.0 cited
Explore alternative orderings of group-by pathkeys during optimization.
- 0452b461bc40 17.0 cited

Attachments

v13-0001-New-table-AMs-for-single-and-multi-inserts.patch (application/octet-stream) patch v13-0001
v13-0002-Optimize-CREATE-TABLE-AS-with-multi-inserts.patch (application/octet-stream) patch v13-0002
v13-0003-Optimize-REFRESH-MATERIALIZED-VIEW-with-multi-in.patch (application/octet-stream) patch v13-0003
v13-0004-Use-new-multi-insert-table-AM-for-COPY-FROM.patch (application/octet-stream) patch v13-0004

On Thu, Mar 21, 2024 at 9:44 AM Bharath Rupireddy
<bharath.rupireddyforpostgres@gmail.com> wrote:
>
> Yes, usingVirtualTupleTableSlot helps improve the performance a lot.
> Below are results from my testing. Note that CMV, RMV, CTAS stand for
> CREATE MATERIALIZED VIEW, REFRESH MATERIALIZED VIEW, CREATE TABLE AS
> respectively. These commands got faster by 62.54%, 68.87%, 74.31% or
> 2.67, 3.21, 3.89 times respectively. I've used the test case specified
> at [1].
>
> HEAD:
> CMV:
> Time: 6276.468 ms (00:06.276)
> CTAS:
> Time: 8141.632 ms (00:08.142)
> RMV:
> Time: 14747.139 ms (00:14.747)
>
> PATCHED:
> CMV:
> Time: 2350.282 ms (00:02.350)
> CTAS:
> Time: 2091.427 ms (00:02.091)
> RMV:
> Time: 4590.180 ms (00:04.590)
>
> I quickly looked at the description of what a "virtual" tuple is from
> src/include/executor/tuptable.h [2]. IIUC, it is invented for
> minimizing data copying, but it also says that it's the responsibility
> of the generating plan node to be sure these resources are not
> released for as long as the virtual tuple needs to be valid or is
> materialized. While it says this, as far as this patch is concerned,
> the virtual slot gets materialized when we copy the tuples from source
> slot (can be any type of slot) to destination slot (which is virtual
> slot). See ExecCopySlot->
> tts_virtual_copyslot->tts_virtual_materialize. This way,
> tts_virtual_copyslot ensures the tuples storage doesn't depend on
> external memory because all the datums that aren't passed by value are
> copied into the slot's memory context.
>
> With the above understanding, it looks safe to use virtual slots for
> the multi insert buffered slots. I'm not so sure if I'm missing
> anything here.

I'm attaching the v13 patches using virtual tuple slots for buffered
tuples for multi inserts.

-- 
Bharath Rupireddy
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com