PostgreSQL在何处处理 sql查询之五十九

postgres=# explain select dept.no_emps,emp.age from dept,emp where emp.name = dept.mgr and dept.dept_name = 'shoe';
                            QUERY PLAN                            
------------------------------------------------------------------
 Hash Join  (cost=19.30..45.07 rows=23 width=8)
   Hash Cond: ((emp.name)::text = (dept.mgr)::text)
   ->  Seq Scan on emp  (cost=0.00..21.30 rows=1130 width=42)
   ->  Hash  (cost=19.25..19.25 rows=4 width=42)
         ->  Seq Scan on dept  (cost=0.00..19.25 rows=4 width=42)
               Filter: ((dept_name)::text = 'shoe'::text)
(6 rows)

postgres=# 

/* ----------------------------------------------------------------
 *        ExecInitHashJoin
 *
 *        Init routine for HashJoin node.
 * ----------------------------------------------------------------
 */
HashJoinState *
ExecInitHashJoin(HashJoin *node, EState *estate, int eflags)
{
    HashJoinState *hjstate;
    Plan       *outerNode;
    Hash       *hashNode;
    List       *lclauses;
    List       *rclauses;
    List       *hoperators;
    ListCell   *l;

    /* check for unsupported flags */
    Assert(!(eflags & (EXEC_FLAG_BACKWARD | EXEC_FLAG_MARK)));

    /*
     * create state structure
     */
    hjstate = makeNode(HashJoinState);
    hjstate->js.ps.plan = (Plan *) node;
    hjstate->js.ps.state = estate;

    /*
     * Miscellaneous initialization
     *
     * create expression context for node
     */
    ExecAssignExprContext(estate, &hjstate->js.ps);

    /*
     * initialize child expressions
     */
    hjstate->js.ps.targetlist = (List *)
        ExecInitExpr((Expr *) node->join.plan.targetlist,
                     (PlanState *) hjstate);
    hjstate->js.ps.qual = (List *)
        ExecInitExpr((Expr *) node->join.plan.qual,
                     (PlanState *) hjstate);
    hjstate->js.jointype = node->join.jointype;
    hjstate->js.joinqual = (List *)
        ExecInitExpr((Expr *) node->join.joinqual,
                     (PlanState *) hjstate);
    hjstate->hashclauses = (List *)
        ExecInitExpr((Expr *) node->hashclauses,
                     (PlanState *) hjstate);

    /*
     * initialize child nodes
     *
     * Note: we could suppress the REWIND flag for the inner input, which
     * would amount to betting that the hash will be a single batch.  Not
     * clear if this would be a win or not.
     */
    outerNode = outerPlan(node);
    hashNode = (Hash *) innerPlan(node);

    outerPlanState(hjstate) = ExecInitNode(outerNode, estate, eflags);
    innerPlanState(hjstate) = ExecInitNode((Plan *) hashNode, estate, eflags);

    /*
     * tuple table initialization
     */
    ExecInitResultTupleSlot(estate, &hjstate->js.ps);
    hjstate->hj_OuterTupleSlot = ExecInitExtraTupleSlot(estate);

    /* set up null tuples for outer joins, if needed */
    switch (node->join.jointype)
    {
        case JOIN_INNER:
        case JOIN_SEMI:
            break;
        case JOIN_LEFT:
        case JOIN_ANTI:
            hjstate->hj_NullInnerTupleSlot =
                ExecInitNullTupleSlot(estate,
                                 ExecGetResultType(innerPlanState(hjstate)));
            break;
        case JOIN_RIGHT:
            hjstate->hj_NullOuterTupleSlot =
                ExecInitNullTupleSlot(estate,
                                 ExecGetResultType(outerPlanState(hjstate)));
            break;
        case JOIN_FULL:
            hjstate->hj_NullOuterTupleSlot =
                ExecInitNullTupleSlot(estate,
                                 ExecGetResultType(outerPlanState(hjstate)));
            hjstate->hj_NullInnerTupleSlot =
                ExecInitNullTupleSlot(estate,
                                 ExecGetResultType(innerPlanState(hjstate)));
            break;
        default:
            elog(ERROR, "unrecognized join type: %d",
                 (int) node->join.jointype);
    }

    /*
     * now for some voodoo.  our temporary tuple slot is actually the result
     * tuple slot of the Hash node (which is our inner plan).  we can do this
     * because Hash nodes don't return tuples via ExecProcNode() -- instead
     * the hash join node uses ExecScanHashBucket() to get at the contents of
     * the hash table.    -cim 6/9/91
     */
    {
        HashState  *hashstate = (HashState *) innerPlanState(hjstate);
        TupleTableSlot *slot = hashstate->ps.ps_ResultTupleSlot;

        hjstate->hj_HashTupleSlot = slot;
    }

    /*
     * initialize tuple type and projection info
     */
    ExecAssignResultTypeFromTL(&hjstate->js.ps);
    ExecAssignProjectionInfo(&hjstate->js.ps, NULL);

    ExecSetSlotDescriptor(hjstate->hj_OuterTupleSlot,
                          ExecGetResultType(outerPlanState(hjstate)));

    /*
     * initialize hash-specific info
     */
    hjstate->hj_HashTable = NULL;
    hjstate->hj_FirstOuterTupleSlot = NULL;

    hjstate->hj_CurHashValue = 0;
    hjstate->hj_CurBucketNo = 0;
    hjstate->hj_CurSkewBucketNo = INVALID_SKEW_BUCKET_NO;
    hjstate->hj_CurTuple = NULL;

    /*
     * Deconstruct the hash clauses into outer and inner argument values, so
     * that we can evaluate those subexpressions separately.  Also make a list
     * of the hash operator OIDs, in preparation for looking up the hash
     * functions to use.
     */
    lclauses = NIL;
    rclauses = NIL;
    hoperators = NIL;
    foreach(l, hjstate->hashclauses)
    {
        FuncExprState *fstate = (FuncExprState *) lfirst(l);
        OpExpr       *hclause;

        Assert(IsA(fstate, FuncExprState));
        hclause = (OpExpr *) fstate->xprstate.expr;
        Assert(IsA(hclause, OpExpr));
        lclauses = lappend(lclauses, linitial(fstate->args));
        rclauses = lappend(rclauses, lsecond(fstate->args));
        hoperators = lappend_oid(hoperators, hclause->opno);
    }
    hjstate->hj_OuterHashKeys = lclauses;
    hjstate->hj_InnerHashKeys = rclauses;
    hjstate->hj_HashOperators = hoperators;
    /* child Hash node needs to evaluate inner hash keys, too */
    ((HashState *) innerPlanState(hjstate))->hashkeys = rclauses;

    hjstate->js.ps.ps_TupFromTlist = false;
    hjstate->hj_JoinState = HJ_BUILD_HASHTABLE;
    hjstate->hj_MatchedOuter = false;
    hjstate->hj_OuterNotEmpty = false;

    return hjstate;
}

/* ----------------------------------------------------------------
 *        ExecInitHashJoin
 *
 *        Init routine for HashJoin node.
 * ----------------------------------------------------------------
 */
HashJoinState *
ExecInitHashJoin(HashJoin *node, EState *estate, int eflags)
{
    HashJoinState *hjstate;
    Plan       *outerNode;
    Hash       *hashNode;
    List       *lclauses;
    List       *rclauses;
    List       *hoperators;
    ListCell   *l;

    ...

    /*
     * initialize child nodes
     *
     * Note: we could suppress the REWIND flag for the inner input, which
     * would amount to betting that the hash will be a single batch.  Not
     * clear if this would be a win or not.
     */
    outerNode = outerPlan(node);
    hashNode = (Hash *) innerPlan(node);

    outerPlanState(hjstate) = ExecInitNode(outerNode, estate, eflags);
    innerPlanState(hjstate) = ExecInitNode((Plan *) hashNode, estate, eflags);

    ...

    return hjstate;
}