Re: New Table Access Methods for Multi and Single Inserts

On Wed, Apr 3, 2024 at 2:32 PM Bharath Rupireddy wrote: > > I too prefer the latter so that the caller doesn't have to have two > paths. The new API can just transparently fallback to single inserts. > I've implemented that in the attached v17 patch. I also tested the > default APIs manually, but

Re: New Table Access Methods for Multi and Single Inserts

On Wed, Apr 3, 2024 at 1:10 AM Jeff Davis wrote: > > On Sun, 2024-03-31 at 21:18 +0530, Bharath Rupireddy wrote: > > if (table_modify_buffer_insert() is defined) > >table_modify_buffer_insert(...); > > else > > { > > myState->bistate = GetBulkInsertState(); > > table_tuple_insert(...); > >

Re: New Table Access Methods for Multi and Single Inserts

On Sun, 2024-03-31 at 21:18 +0530, Bharath Rupireddy wrote: > if (table_modify_buffer_insert() is defined) >    table_modify_buffer_insert(...); > else > { >   myState->bistate = GetBulkInsertState(); >   table_tuple_insert(...); > } We can't alloc/free the bulk insert state for every insert call.

Re: New Table Access Methods for Multi and Single Inserts

On Wed, Mar 27, 2024 at 1:42 PM Jeff Davis wrote: > > On Wed, 2024-03-27 at 01:19 +0530, Bharath Rupireddy wrote: > > > > Similarly, with this new AM, the onus lies on the table AM > > implementers to provide an implementation for these new AMs even if > > they just do single inserts. > > Why not

Re: New Table Access Methods for Multi and Single Inserts

On Wed, 2024-03-27 at 01:19 +0530, Bharath Rupireddy wrote: > > Similarly, with this new AM, the onus lies on the table AM > implementers to provide an implementation for these new AMs even if > they just do single inserts. Why not fall back to using the plain tuple_insert? Surely some table AMs

Re: New Table Access Methods for Multi and Single Inserts

On Tue, Mar 26, 2024 at 9:07 PM Jeff Davis wrote: > > On Tue, 2024-03-26 at 01:28 +0530, Bharath Rupireddy wrote: > > I'm thinking > > of dropping the COPY FROM patch using the new multi insert API for > > the > > following reasons: ... > > I agree with all of this. We do want COPY ... FROM suppor

Re: New Table Access Methods for Multi and Single Inserts

On Tue, 2024-03-26 at 01:28 +0530, Bharath Rupireddy wrote: > I'm thinking > of dropping the COPY FROM patch using the new multi insert API for > the > following reasons: ... I agree with all of this. We do want COPY ... FROM support, but there are some details to work out and we don't want to mak

Re: New Table Access Methods for Multi and Single Inserts

On Sat, Mar 23, 2024 at 5:47 AM Jeff Davis wrote: > > Comments: Thanks for looking into it. > * Do I understand correctly that CMV, RMV, and CTAS experience a > performance benefit, but COPY FROM does not? And is that because COPY > already used table_multi_insert, whereas CMV and RMV did not?

Re: New Table Access Methods for Multi and Single Inserts

On Thu, 2024-03-21 at 13:10 +0530, Bharath Rupireddy wrote: > I'm attaching the v13 patches using virtual tuple slots for buffered > tuples for multi inserts. Comments: * Do I understand correctly that CMV, RMV, and CTAS experience a performance benefit, but COPY FROM does not? And is that becaus

Re: New Table Access Methods for Multi and Single Inserts

On Thu, Mar 21, 2024 at 9:44 AM Bharath Rupireddy 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 com

Re: New Table Access Methods for Multi and Single Inserts

On Tue, Mar 19, 2024 at 10:40 AM Masahiko Sawada wrote: > > I've not reviewed the patches in depth yet, but run performance tests > for CREATE MATERIALIZED VIEW. The test scenarios is: Thanks for looking into this. > Is there any reason why we copy a buffer-heap tuple to another > buffer-heap tu

Re: New Table Access Methods for Multi and Single Inserts

Hi, On Fri, Mar 8, 2024 at 7:37 PM Bharath Rupireddy wrote: > > On Sat, Mar 2, 2024 at 12:02 PM Bharath Rupireddy > wrote: > > > > On Mon, Jan 29, 2024 at 5:16 PM Bharath Rupireddy > > wrote: > > > > > > > Please find the attached v9 patch set. > > > > I've had to rebase the patches due to comm

Re: New Table Access Methods for Multi and Single Inserts

On Sat, Mar 2, 2024 at 12:02 PM Bharath Rupireddy wrote: > > On Mon, Jan 29, 2024 at 5:16 PM Bharath Rupireddy > wrote: > > > > > Please find the attached v9 patch set. > > I've had to rebase the patches due to commit 874d817, please find the > attached v11 patch set. Rebase needed. Please see t

Re: New Table Access Methods for Multi and Single Inserts

On Mon, Jan 29, 2024 at 5:16 PM Bharath Rupireddy wrote: > > > Please find the attached v9 patch set. I've had to rebase the patches due to commit 874d817, please find the attached v11 patch set. -- Bharath Rupireddy PostgreSQL Contributors Team RDS Open Source Databases Amazon Web Services: htt

Re: New Table Access Methods for Multi and Single Inserts

On Mon, Jan 29, 2024 at 12:57 PM Bharath Rupireddy wrote: > > On Wed, Jan 17, 2024 at 10:57 PM Bharath Rupireddy > wrote: > > > > Thank you. I'm attaching v8 patch-set here which includes use of new > > insert TAMs for COPY FROM. With this, postgres not only will have the > > new TAM for inserts,

Re: New Table Access Methods for Multi and Single Inserts

On Wed, Jan 17, 2024 at 10:57 PM Bharath Rupireddy wrote: > > Thank you. I'm attaching v8 patch-set here which includes use of new > insert TAMs for COPY FROM. With this, postgres not only will have the > new TAM for inserts, but they also can make the following commands > faster - CREATE TABLE AS

Re: New Table Access Methods for Multi and Single Inserts

On Tue, Aug 1, 2023 at 10:32 PM Jacob Champion wrote: > > On Tue, Aug 1, 2023 at 9:31 AM Bharath Rupireddy > wrote: > > Thanks. Finally, I started to spend time on this. Just curious - may > > I know the discussion in/for which this patch is referenced? What was > > the motive? Is it captured so

Re: New Table Access Methods for Multi and Single Inserts

On Tue, Aug 1, 2023 at 9:31 AM Bharath Rupireddy wrote: > Thanks. Finally, I started to spend time on this. Just curious - may > I know the discussion in/for which this patch is referenced? What was > the motive? Is it captured somewhere? It may not have been the only place, but we at least touc

Re: New Table Access Methods for Multi and Single Inserts

On Sun, Jun 4, 2023 at 4:08 AM Andres Freund wrote: > > Hi, > > This patch was referenced in a discussion at pgcon, so I thought I'd give it a > look, even though Bharat said that he won't have time to drive it forward... Thanks. Finally, I started to spend time on this. Just curious - may I kno

Re: New Table Access Methods for Multi and Single Inserts

On Sun, Jun 4, 2023 at 4:08 AM Andres Freund wrote: > > Hi, > > This patch was referenced in a discussion at pgcon, so I thought I'd give it a > look, even though Bharat said that he won't have time to drive it forward... Thanks. I'm glad to know that the feature was discussed at PGCon. If there

Re: New Table Access Methods for Multi and Single Inserts

Hi, This patch was referenced in a discussion at pgcon, so I thought I'd give it a look, even though Bharat said that he won't have time to drive it forward... On 2021-04-19 10:21:36 +0530, Bharath Rupireddy wrote: > diff --git a/src/backend/access/heap/heapam_handler.c > b/src/backend/access/h

Re: New Table Access Methods for Multi and Single Inserts

On Wed, Oct 12, 2022 at 11:01 AM Michael Paquier wrote: > > This thread has been idle for 6 months now, so I have marked it as > returned with feedback as of what looks like a lack of activity. I > have looked at what's been proposed, and I am not really sure if the > direction taken is correct,

Re: New Table Access Methods for Multi and Single Inserts

On Mon, Mar 07, 2022 at 05:09:23PM +0100, Matthias van de Meent wrote: > That's for the AM-internal flushing; yes. I was thinking about the AM > api for flushing that's used when finalizing the batched insert; i.e. > table_multi_insert_flush. > > Currently it assumes that all buffered tuples will

Re: New Table Access Methods for Multi and Single Inserts

On Sun, 6 Mar 2022 at 12:12, Bharath Rupireddy wrote: > > On Fri, Mar 4, 2022 at 8:07 PM Matthias van de Meent > wrote: > > > Another rebase due to conflicts in 0003. Attaching v6 for review. > > > > I recently touched the topic of multi_insert, and I remembered this > > patch. I had to dig a bit

Re: New Table Access Methods for Multi and Single Inserts

On Fri, Mar 4, 2022 at 8:07 PM Matthias van de Meent wrote: > > Another rebase due to conflicts in 0003. Attaching v6 for review. > > I recently touched the topic of multi_insert, and I remembered this > patch. I had to dig a bit to find it, but as it's still open I've > added some comments: Than

Re: New Table Access Methods for Multi and Single Inserts

On Mon, 19 Apr 2021 at 06:52, Bharath Rupireddy wrote: > > On Mon, Apr 5, 2021 at 9:49 AM Bharath Rupireddy > wrote: > > > > On Wed, Mar 10, 2021 at 10:21 AM Bharath Rupireddy > > wrote: > > > Attaching the v4 patch set. Please review it further. > > > > Attaching v5 patch set after rebasing ont

Re: New Table Access Methods for Multi and Single Inserts

On Mon, Apr 5, 2021 at 9:49 AM Bharath Rupireddy wrote: > > On Wed, Mar 10, 2021 at 10:21 AM Bharath Rupireddy > wrote: > > Attaching the v4 patch set. Please review it further. > > Attaching v5 patch set after rebasing onto the latest master. Another rebase due to conflicts in 0003. Attaching v

Re: New Table Access Methods for Multi and Single Inserts

On Wed, Mar 10, 2021 at 10:21 AM Bharath Rupireddy wrote: > Attaching the v4 patch set. Please review it further. Attaching v5 patch set after rebasing onto the latest master. Please review it further. With Regards, Bharath Rupireddy. EnterpriseDB: http://www.enterprisedb.com From 6518212583e24

Re: New Table Access Methods for Multi and Single Inserts

On Tue, Mar 9, 2021 at 1:45 PM Bharath Rupireddy wrote: > On Mon, Mar 8, 2021 at 6:37 PM Dilip Kumar wrote: >> > > Why do we need to invent a new version table_insert_v2? And also why > > it is named table_insert* instead of table_tuple_insert*? > > New version, because we changed the input para

Re: New Table Access Methods for Multi and Single Inserts

On Mon, Mar 8, 2021 at 6:37 PM Dilip Kumar wrote: > > On Sat, Feb 20, 2021 at 11:15 AM Bharath Rupireddy > wrote: > > > > Please review the v3 patch set further. > > > > Below is the performance gain measured for CREATE TABLE AS with the > > new multi insert am propsed in this thread: > > > > cas

Re: New Table Access Methods for Multi and Single Inserts

On Sat, Feb 20, 2021 at 11:15 AM Bharath Rupireddy wrote: > > Please review the v3 patch set further. > > Below is the performance gain measured for CREATE TABLE AS with the > new multi insert am propsed in this thread: > > case 1 - 2 integer(of 4 bytes each) columns, 3 varchar(8), tuple size > 5

Re: New Table Access Methods for Multi and Single Inserts

Hi, bq. case 3 - 2 integer(of 4 bytes each) columns, tuple size 32 bytes Is there some other column(s) per row apart from the integer columns ? Since the 2 integer columns only occupy 8 bytes. I wonder where the other 32-8=24 bytes come from. Thanks On Fri, Feb 19, 2021 at 9:45 PM Bharath Rupire

Re: New Table Access Methods for Multi and Single Inserts

On Sat, Feb 20, 2021 at 12:53 PM Zhihong Yu wrote: > > Hi, > bq. case 3 - 2 integer(of 4 bytes each) columns, tuple size 32 bytes > > Is there some other column(s) per row apart from the integer columns ? Since > the 2 integer columns only occupy 8 bytes. I wonder where the other 32-8=24 > bytes

Re: New Table Access Methods for Multi and Single Inserts

On Wed, Feb 17, 2021 at 12:46 PM Bharath Rupireddy wrote: > Hi, > > I addressed the following review comments and attaching v3 patch set. > > 1) ExecClearTuple happens before ExecCopySlot in heap_multi_insert_v2 > and this allowed us to remove clear_mi_slots flag from > TableInsertState. > 2) I re

