【GaussDB】深入剖析Insert Select慢的定位全过程

【GaussDB】深入剖析-Insert Select慢的定位全过程

问题描述

insert into t_target select * from t_source;

• 源表和目标表结构一样，只有两个字段，皆为文本类型，行长度30字节以内，主键包括这两个字段。
• 目标表为空表，源表500万行，表大小一百多MB，主键索引大小两百多MB。
• 插入需要10分钟。
• 删掉目标表的主键，插入只需要十几秒。

官方文档调研

阅读官方文档，发现在506.0版本中引入了一个新特性，叫“支持ubtree批量插入索引”

官方文档链接

当以下任何一个场景满足所有的约束条件时，建议打开GUC参数batch_insert_index_types并设置为"rcr_ubtree"以提升批量插入索引性能：

场景1：使用JDBC连接数据库，通过java的批量插入数据接口executeBatch进行批量插入索引；

场景2：执行语句insert into t1 select * from t2，其中t1和t2表schema相同，t1表包含索引，此语句支持在t1表上批量插入索引；必须在批量插入表数据的前提下才会批量插入索引（explain verbose命令可以查看是否对表数据做批量插入，Batch Insert代表是批量插入）。满足这些前提后，打开批量插入索引开关就会执行批量插入索引；

场景3：执行语句COPY FROM（示例：COPY t1 FROM ‘file.csv’ delimiter ‘,’ csv header），其中t1和file.csv文件中的schema相同，t1表包含索引，此语句支持在t1表上批量插入索引。

约束1：插入的索引类型为UBTree RCR索引；

约束2：插入数据不递增（插入数据递增会利用缓存，即最后一页，这种情况下批量插入索引无性能提升）；

约束3：不是SMP的批量索引插入。

此特性在以下三个场景会影响性能：

1. 如果批量插入数据是递增的（违反约束2），关闭批量插入功能后，会插入到最右缓存中，性能较好。这种场景下开启批量插入功能会导致性能出现轻微劣化。
2. 在执行持续的批量插入时，索引会逐渐变大，进而导致批量插入性能有所下降。
3. 批量插入数据过于分散导致缓存页面命中率过低，或者运行内存过小的场景下，开启批量插入功能会导致性能出现较大幅度劣化。

如果性能劣化幅度较大，建议设置GUC参数batch_insert_index_types为""，关闭该功能，性能劣化问题即可恢复。

如果需要技术支持工程师定位原因，可以设置GUC参数logging_module=‘on(BIINDEX)’，打开批量插入索引的日志打印，执行几分钟批量插入直到日志中出现BIINDEX字符串为止。

其实这个特性的原理简单来说，就是中间加了层索引的缓存页面，可以提前将索引进行排序，加快插入索引的速度（与之相对的其实就是批量数据插入时的单行索引插入）。

• 这个功能在GaussDB中是默认开启的，创建的索引默认就是RCR索引。
• 指定创建PCR索引会报错GAUSS-40615: Extreme redo does not support pcr's ubtree.（Extreme Redo不支持 pcr's ubtree。）（没找到任何有关Extreme redo的说明）。
• batch_insert_index_types默认为rcr_ubtree。

疑问与实验

明明有这个提升性能的特性，但是为什么还是会插入慢呢？

• 尝试在插入前把batch_insert_index_types设置为空，但执行时长没有变化。
• 怀疑源表构造的数据可能是乱序的，如果没有走到索引批量插入的逻辑，或者一批次的索引很少，那么插入的索引不是在最右侧时，对前面的索引可能会有影响，由此带来非常大的性能开销。
• 测试在insert select 的后面，加上order by 主键字段(index only scan)，结果插入只要三十几秒了，其中查询阶段十几秒，插入阶段二十秒。

但这套应用系统里这种场景非常多，不可能每个SQL都去加上排序，而且对大量数据进行排序本来就是开销非常大的，有些表的主键字段非常多，work_mem默认配置也就64MB,会频繁触发落盘。

此时，开发测试人员反馈：

• 他们在另一套实例里构造了相同的数据的表，执行插入只要40秒。

难道是环境差异导致？

