3.1概览
- 解析器，根据SQL语句生成一颗语法解析数(parse tree)
- 分析器，对语法解析树进行语义分析，生成一颗查询树(query tree)
- 重写器，按照规则系统中存在的规则对查询树进行改写
- 计划器，基于查询树生成一颗执行效率最高的计划树(plan tree)
- 执行器，按照计划树中的顺序访问表和索引，执行相应查询
解析器
#/pgdata/pgsql/include/postgresql/server/nodes/parsenodes.h
typedef struct SelectStmt
{
NodeTag type;
/*
* 这些字段只会在SelectStmts “叶节点”中使用.
*/
List *distinctClause; /* NULL, list of DISTINCT ON exprs, or
* lcons(NIL,NIL) for all (SELECT DISTINCT) */
IntoClause *intoClause; /* target for SELECT INTO */
List *targetList; /* the target list (of ResTarget) */
List *fromClause; /* the FROM clause */
Node *whereClause; /* WHERE qualification */
List *groupClause; /* GROUP BY clauses */
Node *havingClause; /* HAVING conditional-expression */
List *windowClause; /* WINDOW window_name AS (...), ... */
/*
* 在一个表示值列表的叶节点中，上面的字段全都为空，而这个字段会被设置
* 注意，这个子列表中的元素都没有ResTarget的修饰的表达式类型
* 还需注意，无论值列表的上下文是什么，列表元素都可能为DEFAULT（表示一个SetToDefault节点）
* 由分析阶段觉得是否合法并拒绝
*/
List *valuesLists; /* 未转换的表达式列表 */
/*
* 这些字段会同时在SelectStmts叶节点与SelectStmts上层节点中使用
*/
List *sortClause; /* sort clause (a list of SortBy's) */
Node *limitOffset; /* # of result tuples to skip */
Node *limitCount; /* # of result tuples to return */
List *lockingClause; /* FOR UPDATE (list of LockingClause's) */
WithClause *withClause; /* WITH clause */
/*
* 这些字段只会在上层的SelectStmts中出现
*/
SetOperation op; /* type of set op */
bool all; /* ALL specified? */
struct SelectStmt *larg; /* left child */
struct SelectStmt *rarg; /* right child */
/* Eventually add fields for CORRESPONDING spec here */
} SelectStmt;
分析器
分析器对解析器产出的语法解析树进行语义分析，并产出一颗查询树
#/pgdata/pgsql/include/postgresql/server/nodes/parsenodes.h
/*
*解析与分析过程会将所有的语法转换为一颗查询树，供重写器与计划器用于进一步的处理，功能语句（即不可优化的语句）会设置utilityStmt字段，而Query的结构本身基本上是空的。
* DECLARE CURSOR 是一个特例，它的形式与Select类似，但原始的DeclareCursorStmt会被放在utilityStmt字段中
*计划过程会将查询树转换为一颗计划树，计划树的根节点是一个PlannedStmt结构
*执行树不会用到查询树结构
*/
typedef struct Query
{
NodeTag type;
CmdType commandType; /* select|insert|update|delete|utility */
QuerySource querySource; /* where did I come from? */
uint32 queryId; /* query identifier (can be set by plugins) */
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; 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 */
List *cteList; /* WITH list (of CommonTableExpr's) */
List *rtable; /* list of range table entries */
FromExpr *jointree; /* table join tree (FROM and WHERE clauses) */
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 */
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) */
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, which are
* only added during rewrite and therefore
* are not written out as part of Query. */
/*
* 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;
重写器
在PostgreSQL中，视图是基于规则系统实现的，当使用CREATE VIEW命令定义一个视图时，PG就会创建相应的规则，并存储到系统目录中。
--视图是基于规则系统实现的。
create view employess_list
as select e.id,e.name,d.name as department
from employess as 3,departments as d where e.department_id=d.id;
select * from employees_list;
--当执行一个包含改视图的查询时，解析器会创建一颗语法树
select * from employees_list;
--在该阶段，重写器会基于pg_rule中存储的视图规则将rangetable节点重写为一颗查询子树，与子查询相对应。
计划器与执行器
在PostgreSQL中，计划器是完全基于代价估计的，它不支持基于规则的优化与提示。
Postgresql不支持sql中的提示(hint),想在查询使用提示，可以考虑pg_hint_plan扩展。
--eg
explan select * from tbl_a where id<300 order by data;
--当处理一个查询时，执行器会使用预先分配的内存空间，比如temp_buffers和work_mem,必要还会创建临时文件。