Re: New Table Access Methods for Multi and Single Inserts

Hi, I addressed the following review comments and attaching v3 patch set. 1) ExecClearTuple happens before ExecCopySlot in heap_multi_insert_v2 and this allowed us to remove clear_mi_slots flag from TableInsertState. 2) I retained the flushed variable inside TableInsertState so that the callers c

Re: New Table Access Methods for Multi and Single Inserts

On Mon, 2021-01-18 at 08:58 +0100, Luc Vlaming wrote: > You mean how it could because of that the table modification API > uses > the table_tuple_insert_speculative ? Just wondering if you think if > it > generally cannot work or would like to see that path / more paths > integrated in to the pa

Re: New Table Access Methods for Multi and Single Inserts

On 17-01-2021 00:04, Jeff Davis wrote: If we agree on removing heap_multi_insert_v2 API and embed that logic inside heap_insert_v2, then we can do this - pass the required information and the functions ExecInsertIndexTuples and ExecARInsertTriggers as callbacks so that, whether or not heap_inse

Re: New Table Access Methods for Multi and Single Inserts

> If we agree on removing heap_multi_insert_v2 API and embed that logic > inside heap_insert_v2, then we can do this - pass the required > information and the functions ExecInsertIndexTuples and > ExecARInsertTriggers as callbacks so that, whether or not > heap_insert_v2 choses single or multi in

