PostgreSQL在何处处理 sql查询之三十七

本文涉及的产品
云原生数据库 PolarDB MySQL 版,通用型 2核4GB 50GB
云原生数据库 PolarDB PostgreSQL 版,标准版 2核4GB 50GB
简介:

看:

复制代码
TupleTableSlot *
ExecProcNode(PlanState *node)
{
    TupleTableSlot *result;

    CHECK_FOR_INTERRUPTS();

    if (node->chgParam != NULL) /* something changed */
        ExecReScan(node);        /* let ReScan handle this */

    if (node->instrument)
        InstrStartNode(node->instrument);

    switch (nodeTag(node))
    {
            /*
             * control nodes
             */
        case T_ResultState:
            fprintf(stderr,"T_ResultState\n");
            result = ExecResult((ResultState *) node);
            break;

        case T_ModifyTableState:
            fprintf(stderr,"T_ModifyTableState\n");
            result = ExecModifyTable((ModifyTableState *) node);
            break;

        case T_AppendState:
            fprintf(stderr,"T_AppendState\n");
            result = ExecAppend((AppendState *) node);
            break;

        case T_MergeAppendState:
            fprintf(stderr,"T_MergeAppendState\n");
            result = ExecMergeAppend((MergeAppendState *) node);
            break;

        case T_RecursiveUnionState:
            fprintf(stderr,"T_RecursiveUnionState\n");
            result = ExecRecursiveUnion((RecursiveUnionState *) node);
            break;

            /* BitmapAndState does not yield tuples */

            /* BitmapOrState does not yield tuples */

            /*
             * scan nodes
             */
        case T_SeqScanState:
            fprintf(stderr,"T_SeqScanState\n");
            result = ExecSeqScan((SeqScanState *) node);
            break;

        case T_IndexScanState:
            fprintf(stderr,"T_IndexScanState\n");
            result = ExecIndexScan((IndexScanState *) node);
            break;

        case T_IndexOnlyScanState:
            fprintf(stderr,"T_IndexOnlyScanState\n");
            result = ExecIndexOnlyScan((IndexOnlyScanState *) node);
            break;

            /* BitmapIndexScanState does not yield tuples */

        case T_BitmapHeapScanState:
            fprintf(stderr,"T_BitmapHeapScanState\n");
            result = ExecBitmapHeapScan((BitmapHeapScanState *) node);
            break;

        case T_TidScanState:
            fprintf(stderr,"T_TidScanState\n");
            result = ExecTidScan((TidScanState *) node);
            break;

        case T_SubqueryScanState:
            fprintf(stderr,"T_SubqueryScanState\n");
            result = ExecSubqueryScan((SubqueryScanState *) node);
            break;

        case T_FunctionScanState:
            fprintf(stderr,"T_FunctionScanState\n");
            result = ExecFunctionScan((FunctionScanState *) node);
            break;

        case T_ValuesScanState:
            fprintf(stderr,"T_ValuesScanState\n");
            result = ExecValuesScan((ValuesScanState *) node);
            break;

        case T_CteScanState:
            fprintf(stderr,"T_CteScanState\n");
            result = ExecCteScan((CteScanState *) node);
            break;

        case T_WorkTableScanState:
            fprintf(stderr,"T_WorkTableScanState\n");
            result = ExecWorkTableScan((WorkTableScanState *) node);
            break;

        case T_ForeignScanState:
            fprintf(stderr,"T_ForeignScanState\n");
            result = ExecForeignScan((ForeignScanState *) node);
            break;

            /*
             * join nodes
             */
        case T_NestLoopState:
            fprintf(stderr,"T_NestLoopState\n");
            result = ExecNestLoop((NestLoopState *) node);
            break;

        case T_MergeJoinState:
            fprintf(stderr,"T_MergeJoinState\n");
            result = ExecMergeJoin((MergeJoinState *) node);
            break;

        case T_HashJoinState:
            fprintf(stderr,"T_HashJoinState\n");
            result = ExecHashJoin((HashJoinState *) node);
            break;

            /*
             * materialization nodes
             */
        case T_MaterialState:
            fprintf(stderr,"T_MaterialState\n");
            result = ExecMaterial((MaterialState *) node);
            break;

        case T_SortState:
            fprintf(stderr,"T_SortState\n");
            result = ExecSort((SortState *) node);
            break;

        case T_GroupState:
            fprintf(stderr,"T_GroupState\n");
            result = ExecGroup((GroupState *) node);
            break;

        case T_AggState:
            fprintf(stderr,"T_AggState\n");
            result = ExecAgg((AggState *) node);
            break;

        case T_WindowAggState:
            fprintf(stderr,"T_WindowAggState\n");
            result = ExecWindowAgg((WindowAggState *) node);
            break;

        case T_UniqueState:
            fprintf(stderr,"T_UniqueState\n");
            result = ExecUnique((UniqueState *) node);
            break;

        case T_HashState:
            fprintf(stderr,"T_HashState\n");
            result = ExecHash((HashState *) node);
            break;

        case T_SetOpState:
            fprintf(stderr,"T_SetOpState\n");
            result = ExecSetOp((SetOpState *) node);
            break;

        case T_LockRowsState:
            fprintf(stderr,"T_LockRowsState\n");
            result = ExecLockRows((LockRowsState *) node);
            break;

        case T_LimitState:
            fprintf(stderr,"T_LimitState\n");
            result = ExecLimit((LimitState *) node);
            break;

        default:
            elog(ERROR, "unrecognized node type: %d", (int) nodeTag(node));
            result = NULL;
            break;
    }

    if (node->instrument)
        InstrStopNode(node->instrument, TupIsNull(result) ? 0.0 : 1.0);

    return result;
}
复制代码

