// classic formula is:
// A X B => |A|*|B|/max(dA, dB) where dA,dB are distinct values of joining columns
// This however does not consider join key distribution. In SQL Server 2014, it introduced
// histogram join to better the estimation.
//
public override ulong LogicJoinCE(LogicJoin node)
{
ulong getDistinct(Expr key)
{
if (key is ColExpr col)
{
var tr = col.tabRef_;
if (tr is FromQueryRef fqr && fqr.MapOutputName(col.colName_) != null)
{
if (fqr.MapOutputName(col.colName_) is ColExpr ce)
{
tr = ce.tabRef_;
}
}
if (tr is BaseTableRef btr)
{
var stats = Catalog.sysstat_.GetColumnStat(btr.relname_, col.colName_);
return(stats?.EstDistinct() ?? 0);
}
}
return(0);
}
ulong card;
node.CreateKeyList();
var cardl = node.lchild_().Card();
var cardr = node.rchild_().Card();
ulong dl = 0, dr = 0, mindlr = 1;
for (int i = 0; i < node.leftKeys_.Count; i++)
{
var lv = node.leftKeys_[i];
dl = getDistinct(lv);
var rv = node.rightKeys_[i];
dr = getDistinct(rv);
if (node.ops_[i] != "=")
{
mindlr = 0;
break;
}
mindlr = mindlr * Math.Min(dl, dr);
}
if (mindlr != 0)
{
card = Math.Max(1, (cardl * cardr) / mindlr);
}
else
{
// fall back to the old estimator
card = DefaultEstimate(node);
}
return(card);
}
// classic formula is:
// A X B => |A|*|B|/max(dA, dB) where dA,dB are distinct values of joining columns
// This however does not consider join key distribution. In SQL Server 2014, it introduced
// histogram join to better the estimation.
//
public override long LogicJoinCE(LogicJoin node)
{
long card;
node.CreateKeyList();
var cardl = node.l_().Card();
var cardr = node.r_().Card();
long dl = 0, dr = 0, mindlr = 1;
for (int i = 0; i < node.leftKeys_.Count; i++)
{
var lv = node.leftKeys_[i];
if (lv is ColExpr vl && vl.tabRef_ is BaseTableRef bvl)
{
var stat = Catalog.sysstat_.GetColumnStat(bvl.relname_, vl.colName_);
dl = stat.EstDistinct();
}
var rv = node.rightKeys_[i];
if (rv is ColExpr vr && vr.tabRef_ is BaseTableRef bvr)
{
var stat = Catalog.sysstat_.GetColumnStat(bvr.relname_, vr.colName_);
dr = stat.EstDistinct();
}
if (node.ops_[i] != "=")
{
mindlr = 0;
break;
}
mindlr = mindlr * Math.Min(dl, dr);
}
if (mindlr != 0)
{
card = Math.Max(1, (cardl * cardr) / mindlr);
}
else
{
// fall back to the old estimator
card = DefaultEstimate(node);
}
return(card);
}
