// 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); }