Skip to content

Commit d9924c9

Browse files
authored
[Improvement](topn) add limit threashold session variable and fuzzy for topn optimizations (#16514)
1. add limit threshold for topn runtime pushdown and key topn optimization 2. use unified session variable topn_opt_limit_threshold for all topn optimizations 3. add fuzzy support for topn_opt_limit_threshold
1 parent 8758cd4 commit d9924c9

8 files changed

Lines changed: 88 additions & 24 deletions

File tree

‎docs/en/docs/advanced/variables.md‎

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -571,3 +571,7 @@ Translated with www.DeepL.com/Translator (free version)
571571
* `enable_file_cache`
572572
573573
Set wether to use block file cache. This variable takes effect only if the BE config enable_file_cache=true. The cache is not used when BE config enable_file_cache=false.
574+
575+
* `topn_opt_limit_threshold`
576+
577+
Set threshold for limit of topn query (eg. SELECT * FROM t ORDER BY k LIMIT n). If n <= threshold, topn optimizations(runtime predicate pushdown, two phase result fetch and read order by key) will enable automatically, otherwise disable. Default value is 1024.

‎docs/zh-CN/docs/advanced/variables.md‎

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -560,3 +560,6 @@ SELECT /*+ SET_VAR(query_timeout = 1, enable_partition_cache=true) */ sleep(3);
560560

561561
控制是否启用block file cache。该变量只有在be.conf中enable_file_cache=true时才有效,如果be.conf中enable_file_cache=false,则block file cache一直处于禁用状态。
562562

563+
* `topn_opt_limit_threshold`
564+
565+
设置topn优化的limit阈值 (例如:SELECT * FROM t ORDER BY k LIMIT n). 如果limit的n小于等于阈值,topn相关优化(动态过滤下推、两阶段获取结果、按key的顺序读数据)会自动启用,否则会禁用。默认值是1024。

‎fe/fe-common/src/main/java/org/apache/doris/common/Config.java‎

Lines changed: 0 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1969,9 +1969,6 @@ public class Config extends ConfigBase {
19691969
@ConfField(masterOnly = true, mutable = true)
19701970
public static int max_error_tablet_of_broker_load = 3;
19711971

1972-
@ConfField(mutable = false)
1973-
public static int topn_two_phase_limit_threshold = 512;
1974-
19751972
/**
19761973
* Used to set session variables randomly to check more issues in github workflow
19771974
*/

‎fe/fe-core/src/main/java/org/apache/doris/analysis/SelectStmt.java‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -708,8 +708,8 @@ public boolean checkEnableTwoPhaseRead(Analyzer analyzer) {
708708
// Only TOPN query at present
709709
if (getOrderByElements() == null
710710
|| !hasLimit()
711-
|| getLimit() == 0
712-
|| getLimit() > ConnectContext.get().getSessionVariable().twoPhaseReadLimitThreshold) {
711+
|| getLimit() <= 0
712+
|| getLimit() > ConnectContext.get().getSessionVariable().topnOptLimitThreshold) {
713713
return false;
714714
}
715715
// Check order by exprs are all slot refs

‎fe/fe-core/src/main/java/org/apache/doris/planner/OlapScanNode.java‎

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -940,6 +940,12 @@ private void computeTabletInfo() throws UserException {
940940
* Check Parent sort node can push down to child olap scan.
941941
*/
942942
public boolean checkPushSort(SortNode sortNode) {
943+
// Ensure limit is less then threshold
944+
if (sortNode.getLimit() <= 0
945+
|| sortNode.getLimit() > ConnectContext.get().getSessionVariable().topnOptLimitThreshold) {
946+
return false;
947+
}
948+
943949
// Ensure all isAscOrder is same, ande length != 0.
944950
// Can't be zorder.
945951
if (sortNode.getSortInfo().getIsAscOrder().stream().distinct().count() != 1

‎fe/fe-core/src/main/java/org/apache/doris/planner/OriginalPlanner.java‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -448,6 +448,8 @@ private void checkAndSetTopnOpt(PlanNode node) {
448448
SortNode sortNode = (SortNode) node;
449449
PlanNode child = sortNode.getChild(0);
450450
if (child instanceof OlapScanNode && sortNode.getLimit() > 0
451+
&& ConnectContext.get() != null && ConnectContext.get().getSessionVariable() != null
452+
&& sortNode.getLimit() <= ConnectContext.get().getSessionVariable().topnOptLimitThreshold
451453
&& sortNode.getSortInfo().getMaterializedOrderingExprs().size() > 0) {
452454
Expr firstSortExpr = sortNode.getSortInfo().getMaterializedOrderingExprs().get(0);
453455
if (firstSortExpr instanceof SlotRef && !firstSortExpr.getType().isStringType()

‎fe/fe-core/src/main/java/org/apache/doris/qe/SessionVariable.java‎

Lines changed: 7 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -259,7 +259,8 @@ public class SessionVariable implements Serializable, Writable {
259259
public static final String EXTERNAL_SORT_BYTES_THRESHOLD = "external_sort_bytes_threshold";
260260

261261
public static final String ENABLE_TWO_PHASE_READ_OPT = "enable_two_phase_read_opt";
262-
public static final String TWO_PHASE_READ_OPT_LIMIT_THRESHOLD = "two_phase_read_opt_limit_threshold";
262+
public static final String TOPN_OPT_LIMIT_THRESHOLD = "topn_opt_limit_threshold";
263+
263264
public static final String ENABLE_FILE_CACHE = "enable_file_cache";
264265

265266
public static final String GROUP_BY_AND_HAVING_USE_ALIAS_FIRST = "group_by_and_having_use_alias_first";
@@ -681,8 +682,8 @@ public class SessionVariable implements Serializable, Writable {
681682
// 2. spawn fetch RPC to other nodes to get related data by sorted rowids
682683
@VariableMgr.VarAttr(name = ENABLE_TWO_PHASE_READ_OPT, fuzzy = true)
683684
public boolean enableTwoPhaseReadOpt = true;
684-
@VariableMgr.VarAttr(name = TWO_PHASE_READ_OPT_LIMIT_THRESHOLD)
685-
public long twoPhaseReadLimitThreshold = 512;
685+
@VariableMgr.VarAttr(name = TOPN_OPT_LIMIT_THRESHOLD)
686+
public long topnOptLimitThreshold = 1024;
686687

687688
// Default value is false, which means the group by and having clause
688689
// should first use column name not alias. According to mysql.
@@ -735,6 +736,9 @@ public void initFuzzyModeVariables() {
735736
// this.enableFoldConstantByBe = false;
736737
// this.enableTwoPhaseReadOpt = true;
737738
}
739+
740+
// set random 1, 10, 100, 1000, 10000
741+
this.topnOptLimitThreshold = 10 ^ (random.nextInt(5));
738742
}
739743

740744
public String printFuzzyVariables() {

‎fe/fe-core/src/test/java/org/apache/doris/planner/PlannerTest.java‎

Lines changed: 64 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -508,17 +508,53 @@ public void testUpdateUnique() throws Exception {
508508

509509
@Test
510510
public void testPushSortToOlapScan() throws Exception {
511-
// Push sort successfully
512-
String sql1 = "explain select k1 from db1.tbl1 order by k1, k2";
511+
// Push sort fail without limit
512+
String sql1 = "explain select k1 from db1.tbl3 order by k1, k2";
513513
StmtExecutor stmtExecutor1 = new StmtExecutor(connectContext, sql1);
514514
stmtExecutor1.execute();
515515
Planner planner1 = stmtExecutor1.planner();
516516
String plan1 = planner1.getExplainString(new ExplainOptions(false, false));
517+
Assertions.assertFalse(plan1.contains("SORT INFO:\n `k1`\n `k2`"));
518+
Assertions.assertFalse(plan1.contains("SORT LIMIT:"));
519+
Assertions.assertFalse(plan1.contains("TOPN OPT"));
520+
521+
// Push sort fail limit > topnOptLimitThreshold
522+
sql1 = "explain select k1 from db1.tbl3 order by k1, k2 limit "
523+
+ (connectContext.getSessionVariable().topnOptLimitThreshold + 1);
524+
stmtExecutor1 = new StmtExecutor(connectContext, sql1);
525+
stmtExecutor1.execute();
526+
planner1 = stmtExecutor1.planner();
527+
plan1 = planner1.getExplainString(new ExplainOptions(false, false));
528+
Assertions.assertFalse(plan1.contains("SORT INFO:\n `k1`\n `k2`"));
529+
Assertions.assertFalse(plan1.contains("SORT LIMIT:"));
530+
Assertions.assertFalse(plan1.contains("TOPN OPT"));
531+
532+
// Push sort success limit = topnOptLimitThreshold
533+
sql1 = "explain select k1 from db1.tbl3 order by k1, k2 limit "
534+
+ (connectContext.getSessionVariable().topnOptLimitThreshold);
535+
stmtExecutor1 = new StmtExecutor(connectContext, sql1);
536+
stmtExecutor1.execute();
537+
planner1 = stmtExecutor1.planner();
538+
plan1 = planner1.getExplainString(new ExplainOptions(false, false));
517539
Assertions.assertTrue(plan1.contains("SORT INFO:\n `k1`\n `k2`"));
518540
Assertions.assertTrue(plan1.contains("SORT LIMIT:"));
541+
Assertions.assertTrue(plan1.contains("TOPN OPT"));
542+
543+
// Push sort success limit < topnOptLimitThreshold
544+
if (connectContext.getSessionVariable().topnOptLimitThreshold > 1) {
545+
sql1 = "explain select k1 from db1.tbl3 order by k1, k2 limit "
546+
+ (connectContext.getSessionVariable().topnOptLimitThreshold - 1);
547+
stmtExecutor1 = new StmtExecutor(connectContext, sql1);
548+
stmtExecutor1.execute();
549+
planner1 = stmtExecutor1.planner();
550+
plan1 = planner1.getExplainString(new ExplainOptions(false, false));
551+
Assertions.assertTrue(plan1.contains("SORT INFO:\n `k1`\n `k2`"));
552+
Assertions.assertTrue(plan1.contains("SORT LIMIT:"));
553+
Assertions.assertTrue(plan1.contains("TOPN OPT"));
554+
}
519555

520556
// Push sort failed
521-
String sql2 = "explain select k1, k2, k3 from db1.tbl1 order by k1, k3, k2";
557+
String sql2 = "explain select k1, k2, k3 from db1.tbl3 order by k1, k3, k2";
522558
StmtExecutor stmtExecutor2 = new StmtExecutor(connectContext, sql2);
523559
stmtExecutor2.execute();
524560
Planner planner2 = stmtExecutor2.planner();
@@ -529,9 +565,10 @@ public void testPushSortToOlapScan() throws Exception {
529565

530566
@Test
531567
public void testEliminatingSortNode() throws Exception {
532-
// fail case 1
568+
// success case 1
533569
{
534-
String sql1 = "explain select k1 from db1.tbl1 where k1 = 1 order by k1, k2";
570+
String sql1 = "explain select k1 from db1.tbl1 where k1 = 1 order by k1, k2 limit "
571+
+ connectContext.getSessionVariable().topnOptLimitThreshold;
535572
StmtExecutor stmtExecutor1 = new StmtExecutor(connectContext, sql1);
536573
stmtExecutor1.execute();
537574
Planner planner1 = stmtExecutor1.planner();
@@ -542,7 +579,8 @@ public void testEliminatingSortNode() throws Exception {
542579

543580
// fail case 2
544581
{
545-
String sql1 = "explain select k1 from db1.tbl1 where k1 = 1 and k3 = 2 order by k1, k2";
582+
String sql1 = "explain select k1 from db1.tbl1 where k1 = 1 and k3 = 2 order by k1, k2 limit "
583+
+ connectContext.getSessionVariable().topnOptLimitThreshold;
546584
StmtExecutor stmtExecutor1 = new StmtExecutor(connectContext, sql1);
547585
stmtExecutor1.execute();
548586
Planner planner1 = stmtExecutor1.planner();
@@ -553,7 +591,8 @@ public void testEliminatingSortNode() throws Exception {
553591

554592
// fail case 3
555593
{
556-
String sql1 = "explain select k1 from db1.tbl1 where k1 = 1 and k2 != 2 order by k1, k2";
594+
String sql1 = "explain select k1 from db1.tbl1 where k1 = 1 and k2 != 2 order by k1, k2 limit "
595+
+ connectContext.getSessionVariable().topnOptLimitThreshold;
557596
StmtExecutor stmtExecutor1 = new StmtExecutor(connectContext, sql1);
558597
stmtExecutor1.execute();
559598
Planner planner1 = stmtExecutor1.planner();
@@ -564,7 +603,8 @@ public void testEliminatingSortNode() throws Exception {
564603

565604
// fail case 4
566605
{
567-
String sql1 = "explain select k1 from db1.tbl1 where k1 = 1 or k2 = 2 order by k1, k2";
606+
String sql1 = "explain select k1 from db1.tbl1 where k1 = 1 or k2 = 2 order by k1, k2 limit "
607+
+ connectContext.getSessionVariable().topnOptLimitThreshold;
568608
StmtExecutor stmtExecutor1 = new StmtExecutor(connectContext, sql1);
569609
stmtExecutor1.execute();
570610
Planner planner1 = stmtExecutor1.planner();
@@ -575,7 +615,8 @@ public void testEliminatingSortNode() throws Exception {
575615

576616
// fail case 5
577617
{
578-
String sql1 = "explain select k1 from db1.tbl1 where k1 = 1 and k2 = 2 or k3 = 3 order by k1, k2";
618+
String sql1 = "explain select k1 from db1.tbl1 where k1 = 1 and k2 = 2 or k3 = 3 order by k1, k2 limit "
619+
+ connectContext.getSessionVariable().topnOptLimitThreshold;
579620
StmtExecutor stmtExecutor1 = new StmtExecutor(connectContext, sql1);
580621
stmtExecutor1.execute();
581622
Planner planner1 = stmtExecutor1.planner();
@@ -587,7 +628,8 @@ public void testEliminatingSortNode() throws Exception {
587628
// fail case 6
588629
// TODO, support: in (select 1)
589630
{
590-
String sql1 = "explain select k1 from db1.tbl1 where k1 in (select 1) and k2 = 2 order by k1, k2";
631+
String sql1 = "explain select k1 from db1.tbl1 where k1 in (select 1) and k2 = 2 order by k1, k2 limit "
632+
+ connectContext.getSessionVariable().topnOptLimitThreshold;
591633
StmtExecutor stmtExecutor1 = new StmtExecutor(connectContext, sql1);
592634
stmtExecutor1.execute();
593635
Planner planner1 = stmtExecutor1.planner();
@@ -597,7 +639,8 @@ public void testEliminatingSortNode() throws Exception {
597639

598640
// fail case 7
599641
{
600-
String sql1 = "explain select k1 from db1.tbl1 where k1 not in (1) and k2 = 2 order by k1, k2";
642+
String sql1 = "explain select k1 from db1.tbl1 where k1 not in (1) and k2 = 2 order by k1, k2 limit "
643+
+ connectContext.getSessionVariable().topnOptLimitThreshold;
601644
StmtExecutor stmtExecutor1 = new StmtExecutor(connectContext, sql1);
602645
stmtExecutor1.execute();
603646
Planner planner1 = stmtExecutor1.planner();
@@ -607,7 +650,8 @@ public void testEliminatingSortNode() throws Exception {
607650

608651
// success case 1
609652
{
610-
String sql1 = "explain select k1 from db1.tbl1 where k1 = 1 and k2 = 2 order by k1, k2";
653+
String sql1 = "explain select k1 from db1.tbl1 where k1 = 1 and k2 = 2 order by k1, k2 limit "
654+
+ connectContext.getSessionVariable().topnOptLimitThreshold;
611655
StmtExecutor stmtExecutor1 = new StmtExecutor(connectContext, sql1);
612656
stmtExecutor1.execute();
613657
Planner planner1 = stmtExecutor1.planner();
@@ -618,7 +662,8 @@ public void testEliminatingSortNode() throws Exception {
618662

619663
// success case 2
620664
{
621-
String sql1 = "explain select k1 from db1.tbl1 where k3 = 3 and k2 = 2 and k1 = 1 order by k1, k2";
665+
String sql1 = "explain select k1 from db1.tbl1 where k3 = 3 and k2 = 2 and k1 = 1 order by k1, k2 limit "
666+
+ connectContext.getSessionVariable().topnOptLimitThreshold;
622667
StmtExecutor stmtExecutor1 = new StmtExecutor(connectContext, sql1);
623668
stmtExecutor1.execute();
624669
Planner planner1 = stmtExecutor1.planner();
@@ -629,7 +674,8 @@ public void testEliminatingSortNode() throws Exception {
629674

630675
// success case 3
631676
{
632-
String sql1 = "explain select k1 from db1.tbl1 where k1 in (1) and k2 in (2) and k2 !=2 order by k1, k2";
677+
String sql1 = "explain select k1 from db1.tbl1 where k1 in (1) and k2 in (2) and k2 !=2 order by k1, k2 limit "
678+
+ connectContext.getSessionVariable().topnOptLimitThreshold;
633679
StmtExecutor stmtExecutor1 = new StmtExecutor(connectContext, sql1);
634680
stmtExecutor1.execute();
635681
Planner planner1 = stmtExecutor1.planner();
@@ -640,7 +686,8 @@ public void testEliminatingSortNode() throws Exception {
640686

641687
// success case 4
642688
{
643-
String sql1 = "explain select k1 from db1.tbl1 where k1 in (concat('1','2')) and k2 = 2 order by k1, k2";
689+
String sql1 = "explain select k1 from db1.tbl1 where k1 in (concat('1','2')) and k2 = 2 order by k1, k2 limit "
690+
+ connectContext.getSessionVariable().topnOptLimitThreshold;
644691
StmtExecutor stmtExecutor1 = new StmtExecutor(connectContext, sql1);
645692
stmtExecutor1.execute();
646693
Planner planner1 = stmtExecutor1.planner();
@@ -652,7 +699,8 @@ public void testEliminatingSortNode() throws Exception {
652699
// success case 5
653700
{
654701
String sql1 = "explain select tbl1.k1 from db1.tbl1 join db1.tbl2 on tbl1.k1 = tbl2.k1"
655-
+ " where tbl1.k1 = 1 and tbl2.k1 = 2 and tbl1.k2 = 3 order by tbl1.k1, tbl2.k1";
702+
+ " where tbl1.k1 = 1 and tbl2.k1 = 2 and tbl1.k2 = 3 order by tbl1.k1, tbl2.k1 limit "
703+
+ connectContext.getSessionVariable().topnOptLimitThreshold;
656704
StmtExecutor stmtExecutor1 = new StmtExecutor(connectContext, sql1);
657705
stmtExecutor1.execute();
658706
Planner planner1 = stmtExecutor1.planner();

0 commit comments

Comments
 (0)