Re: New Table Access Methods for Multi and Single Inserts

On 06-01-2021 14:06, Bharath Rupireddy wrote: On Wed, Jan 6, 2021 at 12:56 PM Luc Vlaming wrote: The main reason for me for wanting a single API is that I would like the decision of using single or multi inserts to move to inside the tableam. For e.g. a heap insert we might want to put the thre

Re: New Table Access Methods for Multi and Single Inserts

On Wed, Jan 6, 2021 at 12:56 PM Luc Vlaming wrote: > The main reason for me for wanting a single API is that I would like the > decision of using single or multi inserts to move to inside the tableam. > For e.g. a heap insert we might want to put the threshold at e.g. 100 > rows so that the overhe

Re: New Table Access Methods for Multi and Single Inserts

On 05-01-2021 11:06, Bharath Rupireddy wrote: On Mon, Jan 4, 2021 at 1:29 PM Luc Vlaming > wrote: >  > table AM patch [2] be reviewed further? > As to the patches themselves: > > I think the API is a huge step forward! I assume that we want to have a > single-insert

Re: New Table Access Methods for Multi and Single Inserts

On 05-01-2021 22:28, Jeff Davis wrote: On Mon, 2021-01-04 at 08:59 +0100, Luc Vlaming wrote: Reason I'm asking is that I quite liked the heap_insert_begin parameter is_multi, which could even be turned into a "expected_rowcount" of the amount of rows expected to be commited in the transaction (e

