Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1ruESm-003j2M-6T for pgsql-general@arkaria.postgresql.org; Tue, 09 Apr 2024 16:36:48 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1ruESl-007blR-BQ for pgsql-general@arkaria.postgresql.org; Tue, 09 Apr 2024 16:36:47 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1ruESk-007blI-KD for pgsql-general@lists.postgresql.org; Tue, 09 Apr 2024 16:36:46 +0000 Received: from mail-qt1-x832.google.com ([2607:f8b0:4864:20::832]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1ruESh-0022Sp-N5 for pgsql-general@lists.postgresql.org; Tue, 09 Apr 2024 16:36:45 +0000 Received: by mail-qt1-x832.google.com with SMTP id d75a77b69052e-4349685c845so13372361cf.0 for ; Tue, 09 Apr 2024 09:36:43 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1712680603; x=1713285403; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=Q9bX0PDK8Lr2vsEwUtWR4RLZhbcaKrNQKVAhciLKWJQ=; b=RmtNoGVVSQPJtF3RackK7cEWZz/EE5j3GmOrBK2i8thTh5kEmfUcOPNSMjw/Haoj8I l4+uQgSljpbaW4TNqXS908O/ze0Fh1e062oss+quoqhRdBq3X97xRupLyU4AqJke4lzO nGmFtfRmv+CHUoXfVnZSOjXKLIMRGM44/Och+Jzlfo/Jz5SOPi/H4u4RWyN9PQ/gt0It V9lbDv6kQGNX3wDP1N9pM9t/k/hoSKZ1FHBCjWS2tbTSc8L6wmXh2oIdIyVBgSi3Tp8d Ja7SqrDGEt9RRsRjKo2YxsW/wLyceoYBuzeaGNsDMeLWnnlyLXbx96Ucf4WetSnYbCr9 Qqsg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1712680603; x=1713285403; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=Q9bX0PDK8Lr2vsEwUtWR4RLZhbcaKrNQKVAhciLKWJQ=; b=VjI4qtzMZmYA+LJWQVyLP6tLMbC8Zh3CJkeakx4I8EB0EvKamlbqkxMNyuqc5YwLlG w5YCQha1noQ99OPGQtfN2H/+HV8s/BXo8grF+1TZNsbGiLoPe+tNAOPz/KRZQzX7C64P OGgwqJ3u2xGpSGcgrTIqTNJRzFLY3A0MO6oBjY/UbEM8B7wn0rUItENMxhK7W/bRPvTX G/2W3f5wES1iEsoq8i1sq1gRqb9mLmMATVscPE59z8VKjhc4kwSe2vDuEzRsMUz35euI PxELhdXFuywwmEdL8yStdVKWVwicaZQ5CCky9oRTBefbwF7Usn6BrwsgJQ6u2eAnP2j2 h1Yw== X-Forwarded-Encrypted: i=1; AJvYcCUzeBEkk0JdzhE/zg6yRVh2u366KtqvDlYXSGEsautEfXfWiB1QG8AwHz/Bmppl1udHZqo606wCHr/91l69oV8eV9ZaxGcVEie+LpL5kxM+K5Dx X-Gm-Message-State: AOJu0YwSXESV8EtQPuWsEouY1f+ARoFGyBUfGODF3pc9k/g36AIhG8c4 jy3eKsR9TrStcuScf6H8W8JyeO+4JGf16UPG+e47gZEvwBcXch6kVnnE8pqf5L8DOv1pXbarFUl UxPyuufWExAV0U5k1OyWR+J9eN4c= X-Google-Smtp-Source: AGHT+IFMr2sEK6A1V6PaSAsq3BpJFRCM6ZJsITAM1cBlbURoMABzllhrhBDTLm+XIIYlBeLmGVf5pEIJSXZ00PlI938= X-Received: by 2002:a05:6214:27ef:b0:699:1657:ec70 with SMTP id jt15-20020a05621427ef00b006991657ec70mr106536qvb.12.1712680602070; Tue, 09 Apr 2024 09:36:42 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: yudhi s Date: Tue, 9 Apr 2024 22:06:30 +0530 Message-ID: Subject: Re: Moving delta data faster To: Adrian Klaver Cc: veem v , Greg Sabino Mullane , pgsql-general Content-Type: multipart/alternative; boundary="000000000000e67c2a0615ac86d0" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000e67c2a0615ac86d0 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Sun, Apr 7, 2024 at 2:25=E2=80=AFAM Adrian Klaver wrote: > > I have no idea how this works in the code, but my suspicion is it is due > to the following: > > https://www.postgresql.org/docs/current/sql-insert.html#SQL-ON-CONFLICT > > "The optional ON CONFLICT clause specifies an alternative action to > raising a unique violation or exclusion constraint violation error. For > each individual row proposed for insertion, either the insertion > proceeds, or, if an arbiter constraint or index specified by > conflict_target is violated, the alternative conflict_action is taken. > ON CONFLICT DO NOTHING simply avoids inserting a row as its alternative > action. ON CONFLICT DO UPDATE updates the existing row that conflicts > with the row proposed for insertion as its alternative action." > > vs this: > > "First, the MERGE command performs a join from data_source to > target_table_name producing zero or more candidate change rows. For each > candidate change row, the status of MATCHED or NOT MATCHED is set just > once, after which WHEN clauses are evaluated in the order specified. For > each candidate change row, the first clause to evaluate as true is > executed. No more than one WHEN clause is executed for any candidate > change row." > > Where ON CONFLICT attempts the INSERT then on failure does the UPDATE > for the ON CONFLICT DO UPDATE case. MERGE on the hand evaluates based on > the join condition(ON tbl1.fld =3Dtbl2.fld) and then based on MATCH/NOT > MATCHED takes the appropriate action for the first WHEN match. In other > words it goes directly to the appropriate action. > > Thank you Adrian. I think you are spoton on the cause of upsert becoming slower than Merge. Below is the explain plan I captured for both the operations and it looks like even the planning time is small for the UPSERT, as because it operates on the constraint i.e the Update will wait for all the failure records from the INSERT and thus it takes longer. The Merge seems to be evaluated on the Joins i.e it is directly able to get the set of rows which has to be Updated rather waiting for the INSERT to make it fail based on the PK constraint. ************** Explain plan for UPSERT ***************** Insert on public.target_tab (cost=3D0.00..17353.00 rows=3D0 width=3D0) (act= ual time=3D19957.569..19957.570 rows=3D0 loops=3D1) Conflict Resolution: UPDATE Conflict Arbiter Indexes: target_tab_pkey Tuples Inserted: 500000 Conflicting Tuples: 500000 Buffers: shared hit=3D8545735 written=3D10094 -> Seq Scan on public.source_tab (cost=3D0.00..17353.00 rows=3D1000000 width=3D29) (actual time=3D0.006..208.306 rows=3D1000000 loops=3D1) Output: source_tab.id, source_tab.column1, source_tab.column2 Buffers: shared hit=3D7353 Settings: effective_cache_size =3D '10475192kB', maintenance_io_concurrency= =3D '1', max_parallel_workers =3D '32', max_parallel_workers_per_gather =3D '4'= , search_path =3D 'public, public, "$user"', temp_buffers =3D '16MB', work_me= m =3D '8MB' Query Identifier: -1356019529835809419 Planning: Buffers: shared hit=3D41 Planning Time: 0.199 ms Execution Time: 19959.261 ms ************** Explain plan for Merge ***************** Merge on public.target_tab t (cost=3D17368.00..53460.01 rows=3D0 width=3D0) (actual time=3D14209.966..14209.968 rows=3D0 loops=3D1) Tuples: inserted=3D500000 updated=3D500000 Buffers: shared hit=3D5040097 written=3D10460, temp read=3D4143 written=3D4= 143 I/O Timings: temp read=3D26.746 write=3D68.596 -> Hash Left Join (cost=3D17368.00..53460.01 rows=3D1000000 width=3D35) (ac= tual time=3D179.233..1332.264 rows=3D1000000 loops=3D1) Output: t.ctid, s.column1, s.column2, s.id Inner Unique: true Hash Cond: (s.id =3D t.id) Buffers: shared hit=3D11029, temp read=3D4143 written=3D4143 I/O Timings: temp read=3D26.746 write=3D68.596 -> Seq Scan on public.source_tab s (cost=3D0.00..17353.00 rows=3D1000000 width=3D29) (actual time=3D0.008..268.506 rows=3D1000000 loops=3D1) Output: s.column1, s.column2, s.id Buffers: shared hit=3D7353 -> Hash (cost=3D8676.00..8676.00 rows=3D500000 width=3D10) (actual time=3D178.101..178.102 rows=3D500000 loops=3D1) Output: t.ctid, t.id Buckets: 524288 Batches: 2 Memory Usage: 14824kB Buffers: shared hit=3D3676, temp written=3D977 I/O Timings: temp write=3D5.904 -> Seq Scan on public.target_tab t (cost=3D0.00..8676.00 rows=3D500000 width=3D10) (actual time=3D0.007..66.441 rows=3D500000 loops=3D1) Output: t.ctid, t.id Buffers: shared hit=3D3676 Settings: effective_cache_size =3D '10475192kB', maintenance_io_concurrency= =3D '1', max_parallel_workers =3D '32', max_parallel_workers_per_gather =3D '4'= , search_path =3D 'public, public, "$user"', temp_buffers =3D '16MB', work_me= m =3D '8MB' Query Identifier: -2297080081674771467 Planning: Buffers: shared hit=3D85 Planning Time: 0.466 ms Execution Time: 14212.061 ms --000000000000e67c2a0615ac86d0 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

On Sun, Apr 7, 2024 at 2:25=E2=80=AFAM Ad= rian Klaver <adrian.klaver@= aklaver.com> wrote:

I have no idea how this works in the code, but my suspicion is it is due to the following:

https://www.postgresql.org/do= cs/current/sql-insert.html#SQL-ON-CONFLICT

"The optional ON CONFLICT clause specifies an alternative action to raising a unique violation or exclusion constraint violation error. For each individual row proposed for insertion, either the insertion
proceeds, or, if an arbiter constraint or index specified by
conflict_target is violated, the alternative conflict_action is taken.
ON CONFLICT DO NOTHING simply avoids inserting a row as its alternative action. ON CONFLICT DO UPDATE updates the existing row that conflicts
with the row proposed for insertion as its alternative action."

vs this:

"First, the MERGE command performs a join from data_source to
target_table_name producing zero or more candidate change rows. For each candidate change row, the status of MATCHED or NOT MATCHED is set just
once, after which WHEN clauses are evaluated in the order specified. For each candidate change row, the first clause to evaluate as true is
executed. No more than one WHEN clause is executed for any candidate
change row."

Where ON CONFLICT attempts the INSERT then on failure does the UPDATE
for the ON CONFLICT DO UPDATE case. MERGE on the hand evaluates based on the join condition(ON tbl1.fld =3Dtbl2.fld) and then based on MATCH/NOT MATCHED takes the appropriate action for the first WHEN match. In other words it goes directly to the appropriate action.

=
Thank you Adrian. I think you are spoton on the cause of ups= ert becoming slower than Merge. Below is the explain plan I captured for bo= th the operations and it looks like even the planning time is small for the= UPSERT, as because it operates on the constraint=C2=A0i.e the Update will = wait for all the failure records from the INSERT and thus it takes longer. = The Merge seems to be evaluated on the Joins i.e it is directly able to get= the set of rows which has to be Updated rather waiting for the INSERT to m= ake it fail based on the PK constraint.

************** Explain plan for UPSERT *****************

I= nsert on public.target_tab (cost=3D0.00..17353.00 rows=3D0 width=3D0) (actu= al time=3D19957.569..19957.570 rows=3D0 loops=3D1)
Conflict Resolution: = UPDATE
Conflict Arbiter Indexes: target_tab_pkey
Tuples Inserted: 500= 000
Conflicting Tuples: 500000
Buffers: shared hit=3D8545735 written= =3D10094
-> Seq Scan on public.source_tab (cost=3D0.00..17353.00 rows= =3D1000000 width=3D29) (actual time=3D0.006..208.306 rows=3D1000000 loops= =3D1)
Output: source_tab.id, source= _tab.column1, source_tab.column2
Buffers: shared hit=3D7353
Settings:= effective_cache_size =3D '10475192kB', maintenance_io_concurrency = =3D '1', max_parallel_workers =3D '32', max_parallel_worker= s_per_gather =3D '4', search_path =3D 'public, public, "$u= ser"', temp_buffers =3D '16MB', work_mem =3D '8MB'=
Query Identifier: -1356019529835809419
Planning:
Buffers: shared = hit=3D41
Planning Time: 0.199 ms
Execution Time: 19959.261 ms

= ************** Explain plan for Merge *****************

Merge on pub= lic.target_tab t (cost=3D17368.00..53460.01 rows=3D0 width=3D0) (actual tim= e=3D14209.966..14209.968 rows=3D0 loops=3D1)
Tuples: inserted=3D500000 u= pdated=3D500000
Buffers: shared hit=3D5040097 written=3D10460, temp read= =3D4143 written=3D4143
I/O Timings: temp read=3D26.746 write=3D68.596-> Hash Left Join (cost=3D17368.00..53460.01 rows=3D1000000 width=3D35)= (actual time=3D179.233..1332.264 rows=3D1000000 loops=3D1)
Output: t.ct= id, s.column1, s.column2, s.id
Inner Unique:= true
Hash Cond: (s.id =3D t.id)
Buffers: shared hit=3D11029, temp read=3D4143 written= =3D4143
I/O Timings: temp read=3D26.746 write=3D68.596
-> Seq Scan= on public.source_tab s (cost=3D0.00..17353.00 rows=3D1000000 width=3D29) (= actual time=3D0.008..268.506 rows=3D1000000 loops=3D1)
Output: s.column1= , s.column2, s.id
Buffers: shared hit=3D7353=
-> Hash (cost=3D8676.00..8676.00 rows=3D500000 width=3D10) (actual t= ime=3D178.101..178.102 rows=3D500000 loops=3D1)
Output: t.ctid, t.id
Buckets: 524288 Batches: 2 Memory Usage: 14824= kB
Buffers: shared hit=3D3676, temp written=3D977
I/O Timings: temp w= rite=3D5.904
-> Seq Scan on public.target_tab t (cost=3D0.00..8676.00= rows=3D500000 width=3D10) (actual time=3D0.007..66.441 rows=3D500000 loops= =3D1)
Output: t.ctid, t.id
Buffers: share= d hit=3D3676
Settings: effective_cache_size =3D '10475192kB', ma= intenance_io_concurrency =3D '1', max_parallel_workers =3D '32&= #39;, max_parallel_workers_per_gather =3D '4', search_path =3D '= ;public, public, "$user"', temp_buffers =3D '16MB', w= ork_mem =3D '8MB'
Query Identifier: -2297080081674771467
Plan= ning:
Buffers: shared hit=3D85
Planning Time: 0.466 ms
Execution Time: 14212.061 ms=C2=A0
--000000000000e67c2a0615ac86d0--