我正在使用 INSERT 查询分块将表中的数据插入tmp_details到details表中。插入数据时，我还在修改数据。查询执行需要花费大量时间。我的读取 IOPS 达到 2500，写入 IOPS 接近 100。我的 I/O 利用率为 100%，而 CPU 利用率不到 10%。我的 RAM 利用率不到 40%。我正在使用ctid2,000,000 行的块插入。我可以做些什么来提高查询性能并降低 I/O 利用率？我想将 CPU 利用率提高到 80% 以上。
服务器规格：
- PostgreSQL 版本：15.6
- 内存：32 GB
- 核心数： 16
- 磁盘空间：SSD 500 GB
- 操作系统：Linux Ubuntu 22.04
PostgreSQL 配置：
max_connections = 200
shared_buffers = 8GB
effective_cache_size = 24GB
maintenance_work_mem = 2GB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 5242kB
huge_pages = try
min_wal_size = 1GB
max_wal_size = 4GB
max_worker_processes = 16
max_parallel_workers_per_gather = 4
max_parallel_workers = 16
max_parallel_maintenance_workers = 4
表格详细信息：
| 表名 | 行数 | 尺寸 |
|---|---|---|
| 来源_cbsupi_tmp_details | 6000 万 | 30 GB |
| 源_npciupi_tmp_详细信息 | 6000 万 | 30 GB |
| 来源_cbsupi_tmp_details | 6000 万 | 30 GB |
uniquekey列、、、和上有索引。我必须使用 DISTINCT ON 子句，因为由于 JOIN 我得到了重复的行。我尝试使用 插入 1,000,000 行，key_priority_radcs但执行仍然需要很长时间，很可能是因为它必须在每次迭代中扫描整个表 C 和 D。因此，我将数据作为整体插入了 6000 万行，然后在最后执行了提交。我的目标是从后端应用服务器并行运行这些类似的表 C 和 D 插入查询，但如果我的 I/O 利用率为 100%，这将毫无意义。key_priority_rtdpsis_processedkey_priority_ratrsctid
插入查询：
EXPLAIN
INSERT
INTO
cbsupi.source_cbsupi_details (codglacct,
refusrno,
key_priority_radcs,
recon_created_date,
dattxnposting,
status,
uniquekey,
coddrcr,
cbsacqiss,
codacctno,
amttxnlcy,
acnotrim,
priority_no,
rrn,
recon_updated_date,
recon_date_1_to_2,
recon_date_1_to_3,
reconciliation_date_time ) (
SELECT
DISTINCT ON
(A.uniquekey) A.codglacct,
A.refusrno,
A.key_priority_radcs,
A.recon_created_date,
A.dattxnposting,
A.status,
A.uniquekey,
A.coddrcr,
A.cbsacqiss,
A.codacctno,
A.amttxnlcy,
A.acnotrim,
A.priority_no,
A.rrn,
'2025-01-07 19:50:41' AS recon_updated_date,
CASE
WHEN C.key_priority_rtdps IS NOT NULL THEN '2025-01-07 19:50:41'
ELSE NULL
END::TIMESTAMP AS recon_date_1_to_2,
CASE
WHEN D.key_priority_ratrs IS NOT NULL THEN '2025-01-07 19:50:41'
ELSE NULL
END::TIMESTAMP AS recon_date_1_to_3,
CASE
WHEN (C.key_priority_rtdps IS NOT NULL
AND D.key_priority_ratrs IS NOT NULL) THEN '2025-01-07 19:50:41'
ELSE NULL
END::TIMESTAMP AS reconciliation_date_time
FROM
cbsupi.source_cbsupi_tmp_details A
LEFT JOIN switchupi.source_switchupi_tmp_details C ON
(A.key_priority_radcs = C.key_priority_rtdps)
LEFT JOIN npciupi.source_npciupi_tmp_details D ON
(A.key_priority_radcs = D.key_priority_ratrs)
WHERE
A.is_processed IS NULL ) ON
CONFLICT (uniquekey) DO
UPDATE
SET
recon_updated_date = EXCLUDED.recon_updated_date,
recon_date_1_to_3 = EXCLUDED.recon_date_1_to_3,
key_priority_radcs = EXCLUDED.key_priority_radcs,
status = EXCLUDED.status,
reconciliation_date_time = EXCLUDED.reconciliation_date_time,
codacctno = EXCLUDED.codacctno,
amttxnlcy = EXCLUDED.amttxnlcy,
recon_date_1_to_2 = EXCLUDED.recon_date_1_to_2,
rrn = EXCLUDED.rrn,
codglacct = EXCLUDED.codglacct,
refusrno = EXCLUDED.refusrno,
dattxnposting = EXCLUDED.dattxnposting,
coddrcr = EXCLUDED.coddrcr,
cbsacqiss = EXCLUDED.cbsacqiss,
acnotrim = EXCLUDED.acnotrim,
priority_no = EXCLUDED.priority_no;
解释结果
"QUERY PLAN"
Insert on source_cbsupi_details (cost=72270111.44..73213761.44 rows=0 width=0)
Conflict Resolution: UPDATE
Conflict Arbiter Indexes: source_cbsupi_details_pkey
" -> Subquery Scan on ""*SELECT*"" (cost=72270111.44..73213761.44 rows=62910000 width=811)"
-> Unique (cost=72270111.44..72584661.44 rows=62910000 width=823)
-> Sort (cost=72270111.44..72427386.44 rows=62910000 width=823)
Sort Key: a.uniquekey
-> Hash Left Join (cost=10739152.00..50771187.50 rows=62910000 width=823)
Hash Cond: (a.key_priority_radcs = d.key_priority_ratrs)
-> Hash Left Join (cost=5337191.00..25537830.00 rows=62910000 width=800)
Hash Cond: (a.key_priority_radcs = c.key_priority_rtdps)
-> Seq Scan on source_cbsupi_tmp_details a (cost=0.00..2092124.00 rows=62910000 width=767)
Filter: (is_processed IS NULL)
-> Hash (cost=4118441.00..4118441.00 rows=60000000 width=33)
-> Seq Scan on source_switchupi_tmp_details c (cost=0.00..4118441.00 rows=60000000 width=33)
-> Hash (cost=4124101.00..4124101.00 rows=62910000 width=33)
-> Seq Scan on source_npciupi_tmp_details d (cost=0.00..4124101.00 rows=62910000 width=33)
JIT:
Functions: 24
" Options: Inlining true, Optimization true, Expressions true, Deforming true"
问题：
- 如何提高查询的性能并降低I/O利用率？
- 有没有办法从应用程序并行运行这些类似的插入查询而不会达到 I/O 利用率限制？
- 分块插入数据对我有好处吗，还是一次性插入全部数据更好？因为，从我的观察来看，分块插入数据比一次性插入全部数据花费的时间更多。
编辑：
我附上了select 语句的查询计划。显然，Insert 查询花费了一个多小时，而 Select 语句仅花费了 265 秒。我认为我的问题在于最后的单个提交，可能是由于过多的日志生成造成的。如果我保持自动提交，它会起作用吗？有没有办法分块插入而不循环遍历整个表？
对于独立选择，它花费了超过 4 分钟，并且使用了 9 个进程。插入不会使用并行化（即使是选择部分），因此我们可能天真地认为仅运行该查询的选择部分就需要超过 36 分钟。这还不包括运行触发器或更新索引，而 INSERT 当然需要这样做，因此是的，我们可以很容易地预期 INSERT 需要一个多小时。
不幸的是，即使你让 INSERT 运行完成
EXPLAIN (ANALYZE, BUFFERS)，并将 track_io_timing 设置为 on，PostgreSQL 仍然不会给你任何有意义的信息，说明维护索引（无论是单独还是集体）花费了多少时间。但它至少会给你有关运行触发器所用时间的信息，以及 ON CONFLICT 解决了多少行。调查索引问题的最佳方法是创建一个具有相同列但没有触发器或索引的虚拟表，然后查看插入该虚拟表需要多长时间。当然，在这种情况下，没有行会导致 ON CONFLICT 操作，因此这不会反映该部分情况。然后，您可以逐个添加索引，看看是否有某个索引主导了运行时间。
您可以在 select 中添加 ORDER BY，这样可以按更有利于索引查找和维护的顺序返回行。但由于您有 5 个单独的索引，因此不太可能有一个顺序可以满足所有索引。当然，如果它确实有效，您甚至不需要从应用程序并行执行它即可获得好处。
这两个决定是独立的吗？还是你只是假设既然 2500 IOPS 达到了你承诺的速率，那么它就必须被 100% 利用，但没有对这一事实进行独立评估？请注意，索引维护通常会生成随机 IO，而你的 SELECT 计划主要会生成顺序 IO。顺序 IO 捆绑到 IOPS 中的效率取决于细节或你的内核/FS/IO 系统，我不知道。
从机械/理论角度来看，这不太可能，因为如果它是真的，您会期望您的 IOPS 主要由写入而不是读取决定。