实际执行发现:我的select id from tst04 where id<3 ,执行了三次 T_SeqScanState。

将上述代码缩略:

复制代码
TupleTableSlot *
ExecProcNode(PlanState *node)
{
    TupleTableSlot *result;

    CHECK_FOR_INTERRUPTS();

    if (node->chgParam != NULL) /* something changed */
        ExecReScan(node);        /* let ReScan handle this */

    if (node->instrument)
        InstrStartNode(node->instrument);

    switch (nodeTag(node))
    {
       ...
            /*
             * scan nodes
             */
        case T_SeqScanState:
            fprintf(stderr,"T_SeqScanState\n");
            result = ExecSeqScan((SeqScanState *) node);
            break;

        ...
    }

    if (node->instrument)
        InstrStopNode(node->instrument, TupIsNull(result) ? 0.0 : 1.0);

    return result;
}
复制代码

 再看 ExecSeaScan 的执行:

复制代码
/* ----------------------------------------------------------------
 *        ExecSeqScan(node)
 *
 *        Scans the relation sequentially and returns the next qualifying
 *        tuple.
 *        We call the ExecScan() routine and pass it the appropriate
 *        access method functions.
 * ----------------------------------------------------------------
 */
TupleTableSlot *
ExecSeqScan(SeqScanState *node)
{
    return ExecScan((ScanState *) node,
                    (ExecScanAccessMtd) SeqNext,
                    (ExecScanRecheckMtd) SeqRecheck);
}
复制代码

这个就是全表扫描中的一步。

复制代码
/* ----------------------------------------------------------------
 *        ExecScan
 *
 *        Scans the relation using the 'access method' indicated and
 *        returns the next qualifying tuple in the direction specified
 *        in the global variable ExecDirection.
 *        The access method returns the next tuple and execScan() is
 *        responsible for checking the tuple returned against the qual-clause.
 *
 *        A 'recheck method' must also be provided that can check an
 *        arbitrary tuple of the relation against any qual conditions
 *        that are implemented internal to the access method.
 *
 *        Conditions:
 *          -- the "cursor" maintained by the AMI is positioned at the tuple
 *             returned previously.
 *
 *        Initial States:
 *          -- the relation indicated is opened for scanning so that the
 *             "cursor" is positioned before the first qualifying tuple.
 * ----------------------------------------------------------------
 */
