PostgreSQL查询语句执行过程
一、背景
为了分析postgresql代码，了解其执行查询语句的过程，我采用eclipse + gdb集成调试环境，在客户端执行一个查询语句，观察分析其执行流程及重要数据结构变化中数据。
二、环境准备
参考文章：使用eclipse调试分析PostgreSQL11
查询执行流程
Postgresql 执行insert、delete、update、select都是通过postgres.c里面的exec_simple_query方法，其基本流程是
- 启动事务 start_xact_command();
- 进行语法分析，生成语法树
parsetree_list = pg_parse_query(query_string);
只是简单的产生raw parse tree，这个里面不涉及语义检查。只是做语法扫描。
- 语义分析和查询重写。
querytree_list = pg_analyze_and_rewrite(parsetree, query_string, NULL, 0, NULL);
会进行语义分析，会访问数据库中的对像，需要持有锁。这个过程会将简单的一个select 语句拆分成多个部分，将parse tree转换成query tree。如将整个select语句转换成：from 部分，where条件部分，group by 部分，order by 部分以及having 部分等。是任何数据库都需要操作的，并且非常重要的一环。
- 生成执行计划
plantree_list = pg_plan_queries(querytree_list,CURSOR_OPT_PARALLEL_OK, NULL);
根据上面的query tree产生执行计划。这部分核心代码在planner.c中，是PG的Query Optimizer。会根据表和索引的统计信息去计算不同路径的可能代价值，最后选出最优者。
- 执行查询
/*
* Run the portal to completion, and then drop it (and the receiver).
*/
(void) PortalRun(portal,
FETCH_ALL,
true, /* always top level */
true,
receiver,
receiver,
completionTag);
执行plan，它会遍历每个节点，以致完成。最后将查询结果返回给客户端。
- 结束事务 finish_xact_command();
三、分析过程
客户端执行查询的SQL
## 查询 IS系的学生名单，返回年龄最大的两位
select s.* from t_student s where s.sdept='IS' order by s.sno desc limit 2;
执行SQL命令，不能用jdbc客户端。
经过分析，jdbc客户端，会把insert、delete、update、select相关的SQL转为preparedStatement执行。把查询语句分开为多个命令执行(执行多轮，上面命令执行了4轮，暂时没有深入每轮执行的区别)：
SQL语法分析命令 parse
绑定参数命令 bind
描述类型 describe
执行 execute
同步、并提交事务sync
所以本次代码分析exec_simple_query执行过程，采用psql执行查询命令。
主要结构体分析
- 链表
/* src/include/nodes/pg_list.h */
typedef struct List
{
NodeTag type; /*节点类型： T_List, T_IntList, or T_OidList */
int length; /*链表长度 */
ListCell *head; /*链表头指针 */
ListCell *tail; /*链表尾指针 */
} List;
struct ListCell
{
union
{ /* 用联合体报错 */
void *ptr_value; /* 除了Oid、int 的节点值，全部用ptr_value指针 */
int int_value; /*int 类型值 */
Oid oid_value; /*Oid类型值，对象id */
} data;
ListCell *next; /*下一节点*/
};
- PostgreSQL的 portal 定义
在postgresql中，portal 作为查询语句的执行状态，可以使用在 查询游标和协议级别上
/* src/include/utils/portal.h */
typedef enum PortalStrategy
{
PORTAL_ONE_SELECT,
PORTAL_ONE_RETURNING,
PORTAL_ONE_MOD_WITH,
PORTAL_UTIL_SELECT,
PORTAL_MULTI_QUERY
} PortalStrategy;
/*
* A portal is always in one of these states. It is possible to transit
* from ACTIVE back to READY if the query is not run to completion;
* otherwise we never back up in status.
*/
typedef enum PortalStatus
{
PORTAL_NEW, /* freshly created */
PORTAL_DEFINED, /* PortalDefineQuery done */
PORTAL_READY, /* PortalStart complete, can run it */
PORTAL_ACTIVE, /* portal is running (can't delete it) */
PORTAL_DONE, /* portal is finished (don't re-run it) */
PORTAL_FAILED /* portal got error (can't re-run it) */
} PortalStatus;
typedef struct PortalData *Portal;
typedef struct PortalData
{
/* Bookkeeping data */
const char *name; /* portal's name */
const char *prepStmtName; /* source prepared statement (NULL if none) */
MemoryContext portalContext; /* subsidiary memory for portal */
ResourceOwner resowner; /* resources owned by portal */
void (*cleanup) (Portal portal); /* cleanup hook */
/*
* State data for remembering which subtransaction(s) the portal was
* created or used in. If the portal is held over from a previous
* transaction, both subxids are InvalidSubTransactionId. Otherwise,
* createSubid is the creating subxact and activeSubid is the last subxact
* in which we ran the portal.
*/
SubTransactionId createSubid; /* 创建的子事务号 the creating subxact */
SubTransactionId activeSubid; /* 当前活动的子事务号 the last subxact with activity */
/* The query or queries the portal will execute */
const char *sourceText; /* text of query (as of 8.4, never NULL) */
const char *commandTag; /* command tag for original query */
List *stmts; /* list of PlannedStmts */
CachedPlan *cplan; /* CachedPlan, if stmts are from one */
ParamListInfo portalParams; /* params to pass to query */
QueryEnvironment *queryEnv; /* environment for query */
/* Features/options */
PortalStrategy strategy; /* 查询策略 see above */
int cursorOptions; /* DECLARE CURSOR option bits */
bool run_once; /* portal will only be run once */
/* Status data */
PortalStatus status; /* 查询状态 see above */
bool portalPinned; /* a pinned portal can't be dropped */
bool autoHeld; /* was automatically converted from pinned to
* held (see HoldPinnedPortals()) */
/* If not NULL, Executor is active; call ExecutorEnd eventually: */
QueryDesc *queryDesc; /* 查询描述 info needed for executor invocation */
/* If portal returns tuples, this is their tupdesc: */
TupleDesc tupDesc; /* descriptor for result tuples */
/* and these are the format codes to use for the columns: */
int16 *formats; /* a format code for each column */
/*
* Where we store tuples for a held cursor or a PORTAL_ONE_RETURNING or
* PORTAL_UTIL_SELECT query. (A cursor held past the end of its
* transaction no longer has any active executor state.)
*/
Tuplestorestate *holdStore; /* store for holdable cursors */
MemoryContext holdContext; /* memory containing holdStore */
/*
* Snapshot under which tuples in the holdStore were read. We must keep a
* reference to this snapshot if there is any possibility that the tuples
* contain TOAST references, because releasing the snapshot could allow
* recently-dead rows to be vacuumed away, along with any toast data
* belonging to them. In the case of a held cursor, we avoid needing to
* keep such a snapshot by forcibly detoasting the data.
*/
Snapshot holdSnapshot; /* registered snapshot, or NULL if none */
/*
* atStart, atEnd and portalPos indicate the current cursor position.
* portalPos is zero before the first row, N after fetching N'th row of
* query. After we run off the end, portalPos = # of rows in query, and
* atEnd is true. Note that atStart implies portalPos == 0, but not the
* reverse: we might have backed up only as far as the first row, not to
* the start. Also note that various code inspects atStart and atEnd, but
* only the portal movement routines should touch portalPos.
*/
bool atStart;
bool atEnd;
uint64 portalPos;
/* Presentation data, primarily used by the pg_cursors system view */
TimestampTz creation_time; /* time at which this portal was defined */
bool visible; /* include this portal in pg_cursors? */
} PortalData;
- 语法分析结果的处理节点
/* src/include/nodes/parsenodes.h */
/*
* RawStmt --- container for any one statement's raw parse tree
*
* Parse analysis converts a raw parse tree headed by a RawStmt node into
* an analyzed statement headed by a Query node. For optimizable statements,
* the conversion is complex. For utility statements, the parser usually just
* transfers the raw parse tree (sans RawStmt) into the utilityStmt field of
* the Query node, and all the useful work happens at execution time.
*
* stmt_location/stmt_len identify the portion of the source text string
* containing this raw statement (useful for multi-statement strings).
*/
typedef struct RawStmt
{
NodeTag type; /* 节点类型 raw parse tree */
Node *stmt; /* 节点内容 raw parse tree */
int stmt_location; /* start location, or -1 if unknown */
int stmt_len; /* length in bytes; 0 means "rest of string" */
} RawStmt;
- 查询树数据结构
/* src/include/nodes/parsenodes.h */
/*****************************************************************************
* Query Tree 查询树
*****************************************************************************/
/*
* Query -
* Parse analysis turns all statements into a Query tree
* for further processing by the rewriter and planner.
*
* Utility statements (i.e. non-optimizable statements) have the
* utilityStmt field set, and the rest of the Query is mostly dummy.
*
* Planning converts a Query tree into a Plan tree headed by a PlannedStmt
* node --- the Query structure is not used by the executor.
*/
typedef struct Query
{
NodeTag type; 节点类型
CmdType commandType; /* 查询操作类型 select|insert|update|delete|utility */
QuerySource querySource; /* 查询来源，一般为输入的SQL。where did I come from? */
uint64 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;
5.执行计划 数据结构
/* src/include/nodes/plannodes.h */
/* ----------------
* PlannedStmt node 执行计划处理节点
*
* The output of the planner is a Plan tree headed by a PlannedStmt node.
* PlannedStmt holds the "one time" information needed by the executor.
*
* For simplicity in APIs, we also wrap utility statements in PlannedStmt
* nodes; in such cases, commandType == CMD_UTILITY, the statement itself
* is in the utilityStmt field, and the rest of the struct is mostly dummy.
* (We do use canSetTag, stmt_location, stmt_len, and possibly queryId.)
* ----------------
*/
typedef struct PlannedStmt
{
NodeTag type;
CmdType commandType; /* 操作类型 select|insert|update|delete|utility */
uint64 queryId; /* query identifier (copied from Query) */
bool hasReturning; /* is it insert|update|delete RETURNING? */
bool hasModifyingCTE; /* has insert|update|delete in WITH? */
bool canSetTag; /* do I set the command result tag? */
bool transientPlan; /* redo plan when TransactionXmin changes? */
bool dependsOnRole; /* is plan specific to current role? */
bool parallelModeNeeded; /* 是否需要并行执行 parallel mode required to execute? */
int jitFlags; /* which forms of JIT should be performed */
struct Plan *planTree; /*执行计划树 tree of Plan nodes */
List *rtable; /*处理涉及到的表 list of RangeTblEntry nodes */
/* rtable indexes of target relations for INSERT/UPDATE/DELETE */
List *resultRelations; /* integer list of RT indexes, or NIL */
/*
* rtable indexes of non-leaf target relations for UPDATE/DELETE on all
* the partitioned tables mentioned in the query.
*/
List *nonleafResultRelations;
/*
* rtable indexes of root target relations for UPDATE/DELETE; this list
* maintains a subset of the RT indexes in nonleafResultRelations,
* indicating the roots of the respective partition hierarchies.
*/
List *rootResultRelations;
List *subplans; /*子计划列表 Plan trees for SubPlan expressions; note
* that some could be NULL */
Bitmapset *rewindPlanIDs; /* indices of subplans that require REWIND */
List *rowMarks; /* a list of PlanRowMark's */
List *relationOids; /* 依赖的对象ID，一般为查询设计的表或者试图的对象id， OIDs of relations the plan depends on */
List *invalItems; /* other dependencies, as PlanInvalItems */
List *paramExecTypes; /* type OIDs for PARAM_EXEC Params */
Node *utilityStmt; /* non-null if this is utility stmt */
/* statement location in source string (copied from Query) */
int stmt_location; /* start location, or -1 if unknown */
int stmt_len; /* length in bytes; 0 means "rest of string" */
} PlannedStmt;
/* ----------------
* Plan node 执行计划节点
*
* All plan nodes "derive" from the Plan structure by having the
* Plan structure as the first field. This ensures that everything works
* when nodes are cast to Plan's. (node pointers are frequently cast to Plan*
* when passed around generically in the executor)
*
* We never actually instantiate any Plan nodes; this is just the common
* abstract superclass for all Plan-type nodes.
* ----------------
*/
typedef struct Plan
{
NodeTag type;
/*
* estimated execution costs for plan (see costsize.c for more info)
*/
Cost startup_cost; /* 启动代价 cost expended before fetching any tuples */
Cost total_cost; /* 总代价 total cost (assuming all tuples fetched) */
/*
* planner's estimate of result size of this plan step
*/
double plan_rows; /* number of rows plan is expected to emit */
int plan_width; /* average row width in bytes */
/*
* information needed for parallel query
*/
bool parallel_aware; /* engage parallel-aware logic? */
bool parallel_safe; /* OK to use as part of parallel plan? */
/*
* Common structural data for all Plan types.
*/
int plan_node_id; /* unique across entire final plan tree */
List *targetlist; /*返回的结果信息 target list to be computed at this node */
List *qual; /* implicitly-ANDed qual conditions */
struct Plan *lefttree; /*执行计划左子树 input plan tree(s) */
struct Plan *righttree; /*执行计划右子树 input plan tree(s) */
List *initPlan; /* Init Plan nodes (un-correlated expr
* subselects) */
/*
* Information for management of parameter-change-driven rescanning
*
* extParam includes the paramIDs of all external PARAM_EXEC params
* affecting this plan node or its children. setParam params from the
* node's initPlans are not included, but their extParams are.
*
* allParam includes all the extParam paramIDs, plus the IDs of local
* params that affect the node (i.e., the setParams of its initplans).
* These are _all_ the PARAM_EXEC params that affect this node.
*/
Bitmapset *extParam;
Bitmapset *allParam;
} Plan;
查询主要方法 exec_simple_query
/* src/backend/tcop/postgres.c */
/*
* exec_simple_query
*
* Execute a "simple Query" protocol message.
*/
static void
exec_simple_query(const char *query_string)
{
CommandDest dest = whereToSendOutput; /*执行结果输出目标，DestRemote */
MemoryContext oldcontext;
List *parsetree_list; /*查询语法树 列表 */
ListCell *parsetree_item; /*查询语法树 */
bool save_log_statement_stats = log_statement_stats;
bool was_logged = false;
bool use_implicit_block;
char msec_str[32];
/*
* Report query to various monitoring facilities.
*/
debug_query_string = query_string;
pgstat_report_activity(STATE_RUNNING, query_string);
TRACE_POSTGRESQL_QUERY_START(query_string);
/*
* We use save_log_statement_stats so ShowUsage doesn't report incorrect
* results because ResetUsage wasn't called.
*/
if (save_log_statement_stats)
ResetUsage();
/*
* Start up a transaction command. All queries generated by the
* query_string will be in this same command block, *unless* we find a
* BEGIN/COMMIT/ABORT statement; we have to force a new xact command after
* one of those, else bad things will happen in xact.c. (Note that this
* will normally change current memory context.)
*/
start_xact_command(); /* 启动事务 */
/*
* Zap any pre-existing unnamed statement. (While not strictly necessary,
* it seems best to define simple-Query mode as if it used the unnamed
* statement and portal; this ensures we recover any storage used by prior
* unnamed operations.)
*/
drop_unnamed_stmt();
/*
* Switch to appropriate context for constructing parsetrees.
*/
oldcontext = MemoryContextSwitchTo(MessageContext);
/*
* Do basic parsing of the query or queries (this should be safe even if
* we are in aborted transaction state!)
*/
parsetree_list = pg_parse_query(query_string); /* 对SQL命令进行语法分析 */
/* Log immediately if dictated by log_statement */
if (check_log_statement(parsetree_list))
{
ereport(LOG,
(errmsg("statement: %s", query_string),
errhidestmt(true),
errdetail_execute(parsetree_list)));
was_logged = true;
}
/*
* Switch back to transaction context to enter the loop.
*/
MemoryContextSwitchTo(oldcontext);
/*
* For historical reasons, if multiple SQL statements are given in a
* single "simple Query" message, we execute them as a single transaction,
* unless explicit transaction control commands are included to make
* portions of the list be separate transactions. To represent this
* behavior properly in the transaction machinery, we use an "implicit"
* transaction block.
*/
use_implicit_block = (list_length(parsetree_list) > 1);
/*
* Run through the raw parsetree(s) and process each one.
*/
foreach(parsetree_item, parsetree_list)
{/* 分析一个语法树 */
RawStmt *parsetree = lfirst_node(RawStmt, parsetree_item); /* 转换语法树为RawStmt类型 */
bool snapshot_set = false;
const char *commandTag;
char completionTag[COMPLETION_TAG_BUFSIZE];
List *querytree_list,
*plantree_list;
Portal portal;
DestReceiver *receiver;
int16 format;
/*
* Get the command name for use in status display (it also becomes the
* default completion tag, down inside PortalRun). Set ps_status and
* do any special start-of-SQL-command processing needed by the
* destination.
*/
commandTag = CreateCommandTag(parsetree->stmt);
set_ps_display(commandTag, false);
BeginCommand(commandTag, dest);
/*
* If we are in an aborted transaction, reject all commands except
* COMMIT/ABORT. It is important that this test occur before we try
* to do parse analysis, rewrite, or planning, since all those phases
* try to do database accesses, which may fail in abort state. (It
* might be safe to allow some additional utility commands in this
* state, but not many...)
*/
if (IsAbortedTransactionBlockState() &&
!IsTransactionExitStmt(parsetree->stmt))
ereport(ERROR,
(errcode(ERRCODE_IN_FAILED_SQL_TRANSACTION),
errmsg("current transaction is aborted, "
"commands ignored until end of transaction block"),
errdetail_abort()));
/* Make sure we are in a transaction command */
start_xact_command(); /* 事务开始 */
/*
* If using an implicit transaction block, and we're not already in a
* transaction block, start an implicit block to force this statement
* to be grouped together with any following ones. (We must do this
* each time through the loop; otherwise, a COMMIT/ROLLBACK in the
* list would cause later statements to not be grouped.)
*/
if (use_implicit_block)
BeginImplicitTransactionBlock();
/* If we got a cancel signal in parsing or prior command, quit */
CHECK_FOR_INTERRUPTS();
/*
* Set up a snapshot if parse analysis/planning will need one.
* 对于insert、delete、update、select 操作，需要建立语法树快照
*/
if (analyze_requires_snapshot(parsetree))
{
PushActiveSnapshot(GetTransactionSnapshot());
snapshot_set = true;
}
/*
* OK to analyze, rewrite, and plan this query.
*
* Switch to appropriate context for constructing querytrees (again,
* these must outlive the execution context).
*/
oldcontext = MemoryContextSwitchTo(MessageContext);
querytree_list = pg_analyze_and_rewrite(parsetree, query_string,
NULL, 0, NULL); /* 语义分析和查询重写，返回查询树列表 */
plantree_list = pg_plan_queries(querytree_list,
CURSOR_OPT_PARALLEL_OK, NULL); /* 生成执行计划，并优化 */
/* Done with the snapshot used for parsing/planning */
if (snapshot_set)
PopActiveSnapshot();
/* If we got a cancel signal in analysis or planning, quit */
CHECK_FOR_INTERRUPTS();
/*
* Create unnamed portal to run the query or queries in. If there
* already is one, silently drop it.
*/
portal = CreatePortal("", true, true); /* 创建一个查询的portal */
/* Don't display the portal in pg_cursors */
portal->visible = false;
/*
* We don't have to copy anything into the portal, because everything
* we are passing here is in MessageContext, which will outlive the
* portal anyway.
*/
PortalDefineQuery(portal,
NULL,
query_string,
commandTag,
plantree_list,
NULL); /* 设置的portal 相关的查询计划 */
/*
* Start the portal. No parameters here.
*/
PortalStart(portal, NULL, 0, InvalidSnapshot);
/*
* Select the appropriate output format: text unless we are doing a
* FETCH from a binary cursor. (Pretty grotty to have to do this here
* --- but it avoids grottiness in other places. Ah, the joys of
* backward compatibility...)
*/
format = 0; /* TEXT is default */
if (IsA(parsetree->stmt, FetchStmt))
{
FetchStmt *stmt = (FetchStmt *) parsetree->stmt;
if (!stmt->ismove)
{
Portal fportal = GetPortalByName(stmt->portalname);
if (PortalIsValid(fportal) &&
(fportal->cursorOptions & CURSOR_OPT_BINARY))
format = 1; /* BINARY */
}
}
PortalSetResultFormat(portal, 1, &format); /* 设置的portal 相关的返回格式 */
/*
* Now we can create the destination receiver object.
*/
receiver = CreateDestReceiver(dest); /* 创建结果接收者 */
if (dest == DestRemote)
SetRemoteDestReceiverParams(receiver, portal);
/*
* Switch back to transaction context for execution.
*/
MemoryContextSwitchTo(oldcontext);
/*
* Run the portal to completion, and then drop it (and the receiver).
*/
(void) PortalRun(portal,
FETCH_ALL,
true, /* always top level */
true,
receiver,
receiver,
completionTag); /* 执行 portal 查询操作 */
receiver->rDestroy(receiver);
PortalDrop(portal, false);
if (lnext(parsetree_item) == NULL)
{
/*
* If this is the last parsetree of the query string, close down
* transaction statement before reporting command-complete. This
* is so that any end-of-transaction errors are reported before
* the command-complete message is issued, to avoid confusing
* clients who will expect either a command-complete message or an
* error, not one and then the other. Also, if we're using an
* implicit transaction block, we must close that out first.
*/
if (use_implicit_block)
EndImplicitTransactionBlock();
finish_xact_command();
}
else if (IsA(parsetree->stmt, TransactionStmt))
{
/*
* If this was a transaction control statement, commit it. We will
* start a new xact command for the next command.
*/
finish_xact_command();
}
else
{
/*
* We need a CommandCounterIncrement after every query, except
* those that start or end a transaction block.
*/
CommandCounterIncrement();
}
/*
* Tell client that we're done with this query. Note we emit exactly
* one EndCommand report for each raw parsetree, thus one for each SQL
* command the client sent, regardless of rewriting. (But a command
* aborted by error will not send an EndCommand report at all.)
*/
EndCommand(completionTag, dest);
} /* end loop over parsetrees */
/*
* Close down transaction statement, if one is open. (This will only do
* something if the parsetree list was empty; otherwise the last loop
* iteration already did it.)
*/
finish_xact_command(); /* 结束事务 */
/*
* If there were no parsetrees, return EmptyQueryResponse message.
*/
if (!parsetree_list)
NullCommand(dest);
/*
* Emit duration logging if appropriate.
*/
switch (check_log_duration(msec_str, was_logged))
{
case 1:
ereport(LOG,
(errmsg("duration: %s ms", msec_str),
errhidestmt(true)));
break;
case 2:
ereport(LOG,
(errmsg("duration: %s ms statement: %s",
msec_str, query_string),
errhidestmt(true),
errdetail_execute(parsetree_list)));
break;
}
if (save_log_statement_stats)
ShowUsage("QUERY STATISTICS");
TRACE_POSTGRESQL_QUERY_DONE(query_string);
debug_query_string = NULL;
}
执行
// psql中获取当前连接的进程id
postgres=# select pg_backend_pid();
pg_backend_pid
----------------
14329
(1 row)
// 切换到gdb 绑定进程
(gdb) attach 14329
Attaching to process 14329
...
// 设置断点
(gdb) b exec_simple_query
Breakpoint 1 at 0x8c35db: file postgres.c, line 893.
(gdb) c
Continuing.
// 切换到psql，执行查询
postgres=# select s.* from t_student s where s.sdept='IS' order by s.sno desc imit 2;
// 切换gdb 绑定进程
函数调用栈信息
函数栈
query_string的值
select s.*
from t_student s
where s.sdept='IS'
order by s.sno desc
limit 2;
## 执行SQL
2018-08-07 19:57:58.718 CST,"appusr","postgres",38496,"127.0.0.1:48658",5b698942.9660,3,"idle",2018-08-07 19:57:54 CST,3/2,0,LOG,00000,"statement: select s.*
from t_student s
where s.sdept='IS'
order by s.sno desc
limit 2;",,,,,,,,,"psql"
查询语法树
在exec_simple_query中调用了pg_parse_query进行语法分析校验
语法分析函数调用栈
- 输入的query_string的值
select s.*
from t_student s
where s.sdept='IS'
order by s.sno desc
limit 2;
- 返回语法树信息如下：
## 语法树
2018-08-07 19:57:58.720 CST,"appusr","postgres",38496,"127.0.0.1:48658",5b698942.9660,4,"SELECT",2018-08-07 19:57:54 CST,3/2,0,LOG,00000,"parse tree:"," {QUERY
:commandType 1
:querySource 0
:canSetTag true
:utilityStmt <>
:resultRelation 0
:hasAggs false
:hasWindowFuncs false
:hasTargetSRFs false
:hasSubLinks false
:hasDistinctOn false
:hasRecursive false
:hasModifyingCTE false
:hasForUpdate false
:hasRowSecurity false
:cteList <>
:rtable (
{RTE
:alias
{ALIAS
:aliasname s
:colnames <>
}
:eref
{ALIAS
:aliasname s
:colnames (""sno"" ""sname"" ""ssex"" ""sage"" ""sdept"")
}
:rtekind 0
:relid 16408
:relkind r
:tablesample <>
:lateral false
:inh true
:inFromCl true
:requiredPerms 2
:checkAsUser 0
:selectedCols (b 9 10 11 12 13)
:insertedCols (b)
:updatedCols (b)
:securityQuals <>
}
)
:jointree
{FROMEXPR
:fromlist (
{RANGETBLREF
:rtindex 1
}
)
:quals
{OPEXPR
:opno 1054
:opfuncid 1048
:opresulttype 16
:opretset false
:opcollid 0
:inputcollid 100
:args (
{VAR
:varno 1
:varattno 5
:vartype 1042
:vartypmod 24
:varcollid 100
:varlevelsup 0
:varnoold 1
:varoattno 5
:location 34
}
{CONST
:consttype 1042
:consttypmod -1
:constcollid 100
:constlen -1
:constbyval false
:constisnull false
:location 42
:constvalue 6 [ 24 0 0 0 73 83 ]
}
)
:location 41
}
}
:targetList (
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 1
:vartype 1042
:vartypmod 13
:varcollid 100
:varlevelsup 0
:varnoold 1
:varoattno 1
:location 7
}
:resno 1
:resname sno
:ressortgroupref 1
:resorigtbl 16408
:resorigcol 1
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 2
:vartype 1042
:vartypmod 24
:varcollid 100
:varlevelsup 0
:varnoold 1
:varoattno 2
:location 7
}
:resno 2
:resname sname
:ressortgroupref 0
:resorigtbl 16408
:resorigcol 2
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 3
:vartype 1042
:vartypmod 6
:varcollid 100
:varlevelsup 0
:varnoold 1
:varoattno 3
:location 7
}
:resno 3
:resname ssex
:ressortgroupref 0
:resorigtbl 16408
:resorigcol 3
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 4
:vartype 21
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnoold 1
:varoattno 4
:location 7
}
:resno 4
:resname sage
:ressortgroupref 0
:resorigtbl 16408
:resorigcol 4
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 5
:vartype 1042
:vartypmod 24
:varcollid 100
:varlevelsup 0
:varnoold 1
:varoattno 5
:location 7
}
:resno 5
:resname sdept
:ressortgroupref 0
:resorigtbl 16408
:resorigcol 5
:resjunk false
}
)
:override 0
:onConflict <>
:returningList <>
:groupClause <>
:groupingSets <>
:havingQual <>
:windowClause <>
:distinctClause <>
:sortClause (
{SORTGROUPCLAUSE
:tleSortGroupRef 1
:eqop 1054
:sortop 1060
:nulls_first true
:hashable true
}
)
:limitOffset <>
:limitCount
{FUNCEXPR
:funcid 481
:funcresulttype 20
:funcretset false
:funcvariadic false
:funcformat 2
:funccollid 0
:inputcollid 0
:args (
{CONST
:consttype 23
:consttypmod -1
:constcollid 0
:constlen 4
:constbyval true
:constisnull false
:location 74
:constvalue 4 [ 2 0 0 0 0 0 0 0 ]
}
)
:location -1
}
:rowMarks <>
:setOperations <>
:constraintDeps <>
:stmt_location 0
:stmt_len 75
}
",,,,,"select s.*
from t_student s
where s.sdept='IS'
order by s.sno desc
limit 2;",,,"psql"
#语法分析树
(gdb) p *(parsetree_list)
$5 = {type = T_List, length = 1, head = 0x1f985d8, tail = 0x1f985d8}
(gdb) p *(parsetree_list->head)
$6 = {data = {ptr_value = 0x1f985a0, int_value = 33129888,
oid_value = 33129888}, next = 0x0}
(gdb) p *(parsetree_list->head->data->ptr_value)
Attempt to dereference a generic pointer.
(gdb) p *((Node*)parsetree_list->head->data->ptr_value)
$7 = {type = T_RawStmt}
(gdb) p *((RawStmt*)parsetree_list->head->data->ptr_value)
$8 = {type = T_RawStmt, stmt = 0x1f981c0, stmt_location = 0, stmt_len = 75}
(gdb) p *((RawStmt*)parsetree_list->head->data->ptr_value)->stmt
$9 = {type = T_SelectStmt}
(gdb) p *((SelectStmt*)((RawStmt*)parsetree_list->head->data->ptr_value)->stmt)
$10 = {type = T_SelectStmt, distinctClause = 0x0, intoClause = 0x0,
targetList = 0x1f97dc8, fromClause = 0x1f97f00, whereClause = 0x1f980e0,
groupClause = 0x0, havingClause = 0x0, windowClause = 0x0,
valuesLists = 0x0, sortClause = 0x1f984a8, limitOffset = 0x0,
limitCount = 0x1f984e0, lockingClause = 0x0, withClause = 0x0,
op = SETOP_NONE, all = false, larg = 0x0, rarg = 0x0}
(gdb) p *((SelectStmt*)((RawStmt*)parsetree_list->head->data->ptr_value)->stmt)->targetList
$11 = {type = T_List, length = 1, head = 0x1f97da0, tail = 0x1f97da0}
(gdb) p *((SelectStmt*)((RawStmt*)parsetree_list->head->data->ptr_value)->stmt)->targetList->head
$12 = {data = {ptr_value = 0x1f97d48, int_value = 33127752,
oid_value = 33127752}, next = 0x0}
(gdb) p *((SelectStmt*)((RawStmt*)parsetree_list->head->data->ptr_value)->stmt)->targetList->head->data->ptr_value
Attempt to dereference a generic pointer.
(gdb) p *((Node*)((SelectStmt*)((RawStmt*)parsetree_list->head->data->ptr_value)->stmt)->targetList->head->data->ptr_value)
$13 = {type = T_ResTarget}
(gdb) p *((ResTarget*)((SelectStmt*)((RawStmt*)parsetree_list->head->data->ptr_value)->stmt)->targetList->head->data->ptr_value)
$14 = {type = T_ResTarget, name = 0x0, indirection = 0x0, val = 0x1f97cc0,
location = 7}
(gdb) p *((ResTarget*)((SelectStmt*)((RawStmt*)parsetree_list->head->data->ptr_value)->stmt)->targetList->head->data->ptr_value)->val
$15 = {type = T_ColumnRef}
(gdb) p *((ColumnRef)((ResTarget*)((SelectStmt*)((RawStmt*)parsetree_list->head->data->ptr_value)->stmt)->targetList->head->data->ptr_value)->val)
Structure has no component named operator*.
(gdb) p *((ColumnRef*)((ResTarget*)((SelectStmt*)((RawStmt*)parsetree_list->head->data->ptr_value)->stmt)->targetList->head->data->ptr_value)->val)
$16 = {type = T_ColumnRef, fields = 0x1f97c88, location = 7}
(gdb) p *((ColumnRef*)((ResTarget*)((SelectStmt*)((RawStmt*)parsetree_list->head->data->ptr_value)->stmt)->targetList->head->data->ptr_value)->val)->fields
$17 = {type = T_List, length = 2, head = 0x1f97d20, tail = 0x1f97c60}
(gdb) p *((ColumnRef*)((ResTarget*)((SelectStmt*)((RawStmt*)parsetree_list->head->data->ptr_value)->stmt)->targetList->head->data->ptr_value)->val)->fields->head
$18 = {data = {ptr_value = 0x1f97cf8, int_value = 33127672,
oid_value = 33127672}, next = 0x1f97c60}
(gdb) p *((ColumnRef*)((ResTarget*)((SelectStmt*)((RawStmt*)parsetree_list->head->data->ptr_value)->stmt)->targetList->head->data->ptr_value)->val)->fields->head->data->ptr_value
Attempt to dereference a generic pointer.
(gdb) p *((Node*)((ColumnRef*)((ResTarget*)((SelectStmt*)((RawStmt*)parsetree_list->head->data->ptr_value)->stmt)->targetList->head->data->ptr_value)->val)->fields->head->data->ptr_value)
$19 = {type = T_String}
(gdb) p *((String*)((ColumnRef*)((ResTarget*)((SelectStmt*)((RawStmt*)parsetree_list->head->data->ptr_value)->stmt)->targetList->head->data->ptr_value)->val)->fields->head->data->ptr_value)
No symbol "String" in current context.
(gdb) p *((Value*)((ColumnRef*)((ResTarget*)((SelectStmt*)((RawStmt*)parsetree_list->head->data->ptr_value)->stmt)->targetList->head->data->ptr_value)->val)->fields->head->data->ptr_value)
$20 = {type = T_String, val = {ival = 33127456, str = 0x1f97c20 "s"}}
(gdb) p *((Value*)((ColumnRef*)((ResTarget*)((SelectStmt*)((RawStmt*)parsetree_list->head->data->ptr_value)->stmt)->targetList->head->data->ptr_value)->val)->fields->head->data->ptr_value)->val->str
$21 = 115 's'
(gdb) p ((Value*)((ColumnRef*)((ResTarget*)((SelectStmt*)((RawStmt*)parsetree_list->head->data->ptr_value)->stmt)->targetList->head->data->ptr_value)->val)->fields->head->data->ptr_value)->val->str
$22 = 0x1f97c20 "s"
(gdb) p ((char*)((Value*)((ColumnRef*)((ResTarget*)((SelectStmt*)((RawStmt*)parsetree_list->head->data->ptr_value)->stmt)->targetList->head->data->ptr_value)->val)->fields->head->data->ptr_value)->val->str)
$23 = 0x1f97c20 "s"
(gdb) p *((char*)((Value*)((ColumnRef*)((ResTarget*)((SelectStmt*)((RawStmt*)parsetree_list->head->data->ptr_value)->stmt)->targetList->head->data->ptr_value)->val)->fields->head->data->ptr_value)->val->str)
$24 = 115 's'
(gdb) p *((ColumnRef*)((ResTarget*)((SelectStmt*)((RawStmt*)parsetree_list->head->data->ptr_value)->stmt)->targetList->head->data->ptr_value)->val)->fields->head->next
$25 = {data = {ptr_value = 0x1f97c40, int_value = 33127488,
oid_value = 33127488}, next = 0x0}
(gdb) p *((Node*)((ColumnRef*)((ResTarget*)((SelectStmt*)((RawStmt*)parsetree_list->head->data->ptr_value)->stmt)->targetList->head->data->ptr_value)->val)->fields->head->next->data->ptr_value)
$26 = {type = T_A_Star}
(gdb) p *((S_Star*)((ColumnRef*)((ResTarget*)((SelectStmt*)((RawStmt*)parsetree_list->head->data->ptr_value)->stmt)->targetList->head->data->ptr_value)->val)->fields->head->next->data->ptr_value)
No symbol "S_Star" in current context.
(gdb) p *((A_Star*)((ColumnRef*)((ResTarget*)((SelectStmt*)((RawStmt*)parsetree_list->head->data->ptr_value)->stmt)->targetList->head->data->ptr_value)->val)->fields->head->next->data->ptr_value)
$27 = {type = T_A_Star}
(gdb) p *((SelectStmt*)((RawStmt*)parsetree_list->head->data->ptr_value)->stmt)->fromClause
$28 = {type = T_List, length = 1, head = 0x1f97ed8, tail = 0x1f97ed8}
(gdb) p *((SelectStmt*)((RawStmt*)parsetree_list->head->data->ptr_value)->stmt)->fromClause->head->data->ptr_value
Attempt to dereference a generic pointer.
(gdb) p *((Node*)((SelectStmt*)((RawStmt*)parsetree_list->head->data->ptr_value)->stmt)->fromClause->head->data->ptr_value)
$29 = {type = T_RangeVar}
(gdb) p *((RangeVar*)((SelectStmt*)((RawStmt*)parsetree_list->head->data->ptr_value)->stmt)->fromClause->head->data->ptr_value)
$30 = {type = T_RangeVar, catalogname = 0x0, schemaname = 0x0,
relname = 0x1f97e00 "t_student", inh = true, relpersistence = 112 'p',
alias = 0x1f97ea0, location = 16}
(gdb) p *((RangeVar*)((SelectStmt*)((RawStmt*)parsetree_list->head->data->ptr_value)->stmt)->fromClause->head->data->ptr_value)->alias
$31 = {type = T_Alias, aliasname = 0x1f97e28 "s", colnames = 0x0}
(gdb) p *((SelectStmt*)((RawStmt*)parsetree_list->head->data->ptr_value)->stmt)->whereClause
$32 = {type = T_A_Expr}
(gdb) p *((A_Expr*)((SelectStmt*)((RawStmt*)parsetree_list->head->data->ptr_value)->stmt)->whereClause)
$33 = {type = T_A_Expr, kind = AEXPR_OP, name = 0x1f98188, lexpr = 0x1f98000,
rexpr = 0x1f980a8, location = 41}
(gdb) p *((A_Expr*)((SelectStmt*)((RawStmt*)parsetree_list->head->data->ptr_value)->stmt)->whereClause)->lexpr
$34 = {type = T_ColumnRef}
(gdb) p *((ColumnRef*)((A_Expr*)((SelectStmt*)((RawStmt*)parsetree_list->head->data->ptr_value)->stmt)->whereClause)->lexpr)
$35 = {type = T_ColumnRef, fields = 0x1f97fc8, location = 34}
(gdb) p *((ColumnRef*)((A_Expr*)((SelectStmt*)((RawStmt*)parsetree_list->head->data->ptr_value)->stmt)->whereClause)->lexpr)->fields
$36 = {type = T_List, length = 2, head = 0x1f98060, tail = 0x1f97fa0}
(gdb) p *((ColumnRef*)((A_Expr*)((SelectStmt*)((RawStmt*)parsetree_list->head->data->ptr_value)->stmt)->whereClause)->lexpr)->fields->head
$37 = {data = {ptr_value = 0x1f98038, int_value = 33128504,
oid_value = 33128504}, next = 0x1f97fa0}
(gdb) p *((ColumnRef*)((A_Expr*)((SelectStmt*)((RawStmt*)parsetree_list->head->data->ptr_value)->stmt)->whereClause)->lexpr)->fields->head->data->ptr_value
Attempt to dereference a generic pointer.
(gdb) p *((Node*)((ColumnRef*)((A_Expr*)((SelectStmt*)((RawStmt*)parsetree_list->head->data->ptr_value)->stmt)->whereClause)->lexpr)->fields->head->data->ptr_value)
$38 = {type = T_String}
(gdb) p ((char*)((ColumnRef*)((A_Expr*)((SelectStmt*)((RawStmt*)parsetree_list->head->data->ptr_value)->stmt)->whereClause)->lexpr)->fields->head->data->ptr_value)
$39 = 0x1f98038 <incomplete sequence \332>
(gdb) p ((char*)((ColumnRef*)((A_Expr*)((SelectStmt*)((RawStmt*)parsetree_list->head->data->ptr_value)->stmt)->whereClause)->lexpr)->fields->head->next->data->ptr_value)
$40 = 0x1f97f78 <incomplete sequence \332>
(gdb) p *((SelectStmt*)((RawStmt*)parsetree_list->head->data->ptr_value)->stmt)->sortClause
$41 = {type = T_List, length = 1, head = 0x1f98480, tail = 0x1f98480}
(gdb) p *((SelectStmt*)((RawStmt*)parsetree_list->head->data->ptr_value)->stmt)->sortClause->head->data->ptr_value
Attempt to dereference a generic pointer.
(gdb) p *((Node*)((SelectStmt*)((RawStmt*)parsetree_list->head->data->ptr_value)->stmt)->sortClause->head->data->ptr_value)
$42 = {type = T_SortBy}
(gdb) p *((SortBy*)((SelectStmt*)((RawStmt*)parsetree_list->head->data->ptr_value)->stmt)->sortClause->head->data->ptr_value)
$43 = {type = T_SortBy, node = 0x1f983a0, sortby_dir = SORTBY_DESC,
sortby_nulls = SORTBY_NULLS_DEFAULT, useOp = 0x0, location = -1}
(gdb) p *((SortBy*)((SelectStmt*)((RawStmt*)parsetree_list->head->data->ptr_value)->stmt)->sortClause->head->data->ptr_value)->node
$44 = {type = T_ColumnRef}
(gdb) p *((ColumnRef*)((SortBy*)((SelectStmt*)((RawStmt*)parsetree_list->head->data->ptr_value)->stmt)->sortClause->head->data->ptr_value)->node)
$45 = {type = T_ColumnRef, fields = 0x1f98368, location = 56}
(gdb) p *((ColumnRef*)((SortBy*)((SelectStmt*)((RawStmt*)parsetree_list->head->data->ptr_value)->stmt)->sortClause->head->data->ptr_value)->node)->fields
$46 = {type = T_List, length = 2, head = 0x1f98400, tail = 0x1f98340}
(gdb) p *((ColumnRef*)((SortBy*)((SelectStmt*)((RawStmt*)parsetree_list->head->data->ptr_value)->stmt)->sortClause->head->data->ptr_value)->node)->fields->head->data->ptr_value
Attempt to dereference a generic pointer.
(gdb) p *((Node*)((ColumnRef*)((SortBy*)((SelectStmt*)((RawStmt*)parsetree_list->head->data->ptr_value)->stmt)->sortClause->head->data->ptr_value)->node)->fields->head->data->ptr_value)
$47 = {type = T_String}
(gdb) p *((Value*)((ColumnRef*)((SortBy*)((SelectStmt*)((RawStmt*)parsetree_list->head->data->ptr_value)->stmt)->sortClause->head->data->ptr_value)->node)->fields->head->data->ptr_value)
$48 = {type = T_String, val = {ival = 33129176, str = 0x1f982d8 "s"}}
(gdb) p *((SelectStmt*)((RawStmt*)parsetree_list->head->data->ptr_value)->stmt)->limitCount
$49 = {type = T_A_Const}
(gdb) p *((A_Const*)((SelectStmt*)((RawStmt*)parsetree_list->head->data->ptr_value)->stmt)->limitCount)
$50 = {type = T_A_Const, val = {type = T_Integer, val = {ival = 2,
str = 0x2 <Address 0x2 out of bounds>}}, location = 74}
对返回的语法树进行语义分析及查询重写 pg_analyze_and_rewrite
image.png
-
输入信息
parsetree
query_string：
select s.*
from t_student s
where s.sdept='IS'
order by s.sno desc
limit 2;
paramTypes =NULL；
numParams=0；
queryEnv =NULL；
- 返回的查询重写结果为
#语义分析和查询重写后的查询树信息
1047 querytree_list = pg_analyze_and_rewrite(parsetree, query_string,
(gdb)
1050 plantree_list = pg_plan_queries(querytree_list,
(gdb) p *(querytree_list)
$51 = {type = T_List, length = 1, head = 0x2066ed0, tail = 0x2066ed0}
(gdb) p *((Node*)querytree_list->head->data->ptr_value)
$52 = {type = T_Query}
(gdb) p *((Query*)querytree_list->head->data->ptr_value)
$53 = {type = T_Query, commandType = CMD_SELECT, querySource = QSRC_ORIGINAL,
queryId = 0, canSetTag = true, utilityStmt = 0x0, resultRelation = 0,
hasAggs = false, hasWindowFuncs = false, hasTargetSRFs = false,
hasSubLinks = false, hasDistinctOn = false, hasRecursive = false,
hasModifyingCTE = false, hasForUpdate = false, hasRowSecurity = false,
cteList = 0x0, rtable = 0x1f98c68, jointree = 0x207a0b8,
targetList = 0x2079a58, override = OVERRIDING_NOT_SET, onConflict = 0x0,
returningList = 0x0, groupClause = 0x0, groupingSets = 0x0,
havingQual = 0x0, windowClause = 0x0, distinctClause = 0x0,
sortClause = 0x2079f70, limitOffset = 0x0, limitCount = 0x207a060,
rowMarks = 0x0, setOperations = 0x0, constraintDeps = 0x0,
withCheckOptions = 0x0, stmt_location = 0, stmt_len = 75}
(gdb) p *((Query*)querytree_list->head->data->ptr_value)->rtable
$54 = {type = T_List, length = 1, head = 0x1f98c40, tail = 0x1f98c40}
(gdb) p *((Node*)((Query*)querytree_list->head->data->ptr_value)->rtable->head->data->ptr_value)
$55 = {type = T_RangeTblEntry}
(gdb) p *((RangeTblEntry*)((Query*)querytree_list->head->data->ptr_value)->rtable->head->data->ptr_value)
$56 = {type = T_RangeTblEntry, rtekind = RTE_RELATION, relid = 16408,
relkind = 114 'r', tablesample = 0x0, subquery = 0x0,
security_barrier = false, jointype = JOIN_INNER, joinaliasvars = 0x0,
functions = 0x0, funcordinality = false, tablefunc = 0x0,
values_lists = 0x0, ctename = 0x0, ctelevelsup = 0, self_reference = false,
coltypes = 0x0, coltypmods = 0x0, colcollations = 0x0, enrname = 0x0,
enrtuples = 0, alias = 0x1f97ea0, eref = 0x1f98980, lateral = false,
inh = true, inFromCl = true, requiredPerms = 2, checkAsUser = 0,
selectedCols = 0x2079a90, insertedCols = 0x0, updatedCols = 0x0,
securityQuals = 0x0}
(gdb) p *((Query*)querytree_list->head->data->ptr_value)->jointree
$57 = {type = T_FromExpr, fromlist = 0x1f98d70, quals = 0x2079e60}
(gdb) p *((Query*)querytree_list->head->data->ptr_value)->jointree->fromlist
$58 = {type = T_List, length = 1, head = 0x1f98d48, tail = 0x1f98d48}
(gdb) p *((Node*)((Query*)querytree_list->head->data->ptr_value)->jointree->fromlist->head->data->ptr_value)
$59 = {type = T_RangeTblRef}
(gdb) p *((RangeTblRef*)((Query*)querytree_list->head->data->ptr_value)->jointree->fromlist->head->data->ptr_value)
$60 = {type = T_RangeTblRef, rtindex = 1}
(gdb) p *((Query*)querytree_list->head->data->ptr_value)->jointree->quals
$61 = {type = T_OpExpr}
(gdb) p *((OpExpr*)((Query*)querytree_list->head->data->ptr_value)->jointree->quals)
$62 = {xpr = {type = T_OpExpr}, opno = 1054, opfuncid = 1048,
opresulttype = 16, opretset = false, opcollid = 0, inputcollid = 100,
args = 0x2079d88, location = 41}
(gdb) p *((OpExpr*)((Query*)querytree_list->head->data->ptr_value)->jointree->quals)->args
$63 = {type = T_List, length = 2, head = 0x2079dc0, tail = 0x2079d60}
(gdb) p *((Node*)((OpExpr*)((Query*)querytree_list->head->data->ptr_value)->jointree->quals)->args->head->data->ptr_value)
$64 = {type = T_Var}
(gdb) p *((Var*)((OpExpr*)((Query*)querytree_list->head->data->ptr_value)->jointree->quals)->args->head->data->ptr_value)
$65 = {xpr = {type = T_Var}, varno = 1, varattno = 5, vartype = 1042,
vartypmod = 24, varcollid = 100, varlevelsup = 0, varnoold = 1,
varoattno = 5, location = 34}
(gdb) p *((Node*)((OpExpr*)((Query*)querytree_list->head->data->ptr_value)->jointree->quals)->args->head->next->data->ptr_value)
$66 = {type = T_Const}
(gdb) p *((Const*)((OpExpr*)((Query*)querytree_list->head->data->ptr_value)->jointree->quals)->args->head->next->data->ptr_value)
$67 = {xpr = {type = T_Const}, consttype = 1042, consttypmod = -1,
constcollid = 100, constlen = -1, constvalue = 34053696,
constisnull = false, constbyval = false, location = 42}
(gdb) p *((Node*)((Const*)((OpExpr*)((Query*)querytree_list->head->data->ptr_value)->jointree->quals)->args->head->next->data->ptr_value)->constvalue)
$75 = {type = T_BitmapHeapScan}
(gdb) p *((BitmapHeapScan*)((Const*)((OpExpr*)((Query*)querytree_list->head->data->ptr_value)->jointree->quals)->args->head->next->data->ptr_value)->constvalue)
$76 = {scan = {plan = {type = T_BitmapHeapScan,
startup_cost = 3.1620201333839779e-322,
total_cost = 2.3715151000379834e-322,
plan_rows = 1.6365691319506455e-316, plan_width = 112,
parallel_aware = 30, parallel_safe = 4, plan_node_id = 1048,
targetlist = 0x0, qual = 0x64, lefttree = 0x2079d88, righttree = 0x29,
initPlan = 0x7f7f7f7f7f7f7f7e, extParam = 0x7f7f7f7f7f7f7f7f,
allParam = 0x40}, scanrelid = 40}, bitmapqualorig = 0x1f970b0}
(gdb) p *((Query*)querytree_list->head->data->ptr_value)->targetList
$77 = {type = T_List, length = 5, head = 0x2079a30, tail = 0x2079c88}
(gdb) p *((Node*)((Query*)querytree_list->head->data->ptr_value)->targetList->head->data->ptr_value)
$78 = {type = T_TargetEntry}
(gdb) p *((TargetEntry*)((Query*)querytree_list->head->data->ptr_value)->targetList->head->data->ptr_value)
$79 = {xpr = {type = T_TargetEntry}, expr = 0x1f98e50, resno = 1,
resname = 0x1f98da8 "sno", ressortgroupref = 1, resorigtbl = 16408,
resorigcol = 1, resjunk = false}
(gdb) p *((TargetEntry*)((Query*)querytree_list->head->data->ptr_value)->targetList->head->next->data->ptr_value)
$80 = {xpr = {type = T_TargetEntry}, expr = 0x1f98f78, resno = 2,
resname = 0x1f98f08 "sname", ressortgroupref = 0, resorigtbl = 16408,
resorigcol = 2, resjunk = false}
(gdb) p *((TargetEntry*)((Query*)querytree_list->head->data->ptr_value)->targetList->head->next->next->data->ptr_value)
$81 = {xpr = {type = T_TargetEntry}, expr = 0x1f99068, resno = 3,
resname = 0x1f98ff8 "ssex", ressortgroupref = 0, resorigtbl = 16408,
resorigcol = 3, resjunk = false}
(gdb) p *((TargetEntry*)((Query*)querytree_list->head->data->ptr_value)->targetList->head->next->next->next->data->ptr_value)
$82 = {xpr = {type = T_TargetEntry}, expr = 0x2079868, resno = 4,
resname = 0x20797f8 "sage", ressortgroupref = 0, resorigtbl = 16408,
resorigcol = 4, resjunk = false}
(gdb) p *((TargetEntry*)((Query*)querytree_list->head->data->ptr_value)->targetList->head->next->next->next->next->data->ptr_value)
$83 = {xpr = {type = T_TargetEntry}, expr = 0x2079958, resno = 5,
resname = 0x20798e8 "sdept", ressortgroupref = 0, resorigtbl = 16408,
resorigcol = 5, resjunk = false}
(gdb) p *((Query*)querytree_list->head->data->ptr_value)->sortClause
$84 = {type = T_List, length = 1, head = 0x2079f48, tail = 0x2079f48}
(gdb) p *((Node*)((Query*)querytree_list->head->data->ptr_value)->sortClause->head->data->ptr_value)
$85 = {type = T_SortGroupClause}
(gdb) p *((SortGroupClause*)((Query*)querytree_list->head->data->ptr_value)->sortClause->head->data->ptr_value)
$86 = {type = T_SortGroupClause, tleSortGroupRef = 1, eqop = 1054,
sortop = 1060, nulls_first = true, hashable = true}
(gdb) p *((Query*)querytree_list->head->data->ptr_value)->limitCount
$87 = {type = T_FuncExpr}
(gdb) p *((FuncExpr*)((Query*)querytree_list->head->data->ptr_value)->limitCount)
$88 = {xpr = {type = T_FuncExpr}, funcid = 481, funcresulttype = 20,
funcretset = false, funcvariadic = false, funcformat = COERCE_IMPLICIT_CAST,
funccollid = 0, inputcollid = 0, args = 0x207a028, location = -1}
(gdb) p *((FuncExpr*)((Query*)querytree_list->head->data->ptr_value)->limitCount)->args
$89 = {type = T_List, length = 1, head = 0x207a000, tail = 0x207a000}
(gdb) p *((Node*)((FuncExpr*)((Query*)querytree_list->head->data->ptr_value)->limitCount)->args->head->data->ptr_value)
$90 = {type = T_Const}
(gdb) p *((Const*)((FuncExpr*)((Query*)querytree_list->head->data->ptr_value)->limitCount)->args->head->data->ptr_value)
$91 = {xpr = {type = T_Const}, consttype = 23, consttypmod = -1,
constcollid = 0, constlen = 4, constvalue = 2, constisnull = false,
constbyval = true, location = 74}
(gdb)
## 语义分析与查询重写
2018-08-07 19:57:58.720 CST,"appusr","postgres",38496,"127.0.0.1:48658",5b698942.9660,5,"SELECT",2018-08-07 19:57:54 CST,3/2,0,LOG,00000,"rewritten parse tree:","(
{QUERY
:commandType 1
:querySource 0
:canSetTag true
:utilityStmt <>
:resultRelation 0
:hasAggs false
:hasWindowFuncs false
:hasTargetSRFs false
:hasSubLinks false
:hasDistinctOn false
:hasRecursive false
:hasModifyingCTE false
:hasForUpdate false
:hasRowSecurity false
:cteList <>
:rtable (
{RTE
:alias
{ALIAS
:aliasname s
:colnames <>
}
:eref
{ALIAS
:aliasname s
:colnames (""sno"" ""sname"" ""ssex"" ""sage"" ""sdept"")
}
:rtekind 0
:relid 16408
:relkind r
:tablesample <>
:lateral false
:inh true
:inFromCl true
:requiredPerms 2
:checkAsUser 0
:selectedCols (b 9 10 11 12 13)
:insertedCols (b)
:updatedCols (b)
:securityQuals <>
}
)
:jointree
{FROMEXPR
:fromlist (
{RANGETBLREF
:rtindex 1
}
)
:quals
{OPEXPR
:opno 1054
:opfuncid 1048
:opresulttype 16
:opretset false
:opcollid 0
:inputcollid 100
:args (
{VAR
:varno 1
:varattno 5
:vartype 1042
:vartypmod 24
:varcollid 100
:varlevelsup 0
:varnoold 1
:varoattno 5
:location 34
}
{CONST
:consttype 1042
:consttypmod -1
:constcollid 100
:constlen -1
:constbyval false
:constisnull false
:location 42
:constvalue 6 [ 24 0 0 0 73 83 ]
}
)
:location 41
}
}
:targetList (
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 1
:vartype 1042
:vartypmod 13
:varcollid 100
:varlevelsup 0
:varnoold 1
:varoattno 1
:location 7
}
:resno 1
:resname sno
:ressortgroupref 1
:resorigtbl 16408
:resorigcol 1
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 2
:vartype 1042
:vartypmod 24
:varcollid 100
:varlevelsup 0
:varnoold 1
:varoattno 2
:location 7
}
:resno 2
:resname sname
:ressortgroupref 0
:resorigtbl 16408
:resorigcol 2
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 3
:vartype 1042
:vartypmod 6
:varcollid 100
:varlevelsup 0
:varnoold 1
:varoattno 3
:location 7
}
:resno 3
:resname ssex
:ressortgroupref 0
:resorigtbl 16408
:resorigcol 3
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 4
:vartype 21
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnoold 1
:varoattno 4
:location 7
}
:resno 4
:resname sage
:ressortgroupref 0
:resorigtbl 16408
:resorigcol 4
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 5
:vartype 1042
:vartypmod 24
:varcollid 100
:varlevelsup 0
:varnoold 1
:varoattno 5
:location 7
}
:resno 5
:resname sdept
:ressortgroupref 0
:resorigtbl 16408
:resorigcol 5
:resjunk false
}
)
:override 0
:onConflict <>
:returningList <>
:groupClause <>
:groupingSets <>
:havingQual <>
:windowClause <>
:distinctClause <>
:sortClause (
{SORTGROUPCLAUSE
:tleSortGroupRef 1
:eqop 1054
:sortop 1060
:nulls_first true
:hashable true
}
)
:limitOffset <>
:limitCount
{FUNCEXPR
:funcid 481
:funcresulttype 20
:funcretset false
:funcvariadic false
:funcformat 2
:funccollid 0
:inputcollid 0
:args (
{CONST
:consttype 23
:consttypmod -1
:constcollid 0
:constlen 4
:constbyval true
:constisnull false
:location 74
:constvalue 4 [ 2 0 0 0 0 0 0 0 ]
}
)
:location -1
}
:rowMarks <>
:setOperations <>
:constraintDeps <>
:stmt_location 0
:stmt_len 75
}
)
",,,,,"select s.*
from t_student s
where s.sdept='IS'
order by s.sno desc
limit 2;",,,"psql"
重写后的查询树
调用pg_plan_queries进行查询计划和优化
/*
* Generate plans for a list of already-rewritten queries.
*
* For normal optimizable statements, invoke the planner. For utility
* statements, just make a wrapper PlannedStmt node.
*
* The result is a list of PlannedStmt nodes.
*/
List *
pg_plan_queries(List *querytrees, int cursorOptions, ParamListInfo boundParams)
{
....
}
根据上面的query tree产生执行计划。这部分核心代码在planner.c中，是PG的Query Optimizer。会根据表和索引的统计信息去计算不同路径的可能代价值，最后选出最优者。
生成执行计划函数调用栈
-
输入值：
querytrees: 语义分析和查询重写后的查询树
cursorOptions: CURSOR_OPT_PARALLEL_OK
boundParams: NULL -
返回查询执行计划：
# 查询执行计划树
(gdb) p *(plantree_list)
$93 = {type = T_List, length = 1, head = 0x2083828, tail = 0x2083828}
(gdb) p *(plantree_list->head->data->ptr_value)
Attempt to dereference a generic pointer.
(gdb) p *((Node*)plantree_list->head->data->ptr_value)
$94 = {type = T_PlannedStmt}
(gdb) p *((PlannedStmt*)plantree_list->head->data->ptr_value)
$95 = {type = T_PlannedStmt, commandType = CMD_SELECT, queryId = 0,
hasReturning = false, hasModifyingCTE = false, canSetTag = true,
transientPlan = false, dependsOnRole = false, parallelModeNeeded = false,
jitFlags = 0, planTree = 0x206a588, rtable = 0x206a760,
resultRelations = 0x0, nonleafResultRelations = 0x0,
rootResultRelations = 0x0, subplans = 0x0, rewindPlanIDs = 0x0,
rowMarks = 0x0, relationOids = 0x206a7c0, invalItems = 0x0,
paramExecTypes = 0x0, utilityStmt = 0x0, stmt_location = 0, stmt_len = 75}
(gdb) p *((PlannedStmt*)plantree_list->head->data->ptr_value)->planTree
$96 = {type = T_Limit, startup_cost = 14.01, total_cost = 14.015000000000001,
plan_rows = 2, plan_width = 222, parallel_aware = false,
parallel_safe = true, plan_node_id = 0, targetlist = 0x206a8d0, qual = 0x0,
lefttree = 0x2069218, righttree = 0x0, initPlan = 0x0, extParam = 0x0,
allParam = 0x0}
(gdb) p *((PlannedStmt*)plantree_list->head->data->ptr_value)->planTree->lefttree
$97 = {type = T_Sort, startup_cost = 14.01, total_cost = 14.015000000000001,
plan_rows = 2, plan_width = 222, parallel_aware = false,
parallel_safe = true, plan_node_id = 1, targetlist = 0x206ad40, qual = 0x0,
lefttree = 0x206a1b8, righttree = 0x0, initPlan = 0x0, extParam = 0x0,
allParam = 0x0}
(gdb) p *((PlannedStmt*)plantree_list->head->data->ptr_value)->planTree->lefttree->lefttree
$98 = {type = T_SeqScan, startup_cost = 0, total_cost = 14, plan_rows = 2,
plan_width = 222, parallel_aware = false, parallel_safe = true,
plan_node_id = 2, targetlist = 0x2069f20, qual = 0x206a180, lefttree = 0x0,
righttree = 0x0, initPlan = 0x0, extParam = 0x0, allParam = 0x0}
(gdb) p *((PlannedStmt*)plantree_list->head->data->ptr_value)->planTree->lefttree->lefttree->targetlist
$99 = {type = T_List, length = 5, head = 0x2069ef8, tail = 0x206a130}
(gdb) p *((Node*)((PlannedStmt*)plantree_list->head->data->ptr_value)->planTree->lefttree->lefttree->targetlist->head->data->ptr_value)
$100 = {type = T_TargetEntry}
(gdb) p *((TargetEntry*)((PlannedStmt*)plantree_list->head->data->ptr_value)->planTree->lefttree->lefttree->targetlist->head->data->ptr_value)
$101 = {xpr = {type = T_TargetEntry}, expr = 0x2067ed0, resno = 1,
resname = 0x0, ressortgroupref = 1, resorigtbl = 0, resorigcol = 0,
resjunk = false}
(gdb) p *((TargetEntry*)((PlannedStmt*)plantree_list->head->data->ptr_value)->planTree->lefttree->lefttree->targetlist->head->data->ptr_value)->expr
$102 = {type = T_Var}
(gdb) p *((Var*)((TargetEntry*)((PlannedStmt*)plantree_list->head->data->ptr_value)->planTree->lefttree->lefttree->targetlist->head->data->ptr_value)->expr)
$103 = {xpr = {type = T_Var}, varno = 1, varattno = 1, vartype = 1042,
vartypmod = 13, varcollid = 100, varlevelsup = 0, varnoold = 1,
varoattno = 1, location = 7}
(gdb) p *((PlannedStmt*)plantree_list->head->data->ptr_value)->planTree->lefttree->lefttree->qual
$104 = {type = T_List, length = 1, head = 0x206a158, tail = 0x206a158}
(gdb) p *((Node*)((PlannedStmt*)plantree_list->head->data->ptr_value)->planTree->lefttree->lefttree->qual->head->data->ptr_value)
$105 = {type = T_OpExpr}
(gdb) p *((OpExpr*)((PlannedStmt*)plantree_list->head->data->ptr_value)->planTree->lefttree->lefttree->qual->head->data->ptr_value)
$106 = {xpr = {type = T_OpExpr}, opno = 1054, opfuncid = 1048,
opresulttype = 16, opretset = false, opcollid = 0, inputcollid = 100,
args = 0x2067460, location = 41}
(gdb) p *((OpExpr*)((PlannedStmt*)plantree_list->head->data->ptr_value)->planTree->lefttree->lefttree->qual->head->data->ptr_value)->args
$107 = {type = T_List, length = 2, head = 0x2067438, tail = 0x20674f0}
(gdb) p *((Node*)((OpExpr*)((PlannedStmt*)plantree_list->head->data->ptr_value)->planTree->lefttree->lefttree->qual->head->data->ptr_value)->args->head->data->ptr_value)
$108 = {type = T_Var}
(gdb) p *((Var*)((OpExpr*)((PlannedStmt*)plantree_list->head->data->ptr_value)->planTree->lefttree->lefttree->qual->head->data->ptr_value)->args->head->data->ptr_value)
$109 = {xpr = {type = T_Var}, varno = 1, varattno = 5, vartype = 1042,
vartypmod = 24, varcollid = 100, varlevelsup = 0, varnoold = 1,
varoattno = 5, location = 34}
(gdb) p *((Node*)((OpExpr*)((PlannedStmt*)plantree_list->head->data->ptr_value)->planTree->lefttree->lefttree->qual->head->data->ptr_value)->args->head->next->data->ptr_value)
$110 = {type = T_Const}
(gdb) p *((Var*)((OpExpr*)((PlannedStmt*)plantree_list->head->data->ptr_value)->planTree->lefttree->lefttree->qual->head->data->ptr_value)->args->head->next->data->ptr_value)
$111 = {xpr = {type = T_Const}, varno = 1042, varattno = -1, vartype = 100,
vartypmod = -1, varcollid = 0, varlevelsup = 34053696, varnoold = 0,
varoattno = 0, location = 42}
(gdb) p *((Node*)((Var*)((OpExpr*)((PlannedStmt*)plantree_list->head->data->ptr_value)->planTree->lefttree->lefttree->qual->head->data->ptr_value)->args->head->next->data->ptr_value))
$112 = {type = T_Const}
(gdb) p *((Node*)((Var*)((OpExpr*)((PlannedStmt*)plantree_list->head->data->ptr_value)->planTree->lefttree->lefttree->qual->head->data->ptr_value)->args->head->next->data->ptr_value)->varlevelsup)
$113 = {type = T_BitmapHeapScan}
(gdb) p *((BitmapHeapScan*)((Var*)((OpExpr*)((PlannedStmt*)plantree_list->head->data->ptr_value)->planTree->lefttree->lefttree->qual->head->data->ptr_value)->args->head->next->data->ptr_value)->varlevelsup)
$114 = {scan = {plan = {type = T_BitmapHeapScan,
startup_cost = 3.1620201333839779e-322,
total_cost = 2.3715151000379834e-322,
plan_rows = 1.6365691319506455e-316, plan_width = 112,
parallel_aware = 30, parallel_safe = 4, plan_node_id = 1048,
targetlist = 0x0, qual = 0x64, lefttree = 0x2079d88, righttree = 0x29,
initPlan = 0x7f7f7f7f7f7f7f7e, extParam = 0x7f7f7f7f7f7f7f7f,
allParam = 0x40}, scanrelid = 40}, bitmapqualorig = 0x1f970b0}
(gdb)
## 执行计划
2018-08-07 19:57:58.722 CST,"appusr","postgres",38496,"127.0.0.1:48658",5b698942.9660,6,"SELECT",2018-08-07 19:57:54 CST,3/2,0,LOG,00000,"plan:"," {PLANNEDSTMT
:commandType 1
:queryId 0
:hasReturning false
:hasModifyingCTE false
:canSetTag true
:transientPlan false
:dependsOnRole false
:parallelModeNeeded false
:jitFlags 0
:planTree
{LIMIT
:startup_cost 14.01
:total_cost 14.02
:plan_rows 2
:plan_width 222
:parallel_aware false
:parallel_safe true
:plan_node_id 0
:targetlist (
{TARGETENTRY
:expr
{VAR
:varno 65001
:varattno 1
:vartype 1042
:vartypmod 13
:varcollid 100
:varlevelsup 0
:varnoold 1
:varoattno 1
:location -1
}
:resno 1
:resname sno
:ressortgroupref 1
:resorigtbl 16408
:resorigcol 1
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 65001
:varattno 2
:vartype 1042
:vartypmod 24
:varcollid 100
:varlevelsup 0
:varnoold 1
:varoattno 2
:location -1
}
:resno 2
:resname sname
:ressortgroupref 0
:resorigtbl 16408
:resorigcol 2
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 65001
:varattno 3
:vartype 1042
:vartypmod 6
:varcollid 100
:varlevelsup 0
:varnoold 1
:varoattno 3
:location -1
}
:resno 3
:resname ssex
:ressortgroupref 0
:resorigtbl 16408
:resorigcol 3
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 65001
:varattno 4
:vartype 21
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnoold 1
:varoattno 4
:location -1
}
:resno 4
:resname sage
:ressortgroupref 0
:resorigtbl 16408
:resorigcol 4
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 65001
:varattno 5
:vartype 1042
:vartypmod 24
:varcollid 100
:varlevelsup 0
:varnoold 1
:varoattno 5
:location -1
}
:resno 5
:resname sdept
:ressortgroupref 0
:resorigtbl 16408
:resorigcol 5
:resjunk false
}
)
:qual <>
:lefttree
{SORT
:startup_cost 14.01
:total_cost 14.02
:plan_rows 2
:plan_width 222
:parallel_aware false
:parallel_safe true
:plan_node_id 1
:targetlist (
{TARGETENTRY
:expr
{VAR
:varno 65001
:varattno 1
:vartype 1042
:vartypmod 13
:varcollid 100
:varlevelsup 0
:varnoold 1
:varoattno 1
:location -1
}
:resno 1
:resname sno
:ressortgroupref 1
:resorigtbl 16408
:resorigcol 1
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 65001
:varattno 2
:vartype 1042
:vartypmod 24
:varcollid 100
:varlevelsup 0
:varnoold 1
:varoattno 2
:location -1
}
:resno 2
:resname sname
:ressortgroupref 0
:resorigtbl 16408
:resorigcol 2
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 65001
:varattno 3
:vartype 1042
:vartypmod 6
:varcollid 100
:varlevelsup 0
:varnoold 1
:varoattno 3
:location -1
}
:resno 3
:resname ssex
:ressortgroupref 0
:resorigtbl 16408
:resorigcol 3
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 65001
:varattno 4
:vartype 21
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnoold 1
:varoattno 4
:location -1
}
:resno 4
:resname sage
:ressortgroupref 0
:resorigtbl 16408
:resorigcol 4
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 65001
:varattno 5
:vartype 1042
:vartypmod 24
:varcollid 100
:varlevelsup 0
:varnoold 1
:varoattno 5
:location -1
}
:resno 5
:resname sdept
:ressortgroupref 0
:resorigtbl 16408
:resorigcol 5
:resjunk false
}
)
:qual <>
:lefttree
{SEQSCAN
:startup_cost 0.00
:total_cost 14.00
:plan_rows 2
:plan_width 222
:parallel_aware false
:parallel_safe true
:plan_node_id 2
:targetlist (
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 1
:vartype 1042
:vartypmod 13
:varcollid 100
:varlevelsup 0
:varnoold 1
:varoattno 1
:location 7
}
:resno 1
:resname <>
:ressortgroupref 1
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 2
:vartype 1042
:vartypmod 24
:varcollid 100
:varlevelsup 0
:varnoold 1
:varoattno 2
:location 7
}
:resno 2
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 3
:vartype 1042
:vartypmod 6
:varcollid 100
:varlevelsup 0
:varnoold 1
:varoattno 3
:location 7
}
:resno 3
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 4
:vartype 21
:vartypmod -1
:varcollid 0
:varlevelsup 0
:varnoold 1
:varoattno 4
:location 7
}
:resno 4
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 5
:vartype 1042
:vartypmod 24
:varcollid 100
:varlevelsup 0
:varnoold 1
:varoattno 5
:location 7
}
:resno 5
:resname <>
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
)
:qual (
{OPEXPR
:opno 1054
:opfuncid 1048
:opresulttype 16
:opretset false
:opcollid 0
:inputcollid 100
:args (
{VAR
:varno 1
:varattno 5
:vartype 1042
:vartypmod 24
:varcollid 100
:varlevelsup 0
:varnoold 1
:varoattno 5
:location 34
}
{CONST
:consttype 1042
:consttypmod -1
:constcollid 100
:constlen -1
:constbyval false
:constisnull false
:location 42
:constvalue 6 [ 24 0 0 0 73 83 ]
}
)
:location 41
}
)
:lefttree <>
:righttree <>
:initPlan <>
:extParam (b)
:allParam (b)
:scanrelid 1
}
:righttree <>
:initPlan <>
:extParam (b)
:allParam (b)
:numCols 1
:sortColIdx 1
:sortOperators 1060
:collations 100
:nullsFirst true
}
:righttree <>
:initPlan <>
:extParam (b)
:allParam (b)
:limitOffset <>
:limitCount
{CONST
:consttype 20
:consttypmod -1
:constcollid 0
:constlen 8
:constbyval true
:constisnull false
:location -1
:constvalue 8 [ 2 0 0 0 0 0 0 0 ]
}
}
:rtable (
{RTE
:alias
{ALIAS
:aliasname s
:colnames <>
}
:eref
{ALIAS
:aliasname s
:colnames (""sno"" ""sname"" ""ssex"" ""sage"" ""sdept"")
}
:rtekind 0
:relid 16408
:relkind r
:tablesample <>
:lateral false
:inh false
:inFromCl true
:requiredPerms 2
:checkAsUser 0
:selectedCols (b 9 10 11 12 13)
:insertedCols (b)
:updatedCols (b)
:securityQuals <>
}
)
:resultRelations <>
:nonleafResultRelations <>
:rootResultRelations <>
:subplans <>
:rewindPlanIDs (b)
:rowMarks <>
:relationOids (o 16408)
:invalItems <>
:paramExecTypes <>
:utilityStmt <>
:stmt_location 0
:stmt_len 75
}
",,,,,"select s.*
from t_student s
where s.sdept='IS'
order by s.sno desc
limit 2;",,,"psql"
查询执行计划树
LOG: duration: 4.676 ms
sno | sname | ssex | sage | sdept
-----------+-------------------------+------+------+----------------------
201215129 | 黄林林 | 男 | 21 | IS
201215126 | 李一平 | 男 | 18 | IS
(2 rows)
postgres=#
总结：
本文以一个简单的查询为例子，大致说明了exec_simple_query的查询函数执行流程，及相关的查询树等数据结构分析。
重要说明了查询语法树、计划执行树的结构说明。
扫一扫
380
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
