PostgreSQL查询语句执行过程

PostgreSQL查询语句执行过程

 

一、背景

为了分析postgresql代码,了解其执行查询语句的过程,我采用eclipse + gdb集成调试环境,在客户端执行一个查询语句,观察分析其执行流程及重要数据结构变化中数据。

二、环境准备

参考文章:使用eclipse调试分析PostgreSQL11

查询执行流程

Postgresql 执行insert、delete、update、select都是通过postgres.c里面的exec_simple_query方法,其基本流程是

  1. 启动事务 start_xact_command();
  2. 进行语法分析,生成语法树
parsetree_list = pg_parse_query(query_string);

只是简单的产生raw parse tree,这个里面不涉及语义检查。只是做语法扫描。

  1. 语义分析和查询重写。
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 部分等。是任何数据库都需要操作的,并且非常重要的一环。

  1. 生成执行计划
plantree_list = pg_plan_queries(querytree_list,CURSOR_OPT_PARALLEL_OK, NULL);

根据上面的query tree产生执行计划。这部分核心代码在planner.c中,是PG的Query Optimizer。会根据表和索引的统计信息去计算不同路径的可能代价值,最后选出最优者。

  1. 执行查询
        /*
         * 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,它会遍历每个节点,以致完成。最后将查询结果返回给客户端。

  1. 结束事务 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执行查询命令。

主要结构体分析

  1. 链表
 /* 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;   /*下一节点*/
};
  1. 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;
  1. 语法分析结果的处理节点
/*  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;
  1. 查询树数据结构
/*  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进行语法分析校验

语法分析函数调用栈

  1. 输入的query_string的值
select s.*
from t_student s
where s.sdept='IS'
order by s.sno desc 
limit 2;
  1. 返回语法树信息如下:
## 语法树
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

  1. 输入信息

     

    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;

  1. 返回的查询重写结果为
#语义分析和查询重写后的查询树信息
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。会根据表和索引的统计信息去计算不同路径的可能代价值,最后选出最优者。

生成执行计划函数调用栈

  1. 输入值:
    querytrees: 语义分析和查询重写后的查询树
    cursorOptions: CURSOR_OPT_PARALLEL_OK
    boundParams: NULL

  2. 返回查询执行计划:

# 查询执行计划树
(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的查询函数执行流程,及相关的查询树等数据结构分析。
重要说明了查询语法树、计划执行树的结构说明。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值