TupleTableSlot *
ExecScan(ScanState *node,
         ExecScanAccessMtd accessMtd,    /* function returning a tuple */
         ExecScanRecheckMtd recheckMtd)
{
    ExprContext *econtext;
    List       *qual;
    ProjectionInfo *projInfo;
    ExprDoneCond isDone;
    TupleTableSlot *resultSlot;

    /*
     * Fetch data from node
     */
    qual = node->ps.qual;
    projInfo = node->ps.ps_ProjInfo;
    econtext = node->ps.ps_ExprContext;

    /*
     * If we have neither a qual to check nor a projection to do, just skip
     * all the overhead and return the raw scan tuple.
     */
    if (!qual && !projInfo)
    {
        ResetExprContext(econtext);
        return ExecScanFetch(node, accessMtd, recheckMtd);
    }

    /*
     * Check to see if we're still projecting out tuples from a previous scan
     * tuple (because there is a function-returning-set in the projection
     * expressions).  If so, try to project another one.
     */
    if (node->ps.ps_TupFromTlist)
    {
        Assert(projInfo);        /* can't get here if not projecting */
        resultSlot = ExecProject(projInfo, &isDone);
        if (isDone == ExprMultipleResult)
            return resultSlot;
        /* Done with that source tuple... */
        node->ps.ps_TupFromTlist = false;
    }

    /*
     * Reset per-tuple memory context to free any expression evaluation
     * storage allocated in the previous tuple cycle.  Note this can't happen
     * until we're done projecting out tuples from a scan tuple.
     */
    ResetExprContext(econtext);

    /*
     * get a tuple from the access method.    Loop until we obtain a tuple that
     * passes the qualification.
     */
    for (;;)
    {
        TupleTableSlot *slot;

        CHECK_FOR_INTERRUPTS();

        slot = ExecScanFetch(node, accessMtd, recheckMtd);

        /*
         * if the slot returned by the accessMtd contains NULL, then it means
         * there is nothing more to scan so we just return an empty slot,
         * being careful to use the projection result slot so it has correct
         * tupleDesc.
         */
        if (TupIsNull(slot))
        {
            if (projInfo)
                return ExecClearTuple(projInfo->pi_slot);
            else
                return slot;
        }

        /*
         * place the current tuple into the expr context
         */
        econtext->ecxt_scantuple = slot;

        /*
         * check that the current tuple satisfies the qual-clause
         *
         * check for non-nil qual here to avoid a function call to ExecQual()
         * when the qual is nil ... saves only a few cycles, but they add up
         * ...
         */
        if (!qual || ExecQual(qual, econtext, false))
        {
            /*
             * Found a satisfactory scan tuple.
             */
            if (projInfo)
            {
                /*
                 * Form a projection tuple, store it in the result tuple slot
                 * and return it --- unless we find we can project no tuples
                 * from this scan tuple, in which case continue scan.
                 */
                resultSlot = ExecProject(projInfo, &isDone);
                if (isDone != ExprEndResult)
                {
                    node->ps.ps_TupFromTlist = (isDone == ExprMultipleResult);
                    return resultSlot;
                }
            }
            else
            {
                /*
                 * Here, we aren't projecting, so just return scan tuple.
                 */
                return slot;
            }
        }
        else
            InstrCountFiltered1(node, 1);

        /*
         * Tuple fails qual, so free per-tuple memory and try again.
         */
        ResetExprContext(econtext);
    }
}
复制代码

现在抛开细节不谈,谈谈自己的观感:

梳理一下:

对于一个特定的查询而言,一旦执行计划指定了seqscan,那么接下来:

ExecutePlan --> ExecProcNode --> ExecSeqScan-->ExecScan

复制代码
static void
ExecutePlan(EState *estate,
            PlanState *planstate,
            CmdType operation,
            bool sendTuples,
            long numberTuples,
            ScanDirection direction,
            DestReceiver *dest)
{
    ...
    /*
     * Loop until we've processed the proper number of tuples from the plan.
     */
    for (;;)
    {
        ...
        /*
         * Execute the plan and obtain a tuple
         */
        slot = ExecProcNode(planstate);
        ...

        /*
         * if the tuple is null, then we assume there is nothing more to
         * process so we just end the loop...
         */
        if (TupIsNull(slot))
            break;
        ...
/* * check our tuple count.. if we've processed the proper number then * quit, else loop again and process more tuples. Zero numberTuples * means no limit. */ current_tuple_count++; if (numberTuples && numberTuples == current_tuple_count) break; } }
复制代码

ExecProcNode:

复制代码
/* ----------------------------------------------------------------
 *        ExecProcNode
 *
 *        Execute the given node to return a(nother) tuple.
 * ----------------------------------------------------------------
 */
TupleTableSlot *
ExecProcNode(PlanState *node)
{
    ...

    switch (nodeTag(node))
    {
            /*
             * control nodes
             */
        case T_ResultState:
            
            result = ExecResult((ResultState *) node);
            break;
        ...
            /*
             * scan nodes
             */
        case T_SeqScanState:
            //fprintf(stderr,"T_SeqScanState\n");
            result = ExecSeqScan((SeqScanState *) node);
            break;

        case T_IndexScanState:
            fprintf(stderr,"T_IndexScanState\n");
            result = ExecIndexScan((IndexScanState *) node);
            break;
        ...
    }
...
return result; }
复制代码

ExecSeqScan:

复制代码
TupleTableSlot *
ExecSeqScan(SeqScanState *node)
{
    return ExecScan((ScanState *) node,
                    (ExecScanAccessMtd) SeqNext,
                    (ExecScanRecheckMtd) SeqRecheck);
}
复制代码

ExecScan:

复制代码
/* ----------------------------------------------------------------
 *        ExecScan
 *
 *        Scans the relation using the 'access method' indicated and
 *        returns the next qualifying tuple in the direction specified
 *        in the global variable ExecDirection.
 *        The access method returns the next tuple and execScan() is
 *        responsible for checking the tuple returned against the qual-clause.
 *
 *        A 'recheck method' must also be provided that can check an
 *        arbitrary tuple of the relation against any qual conditions
 *        that are implemented internal to the access method.
 *
 *        Conditions:
 *          -- the "cursor" maintained by the AMI is positioned at the tuple
 *             returned previously.
 *
 *        Initial States:
 *          -- the relation indicated is opened for scanning so that the
 *             "cursor" is positioned before the first qualifying tuple.
 * ----------------------------------------------------------------
 */
