更新时间：2026-07-28 GMT+08:00
DML
ROWID系统列支持在DML语句中使用，包括INSERT/SELECT/UPDATE/DELETE语句，示例如下：
gaussdb=# CREATE DATABASE ora dbcompatibility 'ORA';
CREATE DATABASE
gaussdb=# \c ora
ora=# CREATE TABLE test(a INT, b INT) WITH (hasrowid);
CREATE TABLE
ora=# -- INSERT
ora=# INSERT INTO test values(1, 1);
INSERT 0 1
ora=# -- ROWID表插入语句行记录会携带ROWID系统列
ora=# SELECT ROWID FROM test;
rowid
-------------------------------
AAAAAAAAAP//AAAEGAAAAAAAAAAAB
(1 row)
ora=# -- SELECT，使用ROWID系统列作为WHERE条件
ora=# SELECT * FROM test WHERE ROWID = 'AAAAAAAAAP//AAAEGAAAAAAAAAAAB';
a | b
---+---
1 | 1
(1 row)
ora=# -- UPDATE，使用ROWID系统列作为WHERE条件
ora=# UPDATE test SET b = 2 WHERE ROWID = 'AAAAAAAAAP//AAAEGAAAAAAAAAAAB';
UPDATE 1
ora=# -- DELETE，使用ROWID系统列作为WHERE条件
ora=# DELETE FROM test WHERE ROWID = 'AAAAAAAAAP//AAAEGAAAAAAAAAAAB';
DELETE 1
ora=# -- CLEAN
ora=# DROP TABLE test;
DROP TABLE
ora=# \c postgres
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "postgres" as user "$user".
gaussdb=# DROP DATABASE ora;
DROP DATABASE 在DML语句中使用ROWID系统列时，若打开GUC参数opt_behavior_knob的ROWID_OPT选项（详情请参见《参考》中“数据库运行参数说明 > GUC参数说明 > 查询规划 > 其他优化器选项”章节内容），则支持ROWID系统列谓词条件改写、ROWID系统列ORDER BY语句改写、ROWID系统列max/min优化、ROWID系统列分布剪枝和分区剪枝查询优化场景。
ROWID系统列谓词条件改写
当ROWID系统列用于WHERE子句或JOIN ON子句的谓词条件判断时，可进行条件语句的改写，当前仅支持比较操作符（=、<>、<、>、<=、>=）作为谓词条件的语句改写。
- 当操作符为等号（=）时，支持全场景改写，形如“ROWID = src_expr”的谓词条件（当src_expr也为ROWID系统列时，不进行改写，无实际意义），改写规则如下：
ROWID = src_expr AND (-1)::smallint = ROWID_HASHBUCKETID(src_expr) AND tableoid = ROWID_TABLEOID(src_expr) AND rowno = ROWID_SEQUENCE(src_expr)
改写后，前三个条件分别触发分布剪枝、bucket剪枝和分区剪枝，最后一个条件触发索引扫描以提升扫描性能。对于不可改写的表达式，比如IN表达式“rowid IN ($1,$2,...)”，会进行全表扫描，遍历每行元组进行条件检查，此时不进行剪枝和索引扫描。
- 当操作符为非等号（<>、<、>、<=、>=）时，仅支持部分场景改写，比如范围表达式“ROWID > src_expr”。由于ROWID的比较需综合考虑内部字段（node_slice、bucket_id、table_oid、row_no），改写仅适用于单节点普通表，且执行计划需为Light Proxy、FQS或PGXC，同时要求src_expr为可计算常量。依次判断node_id、table_id和bucket_id，当上述三者与给定rowid常量不同时，直接根据操作符进行比较，并将结果作为常量表达式返回；当他们与给定rowid常量均相同时，则将表达式改写为rowno系统列的谓词条件，即：
rowno >/</<>/>=/<= ROWID_SEQUENCE(src_expr)
对于无法改写的场景，仍执行全表扫描。
ROWID系统列谓词条件改写遵从如下规格约束：
- WHERE/JOIN ON条件中，当操作符两侧都为ROWID系统列时不改写。
- 形如ROWID IN/NOT IN (xxx)场景，当右侧表达式为子查询表达式、自定义类型表达式、数组表达式时不支持改写。
- 形如NULLIF(ROWID,xxx)或NULLIF(ROWID,xxx)的NULLIF表达式不改写（ROWID = NULLIF(xxx,xxx)可以正常改写）。
- DISTINCT FROM表达式不改写。
- HAVING子句不改写。
- 操作符左右两侧均为RECORD类型表达式时不支持改写。
- ALL/ANY/SOME表达式不支持改写，且形如ROWID = ALL/ANY/SOME (xxx)或ALL/ANY/SOME (xxx)= ROWID的判断条件不支持改写。
上述约束同时适用于CN与DN节点，是否进行谓词改写取决于各节点实际处理的SQL形态。当原始SQL在CN上不满足改写条件时，CN不执行改写，但若下推至DN的SQL满足改写条件，仍可在DN节点进行谓词改写。
示例：
由于在不同场景下tableoid字段值不同，以下示例仅作参考，执行时以实际输出结果为准。
以GUC参数explain_perf_mode设置为normal为例。
-- 数据准备
gaussdb=# CREATE DATABASE ora dbcompatibility 'ORA';
CREATE DATABASE
gaussdb=# \c ora
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "ora" as user "$user".
ora=# CREATE TABLE t1(a int, c int) WITH (storage_type=astore, hasrowid=ON);
NOTICE: The 'DISTRIBUTE BY' clause is not specified. Using 'a' AS the distribution column BY default.
HINT: Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
CREATE TABLE
ora=# INSERT INTO t1 VALUES( 1, 1);
INSERT 0 1
ora=# INSERT INTO t1 VALUES( 2, 2);
INSERT 0 1
ora=# INSERT INTO t1 VALUES(3, 3);
INSERT 0 1
ora=# INSERT INTO t1 VALUES(3, 4);
INSERT 0 1
ora=# INSERT INTO t1 VALUES(1, 5);
INSERT 0 1
ora=# INSERT INTO t1 VALUES( 2, 6);
INSERT 0 1
ora=# INSERT INTO t1 VALUES(3, 7);
INSERT 0 1
ora=# INSERT INTO t1 VALUES( 3, 8);
INSERT 0 1
ora=# INSERT INTO t1 VALUES( 1, 9);
INSERT 0 1
ora=# INSERT INTO t1 VALUES( 2, 10);
INSERT 0 1
ora=# CREATE OR REPLACE FUNCTION func RETURN rowid AS
ora$# temp rowid;
ora$# res int;
ora$# BEGIN
ora$# SELECT rowid INTO temp FROM t1 ORDER BY rowid LIMIT 1;
ora$# RETURN temp;
ora$# END;
ora$# /
CREATE FUNCTION
ora=# EXPLAIN (costs off, verbose) SELECT * FROM t1 WHERE rowid = func();
WARNING: Statistics IN some tables OR columns(public.t1.a) are not collected.
HINT: Do analyze for them IN ORDER to generate optimized plan.
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Data Node Scan ON t1 "_REMOTE_TABLE_QUERY_"
Output: t1.a, t1.c
Node/s: All datanodes
Remote query: SELECT a, c, rowid, tableoid, rowno FROM ONLY public.t1 WHERE true
Coordinator quals: ((t1.rowid = func()) AND ((-1)::smallint = rowid_hashbucketid(func())) AND (t1.tableoid = rowid_tableoid(func())) AND (t1.rowno = rowid_sequence(func())))
(5 rows)
ora=# SELECT * FROM t1 WHERE rowid = func();
a | c
---+---
1 | 1
(1 row)
ora=# --HAVING子句，不支持改写
ora=# EXPLAIN (costs off, verbose) SELECT max(a),max(rowid) FROM t1 GROUP BY rowid HAVING rowid = 'AAAAAAAAAP//BBBBBAAAAAAAAAAAB';
WARNING: Statistics IN some tables OR columns(public.t1.a) are not collected.
HINT: Do analyze for them IN ORDER to generate optimized plan.
QUERY PLAN
---------------------------------------------------------------------------------
HashAggregate
Output: max((max(a))), max((max(rowid))), rowid
Group By Key: t1.rowid
-> Streaming (TYPE: GATHER)
Output: (max(a)), (max(rowid)), rowid
Node/s: datanode1
-> HashAggregate
Output: max(a), max(rowid), rowid
Group By Key: t1.rowid
-> Seq Scan ON public.t1
Output: rowid, a
Distribute Key: a
Filter: (t1.rowid = 'AAAAAAAAAP//BBBBBAAAAAAAAAAAB'::rowid)
(13 rows)
ora=# SELECT max(a),max(rowid) FROM t1 GROUP BY rowid HAVING rowid = 'AAAAAAAAAP//BBBBBAAAAAAAAAAAB';
max | max
-----+-----
(0 rows)
ora=# SELECT max(a) AS newcol1,rowid_sequence(max(rowid)) AS newcol2 FROM t1 GROUP BY rowid ORDER BY newcol1,newcol2;
newcol1 | newcol2
---------+---------
1 | 1
1 | 3
1 | 5
2 | 2
2 | 4
2 | 6
3 | 11
3 | 12
3 | 13
3 | 14
(10 rows)
ora=# -- NULLIF表达式本身不改写，rowid = NULLIF(xxx)改写
ora=# EXPLAIN (costs off, verbose) SELECT rowid FROM t1 WHERE rowid = NULLIF(rowid, rowid);
SELECT rowid FROM t1 WHERE rowid = NULLIF(rowid, rowid);
EXPLAIN (costs off, verbose) SELECT rowid FROM t1 WHERE rowid = NULLIF(rowid, 'AAAAAAAAAP//AAAAAAAAAAAAAAAAA');
SELECT rowid FROM t1 WHERE rowid = NULLIF(rowid, 'AAAAAAAAAP//AAAAAAAAAAAAAAAAA'); QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------
Data Node Scan ON "__REMOTE_FQS_QUERY__"
Output: t1.rowid
Node/s: All datanodes
Remote query: SELECT rowid FROM public.t1 WHERE rowid = NULLIF(rowid, rowid) AND (-1)::smallint = rowid_hashbucketid(NULLIF(rowid, rowid)) AND tableoid = rowid_tableoid(NULLIF(rowid, rowid)) AND rowno
= rowid_sequence(NULLIF(rowid, rowid))
(4 rows)
ora=# SELECT rowid FROM t1 WHERE rowid = NULLIF(rowid, rowid);
rowid
-------
(0 rows)
ora=# EXPLAIN (costs off, verbose) SELECT rowid FROM t1 WHERE rowid = NULLIF(rowid, 'AAAAAAAAAP//AAAAAAAAAAAAAAAAA'); QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Data Node Scan ON "__REMOTE_FQS_QUERY__"
Output: t1.rowid
Node/s: All datanodes
Remote query: SELECT rowid FROM public.t1 WHERE rowid = NULLIF(rowid, 'AAAAAAAAAP//AAAAAAAAAAAAAAAAA'::rowid) AND (-1)::smallint = rowid_hashbucketid(NULLIF(rowid, 'AAAAAAAAAP//AAAAAAAAAAAAAAAAA'::row
id)) AND tableoid = rowid_tableoid(NULLIF(rowid, 'AAAAAAAAAP//AAAAAAAAAAAAAAAAA'::rowid)) AND rowno = rowid_sequence(NULLIF(rowid, 'AAAAAAAAAP//AAAAAAAAAAAAAAAAA'::rowid))
(4 rows)
ora=# SELECT rowid FROM t1 WHERE rowid = NULLIF(rowid, 'AAAAAAAAAP//AAAAAAAAAAAAAAAAA');
rowid
-------------------------------
AAAAAAAAAP//AAAECpAAAAAAAAAAB
AAAAAAAAAP//AAAECpAAAAAAAAAAC
AAAAAAAAAP//AAAECpAAAAAAAAAAD
AAAAAAAAAP//AAAECpAAAAAAAAAAE
AAAAAAAAAP//AAAECpAAAAAAAAAAF
AAAAAAAAAP//AAAECpAAAAAAAAAAG
AAAAAAAABP//AAAECpAAAAAAAAAAL
AAAAAAAABP//AAAECpAAAAAAAAAAM
AAAAAAAABP//AAAECpAAAAAAAAAAN
AAAAAAAABP//AAAECpAAAAAAAAAAO
(10 rows)
ora=# -- rowexpr不改写
ora=# EXPLAIN (costs off, verbose) SELECT a FROM t1 WHERE (rowid,c) IN (SELECT rowid,c FROM t1);
WARNING: Statistics IN some tables OR columns(public.t1.a, public.t1.c) are not collected.
HINT: Do analyze for them IN ORDER to generate optimized plan.
QUERY PLAN
------------------------------------------------------------------------------------------
Streaming (TYPE: GATHER)
Output: public.t1.a
Node/s: All datanodes
-> Hash Right Semi Join
Output: public.t1.a
Hash Cond: ((public.t1.rowid = public.t1.rowid) AND (public.t1.c = public.t1.c))
-> Streaming(TYPE: BROADCAST)
Output: public.t1.rowid, public.t1.c
Spawn ON: All datanodes
Consumer Nodes: All datanodes
-> Seq Scan ON public.t1
Output: public.t1.rowid, public.t1.c
Distribute Key: public.t1.a
-> Hash
Output: public.t1.a, public.t1.rowid, public.t1.c
-> Seq Scan ON public.t1
Output: public.t1.a, public.t1.rowid, public.t1.c
Distribute Key: public.t1.a
(18 rows)
ora=# SELECT a FROM t1 WHERE (rowid,c) IN (SELECT rowid,c FROM t1) ORDER BY a;
a
---
1
1
1
2
2
2
3
3
3
3
(10 rows)
ora=# CREATE TYPE t1_type AS (rowid rowid, c int);
CREATE TYPE
ora=# EXPLAIN (costs off, verbose) SELECT a FROM t1 WHERE (rowid, c) IN t1_type(func(), 1);
WARNING: Statistics IN some tables OR columns(public.t1.a, public.t1.c) are not collected.
HINT: Do analyze for them IN ORDER to generate optimized plan.
QUERY PLAN
-----------------------------------------------------------------
Data Node Scan ON t1 "_REMOTE_TABLE_QUERY_"
Output: t1.a
Node/s: All datanodes
Remote query: SELECT a, rowid FROM ONLY public.t1 WHERE c = 1
Coordinator quals: (t1.rowid = func())
(5 rows)
ora=# SELECT a FROM t1 WHERE (rowid, c) IN t1_type(func(), 1) ORDER BY a;
a
---
1
(1 row)
ora=# -- IN/not IN
ora=# EXPLAIN (costs off, verbose) SELECT rowid FROM t1 WHERE rowid IN func();
WARNING: Statistics IN some tables OR columns(public.t1.a) are not collected.
HINT: Do analyze for them IN ORDER to generate optimized plan.
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Data Node Scan ON t1 "_REMOTE_TABLE_QUERY_"
Output: t1.rowid
Node/s: All datanodes
Remote query: SELECT rowid, tableoid, rowno FROM ONLY public.t1 WHERE true
Coordinator quals: ((t1.rowid = func()) AND ((-1)::smallint = rowid_hashbucketid(func())) AND (t1.tableoid = rowid_tableoid(func())) AND (t1.rowno = rowid_sequence(func())))
(5 rows)
ora=# SELECT rowid FROM t1 WHERE rowid IN func();
rowid
-------------------------------
AAAAAAAAAP//AAAECpAAAAAAAAAAB
(1 row)
ora=# EXPLAIN (costs off, verbose) SELECT rowid FROM t1 WHERE rowid IN (func(), func()); --rowexpr不改写
WARNING: Statistics IN some tables OR columns(public.t1.a) are not collected.
HINT: Do analyze for them IN ORDER to generate optimized plan.
QUERY PLAN
---------------------------------------------------------------
Data Node Scan ON t1 "_REMOTE_TABLE_QUERY_"
Output: t1.rowid
Node/s: All datanodes
Remote query: SELECT rowid FROM ONLY public.t1 WHERE true
Coordinator quals: (t1.rowid = ANY (ARRAY[func(), func()]))
(5 rows)
ora=# SELECT rowid FROM t1 WHERE rowid IN (func(), func());
rowid
-------------------------------
AAAAAAAAAP//AAAECpAAAAAAAAAAB
(1 row)
ora=# EXPLAIN (costs off, verbose) SELECT rowid FROM t1 WHERE rowid not IN func();
WARNING: Statistics IN some tables OR columns(public.t1.a) are not collected.
HINT: Do analyze for them IN ORDER to generate optimized plan.
QUERY PLAN
-------------------------------------------------------------
Data Node Scan ON t1 "_REMOTE_TABLE_QUERY_"
Output: t1.rowid
Node/s: All datanodes
Remote query: SELECT rowid FROM ONLY public.t1 WHERE true
Coordinator quals: (t1.rowid <> func())
(5 rows)
ora=# SELECT rowid FROM t1 WHERE rowid not IN func();
rowid
-------------------------------
AAAAAAAAAP//AAAECpAAAAAAAAAAC
AAAAAAAAAP//AAAECpAAAAAAAAAAD
AAAAAAAAAP//AAAECpAAAAAAAAAAE
AAAAAAAAAP//AAAECpAAAAAAAAAAF
AAAAAAAAAP//AAAECpAAAAAAAAAAG
AAAAAAAABP//AAAECpAAAAAAAAAAL
AAAAAAAABP//AAAECpAAAAAAAAAAM
AAAAAAAABP//AAAECpAAAAAAAAAAN
AAAAAAAABP//AAAECpAAAAAAAAAAO
(9 rows)
ora=# -- distinct FROM不改写
ora=# EXPLAIN (costs off, verbose) SELECT a FROM t1 WHERE rowid is distinct FROM func();
WARNING: Statistics IN some tables OR columns(public.t1.a) are not collected.
HINT: Do analyze for them IN ORDER to generate optimized plan.
QUERY PLAN
----------------------------------------------------------------
Data Node Scan ON t1 "_REMOTE_TABLE_QUERY_"
Output: t1.a
Node/s: All datanodes
Remote query: SELECT a, rowid FROM ONLY public.t1 WHERE true
Coordinator quals: (t1.rowid IS DISTINCT FROM func())
(5 rows)
ora=# SELECT a FROM t1 WHERE rowid is distinct FROM func() ORDER BY a;
a
---
1
1
2
2
2
3
3
3
3
(9 rows)
-- 在JOIN ON子句的谓词条件改写
ora=# CREATE TABLE t2(a int, c text) WITH (storage_type=astore, hasrowid=on);
CREATE TABLE
ora=#
ora=# INSERT INTO t2 VALUES( 1, func());
INSERT 0 1
ora=# INSERT INTO t2 VALUES( 2, func());
INSERT 0 1
ora=# INSERT INTO t2 VALUES(3, func());
INSERT 0 1
ora=# INSERT INTO t2 VALUES(3, func());
INSERT 0 1
ora=# INSERT INTO t2 VALUES(1, func());
INSERT 0 1
ora=# INSERT INTO t2 VALUES( 2, func());
INSERT 0 1
ora=# INSERT INTO t2 VALUES(3, func());
INSERT 0 1
ora=# INSERT INTO t2 VALUES( 3, func());
INSERT 0 1
ora=# INSERT INTO t2 VALUES( 1, func());
INSERT 0 1
ora=# INSERT INTO t2 VALUES( 2, func());
INSERT 0 1
ora=# EXPLAIN (costs off, verbose) SELECT *,t1.rowid, t2.rowid FROM t1 INNER JOIN t2 ON t1.rowid = t2.c;
WARNING: Statistics IN some tables OR columns(public.t1.a, public.t2.a, public.t2.c) are not collected.
HINT: Do analyze for them IN ORDER to generate optimized plan.
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------
Streaming (TYPE: GATHER)
Output: t1.a, t1.c, t2.a, t2.c, t1.rowid, t2.rowid
Node/s: All datanodes
-> Nested Loop
Output: t1.a, t1.c, t2.a, t2.c, t1.rowid, t2.rowid
Inner Unique: true
-> Streaming(TYPE: BROADCAST)
Output: t2.a, t2.c, t2.rowid
Spawn ON: All datanodes
Consumer Nodes: All datanodes
-> Seq Scan ON public.t2
Output: t2.a, t2.c, t2.rowid
Distribute Key: t2.a
Filter: ((-1)::smallint = rowid_hashbucketid((t2.c)::rowid))
-> Index Scan using t1_rowno_idx ON public.t1
Output: t1.a, t1.c, t1.rowid, t1.tableoid, t1.rowno
Distribute Key: t1.a
Index Cond: (t1.rowno = rowid_sequence((t2.c)::rowid))
Filter: (((-1)::smallint = rowid_hashbucketid(t1.rowid)) AND ((t2.c)::rowid = t1.rowid) AND (rowid_tableoid((t2.c)::rowid) = t1.tableoid))
(19 rows)
ora=# SELECT *,t1.rowid, t2.rowid FROM t1 INNER JOIN t2 ON t1.rowid = t2.c;
a | c | a | c | rowid | rowid
---+---+---+-------------------------------+-------------------------------+-------------------------------
1 | 1 | 3 | AAAAAAAAAP//AAAECpAAAAAAAAAAB | AAAAAAAAAP//AAAECpAAAAAAAAAAB | AAAAAAAABP//AAAECzAAAAAAAAAAL
1 | 1 | 3 | AAAAAAAAAP//AAAECpAAAAAAAAAAB | AAAAAAAAAP//AAAECpAAAAAAAAAAB | AAAAAAAABP//AAAECzAAAAAAAAAAM
1 | 1 | 3 | AAAAAAAAAP//AAAECpAAAAAAAAAAB | AAAAAAAAAP//AAAECpAAAAAAAAAAB | AAAAAAAABP//AAAECzAAAAAAAAAAN
1 | 1 | 3 | AAAAAAAAAP//AAAECpAAAAAAAAAAB | AAAAAAAAAP//AAAECpAAAAAAAAAAB | AAAAAAAABP//AAAECzAAAAAAAAAAO
1 | 1 | 1 | AAAAAAAAAP//AAAECpAAAAAAAAAAB | AAAAAAAAAP//AAAECpAAAAAAAAAAB | AAAAAAAAAP//AAAECzAAAAAAAAAAB
1 | 1 | 2 | AAAAAAAAAP//AAAECpAAAAAAAAAAB | AAAAAAAAAP//AAAECpAAAAAAAAAAB | AAAAAAAAAP//AAAECzAAAAAAAAAAC
1 | 1 | 1 | AAAAAAAAAP//AAAECpAAAAAAAAAAB | AAAAAAAAAP//AAAECpAAAAAAAAAAB | AAAAAAAAAP//AAAECzAAAAAAAAAAD
1 | 1 | 2 | AAAAAAAAAP//AAAECpAAAAAAAAAAB | AAAAAAAAAP//AAAECpAAAAAAAAAAB | AAAAAAAAAP//AAAECzAAAAAAAAAAE
1 | 1 | 1 | AAAAAAAAAP//AAAECpAAAAAAAAAAB | AAAAAAAAAP//AAAECpAAAAAAAAAAB | AAAAAAAAAP//AAAECzAAAAAAAAAAF
1 | 1 | 2 | AAAAAAAAAP//AAAECpAAAAAAAAAAB | AAAAAAAAAP//AAAECpAAAAAAAAAAB | AAAAAAAAAP//AAAECzAAAAAAAAAAG
(10 rows)
ora=#
ora=# EXPLAIN (costs off, verbose) SELECT *,t1.rowid, t2.rowid FROM t1 LEFT JOIN t2 ON t1.rowid = t2.c;
WARNING: Statistics IN some tables OR columns(public.t1.a, public.t2.a, public.t2.c) are not collected.
HINT: Do analyze for them IN ORDER to generate optimized plan.
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
Streaming (TYPE: GATHER)
Output: t1.a, t1.c, t2.a, t2.c, t1.rowid, t2.rowid
Node/s: All datanodes
-> Nested Loop Left Join
Output: t1.a, t1.c, t2.a, t2.c, t1.rowid, t2.rowid
Join Filter: ((t1.rowid = (t2.c)::rowid) AND (t1.tableoid = rowid_tableoid((t2.c)::rowid)) AND (t1.rowno = rowid_sequence((t2.c)::rowid)))
-> Index Scan using t1_rowno_idx ON public.t1
Output: t1.a, t1.c, t1.rowid, t1.tableoid, t1.rowno
Distribute Key: t1.a
-> Materialize
Output: t2.a, t2.c, t2.rowid
-> Streaming(TYPE: BROADCAST)
Output: t2.a, t2.c, t2.rowid
Spawn ON: All datanodes
Consumer Nodes: All datanodes
-> Seq Scan ON public.t2
Output: t2.a, t2.c, t2.rowid
Distribute Key: t2.a
Filter: ((-1)::smallint = rowid_hashbucketid((t2.c)::rowid))
(19 rows)
ora=# SELECT *,t1.rowid, t2.rowid FROM t1 LEFT JOIN t2 ON t1.rowid = t2.c;
a | c | a | c | rowid | rowid
---+----+---+-------------------------------+-------------------------------+-------------------------------
3 | 3 | | | AAAAAAAABP//AAAECpAAAAAAAAAAL |
3 | 4 | | | AAAAAAAABP//AAAECpAAAAAAAAAAM |
3 | 7 | | | AAAAAAAABP//AAAECpAAAAAAAAAAN |
3 | 8 | | | AAAAAAAABP//AAAECpAAAAAAAAAAO |
1 | 1 | 1 | AAAAAAAAAP//AAAECpAAAAAAAAAAB | AAAAAAAAAP//AAAECpAAAAAAAAAAB | AAAAAAAAAP//AAAECzAAAAAAAAAAB
1 | 1 | 2 | AAAAAAAAAP//AAAECpAAAAAAAAAAB | AAAAAAAAAP//AAAECpAAAAAAAAAAB | AAAAAAAAAP//AAAECzAAAAAAAAAAC
1 | 1 | 1 | AAAAAAAAAP//AAAECpAAAAAAAAAAB | AAAAAAAAAP//AAAECpAAAAAAAAAAB | AAAAAAAAAP//AAAECzAAAAAAAAAAD
1 | 1 | 2 | AAAAAAAAAP//AAAECpAAAAAAAAAAB | AAAAAAAAAP//AAAECpAAAAAAAAAAB | AAAAAAAAAP//AAAECzAAAAAAAAAAE
1 | 1 | 1 | AAAAAAAAAP//AAAECpAAAAAAAAAAB | AAAAAAAAAP//AAAECpAAAAAAAAAAB | AAAAAAAAAP//AAAECzAAAAAAAAAAF
1 | 1 | 2 | AAAAAAAAAP//AAAECpAAAAAAAAAAB | AAAAAAAAAP//AAAECpAAAAAAAAAAB | AAAAAAAAAP//AAAECzAAAAAAAAAAG
1 | 1 | 3 | AAAAAAAAAP//AAAECpAAAAAAAAAAB | AAAAAAAAAP//AAAECpAAAAAAAAAAB | AAAAAAAABP//AAAECzAAAAAAAAAAL
1 | 1 | 3 | AAAAAAAAAP//AAAECpAAAAAAAAAAB | AAAAAAAAAP//AAAECpAAAAAAAAAAB | AAAAAAAABP//AAAECzAAAAAAAAAAM
1 | 1 | 3 | AAAAAAAAAP//AAAECpAAAAAAAAAAB | AAAAAAAAAP//AAAECpAAAAAAAAAAB | AAAAAAAABP//AAAECzAAAAAAAAAAN
1 | 1 | 3 | AAAAAAAAAP//AAAECpAAAAAAAAAAB | AAAAAAAAAP//AAAECpAAAAAAAAAAB | AAAAAAAABP//AAAECzAAAAAAAAAAO
2 | 2 | | | AAAAAAAAAP//AAAECpAAAAAAAAAAC |
1 | 5 | | | AAAAAAAAAP//AAAECpAAAAAAAAAAD |
2 | 6 | | | AAAAAAAAAP//AAAECpAAAAAAAAAAE |
1 | 9 | | | AAAAAAAAAP//AAAECpAAAAAAAAAAF |
2 | 10 | | | AAAAAAAAAP//AAAECpAAAAAAAAAAG |
(19 rows)
ora=# EXPLAIN (costs off, verbose) SELECT *,t1.rowid, t2.rowid FROM t1 RIGHT JOIN t2 ON t1.rowid = t2.c;
WARNING: Statistics IN some tables OR columns(public.t2.a, public.t2.c, public.t1.a) are not collected.
HINT: Do analyze for them IN ORDER to generate optimized plan.
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
Streaming (TYPE: GATHER)
Output: t1.a, t1.c, t2.a, t2.c, t1.rowid, t2.rowid
Node/s: All datanodes
-> Hash Right Join
Output: t1.a, t1.c, t2.a, t2.c, t1.rowid, t2.rowid
Hash Cond: ((t1.rowid = (t2.c)::rowid) AND (t1.tableoid = rowid_tableoid((t2.c)::rowid)) AND (t1.rowno = rowid_sequence((t2.c)::rowid)))
Join Filter: ((-1)::smallint = rowid_hashbucketid((t2.c)::rowid))
-> Streaming(TYPE: BROADCAST)
Output: t1.a, t1.c, t1.rowid, t1.tableoid, t1.rowno
Spawn ON: All datanodes
Consumer Nodes: All datanodes
-> Index Scan using t1_rowno_idx ON public.t1
Output: t1.a, t1.c, t1.rowid, t1.tableoid, t1.rowno
Distribute Key: t1.a
-> Hash
Output: t2.a, t2.c, t2.rowid
-> Seq Scan ON public.t2
Output: t2.a, t2.c, t2.rowid
Distribute Key: t2.a
(19 rows)
ora=# SELECT *,t1.rowid, t2.rowid FROM t1 RIGHT JOIN t2 ON t1.rowid = t2.c;
a | c | a | c | rowid | rowid
---+---+---+-------------------------------+-------------------------------+-------------------------------
1 | 1 | 2 | AAAAAAAAAP//AAAECpAAAAAAAAAAB | AAAAAAAAAP//AAAECpAAAAAAAAAAB | AAAAAAAAAP//AAAECzAAAAAAAAAAG
1 | 1 | 1 | AAAAAAAAAP//AAAECpAAAAAAAAAAB | AAAAAAAAAP//AAAECpAAAAAAAAAAB | AAAAAAAAAP//AAAECzAAAAAAAAAAF
1 | 1 | 2 | AAAAAAAAAP//AAAECpAAAAAAAAAAB | AAAAAAAAAP//AAAECpAAAAAAAAAAB | AAAAAAAAAP//AAAECzAAAAAAAAAAE
1 | 1 | 1 | AAAAAAAAAP//AAAECpAAAAAAAAAAB | AAAAAAAAAP//AAAECpAAAAAAAAAAB | AAAAAAAAAP//AAAECzAAAAAAAAAAD
1 | 1 | 2 | AAAAAAAAAP//AAAECpAAAAAAAAAAB | AAAAAAAAAP//AAAECpAAAAAAAAAAB | AAAAAAAAAP//AAAECzAAAAAAAAAAC
1 | 1 | 1 | AAAAAAAAAP//AAAECpAAAAAAAAAAB | AAAAAAAAAP//AAAECpAAAAAAAAAAB | AAAAAAAAAP//AAAECzAAAAAAAAAAB
1 | 1 | 3 | AAAAAAAAAP//AAAECpAAAAAAAAAAB | AAAAAAAAAP//AAAECpAAAAAAAAAAB | AAAAAAAABP//AAAECzAAAAAAAAAAO
1 | 1 | 3 | AAAAAAAAAP//AAAECpAAAAAAAAAAB | AAAAAAAAAP//AAAECpAAAAAAAAAAB | AAAAAAAABP//AAAECzAAAAAAAAAAN
1 | 1 | 3 | AAAAAAAAAP//AAAECpAAAAAAAAAAB | AAAAAAAAAP//AAAECpAAAAAAAAAAB | AAAAAAAABP//AAAECzAAAAAAAAAAM
1 | 1 | 3 | AAAAAAAAAP//AAAECpAAAAAAAAAAB | AAAAAAAAAP//AAAECpAAAAAAAAAAB | AAAAAAAABP//AAAECzAAAAAAAAAAL
(10 rows)
ora=#
ora=# EXPLAIN (costs off, verbose) SELECT *,t1.rowid, t2.rowid FROM t1 FULL JOIN t2 ON t1.rowid = t2.c;
WARNING: Statistics IN some tables OR columns(public.t1.a, public.t2.a, public.t2.c) are not collected.
HINT: Do analyze for them IN ORDER to generate optimized plan.
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
Streaming (TYPE: GATHER)
Output: t1.a, t1.c, t2.a, t2.c, t1.rowid, t2.rowid
Node/s: All datanodes
-> Hash Full Join
Output: t1.a, t1.c, t2.a, t2.c, t1.rowid, t2.rowid
Inner Unique: true
Hash Cond: (((t2.c)::rowid = t1.rowid) AND (rowid_tableoid((t2.c)::rowid) = t1.tableoid) AND (rowid_sequence((t2.c)::rowid) = t1.rowno))
Join Filter: ((-1)::smallint = rowid_hashbucketid((t2.c)::rowid))
-> Streaming(TYPE: REDISTRIBUTE)
Output: t2.a, t2.c, t2.rowid, (rowid_sequence((t2.c)::rowid))
Distribute Key: (rowid_sequence((t2.c)::rowid))
Spawn ON: All datanodes
Consumer Nodes: All datanodes
-> Seq Scan ON public.t2
Output: t2.a, t2.c, t2.rowid, rowid_sequence((t2.c)::rowid)
Distribute Key: t2.a
-> Hash
Output: t1.a, t1.c, t1.rowid, t1.tableoid, t1.rowno
-> Streaming(TYPE: REDISTRIBUTE)
Output: t1.a, t1.c, t1.rowid, t1.tableoid, t1.rowno
Distribute Key: t1.rowno
Spawn ON: All datanodes
Consumer Nodes: All datanodes
-> Index Scan using t1_rowno_idx ON public.t1
Output: t1.a, t1.c, t1.rowid, t1.tableoid, t1.rowno
Distribute Key: t1.a
(26 rows)
ora=# SELECT *,t1.rowid, t2.rowid FROM t1 FULL JOIN t2 ON t1.rowid = t2.c;
a | c | a | c | rowid | rowid
---+----+---+-------------------------------+-------------------------------+-------------------------------
1 | 1 | 1 | AAAAAAAAAP//AAAECpAAAAAAAAAAB | AAAAAAAAAP//AAAECpAAAAAAAAAAB | AAAAAAAAAP//AAAECzAAAAAAAAAAB
1 | 1 | 2 | AAAAAAAAAP//AAAECpAAAAAAAAAAB | AAAAAAAAAP//AAAECpAAAAAAAAAAB | AAAAAAAAAP//AAAECzAAAAAAAAAAC
1 | 1 | 1 | AAAAAAAAAP//AAAECpAAAAAAAAAAB | AAAAAAAAAP//AAAECpAAAAAAAAAAB | AAAAAAAAAP//AAAECzAAAAAAAAAAD
1 | 1 | 2 | AAAAAAAAAP//AAAECpAAAAAAAAAAB | AAAAAAAAAP//AAAECpAAAAAAAAAAB | AAAAAAAAAP//AAAECzAAAAAAAAAAE
1 | 1 | 1 | AAAAAAAAAP//AAAECpAAAAAAAAAAB | AAAAAAAAAP//AAAECpAAAAAAAAAAB | AAAAAAAAAP//AAAECzAAAAAAAAAAF
1 | 1 | 2 | AAAAAAAAAP//AAAECpAAAAAAAAAAB | AAAAAAAAAP//AAAECpAAAAAAAAAAB | AAAAAAAAAP//AAAECzAAAAAAAAAAG
1 | 1 | 3 | AAAAAAAAAP//AAAECpAAAAAAAAAAB | AAAAAAAAAP//AAAECpAAAAAAAAAAB | AAAAAAAABP//AAAECzAAAAAAAAAAL
1 | 1 | 3 | AAAAAAAAAP//AAAECpAAAAAAAAAAB | AAAAAAAAAP//AAAECpAAAAAAAAAAB | AAAAAAAABP//AAAECzAAAAAAAAAAM
1 | 1 | 3 | AAAAAAAAAP//AAAECpAAAAAAAAAAB | AAAAAAAAAP//AAAECpAAAAAAAAAAB | AAAAAAAABP//AAAECzAAAAAAAAAAN
1 | 1 | 3 | AAAAAAAAAP//AAAECpAAAAAAAAAAB | AAAAAAAAAP//AAAECpAAAAAAAAAAB | AAAAAAAABP//AAAECzAAAAAAAAAAO
1 | 9 | | | AAAAAAAAAP//AAAECpAAAAAAAAAAF |
2 | 2 | | | AAAAAAAAAP//AAAECpAAAAAAAAAAC |
3 | 7 | | | AAAAAAAABP//AAAECpAAAAAAAAAAN |
3 | 4 | | | AAAAAAAABP//AAAECpAAAAAAAAAAM |
2 | 10 | | | AAAAAAAAAP//AAAECpAAAAAAAAAAG |
2 | 6 | | | AAAAAAAAAP//AAAECpAAAAAAAAAAE |
3 | 3 | | | AAAAAAAABP//AAAECpAAAAAAAAAAL |
3 | 8 | | | AAAAAAAABP//AAAECpAAAAAAAAAAO |
1 | 5 | | | AAAAAAAAAP//AAAECpAAAAAAAAAAD |
(19 rows)
ora=# -- 单节点表
ora=# CREATE TABLE t1_single(a int, c int) WITH (storage_type=astore, hasrowid=on) to group sys_single_group1;
CREATE TABLE
ora=# INSERT INTO t1_single VALUES(1, 1);
INSERT 0 1
ora=# INSERT INTO t1_single VALUES(2, 2);
INSERT 0 1
ora=# INSERT INTO t1_single VALUES(3, 3);
INSERT 0 1
ora=# INSERT INTO t1_single VALUES(3, 4);
INSERT 0 1
ora=# INSERT INTO t1_single VALUES(1, 5);
INSERT 0 1
ora=# INSERT INTO t1_single VALUES(2, 6);
INSERT 0 1
ora=# INSERT INTO t1_single VALUES(3, 7);
INSERT 0 1
ora=# INSERT INTO t1_single VALUES(3, 8);
INSERT 0 1
ora=# INSERT INTO t1_single VALUES(1, 9);
INSERT 0 1
ora=# INSERT INTO t1_single VALUES(2, 10);
INSERT 0 1
-- WHERE子句改写
ora=# EXPLAIN (costs off, verbose) SELECT * FROM t1_single WHERE rowid = func();
WARNING: Statistics in some tables or columns(public.t1_single) are not collected.
HINT: Do analyze for them in order to generate optimized plan.
QUERY PLAN
---------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------
Data Node Scan on t1_single "_REMOTE_TABLE_QUERY_"
Output: t1_single.a, t1_single.c
Node/s: (sys_single_group1) datanode1
Remote query: SELECT a, c, rowid, tableoid, rowno FROM ONLY public.t1_single WHERE true
Coordinator quals: ((t1_single.rowid = func()) AND ((-1)::smallint = rowid_hashbucketid(func())) AND (t1_sin
gle.tableoid = rowid_tableoid(func())) AND (t1_single.rowno = rowid_sequence(func())))
(5 rows)
-- 单节点表非等值场景改写
ora=# SET max_datanode_for_plan = 1;
SET
ora=# EXPLAIN verbose SELECT * FROM t1_single WHERE rowid > 'AAAAAAAAAP//AAAI/TAAAAAAAAAAB';
QUERY PLAN
--------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_LIGHT_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Output: t1_single.a, t1_single.c
Node/s: datanode1
Remote query: SELECT a, c FROM public.t1_single WHERE rowid > 'AAAAAAAAAP//AAAI/TAAAAAAAAAAB'::rowid
Remote SQL: SELECT a, c FROM public.t1_single WHERE rowid > 'AAAAAAAAAP//AAAI/TAAAAAAAAAAB'::rowid
Datanode Name: datanode1
[Bypass]
Index Scan using t1_single_rowno_idx on public.t1_single (cost=0.00..24.88 rows=716 width=8)
Output: a, c
Index Cond: (t1_single.rowno > 1::bigint)
(12 rows)
-- 清理数据
ora=# DROP TABLE t1;
DROP TABLE
ora=# DROP TABLE t2;
DROP TABLE
ora=# DROP TABLE t1_single;
DROP TABLE
ora=# DROP FUNCTION func;
DROP FUNCTION
ora=# \c postgres
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "postgres" as user "$user".
gaussdb=# DROP DATABASE ora;
DROP DATABASE ROWID系统列ORDER BY语句改写
对于ORDER BY rowid的场景，分布式下发到DN节点的SQL可改写为ORDER BY tableoid, rowno，如果为普通表、单节点表或者剪枝到单分区的场景，可进一步简化为ORDER BY rowno。rowid系统列排序规则是按照其包含的字段node_slice、table_oid、bucket_id、row_no依次比较排序。但rowid系统列本身并未建立索引，而rowno字段建立了唯一索引，因此对于普通表、单节点表或者剪枝到单分区的场景，ORDER BY rowid实际等效于ORDER BY rowno系统列，而rowno系统列上具有索引，使用上更高效，在两者排序等价场景下将ORDER BY rowid改写为ORDER BY rowno。
ROWID系统列ORDER BY语句改写，若SQL未下推至DN节点，不支持ORDER BY rowid改写；若SQL下推至DN节点执行时，遵从如下规格约束：
- 普通表和单节点表ORDER BY rowid单列场景下，改写为ORDER BY rowno系统列。
- 分区表和单节点分区表ORDER BY rowid单列场景下，改写为ORDER BY tableoid, rowno，若剪枝到单分区，则进一步改写为ORDER BY rowno。
- 普通表、单节点表或者分区表剪枝到单分区场景下，ORDER BY多列且其中一列为tableoid系统列时，支持去除冗余的ORDER BY tableoid（详情见下文介绍）。
- ORDER BY多列时，不支持ORDER BY rowid改写。
- 语句包含GROUP、DISTINCT时，不支持ORDER BY rowid改写。
- 聚集函数中的ORDER BY rowid不支持改写。
- 仅基表为物理表的支持改写，基表为子查询或CTE等不支持改写。
除ORDER BY rowid改写场景外，还支持对冗余tableoid系统列条件进行消除（包括用户添加的tableoid判断条件），满足以下条件时支持去除tableoid排序：
- 排序键大于或等于2个。
- 若SQL未下推至DN节点，则仅支持普通复制表、单节点表，以及可以裁剪到单分区的分区复制表和单节点分区表；若SQL下推至DN，则支持普通表、单节点表，或者分区表剪枝到单分区。
示例：
由于不同场景下tableoid字段值不同，以下示例仅作参考，执行时以实际输出结果为准。
以GUC参数explain_perf_mode设置为normal为例。
gaussdb=# CREATE DATABASE ora dbcompatibility 'ORA';
CREATE DATABASE
gaussdb=# \c ora
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "ora" as user "$user".
-- 普通表场景
ora=# SET max_datanode_for_plan = 64;
SET
ora=# SET cursor_sharing = force;
SET
ora=# SET dist_query_adaptive_mode=off;
SET
ora=# CREATE TABLE t2 (
ora(# c1 INTEGER,
ora(# c2 INTEGER,
ora(# c3 INTEGER
ora(# ) WITH (storage_type = astore, hasrowid);
NOTICE: The 'DISTRIBUTE BY' clause is not specified. Using 'c1' AS the distribution column BY default.
HINT: Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
CREATE TABLE
ora=# INSERT INTO t2 VALUES(generate_series(1,100),generate_series(1,100),generate_series(1,300));
INSERT 0 300
ora=# ANALYZE t2;
ANALYZE
ora=# SET enable_stream_operator=off;
SET
-- light proxy
ora=# SET enable_fast_query_shipping = on;
SET
ora=# SET enable_light_proxy = on;
SET
ora=# EXPLAIN (VERBOSE, COSTS FALSE) SELECT /*+ indexscan(t2) */ * FROM t2 WHERE c1 = 100 ORDER BY rowid;
QUERY PLAN
--------------------------------------------------------------------------------------------------
[Parameterized]
Data Node Scan on "__REMOTE_LIGHT_QUERY__"
Output: t2.c1, t2.c2, t2.c3
Node expr: $1
Remote query: SELECT/*+ IndexScan(t2)*/ c1, c2, c3 FROM public.t2 WHERE c1 = $1 ORDER BY rowid
Remote SQL: SELECT/*+ IndexScan(t2)*/ c1, c2, c3 FROM public.t2 WHERE c1 = 100 ORDER BY rowid
Datanode Name: datanode2
[Parameterized]
Index Scan using t2_rowno_idx on public.t2
Output: c1, c2, c3, rowid, tableoid, rowno
Filter: (t2.c1 = $1)
(13 rows)
ora=# SELECT /*+ indexscan(t2) */ rowid_nodeid(rowid),rowid_sequence(rowid),c3 FROM t2 WHERE c1 = 100 ORDER BY rowid LIMIT 1;
rowid_nodeid | rowid_sequence | c3
--------------+----------------+-----
1 | 108 | 100
(1 row)
-- FQS
ora=# SET enable_fast_query_shipping = on;
SET
ora=# SET enable_light_proxy = off;
SET
ora=# EXPLAIN (VERBOSE, COSTS FALSE) SELECT /*+ indexscan(t2) */ * FROM t2 WHERE c1 = 100 ORDER BY rowid;
QUERY PLAN
--------------------------------------------------------------------------------------------------
[Parameterized]
Data Node Scan on "__REMOTE_FQS_QUERY__"
Output: t2.c1, t2.c2, t2.c3
Node expr: $1
Remote query: SELECT/*+ IndexScan(t2)*/ c1, c2, c3 FROM public.t2 WHERE c1 = $1 ORDER BY rowid
Remote SQL: SELECT/*+ IndexScan(t2)*/ c1, c2, c3 FROM public.t2 WHERE c1 = 100 ORDER BY rowid
Datanode Name: datanode2
[Parameterized]
Index Scan using t2_rowno_idx on public.t2
Output: c1, c2, c3, rowid, tableoid, rowno
Filter: (t2.c1 = $1)
(13 rows)
ora=# SELECT /*+ indexscan(t2) */ rowid_nodeid(rowid),rowid_sequence(rowid),c3 FROM t2 WHERE c1 = 100 ORDER BY rowid LIMIT 1;
rowid_nodeid | rowid_sequence | c3
--------------+----------------+-----
1 | 108 | 100
(1 row)
-- stream
ora=# RESET enable_stream_operator;
RESET
ora=# SET enable_light_proxy = off;
SET
ora=# SET enable_fast_query_shipping = off;
SET
ora=# EXPLAIN (VERBOSE, COSTS FALSE) SELECT /*+ indexscan(t2) */ * FROM t2 ORDER BY rowid;
WARNING: unused hint: IndexScan(t2)
QUERY PLAN
-----------------------------------------
[Parameterized]
Streaming (type: GATHER)
Output: c1, c2, c3, rowid
Merge Sort Key: t2.rowid
Node/s: All datanodes
-> Sort
Output: c1, c2, c3, rowid
Sort Key: t2.rowid
-> Seq Scan on public.t2
Output: c1, c2, c3, rowid
Distribute Key: c1
(11 rows)
ora=# SELECT /*+ indexscan(t2) */ rowid_nodeid(rowid),rowid_sequence(rowid),c3 FROM t2 ORDER BY rowid LIMIT 1;
rowid_nodeid | rowid_sequence | c3
--------------+----------------+----
0 | 1 | 1
(1 row)
ora=#
ora=# RESET enable_light_proxy;
RESET
ora=# RESET enable_stream_operator;
RESET
ora=# RESET enable_fast_query_shipping;
RESET
-- 分区表场景
ora=# CREATE TABLE t2_p (
ora(# c1 INTEGER,
ora(# c2 INTEGER,
ora(# c3 INTEGER
ora(# ) WITH (storage_type = astore, hasrowid)
ora-# PARTITION BY RANGE (c2)
ora-# (
ora(# PARTITION p2023q1 VALUES LESS THAN (100),
ora(# PARTITION p2023q2 VALUES LESS THAN (200),
ora(# PARTITION p2023q3 VALUES LESS THAN (MAXVALUE)
ora(# );
NOTICE: The 'DISTRIBUTE BY' clause is not specified. Using 'c1' AS the distribution column BY default.
HINT: Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
CREATE TABLE
ora=# INSERT INTO t2_p VALUES(generate_series(1,200),generate_series(1,300),generate_series(1,600));
INSERT 0 600
ora=# ANALYZE t2_p;
ANALYZE
ora=#
ora=# EXPLAIN (VERBOSE, COSTS FALSE) SELECT /*+ indexscan(t2_p) */ * FROM t2_p ORDER BY rowid;
WARNING: unused hint: IndexScan(t2_p)
QUERY PLAN
-------------------------------------------------------
Streaming (type: GATHER)
Output: c1, c2, c3, rowid
Merge Sort Key: t2_p.rowid
Node/s: All datanodes
-> Sort
Output: c1, c2, c3, rowid
Sort Key: t2_p.rowid
-> Partition Iterator
Output: c1, c2, c3, rowid
Iterations: 3
-> Partitioned Seq Scan on public.t2_p
Output: c1, c2, c3, rowid
Distribute Key: c1
Selected Partitions: 1..3
(14 rows)
ora=# SET enable_stream_operator=off;
SET
-- light proxy
ora=# SET enable_fast_query_shipping = on;
SET
ora=# SET enable_light_proxy = on;
SET
ora=# EXPLAIN (VERBOSE, COSTS FALSE) SELECT /*+ indexscan(t2_p) */ * FROM t2_p WHERE c1=100 ORDER BY rowid;
QUERY PLAN
------------------------------------------------------------------------------------------------------
[Parameterized]
Data Node Scan on "__REMOTE_LIGHT_QUERY__"
Output: t2_p.c1, t2_p.c2, t2_p.c3
Node expr: $1
Remote query: SELECT/*+ IndexScan(t2_p)*/ c1, c2, c3 FROM public.t2_p WHERE c1 = $1 ORDER BY rowid
Remote SQL: SELECT/*+ IndexScan(t2_p)*/ c1, c2, c3 FROM public.t2_p WHERE c1 = 100 ORDER BY rowid
Datanode Name: datanode2
[Parameterized]
Sort
Output: c1, c2, c3, rowid, tableoid, rowno
Sort Key: t2_p.tableoid, t2_p.rowno
-> Partition Iterator
Output: c1, c2, c3, rowid, tableoid, rowno
Iterations: 3
-> Partitioned Seq Scan on public.t2_p
Output: c1, c2, c3, rowid, tableoid, rowno
Filter: (t2_p.c1 = $1)
Selected Partitions: 1..3
(20 rows)
ora=# SELECT /*+ indexscan(t2_p) */ rowid_nodeid(rowid),rowid_sequence(rowid),c3 FROM t2_p WHERE c1=100 ORDER BY rowid LIMIT 1;
rowid_nodeid | rowid_sequence | c3
--------------+----------------+-----
1 | 108 | 100
(1 row)
-- FQS
ora=# SET enable_fast_query_shipping = on;
SET
ora=# SET enable_light_proxy = off;
SET
ora=# EXPLAIN (VERBOSE, COSTS FALSE) SELECT /*+ indexscan(t2_p) */ * FROM t2_p ORDER BY rowid;--pgxc
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
[Parameterized]
Sort
Output: t2_p.c1, t2_p.c2, t2_p.c3, t2_p.rowid
Sort Key: t2_p.rowid
-> Data Node Scan on "__REMOTE_SORT_QUERY__"
Output: t2_p.c1, t2_p.c2, t2_p.c3, t2_p.rowid
Node/s: All datanodes
Remote query: SELECT/*+ IndexScan(t2_p)*/ c1, c2, c3, rowid FROM ONLY public.t2_p WHERE true ORDER BY 4
Remote SQL: SELECT/*+ IndexScan(t2_p)*/ c1, c2, c3, rowid FROM ONLY public.t2_p WHERE true ORDER BY 4
Datanode Name: datanode1
[Parameterized]
Sort
Output: c1, c2, c3, rowid, tableoid, rowno
Sort Key: t2_p.tableoid, t2_p.rowno
-> Result
Output: c1, c2, c3, rowid, tableoid, rowno
One-Time Filter: $1
-> Partition Iterator
Output: c1, c2, c3, rowid, tableoid, rowno
Iterations: 3
-> Partitioned Seq Scan on public.t2_p
Output: c1, c2, c3, rowid, tableoid, rowno
Selected Partitions: 1..3
Datanode Name: datanode2
[Parameterized]
Sort
Output: c1, c2, c3, rowid, tableoid, rowno
Sort Key: t2_p.tableoid, t2_p.rowno
-> Result
Output: c1, c2, c3, rowid, tableoid, rowno
One-Time Filter: $1
-> Partition Iterator
Output: c1, c2, c3, rowid, tableoid, rowno
Iterations: 3
-> Partitioned Seq Scan on public.t2_p
Output: c1, c2, c3, rowid, tableoid, rowno
Selected Partitions: 1..3
(40 rows)
ora=# EXPLAIN (VERBOSE, COSTS FALSE) SELECT /*+ indexscan(t2_p) */ * FROM t2_p WHERE c1=100 ORDER BY rowid;
QUERY PLAN
------------------------------------------------------------------------------------------------------
[Parameterized]
Data Node Scan on "__REMOTE_FQS_QUERY__"
Output: t2_p.c1, t2_p.c2, t2_p.c3
Node expr: $1
Remote query: SELECT/*+ IndexScan(t2_p)*/ c1, c2, c3 FROM public.t2_p WHERE c1 = $1 ORDER BY rowid
Remote SQL: SELECT/*+ IndexScan(t2_p)*/ c1, c2, c3 FROM public.t2_p WHERE c1 = 100 ORDER BY rowid
Datanode Name: datanode2
[Parameterized]
Sort
Output: c1, c2, c3, rowid, tableoid, rowno
Sort Key: t2_p.tableoid, t2_p.rowno
-> Partition Iterator
Output: c1, c2, c3, rowid, tableoid, rowno
Iterations: 3
-> Partitioned Seq Scan on public.t2_p
Output: c1, c2, c3, rowid, tableoid, rowno
Filter: (t2_p.c1 = $1)
Selected Partitions: 1..3
(20 rows)
ora=# SELECT /*+ indexscan(t2_p) */ rowid_nodeid(rowid),rowid_sequence(rowid),c3 FROM t2_p WHERE c1=100 ORDER BY rowid LIMIT 1;
rowid_nodeid | rowid_sequence | c3
--------------+----------------+-----
1 | 108 | 100
(1 row)
-- stream
ora=# RESET enable_stream_operator;
RESET
ora=# SET enable_light_proxy = off;
SET
ora=# SET enable_fast_query_shipping = off;
SET
ora=# EXPLAIN (VERBOSE, COSTS FALSE) SELECT /*+ indexscan(t2_p) */ * FROM t2_p ORDER BY rowid;
WARNING: unused hint: IndexScan(t2_p)
QUERY PLAN
-------------------------------------------------------
[Parameterized]
Streaming (type: GATHER)
Output: c1, c2, c3, rowid
Merge Sort Key: t2_p.rowid
Node/s: All datanodes
-> Sort
Output: c1, c2, c3, rowid
Sort Key: t2_p.rowid
-> Partition Iterator
Output: c1, c2, c3, rowid
Iterations: 3
-> Partitioned Seq Scan on public.t2_p
Output: c1, c2, c3, rowid
Distribute Key: c1
Selected Partitions: 1..3
(15 rows)
ora=# SELECT /*+ indexscan(t2_p) */ rowid_nodeid(rowid),rowid_sequence(rowid),c3 FROM t2_p ORDER BY rowid LIMIT 1;
rowid_nodeid | rowid_sequence | c3
--------------+----------------+----
0 | 1 | 1
(1 row)
ora=# EXPLAIN (VERBOSE, COSTS FALSE) SELECT /*+ indexscan(t2_p) */ * FROM t2_p WHERE c1=100 ORDER BY rowid;
WARNING: unused hint: IndexScan(t2_p)
QUERY PLAN
-------------------------------------------------------
[Parameterized]
Streaming (type: GATHER)
Output: c1, c2, c3, rowid
Node/s: datanode2
-> Sort
Output: c1, c2, c3, rowid
Sort Key: t2_p.rowid
-> Partition Iterator
Output: c1, c2, c3, rowid
Iterations: 3
-> Partitioned Seq Scan on public.t2_p
Output: c1, c2, c3, rowid
Distribute Key: c1
Filter: (t2_p.c1 = 100)
Selected Partitions: 1..3
(15 rows)
ora=# SELECT /*+ indexscan(t2_p) */ rowid_nodeid(rowid),rowid_sequence(rowid),c3 FROM t2_p WHERE c1=100 ORDER BY rowid LIMIT 1;
rowid_nodeid | rowid_sequence | c3
--------------+----------------+-----
1 | 108 | 100
(1 row)
ora=# EXPLAIN (VERBOSE, COSTS FALSE) SELECT /*+ indexscan(t2_p) */ * FROM t2_p WHERE c2=100 ORDER BY rowid;
WARNING: unused hint: IndexScan(t2_p)
QUERY PLAN
-------------------------------------------------
[Parameterized]
Streaming (type: GATHER)
Output: c1, c2, c3, rowid
Merge Sort Key: t2_p.rowid
Node/s: All datanodes
-> Sort
Output: c1, c2, c3, rowid
Sort Key: t2_p.rowid
-> Partitioned Seq Scan on public.t2_p
Output: c1, c2, c3, rowid
Distribute Key: c1
Filter: (t2_p.c2 = 100)
Selected Partitions: 2
(13 rows)
ora=# SELECT /*+ indexscan(t2_p) */ rowid_nodeid(rowid),rowid_sequence(rowid),c3 FROM t2_p WHERE c2=100 ORDER BY rowid LIMIT 1;
rowid_nodeid | rowid_sequence | c3
--------------+----------------+-----
0 | 402 | 400
(1 row)
ora=# EXPLAIN (VERBOSE, COSTS FALSE) SELECT /*+ indexscan(t2_p) */ * FROM t2_p WHERE c1=100 and c2=100 ORDER BY rowid;
WARNING: unused hint: IndexScan(t2_p)
QUERY PLAN
-------------------------------------------------------------
[Parameterized]
Streaming (type: GATHER)
Output: c1, c2, c3, rowid
Node/s: datanode2
-> Sort
Output: c1, c2, c3, rowid
Sort Key: t2_p.rowid
-> Partitioned Seq Scan on public.t2_p
Output: c1, c2, c3, rowid
Distribute Key: c1
Filter: ((t2_p.c1 = 100) AND (t2_p.c2 = 100))
Selected Partitions: 2
(12 rows)
ora=# SELECT /*+ indexscan(t2_p) */ rowid_nodeid(rowid),rowid_sequence(rowid),c3 FROM t2_p WHERE c1=100 and c2=100 ORDER BY rowid LIMIT 1;
rowid_nodeid | rowid_sequence | c3
--------------+----------------+-----
1 | 108 | 100
(1 row)
ora=# RESET enable_light_proxy;
RESET
ora=# RESET enable_stream_operator;
RESET
ora=# RESET enable_fast_query_shipping;
RESET
-- 冗余table_oid排序去除
ora=# SET enable_stream_operator=off;
SET
ora=# EXPLAIN (VERBOSE, COSTS FALSE) SELECT /*+ indexscan(t2) */ rowid,rowno FROM t2 ORDER BY tableoid,c1;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
[Parameterized]
Sort
Output: t2.rowid, t2.rowno, t2.tableoid, t2.c1
Sort Key: t2.tableoid, t2.c1
-> Data Node Scan on "__REMOTE_SORT_QUERY__"
Output: t2.rowid, t2.rowno, t2.tableoid, t2.c1
Node/s: All datanodes
Remote query: SELECT/*+ IndexScan(t2)*/ rowid, rowno, tableoid, c1 FROM ONLY public.t2 WHERE true ORDER BY 3, 4
Remote SQL: SELECT/*+ IndexScan(t2)*/ rowid, rowno, tableoid, c1 FROM ONLY public.t2 WHERE true ORDER BY 3, 4
Datanode Name: datanode1
[Parameterized]
Sort
Output: rowid, rowno, tableoid, c1
Sort Key: t2.c1
-> Result
Output: rowid, rowno, tableoid, c1
One-Time Filter: $1
-> Seq Scan on public.t2
Output: rowid, rowno, tableoid, c1
Datanode Name: datanode2
[Parameterized]
Sort
Output: rowid, rowno, tableoid, c1
Sort Key: t2.c1
-> Result
Output: rowid, rowno, tableoid, c1
One-Time Filter: $1
-> Seq Scan on public.t2
Output: rowid, rowno, tableoid, c1
(32 rows)
ora=# EXPLAIN (VERBOSE, COSTS FALSE) SELECT /*+ indexscan(t2_p) */ * FROM t2_p WHERE c2 = 90 ORDER BY tableoid,c1;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
[Parameterized]
Sort
Output: t2_p.c1, t2_p.c2, t2_p.c3, t2_p.tableoid
Sort Key: t2_p.tableoid, t2_p.c1
-> Data Node Scan on "__REMOTE_SORT_QUERY__"
Output: t2_p.c1, t2_p.c2, t2_p.c3, t2_p.tableoid
Node/s: All datanodes
Remote query: SELECT/*+ IndexScan(t2_p)*/ c1, c2, c3, tableoid FROM ONLY public.t2_p WHERE c2 = 90 ORDER BY 4, 1
Remote SQL: SELECT/*+ IndexScan(t2_p)*/ c1, c2, c3, tableoid FROM ONLY public.t2_p WHERE c2 = 90 ORDER BY 4, 1
Datanode Name: datanode1
[Parameterized]
Sort
Output: c1, c2, c3, tableoid
Sort Key: t2_p.c1
-> Partitioned Seq Scan on public.t2_p
Output: c1, c2, c3, tableoid
Filter: (t2_p.c2 = $1)
Selected Partitions: 1 (pbe-pruning)
Datanode Name: datanode2
[Parameterized]
Sort
Output: c1, c2, c3, tableoid
Sort Key: t2_p.c1
-> Partitioned Seq Scan on public.t2_p
Output: c1, c2, c3, tableoid
Filter: (t2_p.c2 = $1)
Selected Partitions: 1 (pbe-pruning)
(30 rows)
ora=# EXPLAIN (VERBOSE, COSTS FALSE) SELECT * FROM (SELECT /*+ indexscan(t2) */ rowid,* FROM t2 ORDER BY c2,tableoid);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
[Parameterized]
Subquery Scan on __unnamed_subquery__
Output: __unnamed_subquery__.rowid, __unnamed_subquery__.c1, __unnamed_subquery__.c2, __unnamed_subquery__.c3
-> Sort
Output: t2.rowid, t2.c1, t2.c2, t2.c3, t2.tableoid
Sort Key: t2.c2, t2.tableoid
-> Data Node Scan on "__REMOTE_SORT_QUERY__"
Output: t2.rowid, t2.c1, t2.c2, t2.c3, t2.tableoid
Node/s: All datanodes
Remote query: SELECT/*+ IndexScan(t2)*/ rowid, c1, c2, c3, tableoid FROM ONLY public.t2 WHERE true ORDER BY 3, 5
Remote SQL: SELECT/*+ IndexScan(t2)*/ rowid, c1, c2, c3, tableoid FROM ONLY public.t2 WHERE true ORDER BY 3, 5
Datanode Name: datanode1
[Parameterized]
Sort
Output: rowid, c1, c2, c3, tableoid
Sort Key: t2.c2
-> Result
Output: rowid, c1, c2, c3, tableoid
One-Time Filter: $1
-> Seq Scan on public.t2
Output: rowid, c1, c2, c3, tableoid
Datanode Name: datanode2
[Parameterized]
Sort
Output: rowid, c1, c2, c3, tableoid
Sort Key: t2.c2
-> Result
Output: rowid, c1, c2, c3, tableoid
One-Time Filter: $1
-> Seq Scan on public.t2
Output: rowid, c1, c2, c3, tableoid
(34 rows)
-- 复制表场景，ORDER BY未下推至DN，去除tableoid排序
ora=# CREATE TABLE t1 (
ora(# c1 INTEGER,
ora(# c2 INTEGER,
ora(# c3 INTEGER
ora(# ) WITH (storage_type = astore) DISTRIBUTE BY REPLICATION;
CREATE TABLE
ora=#
ora=# INSERT INTO t1 VALUES(generate_series(1,300),generate_series(1,300),generate_series(1,300));
INSERT 0 300
ora=# CREATE OR REPLACE FUNCTION func RETURN integer VOLATILE AS
ora$# BEGIN
ora$# RETURN 1;
ora$# END;
ora$# /
CREATE FUNCTION
ora=# SET enable_stream_operator=off;
SET
ora=# EXPLAIN (VERBOSE, COSTS FALSE) SELECT tableoid,func() FROM t1 ORDER BY tableoid, c1;
WARNING: Statistics in some tables OR columns(public.t1) are not collected.
HINT: Do ANALYZE for them in ORDER to generate optimized plan.
QUERY PLAN
--------------------------------------------------------------------------
Sort
Output: t1.tableoid, (func()), t1.c1
Sort Key: t1.c1
-> Data Node Scan on t1 "_REMOTE_TABLE_QUERY_"
Output: t1.tableoid, func(), t1.c1
Node/s: datanode1
Remote query: SELECT tableoid, c1 FROM ONLY public.t1 WHERE true
Remote SQL: SELECT tableoid, c1 FROM ONLY public.t1 WHERE true
Datanode Name: datanode1
Seq Scan on public.t1
Output: tableoid, c1
(13 rows)
ora=# RESET enable_stream_operator;
RESET
-- 单节点表
ora=# CREATE TABLE t1_single (
ora(# c1 INTEGER,
ora(# c2 INTEGER,
ora(# c3 INTEGER
ora(# ) WITH (storage_type = astore, hasrowid) to group sys_single_group1;
CREATE TABLE
ora=# EXPLAIN (verbose, costs false) SELECT /*+ indexscan(t2) */ * FROM t1_single WHERE c1 = 100 ORDER BY rowid;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------
-----
Data Node Scan on "__REMOTE_LIGHT_QUERY__"
Output: t1_single.c1, t1_single.c2, t1_single.c3
Node/s: datanode1
Remote query: SELECT/*+ IndexScan(t2)*/ c1, c2, c3 FROM public.t1_single WHERE c1 = 100 ORDER BY tableoid, r
owno
Remote SQL: SELECT/*+ IndexScan(t2)*/ c1, c2, c3 FROM public.t1_single WHERE c1 = 100 ORDER BY tableoid, rowno
Datanode Name: datanode1
Sort
Output: c1, c2, c3, tableoid, rowno
Sort Key: t1_single.rowno
-> Seq Scan on public.t1_single
Output: c1, c2, c3, tableoid, rowno
Filter: (t1_single.c1 = 100)
(14 rows)
-- 清理数据
ora=# DROP TABLE t1;
DROP TABLE
ora=# DROP TABLE t2;
DROP TABLE
ora=# DROP TABLE t2_p;
DROP TABLE
ora=# DROP TABLE t1_single;
DROP TABLE
ora=# \c postgres
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "postgres" as user "$user".
gaussdb=# DROP DATABASE ora;
DROP DATABASE ROWID系统列max/min优化
对于普通表场景，由于ROWID到rowno的映射严格保序，min/max聚集函数条件为ROWID单列时，可改写为rowno系统列的表达式进行优化。利用索引的有序性，将min/max对应的聚集操作优化为LIMIT和索引扫描，减少数据读取和计算开销。形如“SELECT max(rowid) FROM t1”的SQL语句遵循如下改写规则：
SELECT rowid FROM t1 ORDER BY rowno DESC LIMIT 1
ROWID系统列max/min优化遵从如下规格约束：
- 普通表场景仅支持SQL语句下推至DN时改写，单节点表在此基础上也支持执行stream计划时的改写，不支持分区表等。
- 当关闭GUC参数enable_indexscan时或者有其他不能进行rowno列索引扫描的场景时，该优化默认不生效。enable_indexscan参数详情可见《参考》中“数据库运行参数说明 > GUC参数说明 > 查询规划 > 优化器方法配置”章节内容。
示例：
由于不同场景下tableoid字段值不同，以下示例仅作参考，执行时以实际输出结果为准。
以GUC参数explain_perf_mode设置为normal为例。
gaussdb=# CREATE DATABASE ora dbcompatibility 'ORA';
CREATE DATABASE
gaussdb=# \c ora
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "ora" as user "$user".
-- 准备数据
ora=# SET max_datanode_for_plan = 64;
SET
ora=# SET cursor_sharing = force;
SET
ora=# SET dist_query_adaptive_mode=off;
SET
ora=# CREATE TABLE t1 (a int, b int) WITH (STORAGE_TYPE=Astore, hasrowid=ON);
NOTICE: The 'DISTRIBUTE BY' clause is not specified. Using 'a' as the distribution column by default.
HINT: Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
CREATE TABLE
ora=# INSERT INTO t1 VALUES ( generate_series(1,1000), generate_series(3001,4000));
INSERT 0 1000
ora=# INSERT INTO t1 VALUES ( generate_series(1001,2000), generate_series(3001,4000));
INSERT 0 1000
ora=# CREATE INDEX idx_t1_b ON t1(b);
CREATE INDEX
ora=# ANALYZE t1;
ANALYZE
-- max/min优化场景
ora=# SET enable_stream_operator=off;
SET
ora=# SET enable_fast_query_shipping = ON;
SET
ora=# SET enable_light_proxy = ON;
SET
ora=# EXPLAIN (VERBOSE, COSTS FALSE) SELECT /*+ indexscan(t1) */ max(rowid) FROM t1;
QUERY PLAN
-------------------------------------------------------------------------------------------
[Parameterized]
Aggregate
Output: max((max(t1.rowid)))
-> Data Node Scan ON "__REMOTE_GROUP_QUERY__"
Output: (max(t1.rowid))
Node/s: All datanodes
Remote query: SELECT/*+ IndexScan(t1)*/ max(rowid) FROM ONLY public.t1 WHERE true
Remote SQL: SELECT/*+ IndexScan(t1)*/ max(rowid) FROM ONLY public.t1 WHERE true
Datanode Name: datanode1
[Parameterized]
Result
Output: $0
InitPlan 1 (returns $0)
-> Limit
Output: public.t1.rowid, public.t1.rowno
-> Result
Output: public.t1.rowid, public.t1.rowno
One-Time Filter: $1
-> Index Scan Backward using t1_rowno_idx ON public.t1
Output: public.t1.rowid, public.t1.rowno
Datanode Name: datanode2
[Parameterized]
Result
Output: $0
InitPlan 1 (returns $0)
-> Limit
Output: public.t1.rowid, public.t1.rowno
-> Result
Output: public.t1.rowid, public.t1.rowno
One-Time Filter: $1
-> Index Scan Backward using t1_rowno_idx ON public.t1
Output: public.t1.rowid, public.t1.rowno
(35 rows)
ora=# SELECT /*+ indexscan(t1) */ rowid_sequence(max(rowid)) FROM t1;
rowid_sequence
----------------
2004
(1 row)
ora=# EXPLAIN (VERBOSE, COSTS FALSE) SELECT /*+ indexscan(t1) */ min(rowid) FROM t1;
QUERY PLAN
-------------------------------------------------------------------------------------------
[Parameterized]
Aggregate
Output: min((min(t1.rowid)))
-> Data Node Scan ON "__REMOTE_GROUP_QUERY__"
Output: (min(t1.rowid))
Node/s: All datanodes
Remote query: SELECT/*+ IndexScan(t1)*/ min(rowid) FROM ONLY public.t1 WHERE true
Remote SQL: SELECT/*+ IndexScan(t1)*/ min(rowid) FROM ONLY public.t1 WHERE true
Datanode Name: datanode1
[Parameterized]
Result
Output: $0
InitPlan 1 (returns $0)
-> Limit
Output: public.t1.rowid, public.t1.rowno
-> Result
Output: public.t1.rowid, public.t1.rowno
One-Time Filter: $1
-> Index Scan using t1_rowno_idx ON public.t1
Output: public.t1.rowid, public.t1.rowno
Datanode Name: datanode2
[Parameterized]
Result
Output: $0
InitPlan 1 (returns $0)
-> Limit
Output: public.t1.rowid, public.t1.rowno
-> Result
Output: public.t1.rowid, public.t1.rowno
One-Time Filter: $1
-> Index Scan using t1_rowno_idx ON public.t1
Output: public.t1.rowid, public.t1.rowno
(35 rows)
ora=# SELECT /*+ indexscan(t1) */ rowid_sequence(min(rowid)) FROM t1; -- 1
rowid_sequence
----------------
1
(1 row)
-- 多个max/min使用场景，支持优化
ora=# EXPLAIN (VERBOSE, COSTS FALSE) SELECT /*+ indexscan(t1) */ rowid_sequence(max(rowid)),max(b) FROM t1;
QUERY PLAN
---------------------------------------------------------------------------------------------------
[Parameterized]
Aggregate
Output: rowid_sequence(max((max(t1.rowid)))), max((max(t1.b)))
-> Data Node Scan ON "__REMOTE_GROUP_QUERY__"
Output: (max(t1.rowid)), (max(t1.b))
Node/s: All datanodes
Remote query: SELECT/*+ IndexScan(t1)*/ max(rowid), max(b) FROM ONLY public.t1 WHERE true
Remote SQL: SELECT/*+ IndexScan(t1)*/ max(rowid), max(b) FROM ONLY public.t1 WHERE true
Datanode Name: datanode1
[Parameterized]
Result
Output: $0, $1
InitPlan 1 (returns $0)
-> Limit
Output: public.t1.rowid, public.t1.rowno
-> Result
Output: public.t1.rowid, public.t1.rowno
One-Time Filter: $1
-> Index Scan Backward using t1_rowno_idx ON public.t1
Output: public.t1.rowid, public.t1.rowno
InitPlan 2 (returns $1)
-> Limit
Output: public.t1.b
-> Result
Output: public.t1.b
One-Time Filter: $1
-> Index Only Scan Backward using idx_t1_b ON public.t1
Output: public.t1.b
Index Cond: (public.t1.b IS NOT NULL)
Datanode Name: datanode2
[Parameterized]
Result
Output: $0, $1
InitPlan 1 (returns $0)
-> Limit
Output: public.t1.rowid, public.t1.rowno
-> Result
Output: public.t1.rowid, public.t1.rowno
One-Time Filter: $1
-> Index Scan Backward using t1_rowno_idx ON public.t1
Output: public.t1.rowid, public.t1.rowno
InitPlan 2 (returns $1)
-> Limit
Output: public.t1.b
-> Result
Output: public.t1.b
One-Time Filter: $1
-> Index Only Scan Backward using idx_t1_b ON public.t1
Output: public.t1.b
Index Cond: (public.t1.b IS NOT NULL)
(53 rows)
ora=# SELECT /*+ indexscan(t1) */ rowid_sequence(max(rowid)),max(b) FROM t1;
rowid_sequence | max
----------------+------
2004 | 4000
(1 row)
ora=# EXPLAIN (VERBOSE, COSTS FALSE) SELECT /*+ indexscan(t1) */ rowid_sequence(max(rowid)),max(rowno) FROM t1;
QUERY PLAN
-------------------------------------------------------------------------------------------------------
[Parameterized]
Aggregate
Output: rowid_sequence(max((max(t1.rowid)))), max((max(t1.rowno)))
-> Data Node Scan ON "__REMOTE_GROUP_QUERY__"
Output: (max(t1.rowid)), (max(t1.rowno))
Node/s: All datanodes
Remote query: SELECT/*+ IndexScan(t1)*/ max(rowid), max(rowno) FROM ONLY public.t1 WHERE true
Remote SQL: SELECT/*+ IndexScan(t1)*/ max(rowid), max(rowno) FROM ONLY public.t1 WHERE true
Datanode Name: datanode1
[Parameterized]
Result
Output: $0, $1
InitPlan 1 (returns $0)
-> Limit
Output: public.t1.rowid, public.t1.rowno
-> Result
Output: public.t1.rowid, public.t1.rowno
One-Time Filter: $1
-> Index Scan Backward using t1_rowno_idx ON public.t1
Output: public.t1.rowid, public.t1.rowno
InitPlan 2 (returns $1)
-> Limit
Output: public.t1.rowno
-> Result
Output: public.t1.rowno
One-Time Filter: $1
-> Index Only Scan Backward using t1_rowno_idx ON public.t1
Output: public.t1.rowno
Datanode Name: datanode2
[Parameterized]
Result
Output: $0, $1
InitPlan 1 (returns $0)
-> Limit
Output: public.t1.rowid, public.t1.rowno
-> Result
Output: public.t1.rowid, public.t1.rowno
One-Time Filter: $1
-> Index Scan Backward using t1_rowno_idx ON public.t1
Output: public.t1.rowid, public.t1.rowno
InitPlan 2 (returns $1)
-> Limit
Output: public.t1.rowno
-> Result
Output: public.t1.rowno
One-Time Filter: $1
-> Index Only Scan Backward using t1_rowno_idx ON public.t1
Output: public.t1.rowno
(51 rows)
ora=# SELECT /*+ indexscan(t1) */ rowid_sequence(max(rowid)),max(rowno) FROM t1;
rowid_sequence | max
----------------+------
2004 | 2004
(1 row)
-- 关闭enable_indexscan参数场景，不支持max/min优化
ora=# SET enable_indexscan = off;
SET
ora=# EXPLAIN (VERBOSE, COSTS FALSE) SELECT /*+ indexscan(t1) */ max(rowid) FROM t1;
QUERY PLAN
-------------------------------------------------------------------------------------------
[Parameterized]
Aggregate
Output: max((max(t1.rowid)))
-> Data Node Scan ON "__REMOTE_GROUP_QUERY__"
Output: (max(t1.rowid))
Node/s: All datanodes
Remote query: SELECT/*+ IndexScan(t1)*/ max(rowid) FROM ONLY public.t1 WHERE true
Remote SQL: SELECT/*+ IndexScan(t1)*/ max(rowid) FROM ONLY public.t1 WHERE true
Datanode Name: datanode1
[Parameterized]
Aggregate
Output: max(rowid)
-> Result
Output: rowid
One-Time Filter: $1
-> Seq Scan ON public.t1
Output: rowid
Datanode Name: datanode2
[Parameterized]
Aggregate
Output: max(rowid)
-> Result
Output: rowid
One-Time Filter: $1
-> Seq Scan ON public.t1
Output: rowid
(29 rows)
ora=# SET enable_indexscan = ON;
SET
-- complex SQL，不支持max/min优化
ora=# EXPLAIN (VERBOSE, COSTS FALSE) SELECT /*+ indexscan(t1) */ rowid_sequence(max(rowid)) FROM (SELECT rowid,a FROM t1 WHERE a = 1);
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
[Parameterized]
Data Node Scan ON "__REMOTE_LIGHT_QUERY__"
Output: (rowid_sequence(max(__unnamed_subquery__.rowid)))
Node expr: $1
Remote query: SELECT/*+ IndexScan(t1)*/ rowid_sequence(max(rowid)) AS rowid_sequence FROM (SELECT t1.rowid, t1.a FROM public.t1 WHERE t1.a = $1) __unnamed_subquery__
Remote SQL: SELECT/*+ IndexScan(t1)*/ rowid_sequence(max(rowid)) AS rowid_sequence FROM (SELECT t1.rowid, t1.a FROM public.t1 WHERE t1.a = 1) __unnamed_subquery__
Datanode Name: datanode1
[Parameterized]
Aggregate
Output: rowid_sequence(max(t1.rowid))
-> Seq Scan ON public.t1
Output: t1.rowid
Filter: (t1.a = $1)
(15 rows)
-- 分区表场景，不支持max/min优化
ora=# CREATE TABLE t2
ora-# ( a int,
ora(# b int,
ora(# c int)
ora-# WITH (STORAGE_TYPE=Astore, hasrowid=ON)
ora-# PARTITION BY RANGE (a)
ora-# (
ora(# PARTITION P1 VALUES LESS THAN(100),
ora(# PARTITION P2 VALUES LESS THAN(200),
ora(# PARTITION P3 VALUES LESS THAN(300),
ora(# PARTITION P8 VALUES LESS THAN(MAXVALUE)
ora(# ) ;
NOTICE: The 'DISTRIBUTE BY' clause is not specified. Using 'a' as the distribution column by default.
HINT: Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
CREATE TABLE
ora=# INSERT INTO t2 VALUES ( generate_series(1,1000), generate_series(1001,2000));
INSERT 0 1000
ora=# ANALYZE t2;
ANALYZE
ora=# EXPLAIN (VERBOSE, COSTS FALSE) SELECT /*+ indexscan(t2) */ max(rowid) FROM t2;
QUERY PLAN
-------------------------------------------------------------------------------------------
[Parameterized]
Aggregate
Output: max((max(t2.rowid)))
-> Data Node Scan ON "__REMOTE_GROUP_QUERY__"
Output: (max(t2.rowid))
Node/s: All datanodes
Remote query: SELECT/*+ IndexScan(t2)*/ max(rowid) FROM ONLY public.t2 WHERE true
Remote SQL: SELECT/*+ IndexScan(t2)*/ max(rowid) FROM ONLY public.t2 WHERE true
Datanode Name: datanode1
[Parameterized]
Aggregate
Output: max(rowid)
-> Result
Output: rowid
One-Time Filter: $1
-> Partition Iterator
Output: rowid
Iterations: 4
-> Partitioned Seq Scan ON public.t2
Output: rowid
Selected Partitions: 1..4
Datanode Name: datanode2
[Parameterized]
Aggregate
Output: max(rowid)
-> Result
Output: rowid
One-Time Filter: $1
-> Partition Iterator
Output: rowid
Iterations: 4
-> Partitioned Seq Scan ON public.t2
Output: rowid
Selected Partitions: 1..4
(37 rows)
ora=# EXPLAIN (VERBOSE, COSTS FALSE) SELECT /*+ indexscan(t2) */ max(rowid) FROM t2 WHERE a = 1;
QUERY PLAN
-----------------------------------------------------------------------------------------
[Parameterized]
Data Node Scan ON "__REMOTE_LIGHT_QUERY__"
Output: (max(t2.rowid))
Node expr: $1
Remote query: SELECT/*+ IndexScan(t2)*/ max(rowid) AS max FROM public.t2 WHERE a = $1
Remote SQL: SELECT/*+ IndexScan(t2)*/ max(rowid) AS max FROM public.t2 WHERE a = 1
Datanode Name: datanode1
[Parameterized]
Aggregate
Output: max(rowid)
-> Partitioned Seq Scan ON public.t2
Output: rowid
Filter: (t2.a = $1)
Selected Partitions: 1 (pbe-pruning)
(16 rows)
-- 单节点表
ora=# CREATE TABLE t1_single (a int, b int) WITH (STORAGE_TYPE=Astore, hasrowid=on) to group sys_single_group1;
CREATE TABLE
ora=# INSERT INTO t1_single VALUES ( generate_series(1,1000), generate_series(3001,4000));
INSERT 0 1000
ora=# INSERT INTO t1_single VALUES ( generate_series(1001,2000), generate_series(3001,4000));
INSERT 0 1000
ora=# EXPLAIN (verbose, costs false) SELECT /*+ indexscan(t1_single) */ max(rowid) FROM t1_single;
QUERY PLAN
------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_LIGHT_QUERY__"
Output: (max(t1_single.rowid))
Node/s: datanode1
Remote query: SELECT/*+ IndexScan(t1_single)*/ max(rowid) AS max FROM public.t1_single
Remote SQL: SELECT/*+ IndexScan(t1_single)*/ max(rowid) AS max FROM public.t1_single
Datanode Name: datanode1
Result
Output: $0
InitPlan 1 (returns $0)
-> Limit
Output: public.t1_single.rowid, public.t1_single.rowno
-> Index Scan Backward using t1_single_rowno_idx on public.t1_single
Output: public.t1_single.rowid, public.t1_single.rowno
(15 rows)
-- 清理数据
ora=# DROP TABLE t2;
DROP TABLE
ora=# DROP TABLE t1;
DROP TABLE
ora=# DROP TABLE t1_single;
DROP TABLE
ora=# \c postgres
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "postgres" as user "$user".
gaussdb=# DROP DATABASE ora;
DROP DATABASE ROWID系统列分布剪枝和分区剪枝
分布剪枝在ROWID系统列作为等值谓词条件的场景下触发，可将查询裁剪至唯一的单个DN节点。在分布式静态剪枝阶段，系统会在分布键剪枝之前优先执行ROWID剪枝。在获取ROWID列谓词后，对于能够明确裁剪至单DN节点的场景，直接计算下推节点；对于外部参数场景，则保存相关参数，并在动态剪枝阶段再计算下推节点；对于不支持的场景，则进行常规分布键剪枝。
分布剪枝与当前分布列剪枝遵循相同的规格约束，如下：
- 仅支持ROWID作为等值表达式或者NULL表达式。
- 对于布尔类型组合场景，仅对AND表达式中首个ROWID条件剪枝，不对OR表达式剪枝。
- 仅支持常量（静态剪枝）、参数（动态剪枝）两种场景的剪枝。
- 静态剪枝支持4种分布式计划，动态剪枝只支持CN轻量化、FQS和PGXC计划。
- 分布剪枝不支持函数。
- 当分布剪枝涉及的列或条件存在类型转换时，仅支持隐式二进制强制转换，不支持函数转换或显式转换。
在触发ROWID系统列谓词条件改写，且改写后条件包含tableoid等值判断的场景下，可进行分区剪枝裁剪到唯一单分区。同时支持常量和参数条件，对于ROWID常量条件触发静态剪枝，对于ROWID参数条件触发动态剪枝。更多分区剪枝详情请参见分区剪枝章节内容。
示例：
分布剪枝示例如下：
由于不同场景下tableoid字段值不同，以下示例仅作参考，执行时以实际输出结果为准。
以GUC参数explain_perf_mode设置为normal为例。
-- 数据准备
gaussdb=# CREATE DATABASE ora dbcompatibility 'ORA';
CREATE DATABASE
gaussdb=# \c ora
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "ora" as user "$user".
ora=# SET max_datanode_for_plan = 1;
SET
ora=# SET query_dop=1;
SET
ora=# SET plan_cache_mode = force_custom_plan;
SET
ora=# SET auto_explain_level=notice;
SET
ora=# CREATE TABLE t1 (c1 int, c2 int) WITH (storage_type=astore, hasrowid=on);
NOTICE: The 'DISTRIBUTE BY' clause is not specified. Using 'c1' as the distribution column by default.
HINT: Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
CREATE TABLE
ora=# INSERT INTO t1 SELECT generate_series(1,3), generate_series(1,4);
INSERT 0 12
ora=# ANALYZE t1;
ANALYZE
-- CN轻量化
-- 常量表达式静态剪枝
ora=# EXPLAIN (costs off, verbose on) SELECT * FROM t1 WHERE rowid = 'AAAAAAAAAP//AAAEEOAAAAAAAAAAE'; QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_LIGHT_QUERY__"
Output: t1.c1, t1.c2
Node/s: datanode1
Remote query: SELECT c1, c2 FROM public.t1 WHERE rowid = 'AAAAAAAAAP//AAAEEOAAAAAAAAAAE'::rowid AND (-1)::smallint = rowid_hashbucketid('AAAAAAAAAP//AAAEEOAAAAAAAAAAE'::rowid) AND tableoid = rowid_tab
leoid('AAAAAAAAAP//AAAEEOAAAAAAAAAAE'::rowid) AND rowno = rowid_sequence('AAAAAAAAAP//AAAEEOAAAAAAAAAAE'::rowid)
Remote SQL: SELECT c1, c2 FROM public.t1 WHERE rowid = 'AAAAAAAAAP//AAAEEOAAAAAAAAAAE'::rowid AND (-1)::smallint = rowid_hashbucketid('AAAAAAAAAP//AAAEEOAAAAAAAAAAE'::rowid) AND tableoid = rowid_tableoi
d('AAAAAAAAAP//AAAEEOAAAAAAAAAAE'::rowid) AND rowno = rowid_sequence('AAAAAAAAAP//AAAEEOAAAAAAAAAAE'::rowid)
Datanode Name: datanode1
Index Scan using t1_rowno_idx on public.t1
Output: c1, c2
Index Cond: (t1.rowno = 4::bigint)
Filter: ((t1.rowid = 'AAAAAAAAAP//AAAEEOAAAAAAAAAAE'::rowid) AND (t1.tableoid = 16654::oid))
(12 rows)
-- 参数表达式动态剪枝
ora=# SET plan_cache_mode = force_generic_plan;
SET
ora=# PREPARE p1 AS SELECT * FROM t1 WHERE rowid = $1;
PREPARE
ora=# EXPLAIN (costs off, verbose on) EXECUTE p1('AAAAAAAAAP//AAAEEOAAAAAAAAAAE');
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__"
Output: t1.c1, t1.c2
Node/s: datanode1
Node expr: $1
Remote query: SELECT c1, c2 FROM public.t1 WHERE rowid = $1 AND (-1)::smallint = rowid_hashbucketid($1) AND tableoid = rowid_tableoid($1) AND rowno = rowid_sequence($1)
Remote SQL: SELECT c1, c2 FROM public.t1 WHERE rowid = $1 AND (-1)::smallint = rowid_hashbucketid($1) AND tableoid = rowid_tableoid($1) AND rowno = rowid_sequence($1)
Datanode Name: datanode1
Result
Output: c1, c2
One-Time Filter: ((-1)::smallint = rowid_hashbucketid($1))
-> Index Scan using t1_rowno_idx on public.t1
Output: c1, c2
Index Cond: (t1.rowno = rowid_sequence($1))
Filter: ((t1.rowid = $1) AND (t1.tableoid = rowid_tableoid($1)))
(16 rows)
ora=# EXECUTE p1('AAAAAAAAAP//AAAEEOAAAAAAAAAAE');
c1 | c2
----+----
2 | 1
(1 row)
ora=# DEALLOCATE p1;
DEALLOCATE
-- AND表达式剪枝首个ROWID表达式，静态剪枝
ora=# SET plan_cache_mode = force_custom_plan;
SET
ora=# EXPLAIN (costs off, verbose on) SELECT * FROM t1 WHERE rowid IS NOT NULL AND c1 = 1;
QUERY PLAN
---------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_LIGHT_QUERY__"
Output: t1.c1, t1.c2
Node/s: datanode1
Remote query: SELECT c1, c2 FROM public.t1 WHERE rowid IS NOT NULL AND c1 = 1
Remote SQL: SELECT c1, c2 FROM public.t1 WHERE rowid IS NOT NULL AND c1 = 1
Datanode Name: datanode1
Seq Scan on public.t1
Output: c1, c2
Filter: (t1.c1 = 1)
(11 rows)
ora=# SELECT * FROM t1 WHERE rowid IS NOT NULL AND c1 = 1;
c1 | c2
----+----
1 | 1
1 | 4
1 | 3
1 | 2
(4 rows)
-- OR表达式不支持剪枝
ora=# EXPLAIN (costs off, verbose on) SELECT * FROM t1 WHERE rowid IS NOT NULL OR c1 = 1;
QUERY PLAN
--------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__"
Output: t1.c1, t1.c2
Node/s: All datanodes
Remote query: SELECT c1, c2 FROM public.t1 WHERE rowid IS NOT NULL OR c1 = 1
Remote SQL: SELECT c1, c2 FROM public.t1 WHERE rowid IS NOT NULL OR c1 = 1
Datanode Name: datanode1
Seq Scan on public.t1
Output: c1, c2
(10 rows)
ora=# SELECT * FROM t1 WHERE rowid IS NOT NULL OR c1 = 1;
c1 | c2
----+----
1 | 1
2 | 2
1 | 4
2 | 1
1 | 3
2 | 4
1 | 2
2 | 3
3 | 3
3 | 2
3 | 1
3 | 4
(12 rows)
-- FQS计划
ora=# SET enable_light_proxy = off;
SET
ora=# SET plan_cache_mode = force_custom_plan;
SET
-- 常量表达式静态剪枝
ora=# EXPLAIN (costs off, verbose on) SELECT * FROM t1 WHERE rowid = 'AAAAAAAAAP//AAAEEOAAAAAAAAAAE'; QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__"
Output: t1.c1, t1.c2
Node/s: datanode1
Remote query: SELECT c1, c2 FROM public.t1 WHERE rowid = 'AAAAAAAAAP//AAAEEOAAAAAAAAAAE'::rowid AND (-1)::smallint = rowid_hashbucketid('AAAAAAAAAP//AAAEEOAAAAAAAAAAE'::rowid) AND tableoid = rowid_tab
leoid('AAAAAAAAAP//AAAEEOAAAAAAAAAAE'::rowid) AND rowno = rowid_sequence('AAAAAAAAAP//AAAEEOAAAAAAAAAAE'::rowid)
Remote SQL: SELECT c1, c2 FROM public.t1 WHERE rowid = 'AAAAAAAAAP//AAAEEOAAAAAAAAAAE'::rowid AND (-1)::smallint = rowid_hashbucketid('AAAAAAAAAP//AAAEEOAAAAAAAAAAE'::rowid) AND tableoid = rowid_tableoi
d('AAAAAAAAAP//AAAEEOAAAAAAAAAAE'::rowid) AND rowno = rowid_sequence('AAAAAAAAAP//AAAEEOAAAAAAAAAAE'::rowid)
Datanode Name: datanode1
Index Scan using t1_rowno_idx on public.t1
Output: c1, c2
Index Cond: (t1.rowno = 4::bigint)
Filter: ((t1.rowid = 'AAAAAAAAAP//AAAEEOAAAAAAAAAAE'::rowid) AND (t1.tableoid = 16654::oid))
(12 rows)
-- 参数表达式动态剪枝
ora=# SET plan_cache_mode = force_generic_plan;
SET
ora=# PREPARE p1 AS SELECT * FROM t1 WHERE rowid = $1;
PREPARE
ora=# EXPLAIN (costs off, verbose on) EXECUTE p1('AAAAAAAAAP//AAAEEOAAAAAAAAAAE');
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__"
Output: t1.c1, t1.c2
Node/s: datanode1
Node expr: $1
Remote query: SELECT c1, c2 FROM public.t1 WHERE rowid = $1 AND (-1)::smallint = rowid_hashbucketid($1) AND tableoid = rowid_tableoid($1) AND rowno = rowid_sequence($1)
Remote SQL: SELECT c1, c2 FROM public.t1 WHERE rowid = $1 AND (-1)::smallint = rowid_hashbucketid($1) AND tableoid = rowid_tableoid($1) AND rowno = rowid_sequence($1)
Datanode Name: datanode1
Result
Output: c1, c2
One-Time Filter: ((-1)::smallint = rowid_hashbucketid($1))
-> Index Scan using t1_rowno_idx on public.t1
Output: c1, c2
Index Cond: (t1.rowno = rowid_sequence($1))
Filter: ((t1.rowid = $1) AND (t1.tableoid = rowid_tableoid($1)))
(16 rows)
ora=# EXECUTE p1('AAAAAAAAAP//AAAEEOAAAAAAAAAAE');
c1 | c2
----+----
2 | 1
(1 row)
ora=# DEALLOCATE p1;
DEALLOCATE
-- AND表达式剪枝首个ROWID表达式，静态剪枝
ora=# SET plan_cache_mode = force_custom_plan;
SET
ora=# EXPLAIN (costs off, verbose on) SELECT * FROM t1 WHERE rowid IS NOT NULL AND c1 = 1;
QUERY PLAN
---------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__"
Output: t1.c1, t1.c2
Node/s: datanode1
Remote query: SELECT c1, c2 FROM public.t1 WHERE rowid IS NOT NULL AND c1 = 1
Remote SQL: SELECT c1, c2 FROM public.t1 WHERE rowid IS NOT NULL AND c1 = 1
Datanode Name: datanode1
Seq Scan on public.t1
Output: c1, c2
Filter: (t1.c1 = 1)
(11 rows)
ora=# SELECT * FROM t1 WHERE rowid IS NOT NULL AND c1 = 1;
c1 | c2
----+----
1 | 1
1 | 4
1 | 3
1 | 2
(4 rows)
-- OR表达式不支持剪枝
ora=# EXPLAIN (costs off, verbose on) SELECT * FROM t1 WHERE rowid IS NOT NULL OR c1 = 1; --all
QUERY PLAN
--------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__"
Output: t1.c1, t1.c2
Node/s: All datanodes
Remote query: SELECT c1, c2 FROM public.t1 WHERE rowid IS NOT NULL OR c1 = 1
Remote SQL: SELECT c1, c2 FROM public.t1 WHERE rowid IS NOT NULL OR c1 = 1
Datanode Name: datanode1
Seq Scan on public.t1
Output: c1, c2
(10 rows)
ora=# SELECT * FROM t1 WHERE rowid IS NOT NULL OR c1 = 1;
c1 | c2
----+----
1 | 1
2 | 2
1 | 4
2 | 1
1 | 3
2 | 4
1 | 2
2 | 3
3 | 3
3 | 2
3 | 1
3 | 4
(12 rows)
-- stream计划
ora=# SET enable_fast_query_shipping = off;
SET
ora=# SET plan_cache_mode = force_custom_plan;
SET
-- 常量表达式静态剪枝
ora=# EXPLAIN (costs off, verbose on) SELECT * FROM t1 WHERE rowid = 'AAAAAAAAAP//AAAEEOAAAAAAAAAAE';
QUERY PLAN
------------------------------------------------------------------------------------------------------
Streaming (type: GATHER)
Output: c1, c2
Node/s: datanode1
-> Index Scan using t1_rowno_idx on public.t1
Output: c1, c2
Distribute Key: c1
Index Cond: (t1.rowno = 4::bigint)
Filter: ((t1.rowid = 'AAAAAAAAAP//AAAEEOAAAAAAAAAAE'::rowid) AND (t1.tableoid = 16654::oid))
(8 rows)
-- 参数表达式，动态剪枝不支持stream计划
ora=# SET plan_cache_mode = force_generic_plan;
SET
ora=# PREPARE p1 AS SELECT * FROM t1 WHERE rowid = $1;
PREPARE
ora=# EXPLAIN (costs off, verbose on) EXECUTE p1('AAAAAAAAAP//AAAEEOAAAAAAAAAAE');
QUERY PLAN
--------------------------------------------------------------------------------
Streaming (type: GATHER)
Output: c1, c2
Node/s: All datanodes
-> Result
Output: c1, c2
One-Time Filter: ((-1)::smallint = rowid_hashbucketid($1))
-> Index Scan using t1_rowno_idx on public.t1
Output: c1, c2
Distribute Key: c1
Index Cond: (t1.rowno = rowid_sequence($1))
Filter: ((t1.rowid = $1) AND (t1.tableoid = rowid_tableoid($1)))
(11 rows)
ora=# EXECUTE p1('AAAAAAAAAP//AAAEEOAAAAAAAAAAE');
c1 | c2
----+----
2 | 1
(1 row)
ora=# DEALLOCATE p1;
DEALLOCATE
-- AND表达式剪枝首个ROWID表达式，静态剪枝
ora=# SET plan_cache_mode = force_custom_plan;
SET
ora=# EXPLAIN (costs off, verbose on) SELECT * FROM t1 WHERE rowid IS NOT NULL AND c1 = 1;
QUERY PLAN
-----------------------------
Streaming (type: GATHER)
Output: c1, c2
Node/s: datanode1
-> Seq Scan on public.t1
Output: c1, c2
Distribute Key: c1
Filter: (t1.c1 = 1)
(7 rows)
ora=# SELECT * FROM t1 WHERE rowid IS NOT NULL AND c1 = 1;
c1 | c2
----+----
1 | 1
1 | 4
1 | 3
1 | 2
(4 rows)
-- OR表达式不支持剪枝
ora=# EXPLAIN (costs off, verbose on) SELECT * FROM t1 WHERE rowid IS NOT NULL OR c1 = 1;
QUERY PLAN
-----------------------------
Streaming (type: GATHER)
Output: c1, c2
Node/s: All datanodes
-> Seq Scan on public.t1
Output: c1, c2
Distribute Key: c1
(6 rows)
ora=# SELECT * FROM t1 WHERE rowid IS NOT NULL OR c1 = 1;
c1 | c2
----+----
1 | 1
2 | 2
1 | 4
2 | 1
1 | 3
2 | 4
1 | 2
2 | 3
3 | 3
3 | 2
3 | 1
3 | 4
(12 rows)
-- PGXC计划
ora=# SET enable_fast_query_shipping = off;
SET
ora=# SET enable_stream_operator = off;
SET
ora=# SET plan_cache_mode = force_custom_plan;
SET
-- 常量表达式静态剪枝
ora=# EXPLAIN (costs off, verbose on) SELECT * FROM t1 WHERE rowid = 'AAAAAAAAAP//AAAEEOAAAAAAAAAAE';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
Data Node Scan on t1 "_REMOTE_TABLE_QUERY_"
Output: t1.c1, t1.c2
Node/s: datanode1
Remote query: SELECT c1, c2 FROM ONLY public.t1 WHERE rowid = 'AAAAAAAAAP//AAAEEOAAAAAAAAAAE'::rowid AND tableoid = 16654::oid AND rowno = 4::bigint
Remote SQL: SELECT c1, c2 FROM ONLY public.t1 WHERE rowid = 'AAAAAAAAAP//AAAEEOAAAAAAAAAAE'::rowid AND tableoid = 16654::oid AND rowno = 4::bigint
Datanode Name: datanode1
Index Scan using t1_rowno_idx on public.t1
Output: c1, c2
Index Cond: (t1.rowno = 4::bigint)
Filter: ((t1.rowid = 'AAAAAAAAAP//AAAEEOAAAAAAAAAAE'::rowid) AND (t1.tableoid = 16654::oid))
(12 rows)
-- 参数表达式动态剪枝
ora=# SET plan_cache_mode = force_generic_plan;
SET
ora=# PREPARE p1 AS SELECT * FROM t1 WHERE rowid = $1;
PREPARE
ora=# EXPLAIN (costs off, verbose on) EXECUTE p1('AAAAAAAAAP//AAAEEOAAAAAAAAAAE');
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Result
Output: t1.c1, t1.c2
One-Time Filter: ((-1)::smallint = rowid_hashbucketid($1))
-> Data Node Scan on t1 "_REMOTE_TABLE_QUERY_"
Output: t1.c1, t1.c2
Node expr: $1
Remote query: SELECT c1, c2 FROM ONLY public.t1 WHERE rowid = $1 AND tableoid = rowid_tableoid($1) AND rowno = rowid_sequence($1) AND (-1)::smallint = rowid_hashbucketid($1)
Remote SQL: SELECT c1, c2 FROM ONLY public.t1 WHERE rowid = 'AAAAAAAAAP//AAAEEOAAAAAAAAAAE'::rowid AND tableoid = 16654::oid AND rowno = 4::bigint
Datanode Name: datanode1
Index Scan using t1_rowno_idx on public.t1
Output: c1, c2
Index Cond: (t1.rowno = 4::bigint)
Filter: ((t1.rowid = 'AAAAAAAAAP//AAAEEOAAAAAAAAAAE'::rowid) AND (t1.tableoid = 16654::oid))
(15 rows)
ora=# EXECUTE p1('AAAAAAAAAP//AAAEEOAAAAAAAAAAE');
c1 | c2
----+----
2 | 1
(1 row)
ora=# DEALLOCATE p1;
DEALLOCATE
-- AND表达式剪枝首个ROWID表达式，静态剪枝
ora=# SET plan_cache_mode = force_custom_plan;
SET
ora=# EXPLAIN (costs off, verbose on) SELECT * FROM t1 WHERE rowid IS NOT NULL AND c1 = 1;
QUERY PLAN
----------------------------------------------------------------
Data Node Scan on t1 "_REMOTE_TABLE_QUERY_"
Output: t1.c1, t1.c2
Node/s: datanode1
Remote query: SELECT c1, c2 FROM ONLY public.t1 WHERE c1 = 1
Remote SQL: SELECT c1, c2 FROM ONLY public.t1 WHERE c1 = 1
Datanode Name: datanode1
Seq Scan on public.t1
Output: c1, c2
Filter: (t1.c1 = 1)
(11 rows)
ora=# SELECT * FROM t1 WHERE rowid IS NOT NULL AND c1 = 1;
c1 | c2
----+----
1 | 1
1 | 4
1 | 3
1 | 2
(4 rows)
-- OR表达式不支持剪枝
ora=# EXPLAIN (costs off, verbose on) SELECT * FROM t1 WHERE rowid IS NOT NULL OR c1 = 1;
QUERY PLAN
--------------------------------------------------------------
Data Node Scan on t1 "_REMOTE_TABLE_QUERY_"
Output: t1.c1, t1.c2
Node/s: All datanodes
Remote query: SELECT c1, c2 FROM ONLY public.t1 WHERE true
Remote SQL: SELECT c1, c2 FROM ONLY public.t1 WHERE true
Datanode Name: datanode1
Seq Scan on public.t1
Output: c1, c2
(10 rows)
ora=# SELECT * FROM t1 WHERE rowid IS NOT NULL OR c1 = 1;
c1 | c2
----+----
1 | 1
2 | 2
1 | 4
2 | 1
1 | 3
2 | 4
1 | 2
2 | 3
3 | 3
3 | 2
3 | 1
3 | 4
(12 rows)
-- 清理数据
ora=# DROP TABLE t1;
DROP TABLE
ora=# RESET max_datanode_for_plan;
RESET
ora=# RESET query_dop;
RESET
ora=# RESET enable_light_proxy;
RESET
ora=# RESET enable_fast_query_shipping;
RESET
ora=# RESET enable_stream_operator;
RESET
ora=# RESET plan_cache_mode;
RESET
ora=# RESET auto_explain_level;
ora=# \c postgres
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "postgres" as user "$user".
gaussdb=# DROP DATABASE ora;
DROP DATABASE 分区剪枝示例如下：
由于不同场景下tableoid字段值不同，以下示例仅作参考，执行时以实际输出结果为准。
-- 准备数据
gaussdb=# CREATE DATABASE ora dbcompatibility 'ORA';
CREATE DATABASE
gaussdb=# \c ora
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "ora" as user "$user".
ora=# SET max_datanode_for_plan = 1;
SET
ora=# SET query_dop=1;
SET
ora=# SET auto_explain_level=notice;
SET
ora=# CREATE TABLE t_part (c1 int, c2 int) WITH (storage_type=astore, hasrowid=on) PARTITION BY HASH(c1) PARTITIONS 4;
NOTICE: The 'DISTRIBUTE BY' clause is not specified. Using 'c1' as the distribution column by default.
HINT: Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
CREATE TABLE
ora=# INSERT INTO t_part SELECT generate_series(1,3), generate_series(1,4);
INSERT 0 12
ora=# ANALYZE t_part;
ANALYZE
-- CN轻量化，cplan场景
ora=# SET plan_cache_mode = force_custom_plan;
SET
-- 剪枝至p1分区
ora=# EXPLAIN (costs off, verbose on) SELECT * FROM t_part WHERE rowid = 'AAAAAAAABP//AAAEEYAAAAAAAAAAO'; QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_LIGHT_QUERY__"
Output: t_part.c1, t_part.c2
Node/s: datanode2
Remote query: SELECT c1, c2 FROM public.t_part WHERE rowid = 'AAAAAAAABP//AAAEEYAAAAAAAAAAO'::rowid AND (-1)::smallint = rowid_hashbucketid('AAAAAAAABP//AAAEEYAAAAAAAAAAO'::rowid) AND tableoid = rowid
_tableoid('AAAAAAAABP//AAAEEYAAAAAAAAAAO'::rowid) AND rowno = rowid_sequence('AAAAAAAABP//AAAEEYAAAAAAAAAAO'::rowid)
Remote SQL: SELECT c1, c2 FROM public.t_part WHERE rowid = 'AAAAAAAABP//AAAEEYAAAAAAAAAAO'::rowid AND (-1)::smallint = rowid_hashbucketid('AAAAAAAABP//AAAEEYAAAAAAAAAAO'::rowid) AND tableoid = rowid_tab
leoid('AAAAAAAABP//AAAEEYAAAAAAAAAAO'::rowid) AND rowno = rowid_sequence('AAAAAAAABP//AAAEEYAAAAAAAAAAO'::rowid)
Datanode Name: datanode2
Partitioned Index Scan using t_part_rowno_idx on public.t_part
Output: c1, c2
Index Cond: (t_part.rowno = 14::bigint)
Filter: ((t_part.rowid = 'AAAAAAAABP//AAAEEYAAAAAAAAAAO'::rowid) AND (t_part.tableoid = 16664::oid))
Selected Partitions: 1
(13 rows)
-- FQS计划，gplan场景
ora=# SET enable_light_proxy = off;
SET
ora=# SET plan_cache_mode = force_generic_plan;
SET
-- 剪枝至p1分区
ora=# EXPLAIN (costs off, verbose on) SELECT * FROM t_part WHERE rowid = 'AAAAAAAABP//AAAEEYAAAAAAAAAAO'; QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__"
Output: t_part.c1, t_part.c2
Node/s: datanode2
Remote query: SELECT c1, c2 FROM public.t_part WHERE rowid = 'AAAAAAAABP//AAAEEYAAAAAAAAAAO'::rowid AND (-1)::smallint = rowid_hashbucketid('AAAAAAAABP//AAAEEYAAAAAAAAAAO'::rowid) AND tableoid = rowid
_tableoid('AAAAAAAABP//AAAEEYAAAAAAAAAAO'::rowid) AND rowno = rowid_sequence('AAAAAAAABP//AAAEEYAAAAAAAAAAO'::rowid)
Remote SQL: SELECT c1, c2 FROM public.t_part WHERE rowid = 'AAAAAAAABP//AAAEEYAAAAAAAAAAO'::rowid AND (-1)::smallint = rowid_hashbucketid('AAAAAAAABP//AAAEEYAAAAAAAAAAO'::rowid) AND tableoid = rowid_tab
leoid('AAAAAAAABP//AAAEEYAAAAAAAAAAO'::rowid) AND rowno = rowid_sequence('AAAAAAAABP//AAAEEYAAAAAAAAAAO'::rowid)
Datanode Name: datanode2
Partitioned Index Scan using t_part_rowno_idx on public.t_part
Output: c1, c2
Index Cond: (t_part.rowno = 14::bigint)
Filter: ((t_part.rowid = 'AAAAAAAABP//AAAEEYAAAAAAAAAAO'::rowid) AND (t_part.tableoid = 16664::oid))
Selected Partitions: 1
(13 rows)
-- stream计划，cplan场景
ora=# SET enable_fast_query_shipping = off;
SET
ora=# SET plan_cache_mode = force_custom_plan;
SET
ora=# EXPLAIN (costs off, verbose on) SELECT * FROM t_part WHERE rowid = 'AAAAAAAABP//AAAEEYAAAAAAAAAAO';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
Streaming (type: GATHER)
Output: c1, c2
Node/s: datanode2
-> Partition Iterator
Output: c1, c2
Iterations: PART
-> Partitioned Index Scan using t_part_rowno_idx on public.t_part
Output: c1, c2
Distribute Key: c1
Index Cond: (t_part.rowno = 14::bigint)
Filter: ((t_part.rowid = 'AAAAAAAABP//AAAEEYAAAAAAAAAAO'::rowid) AND (t_part.tableoid = 16664::oid))
Selected Partitions: 1..4 (pbe-pruning)
(12 rows)
-- stream计划，gplan场景
ora=# SET plan_cache_mode = force_generic_plan;
SET
ora=# EXPLAIN (costs off, verbose on) SELECT * FROM t_part WHERE rowid = 'AAAAAAAABP//AAAEEYAAAAAAAAAAO';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
Streaming (type: GATHER)
Output: c1, c2
Node/s: datanode2
-> Partition Iterator
Output: c1, c2
Iterations: PART
-> Partitioned Index Scan using t_part_rowno_idx on public.t_part
Output: c1, c2
Distribute Key: c1
Index Cond: (t_part.rowno = 14::bigint)
Filter: ((t_part.rowid = 'AAAAAAAABP//AAAEEYAAAAAAAAAAO'::rowid) AND (t_part.tableoid = 16664::oid))
Selected Partitions: 1..4 (pbe-pruning)
(12 rows)
-- 清理数据
ora=# DROP TABLE t_part;
DROP TABLE
ora=# RESET max_datanode_for_plan;
RESET
ora=# RESET query_dop;
RESET
ora=# RESET enable_light_proxy;
RESET
ora=# RESET enable_fast_query_shipping;
RESET
ora=# RESET enable_stream_operator;
RESET
ora=# RESET plan_cache_mode;
RESET
ora=# RESET auto_explain_level;
RESET
ora=# \c postgres
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "postgres" as user "$user".
gaussdb=# DROP DATABASE ora;
DROP DATABASE 父主题： 使用指导
