MIME-Version: 1.0
References: <008701db93cd$2eb404d0$8c1c0e70$.ref@ymail.com>
 <008701db93cd$2eb404d0$8c1c0e70$@ymail.com>
 <0942a47d7a89f1b181f7e306f8389d9717eb5fe0.camel@cybertec.at>
 <00a401db9400$86c240f0$9446c2d0$@ymail.com>
 <d58bb88b06a40233aef8e07a39fd234458abc009.camel@cybertec.at>
 <00b001db9418$85c7c770$91575650$@ymail.com>
In-Reply-To: <00b001db9418$85c7c770$91575650$@ymail.com>
From: Renan Alves Fonseca <renanfonseca@gmail.com>
Date: Mon, 17 Mar 2025 18:45:50 +0100
Message-ID: 
 <CAN_p2QjrVE1bUW5qoewmX-5jckJiUK=gCigkgdorgZpJrY-f8g@mail.gmail.com>
Subject: Re: Bulk DML performance
To: bill.poole@ymail.com, pgsql-performance@postgresql.org
Content-Type: multipart/alternative; boundary="00000000000094206f06308d5cbf"
Archived-At: 
 <https://www.postgresql.org/message-id/CAN_p2QjrVE1bUW5qoewmX-5jckJiUK%3DgCigkgdorgZpJrY-f8g%40mail.gmail.com>
Precedence: bulk

--00000000000094206f06308d5cbf
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Hi,
Here are some observations.

Em seg., 17 de mar. de 2025 =C3=A0s 09:19, <bill.poole@ymail.com> escreveu:

> > PostgreSQL has a lot of overhead per row.
>
> Okay, thanks. I'm not actually too worried about this since in my
> scenario, each row is about 1.5 kB, so the % overhead is negligible.
>
> > It is probably not the lookup, but the *modification* of the index that
> is slow.
>
> Yes that makes sense for the original 3 million inserts, but when I
> perform the update of the 3 million rows, the index doesn't change - they
> are all HOT updates.
>

Using "perf" I can see that the overhead is indeed due to index lookup when
we do HOT updates.


> > Then the best you can do is to use COPY rather than INSERT. It will
> perform better (but [not] vastly better).
>
> I need to perform a merge (INSERT ... ON CONFLICT ... DO UPDATE) on the
> data, so sadly I cannot use COPY.
>
> I have discovered that for some reason, performing the original insert
> without the ON CONFLICT statement is twice as fast as performing the
> original insert with an ON CONFLICT ... DO UPDATE clause, completing in 4
> seconds instead of 8. That seems strange to me because I wouldn't have
> thought it would be doing any additional work since a unique constraint i=
s
> on the primary key, so each inserted value would need to be checked in
> either case, and there is no extra work to be done in either case.
>
>
In the INSERT case, we do not check the unique constraint for each row. We
run into an error when inserting a duplicate, aborting the operation.


> INSERT INTO test (id, text1)
> SELECT generate_series, 'x'
> FROM generate_series(1, 3000000)
>
> It remains 4 seconds even when adding a clause to not insert duplicates.
>
> INSERT INTO test (id, text1)
> SELECT generate_series, 'x'
> FROM generate_series(1, 3000000)
> WHERE NOT EXISTS (
>   SELECT 1
>   FROM test4
>   WHERE id =3D generate_series
> )
>
>
In this case, we are not checking duplicates inside the input dataset. If
you can guarantee, at the application level, that there are no duplicates,
this seems a good speedup. Perhaps the MERGE clause...


> Furthermore, I have found that performing an UPDATE rather than an INSERT
> ... ON CONFLICT ... DO UPDATE is twice as slow, completing in 16 seconds
> instead of 14 seconds.
>
> UPDATE test
> SET text1 =3D 'x'
> FROM generate_series(1, 3000000)
> WHERE test4.id =3D generate_series
>
> This also now means that updating 3 million rows takes 4x longer than
> inserting those rows. Do we expect updates to be 4x slower than inserts?
>
>
It is not the update that is slower. It is the attached where clause that
makes it slower. Try:
UPDATE test SET text1=3D'x';

In my tests, the update of non-indexed columns is slightly faster than an
insert.

Regards,
Renan Fonseca