TupleTableSlot *
ExecScan(ScanState *node,
         ExecScanAccessMtd accessMtd,    /* function returning a tuple */
         ExecScanRecheckMtd recheckMtd)
{
    ...

    /*
     * get a tuple from the access method.    Loop until we obtain a tuple that
     * passes the qualification.
     */
    for (;;)
    {
        ...

        slot = ExecScanFetch(node, accessMtd, recheckMtd);

        ...
    }
}
复制代码

可以看到,全表扫描的时候,是一条条地往下捋,完全没有任何并发的可能。

所以,可以想象,遇到大表,Index立马变得十分重要。如果遇到大表和大表 Hash Join 之类的,那就自求多福吧。

我现在看到的是,加入调试信息后,对于如下的表:

复制代码
postgres=# select * from tst04;
 id | val 
----+-----
  4 | 400
  1 | 100
  3 | 300
  2 | 200
(4 rows)
复制代码

然后这样来查询:

复制代码
postgres=# select * from tst04 where id>1;
 id | val 
----+-----
  4 | 400
  3 | 300
  2 | 200
(3 rows)
复制代码

可以看到执行情况:

复制代码
In Standard_ExecutorRun, count is 0
In ExecutePlan ... for loop by process 7365
In ExecScan... for loop ... by process 7365
In ExecutePlan ... for loop by process 7365
In ExecScan... for loop ... by process 7365
In ExecScan... for loop ... by process 7365
In ExecutePlan ... for loop by process 7365
In ExecScan... for loop ... by process 7365
In ExecutePlan ... for loop by process 7365
In ExecScan... for loop ... by process 7365
复制代码

对为何执行这么多次,还不是很理解。







本文转自健哥的数据花园博客园博客,原文链接:http://www.cnblogs.com/gaojian/archive/2013/05/31/3108539.html,如需转载请自行联系原作者

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
相关文章
|
3天前
|
SQL 存储 缓存
如何优化SQL查询性能?
【10月更文挑战第28天】如何优化SQL查询性能?
21 10
|
5天前
|
SQL 关系型数据库 数据库
PostgreSQL性能飙升的秘密:这几个调优技巧让你的数据库查询速度翻倍!
【10月更文挑战第25天】本文介绍了几种有效提升 PostgreSQL 数据库查询效率的方法,包括索引优化、查询优化、配置优化和硬件优化。通过合理设计索引、编写高效 SQL 查询、调整配置参数和选择合适硬件,可以显著提高数据库性能。
40 1
|
10天前
|
SQL 数据库 开发者
功能发布-自定义SQL查询
本期主要为大家介绍ClkLog九月上线的新功能-自定义SQL查询。
|
17天前
|
SQL 移动开发 Oracle
SQL语句实现查询连续六天数据的方法与技巧
在数据库查询中,有时需要筛选出符合特定时间连续性条件的数据记录
|
25天前
|
SQL Java 数据库连接
如何使用`DriverManager.getConnection()`连接数据库,并利用`PreparedStatement`执行参数化查询,有效防止SQL注入。
【10月更文挑战第6天】在代码与逻辑交织的世界中,我从一名数据库新手出发,通过不断探索与实践,最终成为熟练掌握JDBC的开发者。这段旅程充满挑战与惊喜,从建立数据库连接到执行SQL语句,再到理解事务管理和批处理等高级功能,每一步都让我对JDBC有了更深的认识。示例代码展示了如何使用`DriverManager.getConnection()`连接数据库,并利用`PreparedStatement`执行参数化查询,有效防止SQL注入。
68 5
|
25天前
|
SQL 数据挖掘 数据库
SQL查询每秒的数据:技巧、方法与性能优化
id="">SQL查询功能详解 SQL(Structured Query Language,结构化查询语言)是一种专门用于与数据库进行沟通和操作的语言
|
25天前
|
SQL 移动开发 大数据
SQL语句查询连续六天满足条件的记录
在数据库管理和数据分析中,我们经常需要查询符合特定时间范围内连续几天的记录
|
6天前
|
SQL 关系型数据库 MySQL
mysql编写sql脚本:要求表没有主键,但是想查询没有相同值的时候才进行插入
mysql编写sql脚本:要求表没有主键,但是想查询没有相同值的时候才进行插入
14 0
|
17天前
|
SQL 数据可视化 BI
SQL语句及查询结果解析:技巧与方法
在数据库管理和数据分析中,SQL语句扮演着至关重要的角色
|
23天前
|
SQL 监控 关系型数据库
使用SQL语句查询操作耗时的技巧与方法
在数据库管理和优化过程中,了解SQL查询操作的耗时是至关重要的