Postgresql源码（95）优化器关键数据结构实例
Postgresql源码（95）优化器关键数据结构实例
mingjie
发布于 2022-12-27 16:56:35
发布于 2022-12-27 16:56:35
1 测试数据
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(1, 'meth', 10);
insert into course values(2, 'english', 11);
drop table teacher;
create table teacher(tno int primary key, tname varchar(10), tsex int);
insert into teacher values(10, 'te1', 1);
insert into teacher values(11, '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);2 列属性VAR实例
SELECT st.sname FROM STUDENT st WHERE st.sno = ANY(SELECT sno FROM SCORE WHERE st.sno=sno);
-- 结果
sname
-------
stu1
stu2
stu3
stu4
-- 计划
QUERY PLAN
-----------------------------------------------------------------
Seq Scan on student st (cost=0.00..19562.50 rows=550 width=38)
Filter: (SubPlan 1)
SubPlan 1
-> Seq Scan on score (cost=0.00..35.50 rows=10 width=4)
Filter: (st.sno = sno)PLANNER前后结果对比：
在这里插入图片描述
3 表RangeTblEntry实例
SELECT *
FROM STUDENT
LEFT JOIN SCORE ON TRUE,
(SELECT * FROM TEACHER) AS t,
COURSE,(VALUES (1, 1)) AS NUM(x, y),
GENERATE_SERIES(1, 10) AS GS(z);
-- 结果
sno | sname | ssex | sno | cno | degree | tno | tname | tsex | cno | cname | tno | x | y | z
-----+-------+------+-----+-----+--------+-----+-------+------+-----+---------+-----+---+---+----
1 | stu1 | 0 | 1 | 10 | 100 | 10 | te1 | 1 | 1 | meth | 10 | 1 | 1 | 1
1 | stu1 | 0 | 1 | 11 | 89 | 10 | te1 | 1 | 1 | meth | 10 | 1 | 1 | 1
1 | stu1 | 0 | 2 | 10 | 99 | 10 | te1 | 1 | 1 | meth | 10 | 1 | 1 | 1
1 | stu1 | 0 | 2 | 11 | 90 | 10 | te1 | 1 | 1 | meth | 10 | 1 | 1 | 1
1 | stu1 | 0 | 3 | 10 | 87 | 10 | te1 | 1 | 1 | meth | 10 | 1 | 1 | 1
1 | stu1 | 0 | 3 | 11 | 20 | 10 | te1 | 1 | 1 | meth | 10 | 1 | 1 | 1
1 | stu1 | 0 | 4 | 10 | 60 | 10 | te1 | 1 | 1 | meth | 10 | 1 | 1 | 1
1 | stu1 | 0 | 4 | 11 | 70 | 10 | te1 | 1 | 1 | meth | 10 | 1 | 1 | 1
2 | stu2 | 1 | 1 | 10 | 100 | 10 | te1 | 1 | 1 | meth | 10 | 1 | 1 | 1
2 | stu2 | 1 | 1 | 11 | 89 | 10 | te1 | 1 | 1 | meth | 10 | 1 | 1 | 1
2 | stu2 | 1 | 2 | 10 | 99 | 10 | te1 | 1 | 1 | meth | 10 | 1 | 1 | 1
-- 计划
QUERY PLAN
------------------------------------------------------------------------------------------------
Nested Loop Left Join (cost=0.00..339571526494.35 rows=27152400000000 width=162)
-> Nested Loop (cost=0.00..166526458.85 rows=13310000000 width=142)
-> Nested Loop (cost=0.00..151435.10 rows=12100000 width=96)
-> Nested Loop (cost=0.00..161.35 rows=11000 width=50)
-> Function Scan on generate_series gs (cost=0.00..0.10 rows=10 width=4)
-> Materialize (cost=0.00..26.50 rows=1100 width=46)
-> Seq Scan on teacher (cost=0.00..21.00 rows=1100 width=46)
-> Materialize (cost=0.00..26.50 rows=1100 width=46)
-> Seq Scan on course (cost=0.00..21.00 rows=1100 width=46)
-> Materialize (cost=0.00..26.50 rows=1100 width=46)
-> Seq Scan on student (cost=0.00..21.00 rows=1100 width=46)
-> Materialize (cost=0.00..40.60 rows=2040 width=12)
-> Seq Scan on score (cost=0.00..30.40 rows=2040 width=12)PLANNER前后对比：
在这里插入图片描述
3 连接键JoinExpr实例
SELECT *
FROM STUDENT
LEFT JOIN SCORE ON TRUE
LEFT JOIN COURSE ON SCORE.cno = COURSE.cno;
-- 结果
sno | sname | ssex | sno | cno | degree | cno | cname | tno
-----+-------+------+-----+-----+--------+-----+-------+-----
1 | stu1 | 0 | 1 | 10 | 100 | | |
2 | stu2 | 1 | 1 | 10 | 100 | | |
3 | stu3 | 1 | 1 | 10 | 100 | | |
4 | stu4 | 0 | 1 | 10 | 100 | | |
1 | stu1 | 0 | 1 | 11 | 89 | | |
2 | stu2 | 1 | 1 | 11 | 89 | | |
3 | stu3 | 1 | 1 | 11 | 89 | | |
4 | stu4 | 0 | 1 | 11 | 89 | | |
1 | stu1 | 0 | 2 | 10 | 99 | | |
2 | stu2 | 1 | 2 | 10 | 99 | | |
3 | stu3 | 1 | 2 | 10 | 99 | | |
4 | stu4 | 0 | 2 | 10 | 99 | | |
1 | stu1 | 0 | 2 | 11 | 90 | | |
2 | stu2 | 1 | 2 | 11 | 90 | | |
3 | stu3 | 1 | 2 | 11 | 90 | | |
4 | stu4 | 0 | 2 | 11 | 90 | | |
1 | stu1 | 0 | 3 | 10 | 87 | | |
2 | stu2 | 1 | 3 | 10 | 87 | | |
3 | stu3 | 1 | 3 | 10 | 87 | | |
4 | stu4 | 0 | 3 | 10 | 87 | | |
1 | stu1 | 0 | 3 | 11 | 20 | | |
2 | stu2 | 1 | 3 | 11 | 20 | | |
3 | stu3 | 1 | 3 | 11 | 20 | | |
4 | stu4 | 0 | 3 | 11 | 20 | | |
1 | stu1 | 0 | 4 | 10 | 60 | | |
2 | stu2 | 1 | 4 | 10 | 60 | | |
3 | stu3 | 1 | 4 | 10 | 60 | | |
4 | stu4 | 0 | 4 | 10 | 60 | | |
1 | stu1 | 0 | 4 | 11 | 70 | | |
2 | stu2 | 1 | 4 | 11 | 70 | | |
3 | stu3 | 1 | 4 | 11 | 70 | | |
4 | stu4 | 0 | 4 | 11 | 70 | | |
-- 计划
QUERY PLAN
-------------------------------------------------------------------------------
Nested Loop (cost=34.75..28144.28 rows=2244000 width=104)
-> Hash Left Join (cost=34.75..70.53 rows=2040 width=58)
Hash Cond: (score.cno = course.cno)
-> Seq Scan on score (cost=0.00..30.40 rows=2040 width=12)
-> Hash (cost=21.00..21.00 rows=1100 width=46)
-> Seq Scan on course (cost=0.00..21.00 rows=1100 width=46)
-> Materialize (cost=0.00..26.50 rows=1100 width=46)
-> Seq Scan on student "le盯" (cost=0.00..21.00 rows=1100 width=46)PLANNER前：
在这里插入图片描述
5 自然连接键FromExpr实例
SELECT * FROM STUDENT, SCORE, COURSE WHERE STUDENT.sno = SCORE.sno;
-- 结果
sno | sname | ssex | sno | cno | degree | cno | cname | tno
-----+-------+------+-----+-----+--------+-----+---------+-----
1 | stu1 | 0 | 1 | 10 | 100 | 1 | meth | 10
1 | stu1 | 0 | 1 | 10 | 100 | 2 | english | 11
1 | stu1 | 0 | 1 | 11 | 89 | 1 | meth | 10
1 | stu1 | 0 | 1 | 11 | 89 | 2 | english | 11
2 | stu2 | 1 | 2 | 10 | 99 | 1 | meth | 10
2 | stu2 | 1 | 2 | 10 | 99 | 2 | english | 11
2 | stu2 | 1 | 2 | 11 | 90 | 1 | meth | 10
2 | stu2 | 1 | 2 | 11 | 90 | 2 | english | 11
3 | stu3 | 1 | 3 | 10 | 87 | 1 | meth | 10
3 | stu3 | 1 | 3 | 10 | 87 | 2 | english | 11
3 | stu3 | 1 | 3 | 11 | 20 | 1 | meth | 10
3 | stu3 | 1 | 3 | 11 | 20 | 2 | english | 11
4 | stu4 | 0 | 4 | 10 | 60 | 1 | meth | 10
4 | stu4 | 0 | 4 | 10 | 60 | 2 | english | 11
4 | stu4 | 0 | 4 | 11 | 70 | 1 | meth | 10
4 | stu4 | 0 | 4 | 11 | 70 | 2 | english | 11
-- 计划
QUERY PLAN
------------------------------------------------------------------------------
Nested Loop (cost=34.75..28144.28 rows=2244000 width=104)
-> Hash Join (cost=34.75..70.53 rows=2040 width=58)
Hash Cond: (score.sno = student.sno)
-> Seq Scan on score (cost=0.00..30.40 rows=2040 width=12)
-> Hash (cost=21.00..21.00 rows=1100 width=46)
-> Seq Scan on student (cost=0.00..21.00 rows=1100 width=46)
-> Materialize (cost=0.00..26.50 rows=1100 width=46)
-> Seq Scan on course (cost=0.00..21.00 rows=1100 width=46)planner前：
在这里插入图片描述
6 Query树
查询树，准备传入rewriter和planner进一步处理。
typedef struct Query
{
NodeTag type;
CmdType commandType; /* select|insert|update|delete|merge|utility */
QuerySource querySource; /* where did I come from? */
/*
* query identifier (can be set by plugins); ignored for equal, as it
* might not be set; also not stored
*/
uint64 queryId pg_node_attr(equal_ignore, read_write_ignore, read_as(0));
bool canSetTag; /* do I set the command result tag? */
Node *utilityStmt; /* non-null if commandType == CMD_UTILITY */
int resultRelation; /* rtable index of target relation for
* INSERT/UPDATE/DELETE/MERGE; 0 for SELECT */
bool hasAggs; /* has aggregates in tlist or havingQual */
bool hasWindowFuncs; /* has window functions in tlist */
bool hasTargetSRFs; /* has set-returning functions in tlist */
bool hasSubLinks; /* has subquery SubLink */
bool hasDistinctOn; /* distinctClause is from DISTINCT ON */
bool hasRecursive; /* WITH RECURSIVE was specified */
bool hasModifyingCTE; /* has INSERT/UPDATE/DELETE in WITH */
bool hasForUpdate; /* FOR [KEY] UPDATE/SHARE was specified */
bool hasRowSecurity; /* rewriter has applied some RLS policy */
bool isReturn; /* is a RETURN statement */
List *cteList; /* WITH list (of CommonTableExpr's) */
List *rtable; /* list of range table entries */
FromExpr *jointree; /* table join tree (FROM and WHERE clauses);
* also USING clause for MERGE */
List *mergeActionList; /* list of actions for MERGE (only) */
bool mergeUseOuterJoin; /* whether to use outer join */
List *targetList; /* target list (of TargetEntry) */
OverridingKind override; /* OVERRIDING clause */
OnConflictExpr *onConflict; /* ON CONFLICT DO [NOTHING | UPDATE] */
List *returningList; /* return-values list (of TargetEntry) */
List *groupClause; /* a list of SortGroupClause's */
bool groupDistinct; /* is the group by clause distinct? */
List *groupingSets; /* a list of GroupingSet's if present */
Node *havingQual; /* qualifications applied to groups */
List *windowClause; /* a list of WindowClause's */
List *distinctClause; /* a list of SortGroupClause's */
List *sortClause; /* a list of SortGroupClause's */
Node *limitOffset; /* # of result tuples to skip (int8 expr) */
Node *limitCount; /* # of result tuples to return (int8 expr) */
LimitOption limitOption; /* limit type */
List *rowMarks; /* a list of RowMarkClause's */
Node *setOperations; /* set-operation tree if this is top level of
* a UNION/INTERSECT/EXCEPT query */
List *constraintDeps; /* a list of pg_constraint OIDs that the query
* depends on to be semantically valid */
List *withCheckOptions; /* a list of WithCheckOption's (added
* during rewrite) */
/*
* The following two fields identify the portion of the source text string
* containing this query. They are typically only populated in top-level
* Queries, not in sub-queries. When not set, they might both be zero, or
* both be -1 meaning "unknown".
*/
int stmt_location; /* start location, or -1 if unknown */
int stmt_len; /* length in bytes; 0 means "rest of string" */
} Query;- rtable：在查询中 FROM 子句后面会指出需要进行查询的范围表，可能是对单个范围表进行查询，也可能是对几个范围表做连接操作， rtable 中则记录了这些范围表。rtable 是一个 List 指针，所有要查询的范围表就记录在这个 List 中，每个表以 RangeTblEntry 结构体来表示，因此 rtable 是一个以 RangeTblEntry 为节点的 List链表 。
- jointree: rtable 中列出了查询语句中的表，但没有明确指出各个表之间的连接关系，这个 连接的关系 则通过 jointree 来标明， jointree 是一个 FromExpr 类型 的结构体，它有 3 种类型的节点 : FromExpr、 JoinExpr 和 RangeTblRef。
- targetlist: targetlist 中包含了需要投影( Project)的列，也就是 SFW 查询中的投影列 。
7 Query实例：SELECT * FROM STUDENT WHERE SNO=1;
在这里插入图片描述
8 Query实例：SELECT st.sname, sc.degree FROM STUDENT st, SCORE sc WHERE st.sno = sc.sno;
注意自然连接只有两个RTE，有joinexpr才需要连接RTE。看下面例子。
在这里插入图片描述
9 Query实例：SELECT st.sname, sc.degree FROM STUDENT st INNER JOIN SCORE sc ON st.sno = sc.sno;
注意这里面有3个RTE，因为存在joinexpr，必须有对应的链接RTE。
在这里插入图片描述
10 Query实例：SELECT st.sname, c.cname, sc.degree FROM STUDENT st,COURSE c INNER JOIN SCORE sc ON c.cno = sc.cno WHERE st.sno = sc.sno;
注意fromexpr的quals不是连接键，是外层的where条件。
在这里插入图片描述
本文参与 腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。
原始发表：2022-12-26，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
目录
相关产品与服务
数据库
云数据库为企业提供了完善的关系型数据库、非关系型数据库、分析型数据库和数据库生态工具。您可以通过产品选择和组合搭建，轻松实现高可靠、高可用性、高性能等数据库需求。云数据库服务也可大幅减少您的运维工作量，更专注于业务发展，让企业一站式享受数据上云及分布式架构的技术红利！