Excellent idea David, silly me, I didn't think of that. For the other questions:
>How many partitions?
14
>How many rows do they have when performance is slowing considerably?
Not sure, maybe on the low millions
>Does this table get many updates or is it insert only?
insert
>What version of PostgreSQL?
13
>Are the inserts randomly distributed among the partitions or targeting one or a few partitions?
Sequentially one partition at a time, so each set of runs is inserting across each part.
>Are you able to capture an example and run it in a transaction with explain (analyze, buffers, verbose) and then
rollback?
Yes, I'm looking into that
pg
-----Original Message-----
From: David Rowley <dgrowleyml@gmail.com>
Sent: Wednesday, November 24, 2021 7:13 PM
To: Godfrin, Philippe E <Philippe.Godfrin@nov.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-general@lists.postgresql.org
Subject: Re: [EXTERNAL] Re: Inserts and bad performance
On Thu, 25 Nov 2021 at 08:59, Godfrin, Philippe E <Philippe.Godfrin@nov.com> wrote:
> Hi Tom. Good point about the index paging out of the buffer. I did that and no change. I do have the shared buffers
at40GB, so there’s a good bit there, but I also did all those things on the page you referred, except for using copy.
Atthis point the data has not been scrubbed, so I’m trapping data errors and duplicates. I am curios though, as
sidebar,why copy is considered faster than inserts. I was unable to get COPY faster than around 25K inserts a second
(prettyfast anyway). Frankly, initially I was running 3 concurrent insert jobs and getting 90K ins/sec ! but after a
certainnumber of records, the speed just dropped off.
EXPLAIN (ANALYZE, BUFFERS) works with INSERTs. You just need to be aware that using ANALYZE will perform the actual
inserttoo. So you might want to use BEGIN; and ROLLBACK; if it's not data that you want to keep.
SET track_io_timing = on; might help you too.
David