mirror of
https://github.com/GreptimeTeam/greptimedb.git
synced 2026-07-04 04:50:37 +00:00
9cf071808a
* fix(query): push down join filters before MergeScan Signed-off-by: discord9 <discord9@163.com> * fix(query): run optimizer before MergeScan pushdown Signed-off-by: discord9 <discord9@163.com> * fix(query): narrow pre-MergeScan filter pushdown Signed-off-by: discord9 <discord9@163.com> * fix(query): refine pre-MergeScan optimizer prepass Signed-off-by: discord9 <discord9@163.com> * fix(query): satisfy predicate extractor clippy Signed-off-by: discord9 <discord9@163.com> * test(query): cover pre-MergeScan optimizer edges Signed-off-by: discord9 <discord9@163.com> * test(query): cover set comparison prepass Signed-off-by: discord9 <discord9@163.com> * fix(query): guard remote scan filter pushdown Signed-off-by: discord9 <discord9@163.com> * fix(query): preserve subquery planning errors Signed-off-by: discord9 <discord9@163.com> * fix(query): preserve usable scan predicates Signed-off-by: discord9 <discord9@163.com> * fix(query): simplify scan predicate extraction Signed-off-by: discord9 <discord9@163.com> * fix(query): keep scan filter extraction scoped Signed-off-by: discord9 <discord9@163.com> * docs(query): explain pre-MergeScan optimizer Signed-off-by: discord9 <discord9@163.com> --------- Signed-off-by: discord9 <discord9@163.com>
159 lines
11 KiB
Plaintext
159 lines
11 KiB
Plaintext
-- Set-comparison subqueries (`ANY`/`ALL`) must be rewritten before
|
|
-- PushDownFilter. Otherwise the set-comparison subquery can be pushed into
|
|
-- TableScan.partial_filters, which is not a valid remote scan filter.
|
|
CREATE TABLE sc_t (
|
|
ts TIMESTAMP(3) TIME INDEX,
|
|
v INT,
|
|
PRIMARY KEY (v)
|
|
) ENGINE = mito;
|
|
|
|
Affected Rows: 0
|
|
|
|
CREATE TABLE sc_s (
|
|
ts TIMESTAMP(3) TIME INDEX,
|
|
v INT
|
|
) ENGINE = mito;
|
|
|
|
Affected Rows: 0
|
|
|
|
INSERT INTO sc_t VALUES
|
|
('2024-01-30 00:00:00', 1),
|
|
('2024-01-30 01:00:00', 6),
|
|
('2024-01-30 02:00:00', 10);
|
|
|
|
Affected Rows: 3
|
|
|
|
INSERT INTO sc_s VALUES
|
|
('2024-01-30 00:00:00', 5),
|
|
('2024-01-30 01:00:00', NULL);
|
|
|
|
Affected Rows: 2
|
|
|
|
ADMIN FLUSH_TABLE('sc_t');
|
|
|
|
+---------------------------+
|
|
| ADMIN FLUSH_TABLE('sc_t') |
|
|
+---------------------------+
|
|
| 0 |
|
|
+---------------------------+
|
|
|
|
ADMIN FLUSH_TABLE('sc_s');
|
|
|
|
+---------------------------+
|
|
| ADMIN FLUSH_TABLE('sc_s') |
|
|
+---------------------------+
|
|
| 0 |
|
|
+---------------------------+
|
|
|
|
-- SQLNESS REPLACE region=\d+\(\d+,\s+\d+\) region=REDACTED
|
|
-- SQLNESS REPLACE (peers.*) REDACTED
|
|
EXPLAIN SELECT v FROM sc_t WHERE v > ANY(SELECT v FROM sc_s) ORDER BY v;
|
|
|
|
+---------------+---------------------------------------------------------------------------------------------------------------+
|
|
| plan_type | plan |
|
|
+---------------+---------------------------------------------------------------------------------------------------------------+
|
|
| logical_plan | Sort: sc_t.v ASC NULLS LAST |
|
|
| | Projection: sc_t.v |
|
|
| | Filter: __correlated_sq_1.mark OR __correlated_sq_2.mark AND NOT __correlated_sq_1.mark AND Boolean(NULL) |
|
|
| | LeftMark Join: Filter: sc_t.v > __correlated_sq_2.v IS NULL |
|
|
| | Filter: __correlated_sq_1.mark OR NOT __correlated_sq_1.mark AND Boolean(NULL) |
|
|
| | LeftMark Join: Filter: sc_t.v > __correlated_sq_1.v IS TRUE |
|
|
| | Projection: sc_t.v |
|
|
| | MergeScan [is_placeholder=false, remote_input=[ |
|
|
| | TableScan: sc_t |
|
|
| | ]] |
|
|
| | MergeScan [is_placeholder=false, remote_input=[ |
|
|
| | Projection: __correlated_sq_1.v |
|
|
| | SubqueryAlias: __correlated_sq_1 |
|
|
| | Projection: sc_s.v |
|
|
| | TableScan: sc_s |
|
|
| | ]] |
|
|
| | MergeScan [is_placeholder=false, remote_input=[ |
|
|
| | Projection: __correlated_sq_2.v |
|
|
| | SubqueryAlias: __correlated_sq_2 |
|
|
| | Projection: sc_s.v |
|
|
| | TableScan: sc_s |
|
|
| | ]] |
|
|
| physical_plan | SortPreservingMergeExec: [v@0 ASC NULLS LAST] |
|
|
| | SortExec: expr=[v@0 ASC NULLS LAST], preserve_partitioning=[true] |
|
|
| | FilterExec: mark@1 OR mark@2 AND NOT mark@1 AND NULL, projection=[v@0] |
|
|
| | NestedLoopJoinExec: join_type=LeftMark, filter=v@0 > v@1 IS NULL |
|
|
| | CoalescePartitionsExec |
|
|
| | FilterExec: mark@1 OR NOT mark@1 AND NULL |
|
|
| | NestedLoopJoinExec: join_type=LeftMark, filter=(v@0 > v@1) IS NOT DISTINCT FROM true |
|
|
| | CoalescePartitionsExec |
|
|
| | ProjectionExec: expr=[v@1 as v] |
|
|
| | MergeScanExec: REDACTED
|
|
| | MergeScanExec: REDACTED
|
|
| | MergeScanExec: REDACTED
|
|
| | |
|
|
+---------------+---------------------------------------------------------------------------------------------------------------+
|
|
|
|
SELECT v FROM sc_t WHERE v > ANY(SELECT v FROM sc_s) ORDER BY v;
|
|
|
|
+----+
|
|
| v |
|
|
+----+
|
|
| 6 |
|
|
| 10 |
|
|
+----+
|
|
|
|
-- SQLNESS REPLACE region=\d+\(\d+,\s+\d+\) region=REDACTED
|
|
-- SQLNESS REPLACE (peers.*) REDACTED
|
|
EXPLAIN SELECT v FROM sc_t WHERE v != ALL(SELECT v FROM sc_s) ORDER BY v;
|
|
|
|
+---------------+----------------------------------------------------------------------------------------------------+
|
|
| plan_type | plan |
|
|
+---------------+----------------------------------------------------------------------------------------------------+
|
|
| logical_plan | Sort: sc_t.v ASC NULLS LAST |
|
|
| | Projection: sc_t.v |
|
|
| | Filter: __correlated_sq_2.mark AND Boolean(NULL) OR NOT __correlated_sq_2.mark |
|
|
| | LeftMark Join: Filter: sc_t.v != __correlated_sq_2.v IS NULL |
|
|
| | Projection: sc_t.v |
|
|
| | Filter: NOT __correlated_sq_1.mark |
|
|
| | LeftMark Join: Filter: sc_t.v != __correlated_sq_1.v IS FALSE |
|
|
| | Projection: sc_t.v |
|
|
| | MergeScan [is_placeholder=false, remote_input=[ |
|
|
| | TableScan: sc_t |
|
|
| | ]] |
|
|
| | MergeScan [is_placeholder=false, remote_input=[ |
|
|
| | Projection: __correlated_sq_1.v |
|
|
| | SubqueryAlias: __correlated_sq_1 |
|
|
| | Projection: sc_s.v |
|
|
| | TableScan: sc_s |
|
|
| | ]] |
|
|
| | MergeScan [is_placeholder=false, remote_input=[ |
|
|
| | Projection: __correlated_sq_2.v |
|
|
| | SubqueryAlias: __correlated_sq_2 |
|
|
| | Projection: sc_s.v |
|
|
| | TableScan: sc_s |
|
|
| | ]] |
|
|
| physical_plan | SortPreservingMergeExec: [v@0 ASC NULLS LAST] |
|
|
| | SortExec: expr=[v@0 ASC NULLS LAST], preserve_partitioning=[true] |
|
|
| | FilterExec: mark@1 AND NULL OR NOT mark@1, projection=[v@0] |
|
|
| | NestedLoopJoinExec: join_type=LeftMark, filter=v@0 != v@1 IS NULL |
|
|
| | CoalescePartitionsExec |
|
|
| | FilterExec: NOT mark@1, projection=[v@0] |
|
|
| | NestedLoopJoinExec: join_type=LeftMark, filter=(v@0 != v@1) IS NOT DISTINCT FROM false |
|
|
| | CoalescePartitionsExec |
|
|
| | ProjectionExec: expr=[v@1 as v] |
|
|
| | MergeScanExec: REDACTED
|
|
| | MergeScanExec: REDACTED
|
|
| | MergeScanExec: REDACTED
|
|
| | |
|
|
+---------------+----------------------------------------------------------------------------------------------------+
|
|
|
|
SELECT v FROM sc_t WHERE v != ALL(SELECT v FROM sc_s) ORDER BY v;
|
|
|
|
++
|
|
++
|
|
|
|
DROP TABLE sc_t;
|
|
|
|
Affected Rows: 0
|
|
|
|
DROP TABLE sc_s;
|
|
|
|
Affected Rows: 0
|
|
|