Re: New Table Access Methods for Multi and Single Inserts

On Mon, 2021-01-04 at 08:59 +0100, Luc Vlaming wrote: > Reason I'm asking is that I quite liked the heap_insert_begin > parameter > is_multi, which could even be turned into a "expected_rowcount" of > the > amount of rows expected to be commited in the transaction (e.g. > single, > several, thou

Re: New Table Access Methods for Multi and Single Inserts

On Mon, Jan 4, 2021 at 1:29 PM Luc Vlaming wrote: > > table AM patch [2] be reviewed further? > As to the patches themselves: > > I think the API is a huge step forward! I assume that we want to have a > single-insert API like heap_insert_v2 so that we can encode the > knowledge that there will j

Re: New Table Access Methods for Multi and Single Inserts

On 28-12-2020 13:48, Bharath Rupireddy wrote: On Fri, Dec 25, 2020 at 8:10 AM Justin Pryzby wrote: On Thu, Dec 24, 2020 at 05:48:42AM +0530, Bharath Rupireddy wrote: I'm not posting the updated 0002 to 0004 patches, I plan to do so after a couple of reviews happen on the design of the APIs in

Re: New Table Access Methods for Multi and Single Inserts

On Fri, Dec 25, 2020 at 8:10 AM Justin Pryzby wrote: > On Thu, Dec 24, 2020 at 05:48:42AM +0530, Bharath Rupireddy wrote: > > I'm not posting the updated 0002 to 0004 patches, I plan to do so > > after a couple of reviews happen on the design of the APIs in 0001. > > > > Thoughts? > > Are you fami

