【源码解析】postgresql having clause 是如何实现的 (2)
在上一篇中,主要探究了 postgresql 源码层面是怎么实现聚合函数的。本篇将探究 having clause 是如何实现的。
setup
create table foo(a int, b int);
insert into foo select random() * i/2, random() * i from generate_series(10, 20) g(i);
select a, count(b) from foo group by a having count(b) > 1;
parser
the raw parse tree is:
(
{RAWSTMT
:stmt
{SELECTSTMT
:targetList (
{RESTARGET
:val
{COLUMNREF
:fields ("a")
}
}
{RESTARGET
:val
{FUNCCALL
:funcname ("count")
:args (
{COLUMNREF
:fields ("b")
:location 16
}
)
}
}
)
:fromClause (
{RANGEVAR
:relname foo
}
)
:groupClause (
{COLUMNREF
:fields ("a")
}
)
:havingClause
{A_EXPR
:name (">")
:lexpr
{FUNCCALL
:funcname ("count")
:args (
{COLUMNREF
:fields ("b")
}
)
}
:rexpr
{A_CONST
:val 1
}
}
}
}
)
analyser
the query tree is:
{QUERY
:commandType 1
:rtable (
{RANGETBLENTRY
:eref
{ALIAS
:aliasname foo
:colnames ("a" "b")
}
:rtekind 0
:relid 16398
:relkind r
}
)
:targetList (
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 1
:vartype 23
}
:resno 1
:resname a
:resorigtbl 16398
:resorigcol 1
}
{TARGETENTRY
:expr
{AGGREF
:aggfnoid 2147
:aggtype 20
:args (
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 2
:vartype 23
}
:resno 1
}
)
:aggkind n
}
:resno 2
:resname count
}
)
:groupClause (
{SORTGROUPCLAUSE
:tleSortGroupRef 1
:eqop 96
:sortop 97
:hashable true
}
)
:havingQual
{OPEXPR
:opno 419
:opfuncid 477
:opresulttype 16
:args (
{AGGREF
:aggfnoid 2147
:aggtype 20
:args (
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 2
:vartype 23
}
:resno 1
}
)
:aggkind n
}
{CONST
:consttype 23
:constlen 4
:constbyval true
:constvalue 4 [ 1 0 0 0 0 0 0 0 ]
}
)
}
}
rewritter
In this simple case, the rewritter does nothing on the query tree.
planner/optimizer
{PLANNEDSTMT
:commandType 1
:planTree
{AGG
:plan.targetlist (
{TARGETENTRY
:expr
{VAR
:varno -2
:varattno 1
:vartype 23
}
:resno 1
:resname a
:ressortgroupref 1
:resorigtbl 16404
:resorigcol 1
}
{TARGETENTRY
:expr
{AGGREF
:aggfnoid 2147
:aggtype 20
:aggtranstype 20
:aggargtypes (o 23)
:args (
{TARGETENTRY
:expr
{VAR
:varno -2
:varattno 2
:vartype 23
}
:resno 1
}
)
}
:resno 2
:resname count
}
)
:plan.qual (
{OPEXPR
:opno 419
:opfuncid 477
:opresulttype 16
:args (
{AGGREF
:aggfnoid 2147
:aggtype 20
:aggtranstype 20
:aggargtypes (o 23)
:args (
{TARGETENTRY
:expr
{VAR
:varno -2
:varattno 2
:vartype 23
:varnosyn 1
:varattnosyn 2
}
:resno 1
}
)
}
{CONST
:consttype 23
:constlen 4
:constbyval true
:constvalue 4 [ 1 0 0 0 0 0 0 0 ]
}
)
}
)
:plan.lefttree
{SEQSCAN
:scan.plan.targetlist (
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 1
:vartype 23
:varnosyn 1
:varattnosyn 1
}
:resno 1
:ressortgroupref 1
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 2
:vartype 23
:varnosyn 1
:varattnosyn 2
}
:resno 2
}
)
}
:aggstrategy 2
:numCols 1
:grpColIdx ( 1)
:grpOperators ( 96)
:numGroups 200
}
:rtable (
{RANGETBLENTRY
:eref
{ALIAS
:aliasname foo
:colnames ("a" "b")
}
:rtekind 0
:relid 16404
:relkind r
}
)
:relationOids (o 16404)
}
executor
For a sql statement with having clause, the function project_aggregate
needs
check the qualification condition by function ExecQual
. The ExecQual
function evaluates the conditional expression by following steps:
EEOP_AGGREF
EEOP_FUNCEXPR_STRICT
EEOP_QUAL
EEOP_DONE
posted on 2023-08-11 20:52 winter-loo 阅读(6) 评论(0) 编辑 收藏 举报