0 概述
分析三種類型的insert在parse的各個(gè)階段的差異：
insert into TAB_IS SELECT * FROM STUDENT a WHERE a.sno > ANY (SELECT b.sno from STUDENT b);
insert into TAB_IS values(10, 'AAA');
insert into TAB_IS values(20, 'CCC'),(30, 'DDD'),(40, 'EEE');
不同insert的計(jì)劃樹type
# T_NestLoopState
insert into TAB_IS SELECT * FROM STUDENT a WHERE a.sno > ANY (SELECT b.sno from STUDENT b);
# T_ResultState
insert into TAB_IS values(10, 'AAA');
# T_ValuesScanState
insert into TAB_IS values(20, 'CCC'),(30, 'DDD'),(40, 'EEE');
# T_FunctionScanState
insert into TAB_IS select i, 'QQQ', i % 10 from generate_series(1, 1000) t(i);
# T_ProjectSetState
insert into TAB_IS values(generate_series(1,10), 'DDD', 1);
1 語義分析差異
下面三種SQL在語義分析結(jié)果來看有什么區(qū)別？
insert into TAB_IS SELECT * FROM STUDENT a WHERE a.sno > ANY (SELECT b.sno from STUDENT b);
insert into TAB_IS values(10, 'AAA');
insert into TAB_IS values(20, 'CCC'),(30, 'DDD'),(40, 'EEE');
語義分析結(jié)果來看，insert語句都會(huì)構(gòu)造插入表和數(shù)據(jù)表兩張表（RangeTblEntry），數(shù)據(jù)表可能是值構(gòu)造出來的，或者是select查詢出來的。
核心流程都是構(gòu)造數(shù)據(jù)表的RangeTblEntry。
代碼位置：
transformInsertStmt
SelectStmt *selectStmt = (SelectStmt *) stmt->selectStmt;
// 如果selectStmt非空，表示存在select子句
if (selectStmt == NULL)
... // 普通insert
else if (isGeneralSelect)
... // 帶select子句
else if (list_length(selectStmt->valuesLists) > 1)
... // 多values
1 insert select語義分析結(jié)果
pg_analyze_and_rewrite_fixedparams
insert into TAB_IS SELECT * FROM STUDENT a WHERE a.sno > ANY (SELECT b.sno from STUDENT b);語義分析結(jié)果
2 insert values語義分析結(jié)果
insert into TAB_IS values(10, 'AAA');語義分析結(jié)果
3 insert values values語義分析結(jié)果
insert into TAB_IS values(20, 'CCC'),(30, 'DDD'),(40, 'EEE');語義分析結(jié)果
2 優(yōu)化結(jié)果差異
一定存在ModifyTable節(jié)點(diǎn)，因?yàn)檫@是一個(gè)寫表操作，也就是會(huì)進(jìn)入ExecModifyTable函數(shù)。
ExecModifyTable函數(shù)loop下層節(jié)點(diǎn)每次拿一條數(shù)據(jù)，然后執(zhí)行insert操作。知道下層節(jié)點(diǎn)沒數(shù)據(jù)為止。
從ExecModifyTable節(jié)點(diǎn)的lefttree可以知道具體是哪種insert。
3 執(zhí)行階段
從執(zhí)行階段來看，下面三種SQL有什么區(qū)別？
insert into TAB_IS SELECT * FROM STUDENT a WHERE a.sno > ANY (SELECT b.sno from STUDENT b);
QUERY PLAN
-------------------------------------------------------------------------------------------------
Insert on tab_is (cost=0.15..208.42 rows=0 width=0)
-> Nested Loop Semi Join (cost=0.15..208.42 rows=367 width=46)
-> Seq Scan on student a (cost=0.00..21.00 rows=1100 width=46)
-> Index Only Scan using student_pkey on student b (cost=0.15..6.62 rows=367 width=4)
Index Cond: (sno < a.sno)
insert into TAB_IS values(10, 'AAA');
QUERY PLAN
----------------------------------------------------
Insert on tab_is (cost=0.00..0.01 rows=0 width=0)
-> Result (cost=0.00..0.01 rows=1 width=46)
insert into TAB_IS values(20, 'CCC'),(30, 'DDD'),(40, 'EEE');
QUERY PLAN
--------------------------------------------------------------------
Insert on tab_is (cost=0.00..0.04 rows=0 width=0)
-> Values Scan on "*VALUES*" (cost=0.00..0.04 rows=3 width=46)
執(zhí)行階段沒什么區(qū)別，都是走ExecModifyTable內(nèi)部循環(huán)搞定。文章來源：http://www.zghlxwxcb.cn/news/detail-514060.html
- 每次從lefttree中執(zhí)行一把拿到一條，
subplanstate = outerPlanState(node);context.planSlot = ExecProcNode(subplanstate);。 - 根據(jù)operation類型（insert）執(zhí)行具體insert操作ExecInsert，比較簡(jiǎn)單，中間會(huì)有slot到tuple的轉(zhuǎn)換。執(zhí)行器的元組都是包裝在slot中的?，F(xiàn)在PG的存儲(chǔ)引擎提供了AM接口，代碼更清晰了。
（執(zhí)行器層ExecInsert→存儲(chǔ)層入口table_tuple_insert）文章來源地址http://www.zghlxwxcb.cn/news/detail-514060.html
PortalRun
PortalRunMulti
ProcessQuery
CreateQueryDesc
ExecutorStart
ExecutorRun
standard_ExecutorRun
ExecutePlan
ExecProcNode
ExecProcNodeFirst
ExecModifyTable
-----> ExecProcNode(subplanstate) ---
/ switch (operation) \
\ case CMD_INSERT: /
------------- ExecInsert <----------
ps. 測(cè)試數(shù)據(jù)
drop table student;
create table student(sno int primary key, sname varchar(10), ssex int);
insert into student values(1, 'stu1', 0);
insert into student values(2, 'stu2', 1);
insert into student values(3, 'stu3', 1);
insert into student values(4, 'stu4', 0);
drop table course;
create table course(cno int primary key, cname varchar(10), tno int);
insert into course values(10, 'meth', 1);
insert into course values(11, 'english', 2);
drop table teacher;
create table teacher(tno int primary key, tname varchar(10), tsex int);
insert into teacher values(1, 'te1', 1);
insert into teacher values(2, 'te2', 0);
drop table score;
create table score (sno int, cno int, degree int);
insert into score values (1, 10, 100);
insert into score values (1, 11, 89);
insert into score values (2, 10, 99);
insert into score values (2, 11, 90);
insert into score values (3, 10, 87);
insert into score values (3, 11, 20);
insert into score values (4, 10, 60);
insert into score values (4, 11, 70);
SELECT * FROM STUDENT a WHERE a.sno > ANY (SELECT b.sno from STUDENT b);
drop table TAB_IS;
create table TAB_IS(sno int, sname varchar(10), ssex int);
insert into TAB_IS SELECT * FROM STUDENT a WHERE a.sno > ANY (SELECT b.sno from STUDENT b);
insert into TAB_IS values(10, 'AAA');
insert into TAB_IS values(20, 'CCC'),(30, 'DDD'),(40, 'EEE');
到了這里，關(guān)于Postgresql源碼（108）不同類型insert在parse階段的差異分析的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