--00000000000094206f06308d5cbf
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Hi,</div>Here are some observations.<br><br><div clas=
s=3D"gmail_quote gmail_quote_container"><div dir=3D"ltr" class=3D"gmail_att=
r">Em seg., 17 de mar. de 2025 =C3=A0s 09:19, &lt;<a href=3D"mailto:bill.po=
ole@ymail.com">bill.poole@ymail.com</a>&gt; escreveu:<br></div><blockquote =
class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px sol=
id rgb(204,204,204);padding-left:1ex">&gt; PostgreSQL has a lot of overhead=
 per row.<br>
<br>
Okay, thanks. I&#39;m not actually too worried about this since in my scena=
rio, each row is about 1.5 kB, so the % overhead is negligible.<br>
<br>
&gt; It is probably not the lookup, but the *modification* of the index tha=
t is slow.<br>
<br>
Yes that makes sense for the original 3 million inserts, but when I perform=
 the update of the 3 million rows, the index doesn&#39;t change - they are =
all HOT updates.<br></blockquote><div><br></div><div>Using &quot;perf&quot;=
 I can see that the overhead is indeed due to index lookup when we do HOT u=
pdates.</div><div>=C2=A0</div><blockquote class=3D"gmail_quote" style=3D"ma=
rgin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:=
1ex">
&gt; Then the best you can do is to use COPY rather than INSERT. It will pe=
rform better (but [not] vastly better).<br>
<br>
I need to perform a merge (INSERT ... ON CONFLICT ... DO UPDATE) on the dat=
a, so sadly I cannot use COPY.<br>
<br>
I have discovered that for some reason, performing the original insert with=
out the ON CONFLICT statement is twice as fast as performing the original i=
nsert with an ON CONFLICT ... DO UPDATE clause, completing in 4 seconds ins=
tead of 8. That seems strange to me because I wouldn&#39;t have thought it =
would be doing any additional work since a unique constraint is on the prim=
ary key, so each inserted value would need to be checked in either case, an=
d there is no extra work to be done in either case.<br>
<br></blockquote><div><br></div><div>In the INSERT case, we do not check th=
e unique constraint for each row. We run into an error when inserting a dup=
licate, aborting the operation.</div><div>=C2=A0</div><blockquote class=3D"=
gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(20=
4,204,204);padding-left:1ex">
INSERT INTO test (id, text1)<br>
SELECT generate_series, &#39;x&#39;<br>
FROM generate_series(1, 3000000)<br>
<br>
It remains 4 seconds even when adding a clause to not insert duplicates.<br=
>
<br>
INSERT INTO test (id, text1)<br>
SELECT generate_series, &#39;x&#39;<br>
FROM generate_series(1, 3000000)<br>
WHERE NOT EXISTS (<br>
=C2=A0 SELECT 1 <br>
=C2=A0 FROM test4 <br>
=C2=A0 WHERE id =3D generate_series<br>
)<br>
<br></blockquote><div><br></div><div>In this case, we are not checking dupl=
icates inside the input dataset. If you can guarantee, at the application l=
evel, that there are no duplicates, this seems a good speedup. Perhaps the =
MERGE clause...</div><div>=C2=A0</div><blockquote class=3D"gmail_quote" sty=
le=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);paddi=
ng-left:1ex">
Furthermore, I have found that performing an UPDATE rather than an INSERT .=
.. ON CONFLICT ... DO UPDATE is twice as slow, completing in 16 seconds ins=
tead of 14 seconds.<br>
<br>
UPDATE test<br>
SET text1 =3D &#39;x&#39;<br>
FROM generate_series(1, 3000000)<br>
WHERE <a href=3D"http://test4.id" rel=3D"noreferrer" target=3D"_blank">test=
4.id</a> =3D generate_series<br>
<br>
This also now means that updating 3 million rows takes 4x longer than inser=
ting those rows. Do we expect updates to be 4x slower than inserts?<br>
<br></blockquote><div><br></div><div>It is not the update that is slower. I=
t is the attached where clause that makes it slower. Try:<br>UPDATE test SE=
T text1=3D&#39;x&#39;;<br><br>In my tests, the update of non-indexed column=
s=C2=A0is slightly faster than an insert.</div><div>=C2=A0<br>Regards,<br>R=
enan Fonseca<br><br></div></div></div>

--00000000000094206f06308d5cbf--