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>
In-Reply-To: <00a401db9400$86c240f0$9446c2d0$@ymail.com>
From: Greg Sabino Mullane <htamfids@gmail.com>
Date: Mon, 17 Mar 2025 10:14:14 -0400
Message-ID: 
 <CAKAnmmKvxMG7t8pv8FZPkrgU9Sh8V=6Y8HaK4QymK-ZA+i+mcA@mail.gmail.com>
Subject: Re: Bulk DML performance
To: bill.poole@ymail.com
Cc: Laurenz Albe <laurenz.albe@cybertec.at>, pgsql-performance@postgresql.org
Content-Type: multipart/alternative; boundary="00000000000068441306308a6936"
Archived-At: 
 <https://www.postgresql.org/message-id/CAKAnmmKvxMG7t8pv8FZPkrgU9Sh8V%3D6Y8HaK4QymK-ZA%2Bi%2BmcA%40mail.gmail.com>
Precedence: bulk

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

On Mon, Mar 17, 2025 at 4:19=E2=80=AFAM <bill.poole@ymail.com> wrote:

 Can you help me understand why performing 3 million lookups on a b-tree
> index with all pages cached in memory takes so long?


It's not the lookup, it's writing the 3 million rows (and in this
particular upsert case, deleting 3 million, then inserting 3 million)

> Well, that is not a great statement.
>
> Understood, but I was highlighting the performance of deleting 3 million
> rows identified by 3 million IDs, as opposed to deleting rows in a given
> range of IDs or deleting the whole table. It seems like deleting 3 millio=
n
> rows identified by 3 million IDs should be faster than updating 3 million
> rows (also identified by 3 million IDs).
>

It should indeed be faster. But keep in mind a delete immediately after
that upsert now has twice as many rows to walk through as the upsert did.
Also, a subselect like your original query can lead to a large nested loop.
Try another variant such as this one:

with ids as (select x from generate_series(1, 3_000_000) x) delete from
test using ids where id=3Dx;

> With the table as it is you won't get better performance if you want the
> features that a relational database provides.
>
> Sorry to hear that. I had hoped there was room to improve this performanc=
e.
>

If pure upsert performance is the goal, remove the unique index and store a
timestamp along with your inserted data. Back to pure inserts again! (and a
few new downsides). When querying, only use the version of the row with the
highest timestamp.

Other random ideas:

* remove or consolidate columns you don't need, or can store in another
table
* pre-filter the rows in the app, so you can do a pure-insert (or COPY) of
known-to-be-new rows, then upsert the remaining rows
* use the smallest data types possible
* avoid or minimize toasted values
* pack your columns efficiently (e.g. reorder for 8 byte blocks)
* put the indexes on a ram-based tablespace
* boost your work_mem (for things like giant deletes which build hashes)
* revisit unlogged tables and partitioning

Cheers,
Greg

--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support

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

<div dir=3D"ltr"><div dir=3D"ltr">On Mon, Mar 17, 2025 at 4:19=E2=80=AFAM &=
lt;<a href=3D"mailto:bill.poole@ymail.com">bill.poole@ymail.com</a>&gt; wro=
te:</div><div dir=3D"ltr"><br></div><blockquote class=3D"gmail_quote" style=
=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding=
-left:1ex">=C2=A0Can you help me understand why performing 3 million lookup=
s on a b-tree index with all pages cached in memory takes so long?</blockqu=
ote><div dir=3D"ltr"><br></div><div>It&#39;s not the lookup, it&#39;s writi=
ng the 3 million rows (and in this particular upsert case, deleting 3 milli=
on, then inserting 3 million)</div><div dir=3D"ltr"><br></div><div class=3D=
"gmail_quote gmail_quote_container"><blockquote class=3D"gmail_quote" style=
=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding=
-left:1ex">&gt; Well, that is not a great statement.<br>
<br>
Understood, but I was highlighting the performance of deleting 3 million ro=
ws identified by 3 million IDs, as opposed to deleting rows in a given rang=
e of IDs or deleting the whole table. It seems like deleting 3 million rows=
 identified by 3 million IDs should be faster than updating 3 million rows =
(also identified by 3 million IDs).<br></blockquote><div><br></div><div>It =
should indeed be faster. But keep in mind a delete immediately after that u=
psert now has twice as many rows to walk through as the upsert did. Also, a=
 subselect like your original query can lead to a large nested loop. Try an=
other variant such as this one:</div><div><br></div><div>with ids as (selec=
t x from generate_series(1, 3_000_000) x) delete from test using ids where =
id=3Dx;</div><div><br></div><blockquote class=3D"gmail_quote" style=3D"marg=
in:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1e=
x">&gt; With the table as it is you won&#39;t get better performance if you=
 want the features that a relational database provides.<br>
<br>
Sorry to hear that. I had hoped there was room to improve this performance.=
<br></blockquote><div><br></div><div>If pure upsert performance is the goal=
, remove the unique index and store a timestamp along with your inserted da=
ta. Back to pure inserts again! (and a few new downsides). When querying, o=
nly use the version of the row with the highest timestamp.</div><div><br></=
div><div>Other random ideas:</div><div><br></div><div>* remove or consolida=
te columns you don&#39;t need, or can store in another table</div><div>* pr=
e-filter the rows in the app, so you can do a pure-insert (or COPY) of know=
n-to-be-new rows, then upsert the remaining rows</div><div>* use the smalle=
st data types possible</div><div>* avoid or minimize toasted values</div><d=
iv>* pack your columns efficiently (e.g. reorder for 8 byte blocks)</div><d=
iv>* put the indexes on a ram-based tablespace</div><div>* boost your work_=
mem (for things like giant deletes which build hashes)</div><div>* revisit =
unlogged tables and partitioning</div><div><br></div><div>Cheers,</div></di=
v><div dir=3D"ltr" class=3D"gmail_signature"><div dir=3D"ltr"><div>Greg</di=
v><div><br></div><div>--</div><div>Crunchy Data - <a href=3D"https://www.cr=
unchydata.com" target=3D"_blank">https://www.crunchydata.com</a></div><div>=
Enterprise Postgres Software Products &amp; Tech Support</div><div><br></di=
v></div></div></div>

--00000000000068441306308a6936--