Re: New Table Access Methods for Multi and Single Inserts

On Thu, Dec 24, 2020 at 05:48:42AM +0530, Bharath Rupireddy wrote: > I'm not posting the updated 0002 to 0004 patches, I plan to do so > after a couple of reviews happen on the design of the APIs in 0001. > > Thoughts? Are you familiar with this work ? https://commitfest.postgresql.org/31/2717/

Re: New Table Access Methods for Multi and Single Inserts

On Mon, Dec 21, 2020 at 1:17 PM Justin Pryzby wrote: > On Fri, Dec 18, 2020 at 11:54:39AM -0600, Justin Pryzby wrote: > > On Fri, Dec 18, 2020 at 07:39:14AM +0530, Bharath Rupireddy wrote: > > > On Fri, Dec 18, 2020 at 2:14 AM Justin Pryzby > > > wrote: > > > > Are you thinking that TableInsertS

Re: New Table Access Methods for Multi and Single Inserts

On Fri, Dec 18, 2020 at 11:54:39AM -0600, Justin Pryzby wrote: > On Fri, Dec 18, 2020 at 07:39:14AM +0530, Bharath Rupireddy wrote: > > On Fri, Dec 18, 2020 at 2:14 AM Justin Pryzby wrote: > > > Are you thinking that TableInsertState would eventually have additional > > > attributes which would ap

Re: New Table Access Methods for Multi and Single Inserts

On Fri, Dec 18, 2020 at 11:24 PM Justin Pryzby wrote: > On Fri, Dec 18, 2020 at 07:39:14AM +0530, Bharath Rupireddy wrote: > > On Fri, Dec 18, 2020 at 2:14 AM Justin Pryzby wrote: > > > Are you thinking that TableInsertState would eventually have additional > > > attributes which would apply to o

Re: New Table Access Methods for Multi and Single Inserts

On Fri, Dec 18, 2020 at 07:39:14AM +0530, Bharath Rupireddy wrote: > On Fri, Dec 18, 2020 at 2:14 AM Justin Pryzby wrote: > > Are you thinking that TableInsertState would eventually have additional > > attributes which would apply to other tableams, but not to heap ? Is > > heap_insert_begin() re

Re: New Table Access Methods for Multi and Single Inserts

On Fri, Dec 18, 2020 at 2:14 AM Justin Pryzby wrote: > On Thu, Dec 17, 2020 at 04:35:33PM +0530, Bharath Rupireddy wrote: > > > You made the v2 insert interface a requirement for all table AMs. > > > Should it be optional, and fall back to simple inserts if not implemented > > > ? > > > > I tried

Re: New Table Access Methods for Multi and Single Inserts

On Thu, Dec 17, 2020 at 04:35:33PM +0530, Bharath Rupireddy wrote: > > You made the v2 insert interface a requirement for all table AMs. > > Should it be optional, and fall back to simple inserts if not implemented ? > > I tried to implement the APIs mentioned by Andreas here in [1]. I just > used

Re: New Table Access Methods for Multi and Single Inserts

Thanks a lot for taking a look at the patches. On Thu, Dec 17, 2020 at 10:35 AM Justin Pryzby wrote: > Typos: > > + * 1) Specify is_multi as true, then multi insert state is allcoated. > => allocated > + * dropped, short-lived memory context is delted and mistate is freed up. > => deleted > + *

Re: New Table Access Methods for Multi and Single Inserts

Typos: + * 1) Specify is_multi as true, then multi insert state is allcoated. => allocated + * dropped, short-lived memory context is delted and mistate is freed up. => deleted + * 2) Currently, GetTupleSize() handles the existing heap, buffer, minmal and => minimal + /* Mulit insert state

Re: New Table Access Methods for Multi and Single Inserts

On Tue, Dec 8, 2020 at 6:27 PM Bharath Rupireddy < bharath.rupireddyforpostg...@gmail.com> wrote: > Hi, > > Currently, for any component (such as COPY, CTAS[1], CREATE/REFRESH > Mat View[1], INSERT INTO SELECTs[2]) multi insert logic such as buffer > slots allocation, maintenance, decision to flush