This is an automated email from the ASF dual-hosted git repository.
morningman pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris-website.git
The following commit(s) were added to refs/heads/master by this push:
new aec522b089c add documents for topn query opt (#1033)
aec522b089c is described below
commit aec522b089ceab4ef95f392424e0c2597a0e40dc
Author: Kang <kxiao.ti...@gmail.com>
AuthorDate: Sun Sep 1 23:29:49 2024 +0800
add documents for topn query opt (#1033)
---
docs/query/topn-query.md | 150 ++++++++++++++++++++
.../current/query/topn-query.md | 148 ++++++++++++++++++++
.../version-2.0/query/topn-query.md | 148 ++++++++++++++++++++
.../version-2.1/query/topn-query.md | 148 ++++++++++++++++++++
.../version-3.0/query/topn-query.md | 148 ++++++++++++++++++++
sidebars.json | 1 +
versioned_docs/version-2.0/query/topn-query.md | 151 +++++++++++++++++++++
versioned_docs/version-2.1/query/topn-query.md | 151 +++++++++++++++++++++
versioned_docs/version-3.0/query/topn-query.md | 151 +++++++++++++++++++++
versioned_sidebars/version-2.0-sidebars.json | 1 +
versioned_sidebars/version-2.1-sidebars.json | 1 +
versioned_sidebars/version-3.0-sidebars.json | 1 +
12 files changed, 1199 insertions(+)
diff --git a/docs/query/topn-query.md b/docs/query/topn-query.md
new file mode 100644
index 00000000000..b5ce77812e8
--- /dev/null
+++ b/docs/query/topn-query.md
@@ -0,0 +1,150 @@
+---
+{
+ "title": "TOPN Query Optimization",
+ "language": "en"
+}
+---
+
+<!--
+Licensed to the Apache Software Foundation (ASF) under one
+or more contributor license agreements. See the NOTICE file
+distributed with this work for additional information
+regarding copyright ownership. The ASF licenses this file
+to you under the Apache License, Version 2.0 (the
+"License"); you may not use this file except in compliance
+with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing,
+software distributed under the License is distributed on an
+"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+KIND, either express or implied. See the License for the
+specific language governing permissions and limitations
+under the License.
+-->
+
+
+TOPN queries refer to queries that involve ORDER BY LIMIT operations, which
are common in log retrieval and other detailed query scenarios. Doris
automatically optimizes this type of query.
+
+```sql
+SELECT * FROM tablex WHERE xxx ORDER BY c1,c2 ... LIMIT n
+```
+
+## Optimization Points
+
+1. During execution, dynamic range filters are built for the sorting columns
(e.g., c1 >= 1000), which automatically apply the preceding conditions when
reading data, leveraging zonemap indexes to filter out some rows or even entire
files.
+2. If the sorting fields c1, c2 are exactly the prefix of the table key,
further optimization is applied. When reading data, only the header or tail of
the data files is read, reducing the amount of data read to just the n rows
needed.
+3. SELECT * deferred materialization, during the data reading and sorting
process, only the sorting columns are read, not the other columns. After
obtaining the row numbers that meet the conditions, the entire data of those n
rows needed is read, significantly reducing the amount of data read and sorted.
+
+## Limitations
+
+1. It only applies to DUP and MOW tables, not to MOR and AGG tables.
+2. Due to the high memory consumption on very large `n`, it will not take
effect if n is greater than `topn_opt_limit_threshold`.
+
+## Configuration and Query Analysis
+
+The following two parameters are session variables that can be set for a
specific SQL or globally.
+
+1. `topn_opt_limit_threshold`: This session variable determines whether TOPN
optimization is applied. It defaults to 1024, and setting it to 0 disables the
optimization.
+
+2. `enable_two_phase_read_optimization`: This session variable determines
whether to enable this optimization. It defaults to true, and setting it to
false disables the optimization.
+
+### Checking if TOPN Query Optimization is Enabled
+
+To confirm if TOPN query optimization is enabled for a particular SQL, you can
use the `EXPLAIN` statement to get the query plan. An example is as follows:
+
+- `TOPN OPT` indicates that optimization point 1 is applied.
+- `VOlapScanNode` with `SORT LIMIT` indicates optimization point 2 is applied.
+- `OPT TWO PHASE` indicates optimization point 3 is applied.
+
+```sql
+ 1:VTOP-N(137)
+ | order by: @timestamp18 DESC
+ | TOPN OPT
+ | OPT TWO PHASE
+ | offset: 0
+ | limit: 10
+ | distribute expr lists: applicationName5
+ |
+ 0:VOlapScanNode(106)
+ TABLE: log_db.log_core_all_no_index(log_core_all_no_index),
PREAGGREGATION: ON
+ SORT INFO:
+ @timestamp18
+ SORT LIMIT: 10
+ TOPN OPT:1
+ PREDICATES: ZYCFC-TRACE-ID4 like '%flowId-1720055220933%'
+ partitions=1/8 (p20240704), tablets=250/250,
tabletList=1727094,1727096,1727098 ...
+ cardinality=345472780, avgRowSize=0.0, numNodes=1
+ pushAggOp=NONE
+```
+
+### Checking the Effectiveness of TOPN Query Optimization During Execution
+
+First, set `topn_opt_limit_threshold` to 0 to disable TOPN query optimization
and compare the execution time of the SQL with and without optimization enabled.
+
+After enabling TOPN query optimization, search for `RuntimePredicate` in the
query profile and focus on the following metrics:
+
+- `RowsZonemapRuntimePredicateFiltered`: The number of rows filtered out, the
higher the better.
+- `NumSegmentFiltered`: The number of data files filtered out, the higher the
better.
+- `BlockConditionsFilteredZonemapRuntimePredicateTime`: The time taken to
filter data, the lower the better.
+
+Before version 2.0.3, the `RuntimePredicate` metrics were not separated out,
and the `Zonemap` metrics can be used as a rough guide.
+
+```
+ SegmentIterator:
+ - BitmapIndexFilterTimer: 46.54us
+ - BlockConditionsFilteredBloomFilterTime: 10.352us
+ - BlockConditionsFilteredDictTime: 7.299us
+ - BlockConditionsFilteredTime: 202.23ms
+ - BlockConditionsFilteredZonemapRuntimePredicateTime: 0ns
+ - BlockConditionsFilteredZonemapTime: 402.917ms
+ - BlockInitSeekCount: 399
+ - BlockInitSeekTime: 11.309ms
+ - BlockInitTime: 215.59ms
+ - BlockLoadTime: 7s567ms
+ - BlocksLoad: 392.97K (392970)
+ - CachedPagesNum: 0
+ - CollectIteratorMergeTime: 0ns
+ - CollectIteratorNormalTime: 0ns
+ - CompressedBytesRead: 29.76 MB
+ - DecompressorTimer: 427.713ms
+ - ExprFilterEvalTime: 3s930ms
+ - FirstReadSeekCount: 392.921K (392921)
+ - FirstReadSeekTime: 528.287ms
+ - FirstReadTime: 1s134ms
+ - IOTimer: 51.286ms
+ - InvertedIndexFilterTime: 49.457us
+ - InvertedIndexQueryBitmapCopyTime: 0ns
+ - InvertedIndexQueryBitmapOpTime: 0ns
+ - InvertedIndexQueryCacheHit: 0
+ - InvertedIndexQueryCacheMiss: 0
+ - InvertedIndexQueryTime: 0ns
+ - InvertedIndexSearcherOpenTime: 0ns
+ - InvertedIndexSearcherSearchTime: 0ns
+ - LazyReadSeekCount: 0
+ - LazyReadSeekTime: 0ns
+ - LazyReadTime: 106.952us
+ - NumSegmentFiltered: 0
+ - NumSegmentTotal: 50
+ - OutputColumnTime: 61.987ms
+ - OutputIndexResultColumnTimer: 12.345ms
+ - RawRowsRead: 3.929151M (3929151)
+ - RowsBitmapIndexFiltered: 0
+ - RowsBloomFilterFiltered: 0
+ - RowsConditionsFiltered: 6.38976M (6389760)
+ - RowsDictFiltered: 0
+ - RowsInvertedIndexFiltered: 0
+ - RowsKeyRangeFiltered: 0
+ - RowsShortCircuitPredFiltered: 0
+ - RowsShortCircuitPredInput: 0
+ - RowsStatsFiltered: 6.38976M (6389760)
+ - RowsVectorPredFiltered: 0
+ - RowsVectorPredInput: 0
+ - RowsZonemapRuntimePredicateFiltered: 6.38976M (6389760)
+ - SecondReadTime: 0ns
+ - ShortPredEvalTime: 0ns
+ - TotalPagesNum: 2.301K (2301)
+ - UncompressedBytesRead: 137.99 MB
+ - VectorPredEvalTime: 0ns
+```
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/query/topn-query.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/query/topn-query.md
new file mode 100644
index 00000000000..13710cd2afe
--- /dev/null
+++ b/i18n/zh-CN/docusaurus-plugin-content-docs/current/query/topn-query.md
@@ -0,0 +1,148 @@
+---
+{
+ "title": "TOPN 查询优化",
+ "language": "zh-CN"
+}
+---
+
+<!--
+Licensed to the Apache Software Foundation (ASF) under one
+or more contributor license agreements. See the NOTICE file
+distributed with this work for additional information
+regarding copyright ownership. The ASF licenses this file
+to you under the Apache License, Version 2.0 (the
+"License"); you may not use this file except in compliance
+with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing,
+software distributed under the License is distributed on an
+"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+KIND, either express or implied. See the License for the
+specific language governing permissions and limitations
+under the License.
+-->
+
+
+TOPN 查询是指下面这种 ORDER BY LIMIT 查询，在日志检索等明细查询场景中很常见，Doris 会自动对这种类型的查询进行优化。
+
+```
+SELECT * FROM tablex WHERE xxx ORDER BY c1,c2 ... LIMIT n
+```
+
+## TOPN 查询优化的优化点
+
+1. 执行过程中动态对排序列构建范围过滤条件（比如 c1 >= 10000），读数据时自动带上前面的条件，利用 zonemap 索引过滤到一些数据甚至文件。
+2. 如果排序字段c1,c2 正好是table key的前缀，则更进一步优化，读数据的时候只用读数据文件的头部或者尾部n行。
+3. SELECT * 延迟物化，读数据和排序过程中只读排序列不读其它列，得到符合条件的行号后，再去读那n行需要的全部列数据，大幅减少读取和排序的列。
+
+
+## TOPN 查询优化的限制
+
+1. 只能用于 duplicate 表和 unique mow 表，因为 mor 表用这个优化可能有结果错误。
+2. 对于过大的n，优化内存消耗会很大，所以超过 topn_opt_limit_threshold session 变量的n 不会使用优化。
+
+
+## 配置参数和查询分析
+
+下面两个参数都是 session variable，可以针对某个 SQL 或者全局设置。
+1. topn_opt_limit_threshold，LIMIT n 小于这个值才会有优化，默认值1024，将它设置为 0 可以关闭 TOPN 查询优化。
+2. enable_two_phase_read_opt，是否开启优化3，默认为 true，可以调为 false 关闭这个优化。
+
+### 检查 TOPN 查询优化是否启用
+
+explain SQL 拿到 query plan 可以确认这个sql是否启用 TOPN 查询优化，以下面的为例：
+- TOPN OPT 代表有优化1
+- VOlapScanNode 下面有 SORT LIMIT 代表有优化2
+- OPT TWO PHRASE 代表有优化3
+
+```
+ 1:VTOP-N(137)
+ | order by: @timestamp18 DESC
+ | TOPN OPT
+ | OPT TWO PHASE
+ | offset: 0
+ | limit: 10
+ | distribute expr lists: applicationName5
+ |
+ 0:VOlapScanNode(106)
+ TABLE: log_db.log_core_all_no_index(log_core_all_no_index),
PREAGGREGATION: ON
+ SORT INFO:
+ @timestamp18
+ SORT LIMIT: 10
+ TOPN OPT:1
+ PREDICATES: ZYCFC-TRACE-ID4 like '%flowId-1720055220933%'
+ partitions=1/8 (p20240704), tablets=250/250,
tabletList=1727094,1727096,1727098 ...
+ cardinality=345472780, avgRowSize=0.0, numNodes=1
+ pushAggOp=NONE
+```
+
+### 检查 TOPN 查询优化执行时是否有效果
+
+首先，可以将 topn_opt_limit_threshold 设置为 0 关闭 TOPN 查询优化，对比开启和关闭优化的 SQL 执行时间。
+
+开启 TOPN查询优化后，在 query profile 中搜索 RuntimePredicate，关注下面几个指标：
+- RowsZonemapRuntimePredicateFiltered 这个代表过滤掉的行数，越大越好
+- NumSegmentFiltered 这个代表过滤掉的数据文件个数，越大越好
+- BlockConditionsFilteredZonemapRuntimePredicateTime 代表过滤数据的耗时，越小越好
+
+2.0.3 之前的版本RuntimePredicate的指标没有独立出来，可以通过Zonamap指标大致观察。
+
+```
+ SegmentIterator:
+ - BitmapIndexFilterTimer: 46.54us
+ - BlockConditionsFilteredBloomFilterTime: 10.352us
+ - BlockConditionsFilteredDictTime: 7.299us
+ - BlockConditionsFilteredTime: 202.23ms
+ - BlockConditionsFilteredZonemapRuntimePredicateTime: 0ns
+ - BlockConditionsFilteredZonemapTime: 402.917ms
+ - BlockInitSeekCount: 399
+ - BlockInitSeekTime: 11.309ms
+ - BlockInitTime: 215.59ms
+ - BlockLoadTime: 7s567ms
+ - BlocksLoad: 392.97K (392970)
+ - CachedPagesNum: 0
+ - CollectIteratorMergeTime: 0ns
+ - CollectIteratorNormalTime: 0ns
+ - CompressedBytesRead: 29.76 MB
+ - DecompressorTimer: 427.713ms
+ - ExprFilterEvalTime: 3s930ms
+ - FirstReadSeekCount: 392.921K (392921)
+ - FirstReadSeekTime: 528.287ms
+ - FirstReadTime: 1s134ms
+ - IOTimer: 51.286ms
+ - InvertedIndexFilterTime: 49.457us
+ - InvertedIndexQueryBitmapCopyTime: 0ns
+ - InvertedIndexQueryBitmapOpTime: 0ns
+ - InvertedIndexQueryCacheHit: 0
+ - InvertedIndexQueryCacheMiss: 0
+ - InvertedIndexQueryTime: 0ns
+ - InvertedIndexSearcherOpenTime: 0ns
+ - InvertedIndexSearcherSearchTime: 0ns
+ - LazyReadSeekCount: 0
+ - LazyReadSeekTime: 0ns
+ - LazyReadTime: 106.952us
+ - NumSegmentFiltered: 0
+ - NumSegmentTotal: 50
+ - OutputColumnTime: 61.987ms
+ - OutputIndexResultColumnTimer: 12.345ms
+ - RawRowsRead: 3.929151M (3929151)
+ - RowsBitmapIndexFiltered: 0
+ - RowsBloomFilterFiltered: 0
+ - RowsConditionsFiltered: 6.38976M (6389760)
+ - RowsDictFiltered: 0
+ - RowsInvertedIndexFiltered: 0
+ - RowsKeyRangeFiltered: 0
+ - RowsShortCircuitPredFiltered: 0
+ - RowsShortCircuitPredInput: 0
+ - RowsStatsFiltered: 6.38976M (6389760)
+ - RowsVectorPredFiltered: 0
+ - RowsVectorPredInput: 0
+ - RowsZonemapRuntimePredicateFiltered: 6.38976M (6389760)
+ - SecondReadTime: 0ns
+ - ShortPredEvalTime: 0ns
+ - TotalPagesNum: 2.301K (2301)
+ - UncompressedBytesRead: 137.99 MB
+ - VectorPredEvalTime: 0ns
+```
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.0/query/topn-query.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.0/query/topn-query.md
new file mode 100644
index 00000000000..13710cd2afe
--- /dev/null
+++ b/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.0/query/topn-query.md
@@ -0,0 +1,148 @@
+---
+{
+ "title": "TOPN 查询优化",
+ "language": "zh-CN"
+}
+---
+
+<!--
+Licensed to the Apache Software Foundation (ASF) under one
+or more contributor license agreements. See the NOTICE file
+distributed with this work for additional information
+regarding copyright ownership. The ASF licenses this file
+to you under the Apache License, Version 2.0 (the
+"License"); you may not use this file except in compliance
+with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing,
+software distributed under the License is distributed on an
+"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+KIND, either express or implied. See the License for the
+specific language governing permissions and limitations
+under the License.
+-->
+
+
+TOPN 查询是指下面这种 ORDER BY LIMIT 查询，在日志检索等明细查询场景中很常见，Doris 会自动对这种类型的查询进行优化。
+
+```
+SELECT * FROM tablex WHERE xxx ORDER BY c1,c2 ... LIMIT n
+```
+
+## TOPN 查询优化的优化点
+
+1. 执行过程中动态对排序列构建范围过滤条件（比如 c1 >= 10000），读数据时自动带上前面的条件，利用 zonemap 索引过滤到一些数据甚至文件。
+2. 如果排序字段c1,c2 正好是table key的前缀，则更进一步优化，读数据的时候只用读数据文件的头部或者尾部n行。
+3. SELECT * 延迟物化，读数据和排序过程中只读排序列不读其它列，得到符合条件的行号后，再去读那n行需要的全部列数据，大幅减少读取和排序的列。
+
+
+## TOPN 查询优化的限制
+
+1. 只能用于 duplicate 表和 unique mow 表，因为 mor 表用这个优化可能有结果错误。
+2. 对于过大的n，优化内存消耗会很大，所以超过 topn_opt_limit_threshold session 变量的n 不会使用优化。
+
+
+## 配置参数和查询分析
+
+下面两个参数都是 session variable，可以针对某个 SQL 或者全局设置。
+1. topn_opt_limit_threshold，LIMIT n 小于这个值才会有优化，默认值1024，将它设置为 0 可以关闭 TOPN 查询优化。
+2. enable_two_phase_read_opt，是否开启优化3，默认为 true，可以调为 false 关闭这个优化。
+
+### 检查 TOPN 查询优化是否启用
+
+explain SQL 拿到 query plan 可以确认这个sql是否启用 TOPN 查询优化，以下面的为例：
+- TOPN OPT 代表有优化1
+- VOlapScanNode 下面有 SORT LIMIT 代表有优化2
+- OPT TWO PHRASE 代表有优化3
+
+```
+ 1:VTOP-N(137)
+ | order by: @timestamp18 DESC
+ | TOPN OPT
+ | OPT TWO PHASE
+ | offset: 0
+ | limit: 10
+ | distribute expr lists: applicationName5
+ |
+ 0:VOlapScanNode(106)
+ TABLE: log_db.log_core_all_no_index(log_core_all_no_index),
PREAGGREGATION: ON
+ SORT INFO:
+ @timestamp18
+ SORT LIMIT: 10
+ TOPN OPT:1
+ PREDICATES: ZYCFC-TRACE-ID4 like '%flowId-1720055220933%'
+ partitions=1/8 (p20240704), tablets=250/250,
tabletList=1727094,1727096,1727098 ...
+ cardinality=345472780, avgRowSize=0.0, numNodes=1
+ pushAggOp=NONE
+```
+
+### 检查 TOPN 查询优化执行时是否有效果
+
+首先，可以将 topn_opt_limit_threshold 设置为 0 关闭 TOPN 查询优化，对比开启和关闭优化的 SQL 执行时间。
+
+开启 TOPN查询优化后，在 query profile 中搜索 RuntimePredicate，关注下面几个指标：
+- RowsZonemapRuntimePredicateFiltered 这个代表过滤掉的行数，越大越好
+- NumSegmentFiltered 这个代表过滤掉的数据文件个数，越大越好
+- BlockConditionsFilteredZonemapRuntimePredicateTime 代表过滤数据的耗时，越小越好
+
+2.0.3 之前的版本RuntimePredicate的指标没有独立出来，可以通过Zonamap指标大致观察。
+
+```
+ SegmentIterator:
+ - BitmapIndexFilterTimer: 46.54us
+ - BlockConditionsFilteredBloomFilterTime: 10.352us
+ - BlockConditionsFilteredDictTime: 7.299us
+ - BlockConditionsFilteredTime: 202.23ms
+ - BlockConditionsFilteredZonemapRuntimePredicateTime: 0ns
+ - BlockConditionsFilteredZonemapTime: 402.917ms
+ - BlockInitSeekCount: 399
+ - BlockInitSeekTime: 11.309ms
+ - BlockInitTime: 215.59ms
+ - BlockLoadTime: 7s567ms
+ - BlocksLoad: 392.97K (392970)
+ - CachedPagesNum: 0
+ - CollectIteratorMergeTime: 0ns
+ - CollectIteratorNormalTime: 0ns
+ - CompressedBytesRead: 29.76 MB
+ - DecompressorTimer: 427.713ms
+ - ExprFilterEvalTime: 3s930ms
+ - FirstReadSeekCount: 392.921K (392921)
+ - FirstReadSeekTime: 528.287ms
+ - FirstReadTime: 1s134ms
+ - IOTimer: 51.286ms
+ - InvertedIndexFilterTime: 49.457us
+ - InvertedIndexQueryBitmapCopyTime: 0ns
+ - InvertedIndexQueryBitmapOpTime: 0ns
+ - InvertedIndexQueryCacheHit: 0
+ - InvertedIndexQueryCacheMiss: 0
+ - InvertedIndexQueryTime: 0ns
+ - InvertedIndexSearcherOpenTime: 0ns
+ - InvertedIndexSearcherSearchTime: 0ns
+ - LazyReadSeekCount: 0
+ - LazyReadSeekTime: 0ns
+ - LazyReadTime: 106.952us
+ - NumSegmentFiltered: 0
+ - NumSegmentTotal: 50
+ - OutputColumnTime: 61.987ms
+ - OutputIndexResultColumnTimer: 12.345ms
+ - RawRowsRead: 3.929151M (3929151)
+ - RowsBitmapIndexFiltered: 0
+ - RowsBloomFilterFiltered: 0
+ - RowsConditionsFiltered: 6.38976M (6389760)
+ - RowsDictFiltered: 0
+ - RowsInvertedIndexFiltered: 0
+ - RowsKeyRangeFiltered: 0
+ - RowsShortCircuitPredFiltered: 0
+ - RowsShortCircuitPredInput: 0
+ - RowsStatsFiltered: 6.38976M (6389760)
+ - RowsVectorPredFiltered: 0
+ - RowsVectorPredInput: 0
+ - RowsZonemapRuntimePredicateFiltered: 6.38976M (6389760)
+ - SecondReadTime: 0ns
+ - ShortPredEvalTime: 0ns
+ - TotalPagesNum: 2.301K (2301)
+ - UncompressedBytesRead: 137.99 MB
+ - VectorPredEvalTime: 0ns
+```
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/query/topn-query.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/query/topn-query.md
new file mode 100644
index 00000000000..13710cd2afe
--- /dev/null
+++ b/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/query/topn-query.md
@@ -0,0 +1,148 @@
+---
+{
+ "title": "TOPN 查询优化",
+ "language": "zh-CN"
+}
+---
+
+<!--
+Licensed to the Apache Software Foundation (ASF) under one
+or more contributor license agreements. See the NOTICE file
+distributed with this work for additional information
+regarding copyright ownership. The ASF licenses this file
+to you under the Apache License, Version 2.0 (the
+"License"); you may not use this file except in compliance
+with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing,
+software distributed under the License is distributed on an
+"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+KIND, either express or implied. See the License for the
+specific language governing permissions and limitations
+under the License.
+-->
+
+
+TOPN 查询是指下面这种 ORDER BY LIMIT 查询，在日志检索等明细查询场景中很常见，Doris 会自动对这种类型的查询进行优化。
+
+```
+SELECT * FROM tablex WHERE xxx ORDER BY c1,c2 ... LIMIT n
+```
+
+## TOPN 查询优化的优化点
+
+1. 执行过程中动态对排序列构建范围过滤条件（比如 c1 >= 10000），读数据时自动带上前面的条件，利用 zonemap 索引过滤到一些数据甚至文件。
+2. 如果排序字段c1,c2 正好是table key的前缀，则更进一步优化，读数据的时候只用读数据文件的头部或者尾部n行。
+3. SELECT * 延迟物化，读数据和排序过程中只读排序列不读其它列，得到符合条件的行号后，再去读那n行需要的全部列数据，大幅减少读取和排序的列。
+
+
+## TOPN 查询优化的限制
+
+1. 只能用于 duplicate 表和 unique mow 表，因为 mor 表用这个优化可能有结果错误。
+2. 对于过大的n，优化内存消耗会很大，所以超过 topn_opt_limit_threshold session 变量的n 不会使用优化。
+
+
+## 配置参数和查询分析
+
+下面两个参数都是 session variable，可以针对某个 SQL 或者全局设置。
+1. topn_opt_limit_threshold，LIMIT n 小于这个值才会有优化，默认值1024，将它设置为 0 可以关闭 TOPN 查询优化。
+2. enable_two_phase_read_opt，是否开启优化3，默认为 true，可以调为 false 关闭这个优化。
+
+### 检查 TOPN 查询优化是否启用
+
+explain SQL 拿到 query plan 可以确认这个sql是否启用 TOPN 查询优化，以下面的为例：
+- TOPN OPT 代表有优化1
+- VOlapScanNode 下面有 SORT LIMIT 代表有优化2
+- OPT TWO PHRASE 代表有优化3
+
+```
+ 1:VTOP-N(137)
+ | order by: @timestamp18 DESC
+ | TOPN OPT
+ | OPT TWO PHASE
+ | offset: 0
+ | limit: 10
+ | distribute expr lists: applicationName5
+ |
+ 0:VOlapScanNode(106)
+ TABLE: log_db.log_core_all_no_index(log_core_all_no_index),
PREAGGREGATION: ON
+ SORT INFO:
+ @timestamp18
+ SORT LIMIT: 10
+ TOPN OPT:1
+ PREDICATES: ZYCFC-TRACE-ID4 like '%flowId-1720055220933%'
+ partitions=1/8 (p20240704), tablets=250/250,
tabletList=1727094,1727096,1727098 ...
+ cardinality=345472780, avgRowSize=0.0, numNodes=1
+ pushAggOp=NONE
+```
+
+### 检查 TOPN 查询优化执行时是否有效果
+
+首先，可以将 topn_opt_limit_threshold 设置为 0 关闭 TOPN 查询优化，对比开启和关闭优化的 SQL 执行时间。
+
+开启 TOPN查询优化后，在 query profile 中搜索 RuntimePredicate，关注下面几个指标：
+- RowsZonemapRuntimePredicateFiltered 这个代表过滤掉的行数，越大越好
+- NumSegmentFiltered 这个代表过滤掉的数据文件个数，越大越好
+- BlockConditionsFilteredZonemapRuntimePredicateTime 代表过滤数据的耗时，越小越好
+
+2.0.3 之前的版本RuntimePredicate的指标没有独立出来，可以通过Zonamap指标大致观察。
+
+```
+ SegmentIterator:
+ - BitmapIndexFilterTimer: 46.54us
+ - BlockConditionsFilteredBloomFilterTime: 10.352us
+ - BlockConditionsFilteredDictTime: 7.299us
+ - BlockConditionsFilteredTime: 202.23ms
+ - BlockConditionsFilteredZonemapRuntimePredicateTime: 0ns
+ - BlockConditionsFilteredZonemapTime: 402.917ms
+ - BlockInitSeekCount: 399
+ - BlockInitSeekTime: 11.309ms
+ - BlockInitTime: 215.59ms
+ - BlockLoadTime: 7s567ms
+ - BlocksLoad: 392.97K (392970)
+ - CachedPagesNum: 0
+ - CollectIteratorMergeTime: 0ns
+ - CollectIteratorNormalTime: 0ns
+ - CompressedBytesRead: 29.76 MB
+ - DecompressorTimer: 427.713ms
+ - ExprFilterEvalTime: 3s930ms
+ - FirstReadSeekCount: 392.921K (392921)
+ - FirstReadSeekTime: 528.287ms
+ - FirstReadTime: 1s134ms
+ - IOTimer: 51.286ms
+ - InvertedIndexFilterTime: 49.457us
+ - InvertedIndexQueryBitmapCopyTime: 0ns
+ - InvertedIndexQueryBitmapOpTime: 0ns
+ - InvertedIndexQueryCacheHit: 0
+ - InvertedIndexQueryCacheMiss: 0
+ - InvertedIndexQueryTime: 0ns
+ - InvertedIndexSearcherOpenTime: 0ns
+ - InvertedIndexSearcherSearchTime: 0ns
+ - LazyReadSeekCount: 0
+ - LazyReadSeekTime: 0ns
+ - LazyReadTime: 106.952us
+ - NumSegmentFiltered: 0
+ - NumSegmentTotal: 50
+ - OutputColumnTime: 61.987ms
+ - OutputIndexResultColumnTimer: 12.345ms
+ - RawRowsRead: 3.929151M (3929151)
+ - RowsBitmapIndexFiltered: 0
+ - RowsBloomFilterFiltered: 0
+ - RowsConditionsFiltered: 6.38976M (6389760)
+ - RowsDictFiltered: 0
+ - RowsInvertedIndexFiltered: 0
+ - RowsKeyRangeFiltered: 0
+ - RowsShortCircuitPredFiltered: 0
+ - RowsShortCircuitPredInput: 0
+ - RowsStatsFiltered: 6.38976M (6389760)
+ - RowsVectorPredFiltered: 0
+ - RowsVectorPredInput: 0
+ - RowsZonemapRuntimePredicateFiltered: 6.38976M (6389760)
+ - SecondReadTime: 0ns
+ - ShortPredEvalTime: 0ns
+ - TotalPagesNum: 2.301K (2301)
+ - UncompressedBytesRead: 137.99 MB
+ - VectorPredEvalTime: 0ns
+```
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-3.0/query/topn-query.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-3.0/query/topn-query.md
new file mode 100644
index 00000000000..13710cd2afe
--- /dev/null
+++ b/i18n/zh-CN/docusaurus-plugin-content-docs/version-3.0/query/topn-query.md
@@ -0,0 +1,148 @@
+---
+{
+ "title": "TOPN 查询优化",
+ "language": "zh-CN"
+}
+---
+
+<!--
+Licensed to the Apache Software Foundation (ASF) under one
+or more contributor license agreements. See the NOTICE file
+distributed with this work for additional information
+regarding copyright ownership. The ASF licenses this file
+to you under the Apache License, Version 2.0 (the
+"License"); you may not use this file except in compliance
+with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing,
+software distributed under the License is distributed on an
+"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+KIND, either express or implied. See the License for the
+specific language governing permissions and limitations
+under the License.
+-->
+
+
+TOPN 查询是指下面这种 ORDER BY LIMIT 查询，在日志检索等明细查询场景中很常见，Doris 会自动对这种类型的查询进行优化。
+
+```
+SELECT * FROM tablex WHERE xxx ORDER BY c1,c2 ... LIMIT n
+```
+
+## TOPN 查询优化的优化点
+
+1. 执行过程中动态对排序列构建范围过滤条件（比如 c1 >= 10000），读数据时自动带上前面的条件，利用 zonemap 索引过滤到一些数据甚至文件。
+2. 如果排序字段c1,c2 正好是table key的前缀，则更进一步优化，读数据的时候只用读数据文件的头部或者尾部n行。
+3. SELECT * 延迟物化，读数据和排序过程中只读排序列不读其它列，得到符合条件的行号后，再去读那n行需要的全部列数据，大幅减少读取和排序的列。
+
+
+## TOPN 查询优化的限制
+
+1. 只能用于 duplicate 表和 unique mow 表，因为 mor 表用这个优化可能有结果错误。
+2. 对于过大的n，优化内存消耗会很大，所以超过 topn_opt_limit_threshold session 变量的n 不会使用优化。
+
+
+## 配置参数和查询分析
+
+下面两个参数都是 session variable，可以针对某个 SQL 或者全局设置。
+1. topn_opt_limit_threshold，LIMIT n 小于这个值才会有优化，默认值1024，将它设置为 0 可以关闭 TOPN 查询优化。
+2. enable_two_phase_read_opt，是否开启优化3，默认为 true，可以调为 false 关闭这个优化。
+
+### 检查 TOPN 查询优化是否启用
+
+explain SQL 拿到 query plan 可以确认这个sql是否启用 TOPN 查询优化，以下面的为例：
+- TOPN OPT 代表有优化1
+- VOlapScanNode 下面有 SORT LIMIT 代表有优化2
+- OPT TWO PHRASE 代表有优化3
+
+```
+ 1:VTOP-N(137)
+ | order by: @timestamp18 DESC
+ | TOPN OPT
+ | OPT TWO PHASE
+ | offset: 0
+ | limit: 10
+ | distribute expr lists: applicationName5
+ |
+ 0:VOlapScanNode(106)
+ TABLE: log_db.log_core_all_no_index(log_core_all_no_index),
PREAGGREGATION: ON
+ SORT INFO:
+ @timestamp18
+ SORT LIMIT: 10
+ TOPN OPT:1
+ PREDICATES: ZYCFC-TRACE-ID4 like '%flowId-1720055220933%'
+ partitions=1/8 (p20240704), tablets=250/250,
tabletList=1727094,1727096,1727098 ...
+ cardinality=345472780, avgRowSize=0.0, numNodes=1
+ pushAggOp=NONE
+```
+
+### 检查 TOPN 查询优化执行时是否有效果
+
+首先，可以将 topn_opt_limit_threshold 设置为 0 关闭 TOPN 查询优化，对比开启和关闭优化的 SQL 执行时间。
+
+开启 TOPN查询优化后，在 query profile 中搜索 RuntimePredicate，关注下面几个指标：
+- RowsZonemapRuntimePredicateFiltered 这个代表过滤掉的行数，越大越好
+- NumSegmentFiltered 这个代表过滤掉的数据文件个数，越大越好
+- BlockConditionsFilteredZonemapRuntimePredicateTime 代表过滤数据的耗时，越小越好
+
+2.0.3 之前的版本RuntimePredicate的指标没有独立出来，可以通过Zonamap指标大致观察。
+
+```
+ SegmentIterator:
+ - BitmapIndexFilterTimer: 46.54us
+ - BlockConditionsFilteredBloomFilterTime: 10.352us
+ - BlockConditionsFilteredDictTime: 7.299us
+ - BlockConditionsFilteredTime: 202.23ms
+ - BlockConditionsFilteredZonemapRuntimePredicateTime: 0ns
+ - BlockConditionsFilteredZonemapTime: 402.917ms
+ - BlockInitSeekCount: 399
+ - BlockInitSeekTime: 11.309ms
+ - BlockInitTime: 215.59ms
+ - BlockLoadTime: 7s567ms
+ - BlocksLoad: 392.97K (392970)
+ - CachedPagesNum: 0
+ - CollectIteratorMergeTime: 0ns
+ - CollectIteratorNormalTime: 0ns
+ - CompressedBytesRead: 29.76 MB
+ - DecompressorTimer: 427.713ms
+ - ExprFilterEvalTime: 3s930ms
+ - FirstReadSeekCount: 392.921K (392921)
+ - FirstReadSeekTime: 528.287ms
+ - FirstReadTime: 1s134ms
+ - IOTimer: 51.286ms
+ - InvertedIndexFilterTime: 49.457us
+ - InvertedIndexQueryBitmapCopyTime: 0ns
+ - InvertedIndexQueryBitmapOpTime: 0ns
+ - InvertedIndexQueryCacheHit: 0
+ - InvertedIndexQueryCacheMiss: 0
+ - InvertedIndexQueryTime: 0ns
+ - InvertedIndexSearcherOpenTime: 0ns
+ - InvertedIndexSearcherSearchTime: 0ns
+ - LazyReadSeekCount: 0
+ - LazyReadSeekTime: 0ns
+ - LazyReadTime: 106.952us
+ - NumSegmentFiltered: 0
+ - NumSegmentTotal: 50
+ - OutputColumnTime: 61.987ms
+ - OutputIndexResultColumnTimer: 12.345ms
+ - RawRowsRead: 3.929151M (3929151)
+ - RowsBitmapIndexFiltered: 0
+ - RowsBloomFilterFiltered: 0
+ - RowsConditionsFiltered: 6.38976M (6389760)
+ - RowsDictFiltered: 0
+ - RowsInvertedIndexFiltered: 0
+ - RowsKeyRangeFiltered: 0
+ - RowsShortCircuitPredFiltered: 0
+ - RowsShortCircuitPredInput: 0
+ - RowsStatsFiltered: 6.38976M (6389760)
+ - RowsVectorPredFiltered: 0
+ - RowsVectorPredInput: 0
+ - RowsZonemapRuntimePredicateFiltered: 6.38976M (6389760)
+ - SecondReadTime: 0ns
+ - ShortPredEvalTime: 0ns
+ - TotalPagesNum: 2.301K (2301)
+ - UncompressedBytesRead: 137.99 MB
+ - VectorPredEvalTime: 0ns
+```
diff --git a/sidebars.json b/sidebars.json
index 0732b538234..9ac06de4256 100644
--- a/sidebars.json
+++ b/sidebars.json
@@ -274,6 +274,7 @@
]
},
"query/high-concurrent-point-query",
+ "query/topn-query",
{
"type": "category",
"label": "Analyzing Query",
diff --git a/versioned_docs/version-2.0/query/topn-query.md
b/versioned_docs/version-2.0/query/topn-query.md
new file mode 100644
index 00000000000..cac128f97e3
--- /dev/null
+++ b/versioned_docs/version-2.0/query/topn-query.md
@@ -0,0 +1,151 @@
+---
+{
+ "title": "TOPN Query Optimization",
+ "language": "en"
+}
+---
+
+<!--
+Licensed to the Apache Software Foundation (ASF) under one
+or more contributor license agreements. See the NOTICE file
+distributed with this work for additional information
+regarding copyright ownership. The ASF licenses this file
+to you under the Apache License, Version 2.0 (the
+"License"); you may not use this file except in compliance
+with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing,
+software distributed under the License is distributed on an
+"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+KIND, either express or implied. See the License for the
+specific language governing permissions and limitations
+under the License.
+-->
+
+
+
+TOPN queries refer to queries that involve ORDER BY LIMIT operations, which
are common in log retrieval and other detailed query scenarios. Doris
automatically optimizes this type of query.
+
+```sql
+SELECT * FROM tablex WHERE xxx ORDER BY c1,c2 ... LIMIT n
+```
+
+## Optimization Points
+
+1. During execution, dynamic range filters are built for the sorting columns
(e.g., c1 >= 1000), which automatically apply the preceding conditions when
reading data, leveraging zonemap indexes to filter out some rows or even entire
files.
+2. If the sorting fields c1, c2 are exactly the prefix of the table key,
further optimization is applied. When reading data, only the header or tail of
the data files is read, reducing the amount of data read to just the n rows
needed.
+3. SELECT * deferred materialization, during the data reading and sorting
process, only the sorting columns are read, not the other columns. After
obtaining the row numbers that meet the conditions, the entire data of those n
rows needed is read, significantly reducing the amount of data read and sorted.
+
+## Limitations
+
+1. It only applies to DUP and MOW tables, not to MOR and AGG tables.
+2. Due to the high memory consumption on very large `n`, it will not take
effect if n is greater than `topn_opt_limit_threshold`.
+
+## Configuration and Query Analysis
+
+The following two parameters are session variables that can be set for a
specific SQL or globally.
+
+1. `topn_opt_limit_threshold`: This session variable determines whether TOPN
optimization is applied. It defaults to 1024, and setting it to 0 disables the
optimization.
+
+2. `enable_two_phase_read_optimization`: This session variable determines
whether to enable this optimization. It defaults to true, and setting it to
false disables the optimization.
+
+### Checking if TOPN Query Optimization is Enabled
+
+To confirm if TOPN query optimization is enabled for a particular SQL, you can
use the `EXPLAIN` statement to get the query plan. An example is as follows:
+
+- `TOPN OPT` indicates that optimization point 1 is applied.
+- `VOlapScanNode` with `SORT LIMIT` indicates optimization point 2 is applied.
+- `OPT TWO PHASE` indicates optimization point 3 is applied.
+
+```sql
+ 1:VTOP-N(137)
+ | order by: @timestamp18 DESC
+ | TOPN OPT
+ | OPT TWO PHASE
+ | offset: 0
+ | limit: 10
+ | distribute expr lists: applicationName5
+ |
+ 0:VOlapScanNode(106)
+ TABLE: log_db.log_core_all_no_index(log_core_all_no_index),
PREAGGREGATION: ON
+ SORT INFO:
+ @timestamp18
+ SORT LIMIT: 10
+ TOPN OPT:1
+ PREDICATES: ZYCFC-TRACE-ID4 like '%flowId-1720055220933%'
+ partitions=1/8 (p20240704), tablets=250/250,
tabletList=1727094,1727096,1727098 ...
+ cardinality=345472780, avgRowSize=0.0, numNodes=1
+ pushAggOp=NONE
+```
+
+### Checking the Effectiveness of TOPN Query Optimization During Execution
+
+First, set `topn_opt_limit_threshold` to 0 to disable TOPN query optimization
and compare the execution time of the SQL with and without optimization enabled.
+
+After enabling TOPN query optimization, search for `RuntimePredicate` in the
query profile and focus on the following metrics:
+
+- `RowsZonemapRuntimePredicateFiltered`: The number of rows filtered out, the
higher the better.
+- `NumSegmentFiltered`: The number of data files filtered out, the higher the
better.
+- `BlockConditionsFilteredZonemapRuntimePredicateTime`: The time taken to
filter data, the lower the better.
+
+Before version 2.0.3, the `RuntimePredicate` metrics were not separated out,
and the `Zonemap` metrics can be used as a rough guide.
+
+```
+ SegmentIterator:
+ - BitmapIndexFilterTimer: 46.54us
+ - BlockConditionsFilteredBloomFilterTime: 10.352us
+ - BlockConditionsFilteredDictTime: 7.299us
+ - BlockConditionsFilteredTime: 202.23ms
+ - BlockConditionsFilteredZonemapRuntimePredicateTime: 0ns
+ - BlockConditionsFilteredZonemapTime: 402.917ms
+ - BlockInitSeekCount: 399
+ - BlockInitSeekTime: 11.309ms
+ - BlockInitTime: 215.59ms
+ - BlockLoadTime: 7s567ms
+ - BlocksLoad: 392.97K (392970)
+ - CachedPagesNum: 0
+ - CollectIteratorMergeTime: 0ns
+ - CollectIteratorNormalTime: 0ns
+ - CompressedBytesRead: 29.76 MB
+ - DecompressorTimer: 427.713ms
+ - ExprFilterEvalTime: 3s930ms
+ - FirstReadSeekCount: 392.921K (392921)
+ - FirstReadSeekTime: 528.287ms
+ - FirstReadTime: 1s134ms
+ - IOTimer: 51.286ms
+ - InvertedIndexFilterTime: 49.457us
+ - InvertedIndexQueryBitmapCopyTime: 0ns
+ - InvertedIndexQueryBitmapOpTime: 0ns
+ - InvertedIndexQueryCacheHit: 0
+ - InvertedIndexQueryCacheMiss: 0
+ - InvertedIndexQueryTime: 0ns
+ - InvertedIndexSearcherOpenTime: 0ns
+ - InvertedIndexSearcherSearchTime: 0ns
+ - LazyReadSeekCount: 0
+ - LazyReadSeekTime: 0ns
+ - LazyReadTime: 106.952us
+ - NumSegmentFiltered: 0
+ - NumSegmentTotal: 50
+ - OutputColumnTime: 61.987ms
+ - OutputIndexResultColumnTimer: 12.345ms
+ - RawRowsRead: 3.929151M (3929151)
+ - RowsBitmapIndexFiltered: 0
+ - RowsBloomFilterFiltered: 0
+ - RowsConditionsFiltered: 6.38976M (6389760)
+ - RowsDictFiltered: 0
+ - RowsInvertedIndexFiltered: 0
+ - RowsKeyRangeFiltered: 0
+ - RowsShortCircuitPredFiltered: 0
+ - RowsShortCircuitPredInput: 0
+ - RowsStatsFiltered: 6.38976M (6389760)
+ - RowsVectorPredFiltered: 0
+ - RowsVectorPredInput: 0
+ - RowsZonemapRuntimePredicateFiltered: 6.38976M (6389760)
+ - SecondReadTime: 0ns
+ - ShortPredEvalTime: 0ns
+ - TotalPagesNum: 2.301K (2301)
+ - UncompressedBytesRead: 137.99 MB
+ - VectorPredEvalTime: 0ns
+```
diff --git a/versioned_docs/version-2.1/query/topn-query.md
b/versioned_docs/version-2.1/query/topn-query.md
new file mode 100644
index 00000000000..cac128f97e3
--- /dev/null
+++ b/versioned_docs/version-2.1/query/topn-query.md
@@ -0,0 +1,151 @@
+---
+{
+ "title": "TOPN Query Optimization",
+ "language": "en"
+}
+---
+
+<!--
+Licensed to the Apache Software Foundation (ASF) under one
+or more contributor license agreements. See the NOTICE file
+distributed with this work for additional information
+regarding copyright ownership. The ASF licenses this file
+to you under the Apache License, Version 2.0 (the
+"License"); you may not use this file except in compliance
+with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing,
+software distributed under the License is distributed on an
+"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+KIND, either express or implied. See the License for the
+specific language governing permissions and limitations
+under the License.
+-->
+
+
+
+TOPN queries refer to queries that involve ORDER BY LIMIT operations, which
are common in log retrieval and other detailed query scenarios. Doris
automatically optimizes this type of query.
+
+```sql
+SELECT * FROM tablex WHERE xxx ORDER BY c1,c2 ... LIMIT n
+```
+
+## Optimization Points
+
+1. During execution, dynamic range filters are built for the sorting columns
(e.g., c1 >= 1000), which automatically apply the preceding conditions when
reading data, leveraging zonemap indexes to filter out some rows or even entire
files.
+2. If the sorting fields c1, c2 are exactly the prefix of the table key,
further optimization is applied. When reading data, only the header or tail of
the data files is read, reducing the amount of data read to just the n rows
needed.
+3. SELECT * deferred materialization, during the data reading and sorting
process, only the sorting columns are read, not the other columns. After
obtaining the row numbers that meet the conditions, the entire data of those n
rows needed is read, significantly reducing the amount of data read and sorted.
+
+## Limitations
+
+1. It only applies to DUP and MOW tables, not to MOR and AGG tables.
+2. Due to the high memory consumption on very large `n`, it will not take
effect if n is greater than `topn_opt_limit_threshold`.
+
+## Configuration and Query Analysis
+
+The following two parameters are session variables that can be set for a
specific SQL or globally.
+
+1. `topn_opt_limit_threshold`: This session variable determines whether TOPN
optimization is applied. It defaults to 1024, and setting it to 0 disables the
optimization.
+
+2. `enable_two_phase_read_optimization`: This session variable determines
whether to enable this optimization. It defaults to true, and setting it to
false disables the optimization.
+
+### Checking if TOPN Query Optimization is Enabled
+
+To confirm if TOPN query optimization is enabled for a particular SQL, you can
use the `EXPLAIN` statement to get the query plan. An example is as follows:
+
+- `TOPN OPT` indicates that optimization point 1 is applied.
+- `VOlapScanNode` with `SORT LIMIT` indicates optimization point 2 is applied.
+- `OPT TWO PHASE` indicates optimization point 3 is applied.
+
+```sql
+ 1:VTOP-N(137)
+ | order by: @timestamp18 DESC
+ | TOPN OPT
+ | OPT TWO PHASE
+ | offset: 0
+ | limit: 10
+ | distribute expr lists: applicationName5
+ |
+ 0:VOlapScanNode(106)
+ TABLE: log_db.log_core_all_no_index(log_core_all_no_index),
PREAGGREGATION: ON
+ SORT INFO:
+ @timestamp18
+ SORT LIMIT: 10
+ TOPN OPT:1
+ PREDICATES: ZYCFC-TRACE-ID4 like '%flowId-1720055220933%'
+ partitions=1/8 (p20240704), tablets=250/250,
tabletList=1727094,1727096,1727098 ...
+ cardinality=345472780, avgRowSize=0.0, numNodes=1
+ pushAggOp=NONE
+```
+
+### Checking the Effectiveness of TOPN Query Optimization During Execution
+
+First, set `topn_opt_limit_threshold` to 0 to disable TOPN query optimization
and compare the execution time of the SQL with and without optimization enabled.
+
+After enabling TOPN query optimization, search for `RuntimePredicate` in the
query profile and focus on the following metrics:
+
+- `RowsZonemapRuntimePredicateFiltered`: The number of rows filtered out, the
higher the better.
+- `NumSegmentFiltered`: The number of data files filtered out, the higher the
better.
+- `BlockConditionsFilteredZonemapRuntimePredicateTime`: The time taken to
filter data, the lower the better.
+
+Before version 2.0.3, the `RuntimePredicate` metrics were not separated out,
and the `Zonemap` metrics can be used as a rough guide.
+
+```
+ SegmentIterator:
+ - BitmapIndexFilterTimer: 46.54us
+ - BlockConditionsFilteredBloomFilterTime: 10.352us
+ - BlockConditionsFilteredDictTime: 7.299us
+ - BlockConditionsFilteredTime: 202.23ms
+ - BlockConditionsFilteredZonemapRuntimePredicateTime: 0ns
+ - BlockConditionsFilteredZonemapTime: 402.917ms
+ - BlockInitSeekCount: 399
+ - BlockInitSeekTime: 11.309ms
+ - BlockInitTime: 215.59ms
+ - BlockLoadTime: 7s567ms
+ - BlocksLoad: 392.97K (392970)
+ - CachedPagesNum: 0
+ - CollectIteratorMergeTime: 0ns
+ - CollectIteratorNormalTime: 0ns
+ - CompressedBytesRead: 29.76 MB
+ - DecompressorTimer: 427.713ms
+ - ExprFilterEvalTime: 3s930ms
+ - FirstReadSeekCount: 392.921K (392921)
+ - FirstReadSeekTime: 528.287ms
+ - FirstReadTime: 1s134ms
+ - IOTimer: 51.286ms
+ - InvertedIndexFilterTime: 49.457us
+ - InvertedIndexQueryBitmapCopyTime: 0ns
+ - InvertedIndexQueryBitmapOpTime: 0ns
+ - InvertedIndexQueryCacheHit: 0
+ - InvertedIndexQueryCacheMiss: 0
+ - InvertedIndexQueryTime: 0ns
+ - InvertedIndexSearcherOpenTime: 0ns
+ - InvertedIndexSearcherSearchTime: 0ns
+ - LazyReadSeekCount: 0
+ - LazyReadSeekTime: 0ns
+ - LazyReadTime: 106.952us
+ - NumSegmentFiltered: 0
+ - NumSegmentTotal: 50
+ - OutputColumnTime: 61.987ms
+ - OutputIndexResultColumnTimer: 12.345ms
+ - RawRowsRead: 3.929151M (3929151)
+ - RowsBitmapIndexFiltered: 0
+ - RowsBloomFilterFiltered: 0
+ - RowsConditionsFiltered: 6.38976M (6389760)
+ - RowsDictFiltered: 0
+ - RowsInvertedIndexFiltered: 0
+ - RowsKeyRangeFiltered: 0
+ - RowsShortCircuitPredFiltered: 0
+ - RowsShortCircuitPredInput: 0
+ - RowsStatsFiltered: 6.38976M (6389760)
+ - RowsVectorPredFiltered: 0
+ - RowsVectorPredInput: 0
+ - RowsZonemapRuntimePredicateFiltered: 6.38976M (6389760)
+ - SecondReadTime: 0ns
+ - ShortPredEvalTime: 0ns
+ - TotalPagesNum: 2.301K (2301)
+ - UncompressedBytesRead: 137.99 MB
+ - VectorPredEvalTime: 0ns
+```
diff --git a/versioned_docs/version-3.0/query/topn-query.md
b/versioned_docs/version-3.0/query/topn-query.md
new file mode 100644
index 00000000000..cac128f97e3
--- /dev/null
+++ b/versioned_docs/version-3.0/query/topn-query.md
@@ -0,0 +1,151 @@
+---
+{
+ "title": "TOPN Query Optimization",
+ "language": "en"
+}
+---
+
+<!--
+Licensed to the Apache Software Foundation (ASF) under one
+or more contributor license agreements. See the NOTICE file
+distributed with this work for additional information
+regarding copyright ownership. The ASF licenses this file
+to you under the Apache License, Version 2.0 (the
+"License"); you may not use this file except in compliance
+with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing,
+software distributed under the License is distributed on an
+"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+KIND, either express or implied. See the License for the
+specific language governing permissions and limitations
+under the License.
+-->
+
+
+
+TOPN queries refer to queries that involve ORDER BY LIMIT operations, which
are common in log retrieval and other detailed query scenarios. Doris
automatically optimizes this type of query.
+
+```sql
+SELECT * FROM tablex WHERE xxx ORDER BY c1,c2 ... LIMIT n
+```
+
+## Optimization Points
+
+1. During execution, dynamic range filters are built for the sorting columns
(e.g., c1 >= 1000), which automatically apply the preceding conditions when
reading data, leveraging zonemap indexes to filter out some rows or even entire
files.
+2. If the sorting fields c1, c2 are exactly the prefix of the table key,
further optimization is applied. When reading data, only the header or tail of
the data files is read, reducing the amount of data read to just the n rows
needed.
+3. SELECT * deferred materialization, during the data reading and sorting
process, only the sorting columns are read, not the other columns. After
obtaining the row numbers that meet the conditions, the entire data of those n
rows needed is read, significantly reducing the amount of data read and sorted.
+
+## Limitations
+
+1. It only applies to DUP and MOW tables, not to MOR and AGG tables.
+2. Due to the high memory consumption on very large `n`, it will not take
effect if n is greater than `topn_opt_limit_threshold`.
+
+## Configuration and Query Analysis
+
+The following two parameters are session variables that can be set for a
specific SQL or globally.
+
+1. `topn_opt_limit_threshold`: This session variable determines whether TOPN
optimization is applied. It defaults to 1024, and setting it to 0 disables the
optimization.
+
+2. `enable_two_phase_read_optimization`: This session variable determines
whether to enable this optimization. It defaults to true, and setting it to
false disables the optimization.
+
+### Checking if TOPN Query Optimization is Enabled
+
+To confirm if TOPN query optimization is enabled for a particular SQL, you can
use the `EXPLAIN` statement to get the query plan. An example is as follows:
+
+- `TOPN OPT` indicates that optimization point 1 is applied.
+- `VOlapScanNode` with `SORT LIMIT` indicates optimization point 2 is applied.
+- `OPT TWO PHASE` indicates optimization point 3 is applied.
+
+```sql
+ 1:VTOP-N(137)
+ | order by: @timestamp18 DESC
+ | TOPN OPT
+ | OPT TWO PHASE
+ | offset: 0
+ | limit: 10
+ | distribute expr lists: applicationName5
+ |
+ 0:VOlapScanNode(106)
+ TABLE: log_db.log_core_all_no_index(log_core_all_no_index),
PREAGGREGATION: ON
+ SORT INFO:
+ @timestamp18
+ SORT LIMIT: 10
+ TOPN OPT:1
+ PREDICATES: ZYCFC-TRACE-ID4 like '%flowId-1720055220933%'
+ partitions=1/8 (p20240704), tablets=250/250,
tabletList=1727094,1727096,1727098 ...
+ cardinality=345472780, avgRowSize=0.0, numNodes=1
+ pushAggOp=NONE
+```
+
+### Checking the Effectiveness of TOPN Query Optimization During Execution
+
+First, set `topn_opt_limit_threshold` to 0 to disable TOPN query optimization
and compare the execution time of the SQL with and without optimization enabled.
+
+After enabling TOPN query optimization, search for `RuntimePredicate` in the
query profile and focus on the following metrics:
+
+- `RowsZonemapRuntimePredicateFiltered`: The number of rows filtered out, the
higher the better.
+- `NumSegmentFiltered`: The number of data files filtered out, the higher the
better.
+- `BlockConditionsFilteredZonemapRuntimePredicateTime`: The time taken to
filter data, the lower the better.
+
+Before version 2.0.3, the `RuntimePredicate` metrics were not separated out,
and the `Zonemap` metrics can be used as a rough guide.
+
+```
+ SegmentIterator:
+ - BitmapIndexFilterTimer: 46.54us
+ - BlockConditionsFilteredBloomFilterTime: 10.352us
+ - BlockConditionsFilteredDictTime: 7.299us
+ - BlockConditionsFilteredTime: 202.23ms
+ - BlockConditionsFilteredZonemapRuntimePredicateTime: 0ns
+ - BlockConditionsFilteredZonemapTime: 402.917ms
+ - BlockInitSeekCount: 399
+ - BlockInitSeekTime: 11.309ms
+ - BlockInitTime: 215.59ms
+ - BlockLoadTime: 7s567ms
+ - BlocksLoad: 392.97K (392970)
+ - CachedPagesNum: 0
+ - CollectIteratorMergeTime: 0ns
+ - CollectIteratorNormalTime: 0ns
+ - CompressedBytesRead: 29.76 MB
+ - DecompressorTimer: 427.713ms
+ - ExprFilterEvalTime: 3s930ms
+ - FirstReadSeekCount: 392.921K (392921)
+ - FirstReadSeekTime: 528.287ms
+ - FirstReadTime: 1s134ms
+ - IOTimer: 51.286ms
+ - InvertedIndexFilterTime: 49.457us
+ - InvertedIndexQueryBitmapCopyTime: 0ns
+ - InvertedIndexQueryBitmapOpTime: 0ns
+ - InvertedIndexQueryCacheHit: 0
+ - InvertedIndexQueryCacheMiss: 0
+ - InvertedIndexQueryTime: 0ns
+ - InvertedIndexSearcherOpenTime: 0ns
+ - InvertedIndexSearcherSearchTime: 0ns
+ - LazyReadSeekCount: 0
+ - LazyReadSeekTime: 0ns
+ - LazyReadTime: 106.952us
+ - NumSegmentFiltered: 0
+ - NumSegmentTotal: 50
+ - OutputColumnTime: 61.987ms
+ - OutputIndexResultColumnTimer: 12.345ms
+ - RawRowsRead: 3.929151M (3929151)
+ - RowsBitmapIndexFiltered: 0
+ - RowsBloomFilterFiltered: 0
+ - RowsConditionsFiltered: 6.38976M (6389760)
+ - RowsDictFiltered: 0
+ - RowsInvertedIndexFiltered: 0
+ - RowsKeyRangeFiltered: 0
+ - RowsShortCircuitPredFiltered: 0
+ - RowsShortCircuitPredInput: 0
+ - RowsStatsFiltered: 6.38976M (6389760)
+ - RowsVectorPredFiltered: 0
+ - RowsVectorPredInput: 0
+ - RowsZonemapRuntimePredicateFiltered: 6.38976M (6389760)
+ - SecondReadTime: 0ns
+ - ShortPredEvalTime: 0ns
+ - TotalPagesNum: 2.301K (2301)
+ - UncompressedBytesRead: 137.99 MB
+ - VectorPredEvalTime: 0ns
+```
diff --git a/versioned_sidebars/version-2.0-sidebars.json
b/versioned_sidebars/version-2.0-sidebars.json
index 447a6278694..0f74ff5fd5f 100644
--- a/versioned_sidebars/version-2.0-sidebars.json
+++ b/versioned_sidebars/version-2.0-sidebars.json
@@ -209,6 +209,7 @@
]
},
"query/high-concurrent-point-query",
+ "query/topn-query",
{
"type": "category",
"label": "Analyzing Query",
diff --git a/versioned_sidebars/version-2.1-sidebars.json
b/versioned_sidebars/version-2.1-sidebars.json
index b245a57ab8a..ca0b069114c 100644
--- a/versioned_sidebars/version-2.1-sidebars.json
+++ b/versioned_sidebars/version-2.1-sidebars.json
@@ -229,6 +229,7 @@
]
},
"query/high-concurrent-point-query",
+ "query/topn-query",
{
"type": "category",
"label": "Analyzing Query",
diff --git a/versioned_sidebars/version-3.0-sidebars.json
b/versioned_sidebars/version-3.0-sidebars.json
index 4c31bbaf595..424ad09d360 100644
--- a/versioned_sidebars/version-3.0-sidebars.json
+++ b/versioned_sidebars/version-3.0-sidebars.json
@@ -261,6 +261,7 @@
]
},
"query/high-concurrent-point-query",
+ "query/topn-query",
{
"type": "category",
"label": "Analyzing Query",
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org
For additional commands, e-mail: commits-h...@doris.apache.org
