Message-ID: <d58bb88b06a40233aef8e07a39fd234458abc009.camel@cybertec.at>
Subject: Re: Bulk DML performance
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: bill.poole@ymail.com, pgsql-performance@postgresql.org
Date: Thu, 13 Mar 2025 12:28:09 +0100
In-Reply-To: <00a401db9400$86c240f0$9446c2d0$@ymail.com>
References: <008701db93cd$2eb404d0$8c1c0e70$.ref@ymail.com>
		 <008701db93cd$2eb404d0$8c1c0e70$@ymail.com>
	 <0942a47d7a89f1b181f7e306f8389d9717eb5fe0.camel@cybertec.at>
	 <00a401db9400$86c240f0$9446c2d0$@ymail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.54.3 (3.54.3-1.fc41) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/d58bb88b06a40233aef8e07a39fd234458abc009.camel%40cybertec.at>
Precedence: bulk

On Thu, 2025-03-13 at 18:13 +0800, bill.poole@ymail.com wrote:
>=20
> it is noteworthy that inserting 27 MB of data into a newly created table =
creates
> 191 MB of data including the index and 127 MB of data excluding the index=
.

PostgreSQL has a lot of overhead per row.

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

It is probably not the lookup, but the *modification* of the index that is =
slow.

>=20
> 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).

It should be, yes.
To find out where the time is spent, use EXPLAIN (ANALYZE, BUFFERS) on the
statement.

> > To get better performance, the best I can think of is to parallelize lo=
ading
> > the data until you saturate CPU, disk or hit internal contention in the=
 database.
>=20
> Sadly, I cannot do that because I need all rows to be inserted in a singl=
e
> database transaction, which I cannot do over multiple database connection=
s.

Then the best you can do is to use COPY rather than INSERT.
It will perform better (but now vastly better).

Yours,
Laurenz Albe