• 对比两个机器硬件配置，执行耗时长的机器，硬件配置还更高。
• 再对比两套数据库的参数配置，并逐个调成一样进行测试，最终发现是shared_buffers过小影响的。
• 在执行耗时长的那个环境中，shared_buffers只有初始的32MB，而正常的那个环境则有10GB。

小型模拟测试用例

尝试复现这个问题：

show shared_buffers; --32MB

drop table if exists t_target;
drop table if existst_source;

CREATE TABLE t_target(c1 varchar2(6) ,c2 varchar2(12));

CREATE TABLE t_source(c1 varchar2(6) ,c2 varchar2(12));

--38MB
insert into t_source 
select  FLOOR(random() * 1e6)::NUMERIC(6, 0),FLOOR(random() * 1e12)::NUMERIC(12, 0) from pg_catalog.generate_series(1,1000000) ;

alter table t_source add primary key (c1,c2);

truncate table t_target;
insert into t_target select * from t_source;--3秒

truncate table t_target;
alter table t_target add primary key (c1,c2);
insert into t_target select * from t_source;--200秒

有了主键后，插入耗时有两个数量级的差异。

日志分析

在会话中开启set logging_module='on(BIINDEX)'，再执行插入，在日志中能看到详细的信息：

2025-07-17 14:43:55.005 primary admin postgres 172.17.0.1 140393958078208 63507[0:0#0] 1335387 Mogeaver 23.3.1 - SQLEditor <Script-22.sql> 00000 19140298416761840 [BIINDEX] LOCATION:  batch_insert_ubtree, storage_ubtree_base.cpp:3068
2025-07-17 14:43:55.005 primary admin postgres 172.17.0.1 140393958078208 63507[0:0#0] 1335387 Mogeaver 23.3.1 - SQLEditor <Script-22.sql> 00000 19140298416761840 [BIINDEX] LOG:  00000: [BATCH_INSERT_INDEX_USTORE] end batch insert index, table oid 315657, index num 1.
2025-07-17 14:43:55.005 primary admin postgres 172.17.0.1 140393958078208 63507[0:0#0] 1335387 Mogeaver 23.3.1 - SQLEditor <Script-22.sql> 00000 19140298416761840 [BIINDEX] LOCATION:  batch_insert_index, nbtsort.cpp:2223
2025-07-17 14:43:55.006 primary admin postgres 172.17.0.1 140393958078208 63507[0:0#0] 1335387 Mogeaver 23.3.1 - SQLEditor <Script-22.sql> 00000 19140298416761840 [BIINDEX] LOG:  00000: [BATCH_INSERT_INDEX_USTORE] begin batch insert index, table oid 315657, index num 1.
2025-07-17 14:43:55.006 primary admin postgres 172.17.0.1 140393958078208 63507[0:0#0] 1335387 Mogeaver 23.3.1 - SQLEditor <Script-22.sql> 00000 19140298416761840 [BIINDEX] LOCATION:  batch_insert_index, nbtsort.cpp:2200
2025-07-17 14:43:55.006 primary admin postgres 172.17.0.1 140393958078208 63507[0:0#0] 1335387 Mogeaver 23.3.1 - SQLEditor <Script-22.sql> 00000 19140298416761840 [BIINDEX] LOG:  00000: [BATCH_INSERT_INDEX_USTORE] batch insert normal index t_target_pkey(315660), heap t_target(315657), num_index_tuples: 577.
2025-07-17 14:43:55.006 primary admin postgres 172.17.0.1 140393958078208 63507[0:0#0] 1335387 Mogeaver 23.3.1 - SQLEditor <Script-22.sql> 00000 19140298416761840 [BIINDEX] LOCATION:  open_normal_or_global_index, nbtsort.cpp:2358
2025-07-17 14:43:55.015 primary admin postgres 172.17.0.1 140393958078208 63507[0:0#0] 1335387 Mogeaver 23.3.1 - SQLEditor <Script-22.sql> 00000 19140298416761840 [BIINDEX] LOG:  00000: [BATCH_INSERT_INDEX_USTORE] stringdata:bi_info_datas = [(0, 0, 0, 17540), (1, 0, 0, 16403), (2, 0, 0, 7508), (3, 0, 0, 14139), (4, 0, 0, 18439), (5, 0, 0, 1322), (6, 0, 0, 18616), (7, 0, 0, 10662), (8, 0, 0, 17898), (9, 0, 0, 2365), (10, 0, 0, 16977), (11, 0, 0, 17387), (12, 0, 0, 544), (13, 0, 0, 12399), (14, 0, 0, 6825), (15, 0, 0, 10745), (16, 0, 0, 5191), (17, 0, 0, 13062), (18, 0, 0, 4677), (19, 0, 0, 5429), (20, 0, 0, 12442), (21, 0, 0, 11167), (22, 0, 0, 9308), (23, 0, 0, 11858), (24, 0, 0, 12269), (25, 0, 0, 3726), (26, 0, 0, 15781), (27, 0, 0, 17217), (28, 0, 0, 5843), (29, 0, 0, 19259), (30, 0, 0, 10490), (31, 0, 0, 15464), (32, 0, 0, 18382), (33, 0, 0, 14359), (34, 0, 0, 7962), (35, 0, 0, 3110), (36, 0, 0, 6929), (37, 0, 0, 15941), (38, 0, 0, 14851), (39, 0, 0, 14840), (40, 0, 0, 13932), (41, 0, 0, 5679), (42, 0, 0, 11388), (43, 0, 0, 2542), (44, 0, 0, 15708), (45, 0, 0, 12770), (46, 0, 0, 11744), (47, 0, 0, 3966), (48, 0, 0, 13057), (49, 0, 0, 9268), (50, 0, 0, 12992), (51, 0, 0, 11874), (52, 0, 0, 12351), (53, 0, 0, 20069), (54, 0, 0, 3222), (55, 0, 0, 12779), (56, 0, 0, 1476), (57, 0, 0, 1274), (58, 0, 0, 6548), (59, 0, 0, 7431), (60, 0, 0, 13039), (61, 0, 0, 6384), (62, 0, 0, 11056), (63, 0, 0, 6701), (64, 0, 0, 11116), (65, 0, 0, 5173), (66, 0, 0, 10246), (67, 0, 0, 17924), (68, 0, 0, 9581), (69, 0, 0, 13053), (70, 0, 0, 19165), (71, 0, 0, 11271), (72, 0, 0, 19155), (73, 0, 0, 11618), (74, 0, 0, 5858), (75, 0, 0, 12331), (76, 0, 0, 11793), (77, 0, 0, 5606), (78, 0, 0, 11113), (79, 0, 0, 13519), (80, 0, 0, 20535), (81, 0, 0, 2977), (82, 0, 0, 3803), (83, 0, 0, 16767), (84, 0, 0, 16405), (85, 0, 0, 6502), (86, 0, 0, 19854), (87, 0, 0, 11630), (88, 0, 0, 9721), (89, 0, 0, 15030), (90, 0, 0, 17435), (91, 0, 0, 18196), (92, 0, 0, 12340), (93, 0, 0, 3192), (94, 0, 0, 13779), (95, 0, 0, 17600), (96, 0, 0, 2789), (97, 0, 0, 1128), (98, 0, 0, 5620), (99, 0, 0, 15250), (100, 0, 0, 963), (101, 0, 0, 17189), (102, 0, 0, 14080), (103, 0, 0, 15623), (104, 0, 0, 7386), (105, 0, 0, 18292), (106, 0, 0, 9805), (107, 0, 0, 13993), (108, 0, 0, 18154), (109, 0, 0, 10204), (110, 0, 0, 9638), (111, 0, 0, 19694), (112, 0, 0, 9664), (113, 0, 0, 9415), (114, 0, 0, 16983), (115, 0, 0, 3722), (116, 0, 0, 15314), (117, 0, 0, 15204), (118, 0, 0, 16705), (119, 0, 0, 17313), (120, 0, 0, 12978), (121, 0, 0, 15393), (122, 0, 0, 6847), (123, 0, 0, 13194), (124, 0, 0, 679), (125, 0, 0, 12425), (126, 0, 0, 2859), (127, 0, 0, 20163), (128, 0, 0, 5965), (129, 0, 0, 8555), (130, 0, 0, 2198), (131, 0, 0, 8076), (132, 0, 0, 11780), (133, 0, 0, 11118), (134, 0, 0, 1943), (135, 0, 0, 3693), (136, 0, 0, 17993), (137, 0, 0, 2255), (138, 0, 0, 6806), (139, 0, 0, 12666), (140, 0, 0, 14177), (141, 0, 0, 8195), (142, 0, 0, 12960), (143, 0, 0, 11806), (144, 0, 0, 2162), (145, 0, 0, 15572), (146, 0, 0, 8621), (147, 0, 0, 14110), (148, 0, 0, 7551), (149, 0, 0, 15156), (150, 0, 0, 17098), (151, 0, 0, 9718), (152, 0, 0, 16581), (153, 0, 0, 13839), (154, 0, 0, 11879), (155, 0, 0, 10274), (156, 0, 0, 12789), (157, 0, 0, 12814), (158, 0, 0, 4709), (159, 0, 0, 20098), (160, 0, 0, 16443), (161, 0, 0, 1783), (162, 0, 0, 493), (163, 0, 0, 14341), (164, 0, 0, 10955), (165, 0, 0, 21249), (166, 0, 0, 12980), (167, 0, 0, 2753), (168, 0, 0, 12310), (169, 0, 0, 4763), (170, 0, 0, 13700), (171, 0, 0, 7610), (172, 0, 0, 13112), (173, 0, 0, 964), (174, 0, 0, 6772), (175, 0, 0, 6874), (176, 0, 0, 14449), (177, 0, 0, 9420), (178, 0, 0, 14581), (179, 0, 0, 1651), (180, 0, 0, 5033), (181, 0, 0, 12730), (182, 0, 0, 6123), (183, 0, 0, 21382), (184, 0, 0, 2557), (185, 0, 0, 3037), (186, 0, 0, 1586), (187, 0, 0, 379), (188, 0, 0, 1561), (189, 0, 0, 16263), (190, 0, 0, 4445), (191, 0, 0, 1213), (192, 0, 0, 11503), (193, 0, 0, 2309), (194, 0, 0, 4747), (195, 0, 0, 12500), (196, 0, 0, 16355), (197, 0, 0, 8140), (198, 0, 0, 15524), (199, 0, 0, 5193), (200, 0, 0, 16746), (201, 0, 0, 12298), (202, 0, 0, 2754), (203, 0, 0, 15862), (204, 0, 0, 12670), (205, 0, 0, 15655), (206, 0, 0, 15099), (207, 0, 0, 18336), (208, 0, 0, 17571), (209, 0, 0, 12817), (210, 0, 0, 3338), (211, 0, 0, 20148), (212, 0, 0, 9701), (213, 0, 0, 9560), (214, 0, 0, 5453), (215, 0, 0, 3343), (216, 0, 0, 17116), (217, 0, 0, 2525), (218, 0, 0, 19989), (219, 0, 0, 10987), (220, 0, 0, 17762), (221, 0, 0, 8294), (222, 0, 0, 20612), (223, 0, 0, 18755), (224, 0, 0, 18000), (225, 0, 0, 19678), (226, 0, 0, 21004), (227, 0, 0, 8559), (228, 0, 0, 13793), (229, 0, 0, 15593), (230, 0, 0, 5947), (231, 0, 0, 2962), (232, 0, 0, 18567), (233, 0, 0, 5982), (234, 0, 0, 6360), (235, 0, 0, 13435), (236, 0, 0, 2408), (237, 0, 0, 4410), (238, 0, 0, 15625), (239, 0, 0, 16670), (240, 0, 0, 14694), (241, 0, 0, 488), (242, 0, 0, 6272), (243, 0, 0, 966), (244, 0, 0, 18387), (245, 0, 0, 14252), (246, 0, 0, 10761), (247, 0, 0, 11562), (248, 0, 0, 5447), (249, 0, 0, 12547), (250, 0, 0, 15585), (251, 0, 0, 17316), (252, 0, 0, 46), (253, 0, 0, 11417), (254, 0, 0, 15614), (255, 0, 0, 17291), (256, 0, 0, 19646), (257, 0, 0, 16986), (258, 0, 0, 18520), (259, 0, 0, 12415), (260, 0, 0, 8241), (261, 0, 0, 13206), (262, 0, 0, 12724), (263, 0, 0, 10818), (264, 0, 0, 1250), (265, 0, 0, 18071), (266, 0, 0, 14735), (267, 0, 0, 20994), (268, 0, 0, 11745), (269, 0, 0, 9511), (270, 0, 0, 20600), (271, 0, 0, 11574), (272, 0, 0, 1883), (273, 0, 0, 14086), (274, 0, 0, 9055), (275, 0, 0, 9258), (276, 0, 0, 6350), (277, 0, 0, 10652), (278, 0, 0, 20254), (279, 0, 0, 8936), (280, 0, 0, 2326), (281, 0, 0, 16226), (282, 0, 0, 8365), (283, 0, 0, 1016), (284, 0, 0, 13802), (285, 0, 0, 14478), (286, 0, 1, 14478), (287, 0, 0, 8943), (288, 0, 0, 1917), (289, 0, 0, 4405), (290, 0, 0, 1134), (291, 0, 0, 8510), (292, 0, 0, 4182), (293, 0, 0, 12869), (294, 0, 0, 10720), (295, 0, 0, 1756), (296, 0, 0, 15764), (297, 0, 0, 20968), (298, 0, 0, 17006), (299, 0, 0, 20591), (300, 0, 0, 15467), (301, 0, 1, 15467), (302, 0, 0, 6125), (303, 0, 0, 1980), (304, 0, 0, 17260), (305, 0, 0, 8484), (306, 0, 0, 10295), (307, 0, 0, 6840), (308, 0, 0, 16151), (309, 0, 0, 15234), (310, 0, 0, 13162), (311, 0, 0, 18084), (312, 0, 0, 8242), (313, 0, 0, 6297), (314, 0, 0, 1596), (315, 0, 0, 16133), (316, 0, 0, 12905), (317, 0, 0, 13274), (318, 0, 0, 20370), (319, 0, 0, 12540), (320, 0, 0, 3097), (321, 0, 0, 14112), (322, 0, 0, 14655), (323, 0, 0, 19105), (324, 0, 0, 19061), (325, 0, 0, 19701), (326, 0, 0, 6581), (327, 0, 0, 8394), (328, 0, 0, 5778), (329, 0, 0, 14467), (330, 0, 0, 16376), (331, 0, 0, 5779), (332, 0, 0, 11398), (333, 0, 0, 16727), (334, 0, 0, 8247), (335, 0, 0, 11531), (336, 0, 0, 3823), (337, 0, 0, 21299), (338, 0, 0, 3055), (339, 0, 0, 1974), (340, 0, 0, 882), (341, 0, 0, 13230), (342, 0, 0, 1307), (343, 0, 0, 14656), (344, 0, 0, 20743), (345, 0, 0, 7223), (346, 0, 0, 14708), (347, 0, 0, 20652), (348, 0, 0, 8507), (349, 0, 0, 3933), (350, 0, 0, 1056), (351, 0, 0, 10115), (352, 0, 0, 3211), (353, 0, 0, 15674), (354, 0, 0, 5836), (355, 0, 0, 5455), (356, 0, 0, 10141), (357, 0, 0, 19714), (358, 0, 0, 6600), (359, 0, 0, 2803), (360, 0, 0, 320), (361, 0, 0, 15870), (362, 0, 0, 15341), (363, 0, 0, 13808), (364, 0, 0, 17797), (365, 0, 0, 10292), (366, 0, 0, 2463), (367, 0, 0, 8355), (368, 0, 0, 985), (369, 0, 0, 6505), (370, 0, 0, 12000), (371, 0, 0, 4968), (372, 0, 0, 10861), (373, 0, 0, 7689), (374, 0, 1, 7689), (375, 0, 0, 16284), (376, 0, 0, 7402), (377, 0, 0, 10024), (378, 0, 0, 7401), (379, 0, 0, 8308), (380, 0, 0, 13270), (381, 0, 0, 14486), (382, 0, 0, 19293), (383, 0, 0, 8580), (384, 0, 1, 8580), (385, 0, 0, 20532), (386, 0, 0, 14225), (387, 0, 0, 15372), (388, 0, 0, 4006), (389, 0, 0, 10043), (390, 0, 0, 13164), (391, 0, 0, 917), (392, 0, 0, 9237), (393, 0, 0, 11699), (394, 0, 0, 14382), (395, 0, 0, 4092), (396, 0, 0, 9499), (397, 0, 0, 7418), (398, 0, 0, 8634), (399, 0, 0, 9928), (400, 0, 0, 19665), (401, 0, 0, 85), (402, 0, 0, 10134), (403, 0, 0, 10999), (404, 0, 0, 14191), (405, 0, 0, 21221), (406, 0, 0, 19351), (407, 0, 0, 9465), (408, 0, 0, 2504), (409, 0, 0, 5296), (410, 0, 0, 19722), (411, 0, 0, 17609), (412, 0, 0, 2636), (413, 0, 0, 8270), (414, 0, 0, 18121), (415, 0, 0, 1976), (416, 0, 0, 20714), (417, 0, 0, 6574), (418, 0, 0, 12019), (419, 0, 0, 14877), (420, 0, 0, 2591), (421, 0, 0, 6860), (422, 0, 0, 20575), (423, 0, 0, 5748), (424, 0, 0, 3871), (425, 0, 0, 14207), (426, 0, 0, 9381), (427, 0, 0, 17776), (428, 0, 1, 17776), (429, 0, 0, 19606), (430, 0, 0, 5202), (431, 0, 0, 16331), (432, 0, 0, 7993), (433, 0, 0, 516), (434, 0, 0, 3938), (435, 0, 0, 14590), (436, 0, 0, 19273), (437, 0, 0, 4715), (438, 0, 0, 19204), (439, 0, 0, 1997), (440, 0, 0, 4671), (441, 0, 0, 13991), (442, 0, 0, 7110), (443, 0, 0, 1062), (444, 0, 0, 7161), (445, 0, 0, 17381), (446, 0, 0, 8225), (447, 0, 0, 2492), (448, 0, 0, 1503), (449, 0, 0, 15721), (450, 0, 0, 3842), (451, 0, 0, 5999), (452, 0, 0, 11752), (453, 0, 0, 20404), (454, 0, 0, 5624), (455, 0, 0, 10339), (456, 0, 0, 12321), (457, 0, 0, 16067), (458, 0, 0, 11976), (459, 0, 0, 3000), (460, 0, 0, 6695), (461, 0, 0, 21316), (462, 0, 0, 13220), (463, 0, 0, 12128), (464, 0, 0, 15136), (465, 0, 0, 10284), (466, 0, 0, 9974), (467, 0, 0, 2651), (468, 0, 0, 21326), (469, 0, 0, 2427), (470, 0, 0, 10857), (471, 0, 0, 986), (472, 0, 0, 5378), (473, 0, 0, 3592), (474, 0, 0, 14416), (475, 0, 0, 5941), (476, 0, 0, 13181), (477, 0, 0, 17497), (478, 0, 0, 3083), (479, 0, 0, 6949), (480, 0, 0, 3532), (481, 0, 0, 15270), (482, 0, 0, 16411), (483, 0, 0, 12420), (484, 0, 0, 19263), (485, 0, 0, 9903), (486, 0, 0, 15198), (487, 0, 0, 420), (488, 0, 0, 12844), (489, 0, 0, 15230), (490, 0, 0, 16500), (491, 0, 0, 13544), (492, 0, 0, 3505), (493, 0, 0, 18812), (494, 0, 0, 7896), (495, 0, 0, 8554), (496, 0, 0, 13652), (497, 0, 0, 1830), (498, 0, 0, 10621), (499, 0, 0, 9082), (500, 0, 0, 12645), (501, 0, 0, 13506), (502, 0, 0, 11277), (503, 0, 0, 8624), (504, 0, 0, 10574), (505, 0, 0, 9260), (506, 0, 0, 825), (507, 0, 0, 12014), (508, 0, 0, 5056), (509, 0, 0, 2566), (510, 0, 0, 7047), (511, 0, 0, 6787), (512, 0, 0, 6469), (513, 0, 0, 16229), (514, 0, 0, 12805), (515, 0, 0, 8625), (516, 0, 0, 15637), (517, 0, 0, 15343), (518, 0, 0, 8095), (519, 0, 0, 13263), (520, 0, 0, 453), (521, 0, 0, 9648), (522, 0, 0, 19996), (523, 0, 0, 17766), (524, 0, 0, 16695), (525, 0, 0, 18073), (526, 0, 0, 3441), (527, 0, 0, 867), (528, 0, 0, 15184), (529, 0, 0, 11414), (530, 0, 0, 20555), (531, 0, 0, 14533), (532, 0, 0, 11369), (533, 0, 0, 15391), (534, 0, 0, 3331), (535, 0, 0, 3608), (536, 0, 0, 15563), (537, 0, 0, 173), (538, 0, 1, 173), (539, 0, 0, 20859), (540, 0, 0, 7263), (541, 0, 0, 6812), (542, 0, 0, 1537), (543, 0, 0, 19443), (544, 0, 0, 9508), (545, 0, 0, 16770), (546, 0, 0, 5036), (547, 0, 0, 4070), (548, 0, 0, 4500), (549, 0, 0, 4574), (550, 0, 0, 20569), (551, 0, 0, 21128), (552, 0, 0, 10645), (553, 0, 0, 20982), (554, 0, 0, 7146), (555, 0, 0, 7316), (556, 0, 0, 15397), (557, 0, 0, 1554), (558, 0, 0, 5545), (559, 0, 0, 3270), (560, 0, 0, 20756), (561, 0, 0, 16300), (562, 0, 0, 702), (563, 0, 0, 6485), (564, 0, 0, 5636), (565, 0, 0, 20094), (566, 0, 0, 8831), (567, 0, 0, 3506), (568, 0, 0, 7603), (569, 0, 0, 1618), (570, 0, 0, 3488), (571, 0, 0, 17034), (572, 0, 0, 21161), (573, 0, 0, 2852), (574, 0, 0, 3032), (575, 0, 0, 748), (576, 1, 0, 10894), ].
2025-07-17 14:43:55.015 primary admin postgres 172.17.0.1 140393958078208 63507[0:0#0] 1335387 Mogeaver 23.3.1 - SQLEditor <Script-22.sql> 00000 19140298416761840 [BIINDEX] LOCATION:  batch_insert_ubtree, storage_ubtree_base.cpp:3057
2025-07-17 14:43:55.015 primary admin postgres 172.17.0.1 140393958078208 63507[0:0#0] 1335387 Mogeaver 23.3.1 - SQLEditor <Script-22.sql> 00000 19140298416761840 [BIINDEX] LOG:  00000: [BATCH_INSERT_INDEX_USTORE] count is 6, rate is 0.010399.
2025-07-17 14:43:55.015 primary admin postgres 172.17.0.1 140393958078208 63507[0:0#0] 1335387 Mogeaver 23.3.1 - SQLEditor <Script-22.sql> 00000 19140298416761840 [BIINDEX] LOCATION:  batch_insert_ubtree, storage_ubtree_base.cpp:3064
2025-07-17 14:43:55.015 primary admin postgres 172.17.0.1 140393958078208 63507[0:0#0] 1335387 Mogeaver 23.3.1 - SQLEditor <Script-22.sql> 00000 19140298416761840 [BIINDEX] LOG:  00000: [BATCH_INSERT_INDEX_USTORE] cache hit rate is low, which may affect actual performance. It is recommended to set the batch_insert_index_types=''.

cache hit rate is low, which may affect actual performance. It is recommended to set the batch_insert_index_types=‘’.

日志里说缓存命中率太低，影响性能，建议设置batch_insert_index_types=''

但正如前文所说，这个场景下设置这个没什么用。

• 尝试把shared_buffers调整为大于这个表的大小，比如75MB，然后重启数据库再测试，不带主键时1秒，带主键时9s。

内存上下文观察

这里要注意一个很容易引起误会的点，gs_shared_memory_detail视图并非指的shared_buffers里的东西，而是动态内存里的共享内存，动态内存包含了 动态共享内存 和 动态会话内存。

dynamic_used_shrctx 对应视图 gs_shared_memory_detail
dynamic_used_memory 包含了 dynamic_used_shrctx
dynamic_used_memory 是 gs_shared_memory_detail和gs_session_memory_context相加

动态共享内存上下文变化

select contextname,sum(usedsize) usedsize from pg_catalog.gs_shared_memory_detail  group by contextname order by 2 desc;

可以发现内存占用大头在ASP unique sql hash table，但这是因为是数据库启动后第一次执行这条SQL，所以才增加了这个内存，后续重复执行这个SQL时，这个内存并不会变，批量插入时似乎并没有额外去申请比较大的动态共享内存。

动态会话内存上下文变化

select * from  gs_session_memory_context where sessid like '%.12' order by usedsize desc;

执行期间，多出了个ExecutorState->ModifyTable->BulkMemory，用量会有轻微的上下浮动，执行完就没了。动态会话内存占用也不大。

shared_buffers对性能的影响

另外调整shared_buffers为不同的值进行相同的插入测试，得到的时间如下（粗略测试，不排除虚拟机性能抖动）：

xychart-beta
    title "shared_buffers对批量索引性能的影响"
    x-axis "shared_buffers大小 MB" [32, 40, 45, 50, 55, 60, 65, 70, 75]
    y-axis "执行时间 秒 " 
    line "开启批量插入索引" [200, 92, 66, 47, 27, 17, 11, 10, 9]
    line "关闭批量插入索引" [200, 107, 67, 52, 28, 16, 12, 12, 10]

可以观察到，在目前这个测试环境中，insert select的插入性能受shared_buffers非常大，而batch_insert_index_types这个参数开启带来的性能提升并不明显。

也就是说，触发批量索引插入时，shared_buffers过小，会非常影响性能，由于可用动态内存是减出来的，因此大部分情况下动态内存是足够进行批量索引插入的。

额外测试

后面加测了下，如果在现场那个正常的那个环境里，插入时也加上order by，插入时间从40秒降到20秒了。

这意味着在特定场景下，先把数据顺序排好，这个批量索引插入的特性也是对性能有优化的，但官方文档说这种场景会导致性能轻微劣化，其实也并不是绝对的。在没有数据库内核源码的情况下，还是得多测试一些不同场景比较才能得到真实情况。

总结

通过对GaussDB中insert into ... select ...慢的现象进行定位和实验，发现影响批量插入性能的关键因素主要有：

1. 主键索引的存在会极大增加插入耗时，尤其在数据量大时，主键索引维护成为瓶颈。
2. shared_buffers参数过小会显著拖慢批量插入性能，建议设置为大于目标表和索引的总大小。
3. batch_insert_index_types参数（批量索引插入）在shared_buffers足够大时提升有限，但shared_buffers过小时无论开关都很慢。
4. 数据顺序对性能有影响，提前排序有时能提升插入效率，但并非所有场景都适用。
5. 实际批量索引插入动作会消耗会话内存，但shared_buffers依然是性能的决定性因素。

优化建议：

• 生产环境应合理调大shared_buffers，避免默认过小。
• 大批量数据迁移/同步时，必要时可临时去除主键索引，插入后再重建。
• 对于极端慢的场景，可尝试加order by主键字段，但需权衡排序开销。
• 关注数据库内存参数和实际物理资源，必要时与内核研发沟通原理。

参数调优和场景测试是解决数据库性能瓶颈的有效手段，实际效果需结合业务和环境多维度验证。

• 本文作者： DarkAthena
• 本文链接： https://www.darkathena.top/archives/GaussDB-A-Deep-Dive-into-Troubleshooting-Slow-Insert-Select-Operations
• 版权声明： 本博客所有文章除特别声明外，均采用CC BY-NC-SA 3.0 许可协议。转载请注明出处