【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字符串為止。

其實這個特性的原理簡單來說，就是字面意義上的"批量"，即一次性處理多行索引，而且似乎不會重用之前的索引塊，類似于ORACLE中插入數據時的APPEND是直接新增塊。

• 這個功能在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 許可協議。轉載請注明出處