文档首页/ 云数据库 GaussDB/ 开发指南（集中式_V2.0-10.x）/ 特性使用指导/ ROWID表/ 使用指导/ DML
更新时间：2026-07-28 GMT+08:00
查看PDF
分享

DML

ROWID系统列支持在DML语句中使用，包括INSERT/SELECT/UPDATE/DELETE语句，示例如下：

gaussdb=# CREATE TABLE test(a INT, b INT) WITH (hasrowid);
CREATE TABLE
gaussdb=# -- INSERT
gaussdb=# INSERT INTO test values(1, 1);
INSERT 0 1
gaussdb=# -- ROWID表插入语句行记录会携带ROWID系统列
gaussdb=# SELECT ROWID FROM test;
             rowid
-------------------------------
 AAAAAAAAAP//AAAEGAAAAAAAAAAAB
(1 row)
gaussdb=# -- SELECT，使用ROWID系统列作为WHERE条件
gaussdb=# SELECT * FROM test WHERE ROWID = 'AAAAAAAAAP//AAAEGAAAAAAAAAAAB';
 a | b
---+---
 1 | 1
(1 row)
gaussdb=# -- UPDATE，使用ROWID系统列作为WHERE条件
gaussdb=# UPDATE test SET b = 2 WHERE ROWID = 'AAAAAAAAAP//AAAEGAAAAAAAAAAAB';
UPDATE 1
gaussdb=# -- DELETE，使用ROWID系统列作为WHERE条件
gaussdb=# DELETE FROM test WHERE ROWID = 'AAAAAAAAAP//AAAEGAAAAAAAAAAAB';
DELETE 1
gaussdb=# -- CLEAN
gaussdb=# DROP TABLE test;
DROP TABLE

在DML语句中使用ROWID系统列时，若打开GUC参数opt_behavior_knob的ROWID_OPT选项（详情请参见《参考》中“数据库运行参数说明 > GUC参数说明 > 查询规划 > 其他优化器选项”章节内容），则支持ROWID系统列谓词条件改写、ROWID系统列ORDER BY语句改写、ROWID系统列max/min优化、ROWID系统列分区剪枝查询优化场景。

ROWID系统列谓词条件改写

  • ROWID系统列谓词条件改写

    当ROWID系统列用于WHERE子句或JOIN ON子句的谓词条件判断时，可进行条件语句的改写，当前仅支持比较操作符（=、<>、<、>、<=、>=）作为谓词条件的语句改写。

    • 当操作符为等号（=）时，支持全场景改写，形如“ROWID = src_expr”的谓词条件（当src_expr也为ROWID系统列时，不进行改写，无实际意义），改写规则如下： 
      tableoid = ROWID_TABLEOID(src_expr) AND rowno = ROWID_SEQUENCE(src_expr)

      改写后两个条件分别触发分区剪枝和索引扫描以提升扫描性能， 对于不可改写的表达式，比如IN表达式“rowid IN ($1,$2,...)”，会进行全表扫描，遍历每行元组进行条件检查，此时不进行剪枝和索引扫描。

    • 当操作符为非等号（<>、<、>、<=、>=）时，仅支持部分场景改写，比如范围表达式“ROWID > src_expr”。由于ROWID的比较需综合考虑内部字段（node_slice、bucket_id、table_oid、row_no），改写仅支持普通表且src_expr表达式为可计算常量条件的场景。此时ROWID除了row_no以外的字段完全相同，ROWID到rowno系统列的映射严格保序，形如“ROWID >/</<>/>=/<= src_expr”的谓词条件，会在检查其他字段相同的情况下（对于src_expr的table_oid不同于当前表的场景，由于src_expr为常量条件，可在改写前直接提前计算谓词条件结果，若结果为TRUE，改写为ROWID IS NOT NULL条件，若结果为FALSE，则直接拼接布尔条件FALSE），按如下规则进行改写： 
      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的判断条件不支持改写。

    示例：

    由于不同场景下tableoid字段值不同，以下示例仅作参考，执行时以实际输出结果为准。

    以GUC参数explain_perf_mode设置为normal为例。

    -- 数据准备
gaussdb=# DROP TABLE IF EXISTS t1;
DROP TABLE
gaussdb=# CREATE TABLE t1(a int, c int) with (storage_type=astore, hasrowid=ON);
CREATE TABLE
gaussdb=# INSERT INTO t1 VALUES( 1, 1);
INSERT 0 1
gaussdb=# INSERT INTO t1 VALUES( 2,  2);
INSERT 0 1
gaussdb=# INSERT INTO t1 VALUES(3, 3);
INSERT 0 1
gaussdb=# INSERT INTO t1 VALUES(3, 4);
INSERT 0 1
gaussdb=# INSERT INTO t1 VALUES(1,  5);
INSERT 0 1
gaussdb=# INSERT INTO t1 VALUES( 2,  6);
INSERT 0 1
gaussdb=# INSERT INTO t1 VALUES(3,  7);
INSERT 0 1
gaussdb=# INSERT INTO t1 VALUES( 3,  8);
INSERT 0 1
gaussdb=# INSERT INTO t1 VALUES( 1, 9);
INSERT 0 1
gaussdb=# INSERT INTO t1 VALUES( 2, 10);
INSERT 0 1
gaussdb=# --IMMUTABLE func
gaussdb=# CREATE OR REPLACE FUNCTION func RETURN rowid IMMUTABLE AS
gaussdb$#     temp rowid;
gaussdb$#     res int;
gaussdb$# begin
gaussdb$#     SELECT rowid INTO temp FROM t1 ORDER BY rowid LIMIT 1;
gaussdb$#     RETURN temp;
gaussdb$# end;
gaussdb$# /
CREATE FUNCTION
gaussdb=# -- WHERE子句
gaussdb=# EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM t1 WHERE rowid = func();
                              QUERY PLAN
----------------------------------------------------------------------
 [Parameterized]
 Index Scan using t1_rowno_idx ON public.t1
   Output: a, c, (Expression Flatten Optimized)
   Index Cond: (t1.rowno = 1::bigint), (Expression Flatten Optimized)
   Filter: (t1.tableoid = 16760::oid), (Expression Flatten Optimized)
(5 rows)
gaussdb=# SELECT * FROM t1 WHERE rowid = func();
 a | c
---+---
 1 | 1
(1 row)
gaussdb=# EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM t1 WHERE func() = rowid;
                              QUERY PLAN
----------------------------------------------------------------------
 [Parameterized]
 Index Scan using t1_rowno_idx ON public.t1
   Output: a, c, (Expression Flatten Optimized)
   Index Cond: (t1.rowno = 1::bigint), (Expression Flatten Optimized)
   Filter: (t1.tableoid = 16760::oid), (Expression Flatten Optimized)
(5 rows)
gaussdb=# SELECT * FROM t1 WHERE func() = rowid;
 a | c
---+---
 1 | 1
(1 row)
gaussdb=# EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM t1 WHERE rowid = rowid;
                           QUERY PLAN
-----------------------------------------------------------------
 [Parameterized]
 Seq Scan ON public.t1
   Output: a, c, (Expression Flatten Optimized)
   Filter: (t1.rowid = t1.rowid), (Expression Flatten Optimized)
(4 rows)
gaussdb=# SELECT * FROM t1 WHERE rowid = rowid;
 a | c
---+----
 1 |  1
 2 |  2
 3 |  3
 3 |  4
 1 |  5
 2 |  6
 3 |  7
 3 |  8
 1 |  9
 2 | 10
(10 rows)
gaussdb=# --HAVING子句，不支持改写
gaussdb=# EXPLAIN (COSTS OFF, VERBOSE) SELECT MAX(a),MAX(rowid) FROM t1 GROUP BY rowid HAVING rowid = 'AAAAAAAAAP//BBBBBAAAAAAAAAAAB';
                                            QUERY PLAN
-----------------------------------------------------------------------------------------------------
 [Parameterized]
 GroupAggregate
   Output: MAX(a), MAX(rowid), rowid, (Expression Flatten Optimized)
   Group By Key: t1.rowid
   ->  Seq Scan ON public.t1
         Output: rowid, a, (Expression Flatten Optimized)
         Filter: (t1.rowid = 'AAAAAAAAAP//BBBBBAAAAAAAAAAAB'::rowid), (Expression Flatten Optimized)
(7 rows)
gaussdb=# SELECT MAX(a),MAX(rowid) FROM t1 GROUP BY rowid HAVING rowid = 'AAAAAAAAAP//BBBBBAAAAAAAAAAAB';
 MAX | MAX
-----+-----
(0 rows)
gaussdb=# 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 |       5
       1 |       9
       2 |       2
       2 |       6
       2 |      10
       3 |       3
       3 |       4
       3 |       7
       3 |       8
(10 rows)
gaussdb=# -- NULLIF表达式本身不改写，rowid = NULLIF(xxx)改写
gaussdb=# EXPLAIN (COSTS OFF, VERBOSE) SELECT  rowid FROM t1 WHERE rowid = NULLIF(rowid, rowid);
                                                                             QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
 [Parameterized]
 Seq Scan ON public.t1
   Output: rowid, (Expression Flatten Optimized)
   Filter: ((t1.tableoid = rowid_tableoid(NULLIF(t1.rowid, t1.rowid))) AND (t1.rowno = rowid_sequence(NULLIF(t1.rowid, t1.rowid)))), (Expression Flatten Optimized)
(4 rows)
gaussdb=# SELECT  rowid FROM t1 WHERE rowid = NULLIF(rowid, rowid);
 rowid
-------
(0 rows)
gaussdb=# EXPLAIN (COSTS OFF, VERBOSE) SELECT  rowid FROM t1 WHERE rowid = NULLIF(rowid, 'AAAAAAAAAP//AAAAAAAAAAAAAAAAA');
                                                                                                           QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 [Parameterized]
 Seq Scan ON public.t1
   Output: rowid, (Expression Flatten Optimized)
   Filter: ((t1.tableoid = rowid_tableoid(NULLIF(t1.rowid, 'AAAAAAAAAP//AAAAAAAAAAAAAAAAA'::rowid))) AND (t1.rowno = rowid_sequence(NULLIF(t1.rowid, 'AAAAAAAAAP//AAAAAAAAAAAAAAAAA'::rowid)))), (Expression Flatten Optimized)
(4 rows)
gaussdb=# SELECT  rowid FROM t1 WHERE rowid = NULLIF(rowid, 'AAAAAAAAAP//AAAAAAAAAAAAAAAAA');
             rowid
-------------------------------
 AAAAAAAAAP//AAAEF4AAAAAAAAAAB
 AAAAAAAAAP//AAAEF4AAAAAAAAAAC
 AAAAAAAAAP//AAAEF4AAAAAAAAAAD
 AAAAAAAAAP//AAAEF4AAAAAAAAAAE
 AAAAAAAAAP//AAAEF4AAAAAAAAAAF
 AAAAAAAAAP//AAAEF4AAAAAAAAAAG
 AAAAAAAAAP//AAAEF4AAAAAAAAAAH
 AAAAAAAAAP//AAAEF4AAAAAAAAAAI
 AAAAAAAAAP//AAAEF4AAAAAAAAAAJ
 AAAAAAAAAP//AAAEF4AAAAAAAAAAK
(10 rows)
gaussdb=# -- rowexpr不改写
gaussdb=# EXPLAIN (COSTS OFF, VERBOSE) SELECT a FROM t1 WHERE (rowid,c) IN (SELECT rowid,c FROM t1);
                                                     QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
 [Parameterized]
 Hash Join
   Output: public.t1.a, (Expression Flatten Optimized)
   Inner Unique: true
   Hash Cond: ((public.t1.rowid = public.t1.rowid) AND (public.t1.c = public.t1.c)), (Expression Flatten Optimized)
   ->  Seq Scan ON public.t1
         Output: public.t1.a, public.t1.rowid, public.t1.c, (Expression Flatten Optimized)
   ->  Hash
         Output: public.t1.rowid, public.t1.c
         ->  HashAggregate
               Output: public.t1.rowid, public.t1.c, (Expression Flatten Optimized)
               Group By Key: public.t1.rowid, public.t1.c
               ->  Seq Scan ON public.t1
                     Output: public.t1.rowid, public.t1.c, (Expression Flatten Optimized)
(14 rows)
gaussdb=# SELECT a FROM t1 WHERE (rowid,c) IN (SELECT rowid,c FROM t1);
 a
---
 1
 2
 3
 3
 1
 2
 3
 3
 1
 2
(10 rows)
gaussdb=# CREATE TYPE t1_type AS (rowid rowid, c int);
CREATE TYPE
gaussdb=# EXPLAIN (COSTS OFF, VERBOSE) SELECT a FROM t1 WHERE (rowid, c) IN t1_type(func(), 1);
                                                        QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
 [Parameterized]
 Seq Scan ON public.t1
   Output: a, (Expression Flatten Optimized)
   Filter Unordered: ((t1.rowid = 'AAAAAAAAAP//AAAEF4AAAAAAAAAAB'::rowid) AND (t1.c = 1)), (Expression Flatten Optimized)
   Filter Reordered: ((t1.c = 1) AND (t1.rowid = 'AAAAAAAAAP//AAAEF4AAAAAAAAAAB'::rowid)), (Expression Flatten Optimized)
(5 rows)
gaussdb=# SELECT a FROM t1 WHERE (rowid, c) IN t1_type(func(), 1);
 a
---
 1
(1 row)
gaussdb=# -- IN/NOT IN
gaussdb=# EXPLAIN (COSTS OFF, VERBOSE) SELECT  rowid FROM t1 WHERE rowid IN func(); --改写
QUERY PLAN
----------------------------------------------------------------------
 [Parameterized]
 Index Scan using t1_rowno_idx ON public.t1
   Output: rowid, (Expression Flatten Optimized)
   Index Cond: (t1.rowno = 1::bigint), (Expression Flatten Optimized)
   Filter: (t1.tableoid = 16760::oid), (Expression Flatten Optimized)
(5 rows)
gaussdb=# SELECT  rowid FROM t1 WHERE rowid IN func();
             rowid
-------------------------------
 AAAAAAAAAP//AAAEF4AAAAAAAAAAB
(1 row)
gaussdb=# EXPLAIN (COSTS OFF, VERBOSE) SELECT  rowid FROM t1 WHERE rowid NOT IN func(); --改写
                            QUERY PLAN
-------------------------------------------------------------------
 [Parameterized]
 Seq Scan ON public.t1
   Output: rowid, (Expression Flatten Optimized)
   Filter: (t1.rowno <> 1::bigint), (Expression Flatten Optimized)
(4 rows)
gaussdb=# SELECT rowid FROM t1 WHERE rowid NOT IN func();
             rowid
-------------------------------
 AAAAAAAAAP//AAAEF4AAAAAAAAAAC
 AAAAAAAAAP//AAAEF4AAAAAAAAAAD
 AAAAAAAAAP//AAAEF4AAAAAAAAAAE
 AAAAAAAAAP//AAAEF4AAAAAAAAAAF
 AAAAAAAAAP//AAAEF4AAAAAAAAAAG
 AAAAAAAAAP//AAAEF4AAAAAAAAAAH
 AAAAAAAAAP//AAAEF4AAAAAAAAAAI
 AAAAAAAAAP//AAAEF4AAAAAAAAAAJ
 AAAAAAAAAP//AAAEF4AAAAAAAAAAK
(9 rows)
gaussdb=# -- distinct FROM不改写
gaussdb=# EXPLAIN (COSTS OFF, VERBOSE) SELECT a FROM t1 WHERE rowid is distinct FROM func();
                                                  QUERY PLAN
--------------------------------------------------------------------------------------------------------------
 [Parameterized]
 Seq Scan ON public.t1
   Output: a, (Expression Flatten Optimized)
   Filter: (t1.rowid IS DISTINCT FROM 'AAAAAAAAAP//AAAEF4AAAAAAAAAAB'::rowid), (Expression Flatten Optimized)
(4 rows)
gaussdb=# SELECT a FROM t1 WHERE rowid is distinct FROM func();
 a
---
 2
 3
 3
 1
 2
 3
 3
 1
 2
(9 rows)


-- 非等号改写示例
gaussdb=# SET cursor_sharing=off; -- 关闭自动参数化
SET
gaussdb=# EXPLAIN (COSTS OFF, VERBOSE) SELECT *, rowid FROM t1 WHERE rowid <> 'AAAAAAAAAP//AAAAAAAAAAAAAAAAA';
                      QUERY PLAN
-------------------------------------------------------
 Seq Scan ON public.t1
   Output: a, c, rowid, (Expression Flatten Optimized)
(2 rows)
gaussdb=# EXPLAIN (COSTS OFF, VERBOSE) SELECT *, rowid FROM t1 WHERE rowid <> 'AAAAAAAAAP//AAAEFaAAAAAAAAAAA';
                      QUERY PLAN
-------------------------------------------------------
 Seq Scan ON public.t1
   Output: a, c, rowid, (Expression Flatten Optimized)
(2 rows)
gaussdb=# EXPLAIN (COSTS OFF, VERBOSE) SELECT *, rowid FROM t1 WHERE 'AAAAAAAAAP//AAAAAAAAAAAAAAAAA' <> rowid;
                      QUERY PLAN
-------------------------------------------------------
 Seq Scan ON public.t1
   Output: a, c, rowid, (Expression Flatten Optimized)
(2 rows)
gaussdb=# EXPLAIN (COSTS OFF, VERBOSE) SELECT *, rowid FROM t1 WHERE 'AAAAAAAAAP//AAAEFaAAAAAAAAAAA' <> rowid;
                      QUERY PLAN
-------------------------------------------------------
 Seq Scan ON public.t1
   Output: a, c, rowid, (Expression Flatten Optimized)
(2 rows)
gaussdb=# EXPLAIN (COSTS OFF, VERBOSE) SELECT *, rowid FROM t1 WHERE rowid <> func();
                            QUERY PLAN
-------------------------------------------------------------------
 Seq Scan ON public.t1
   Output: a, c, rowid, (Expression Flatten Optimized)
   Filter: (t1.rowno <> 1::bigint), (Expression Flatten Optimized)
(3 rows)
gaussdb=# EXPLAIN (COSTS OFF, VERBOSE) SELECT *, rowid FROM t1 WHERE rowid <> func() ORDER BY rowid;
                               QUERY PLAN
------------------------------------------------------------------------
 Index Scan using t1_rowno_idx ON public.t1
   Output: a, c, rowid, tableoid, rowno, (Expression Flatten Optimized)
   Filter: (t1.rowno <> 1::bigint), (Expression Flatten Optimized)
(3 rows)
gaussdb=# RESET cursor_sharing; -- 恢复自动参数化
RESET
-- 在JOIN ON子句的谓词条件改写
gaussdb=# CREATE TABLE t2(a int, c text) with (storage_type=astore);
CREATE TABLE
gaussdb=#
gaussdb=# INSERT INTO t2 VALUES( 1, func());
INSERT 0 1
gaussdb=# INSERT INTO t2 VALUES( 2,  func());
INSERT 0 1
gaussdb=# INSERT INTO t2 VALUES(3, func());
INSERT 0 1
gaussdb=# INSERT INTO t2 VALUES(3, func());
INSERT 0 1
gaussdb=# INSERT INTO t2 VALUES(1,  func());
INSERT 0 1
gaussdb=# INSERT INTO t2 VALUES( 2,  func());
INSERT 0 1
gaussdb=# INSERT INTO t2 VALUES(3,  func());
INSERT 0 1
gaussdb=# INSERT INTO t2 VALUES( 3,  func());
INSERT 0 1
gaussdb=# INSERT INTO t2 VALUES( 1, func());
INSERT 0 1
gaussdb=# INSERT INTO t2 VALUES( 2, func());
INSERT 0 1
gaussdb=# SELECT * FROM t2;
 a |               c
---+-------------------------------
 1 | AAAAAAAAAP//AAAEF4AAAAAAAAAAB
 2 | AAAAAAAAAP//AAAEF4AAAAAAAAAAB
 3 | AAAAAAAAAP//AAAEF4AAAAAAAAAAB
 3 | AAAAAAAAAP//AAAEF4AAAAAAAAAAB
 1 | AAAAAAAAAP//AAAEF4AAAAAAAAAAB
 2 | AAAAAAAAAP//AAAEF4AAAAAAAAAAB
 3 | AAAAAAAAAP//AAAEF4AAAAAAAAAAB
 3 | AAAAAAAAAP//AAAEF4AAAAAAAAAAB
 1 | AAAAAAAAAP//AAAEF4AAAAAAAAAAB
 2 | AAAAAAAAAP//AAAEF4AAAAAAAAAAB
(10 rows)
gaussdb=# EXPLAIN (COSTS OFF, VERBOSE) SELECT *,t1.rowid FROM t1 INNER JOIN t2 ON t1.rowid = t2.c;
                                                     QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
 [Parameterized]
 Hash Join
   Output: t1.a, t1.c, t2.a, t2.c, t1.rowid, (Expression Flatten Optimized)
   Inner Unique: true
   Hash Cond: ((rowid_tableoid((t2.c)::rowid) = t1.tableoid) AND (rowid_sequence((t2.c)::rowid) = t1.rowno)), (Expression Flatten Optimized)
   ->  Seq Scan ON public.t2
         Output: t2.a, t2.c
   ->  Hash
         Output: t1.a, t1.c, t1.rowid, t1.tableoid, t1.rowno
         ->  Seq Scan ON public.t1
               Output: t1.a, t1.c, t1.rowid, t1.tableoid, t1.rowno, (Expression Flatten Optimized)
(11 rows)
gaussdb=# SELECT *,t1.rowid FROM t1 INNER JOIN t2 ON t1.rowid = t2.c;
 a | c | a |               c               |             rowid
---+---+---+-------------------------------+-------------------------------
 1 | 1 | 1 | AAAAAAAAAP//AAAEF4AAAAAAAAAAB | AAAAAAAAAP//AAAEF4AAAAAAAAAAB
 1 | 1 | 2 | AAAAAAAAAP//AAAEF4AAAAAAAAAAB | AAAAAAAAAP//AAAEF4AAAAAAAAAAB
 1 | 1 | 3 | AAAAAAAAAP//AAAEF4AAAAAAAAAAB | AAAAAAAAAP//AAAEF4AAAAAAAAAAB
 1 | 1 | 3 | AAAAAAAAAP//AAAEF4AAAAAAAAAAB | AAAAAAAAAP//AAAEF4AAAAAAAAAAB
 1 | 1 | 1 | AAAAAAAAAP//AAAEF4AAAAAAAAAAB | AAAAAAAAAP//AAAEF4AAAAAAAAAAB
 1 | 1 | 2 | AAAAAAAAAP//AAAEF4AAAAAAAAAAB | AAAAAAAAAP//AAAEF4AAAAAAAAAAB
 1 | 1 | 3 | AAAAAAAAAP//AAAEF4AAAAAAAAAAB | AAAAAAAAAP//AAAEF4AAAAAAAAAAB
 1 | 1 | 3 | AAAAAAAAAP//AAAEF4AAAAAAAAAAB | AAAAAAAAAP//AAAEF4AAAAAAAAAAB
 1 | 1 | 1 | AAAAAAAAAP//AAAEF4AAAAAAAAAAB | AAAAAAAAAP//AAAEF4AAAAAAAAAAB
 1 | 1 | 2 | AAAAAAAAAP//AAAEF4AAAAAAAAAAB | AAAAAAAAAP//AAAEF4AAAAAAAAAAB
(10 rows)
gaussdb=# EXPLAIN (COSTS OFF, VERBOSE) SELECT *,t1.rowid FROM t1 LEFT JOIN t2 ON t1.rowid = t2.c;
                                                                 QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
 [Parameterized]
 Hash Right Join
   Output: t1.a, t1.c, t2.a, t2.c, t1.rowid, (Expression Flatten Optimized)
   Inner Unique: true
   Hash Cond: ((rowid_tableoid((t2.c)::rowid) = t1.tableoid) AND (rowid_sequence((t2.c)::rowid) = t1.rowno)), (Expression Flatten Optimized)
   ->  Seq Scan ON public.t2
         Output: t2.a, t2.c
   ->  Hash
         Output: t1.a, t1.c, t1.rowid, t1.tableoid, t1.rowno
         ->  Seq Scan ON public.t1
               Output: t1.a, t1.c, t1.rowid, t1.tableoid, t1.rowno, (Expression Flatten Optimized)
(11 rows)
gaussdb=# SELECT *,t1.rowid FROM t1 LEFT JOIN t2 ON t1.rowid = t2.c;
 a | c  | a |               c               |             rowid
---+----+---+-------------------------------+-------------------------------
 1 |  1 | 1 | AAAAAAAAAP//AAAEF4AAAAAAAAAAB | AAAAAAAAAP//AAAEF4AAAAAAAAAAB
 1 |  1 | 2 | AAAAAAAAAP//AAAEF4AAAAAAAAAAB | AAAAAAAAAP//AAAEF4AAAAAAAAAAB
 1 |  1 | 3 | AAAAAAAAAP//AAAEF4AAAAAAAAAAB | AAAAAAAAAP//AAAEF4AAAAAAAAAAB
 1 |  1 | 3 | AAAAAAAAAP//AAAEF4AAAAAAAAAAB | AAAAAAAAAP//AAAEF4AAAAAAAAAAB
 1 |  1 | 1 | AAAAAAAAAP//AAAEF4AAAAAAAAAAB | AAAAAAAAAP//AAAEF4AAAAAAAAAAB
 1 |  1 | 2 | AAAAAAAAAP//AAAEF4AAAAAAAAAAB | AAAAAAAAAP//AAAEF4AAAAAAAAAAB
 1 |  1 | 3 | AAAAAAAAAP//AAAEF4AAAAAAAAAAB | AAAAAAAAAP//AAAEF4AAAAAAAAAAB
 1 |  1 | 3 | AAAAAAAAAP//AAAEF4AAAAAAAAAAB | AAAAAAAAAP//AAAEF4AAAAAAAAAAB
 1 |  1 | 1 | AAAAAAAAAP//AAAEF4AAAAAAAAAAB | AAAAAAAAAP//AAAEF4AAAAAAAAAAB
 1 |  1 | 2 | AAAAAAAAAP//AAAEF4AAAAAAAAAAB | AAAAAAAAAP//AAAEF4AAAAAAAAAAB
 3 |  4 |   |                               | AAAAAAAAAP//AAAEF4AAAAAAAAAAE
 2 | 10 |   |                               | AAAAAAAAAP//AAAEF4AAAAAAAAAAK
 1 |  9 |   |                               | AAAAAAAAAP//AAAEF4AAAAAAAAAAJ
 3 |  8 |   |                               | AAAAAAAAAP//AAAEF4AAAAAAAAAAI
 3 |  3 |   |                               | AAAAAAAAAP//AAAEF4AAAAAAAAAAD
 1 |  5 |   |                               | AAAAAAAAAP//AAAEF4AAAAAAAAAAF
 2 |  2 |   |                               | AAAAAAAAAP//AAAEF4AAAAAAAAAAC
 3 |  7 |   |                               | AAAAAAAAAP//AAAEF4AAAAAAAAAAH
 2 |  6 |   |                               | AAAAAAAAAP//AAAEF4AAAAAAAAAAG
(19 rows)
gaussdb=# EXPLAIN (COSTS OFF, VERBOSE) SELECT *,t1.rowid FROM t1 RIGHT JOIN t2 ON t1.rowid = t2.c;
                                                                 QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
 [Parameterized]
 Hash Left Join
   Output: t1.a, t1.c, t2.a, t2.c, t1.rowid, (Expression Flatten Optimized)
   Inner Unique: true
   Hash Cond: ((rowid_tableoid((t2.c)::rowid) = t1.tableoid) AND (rowid_sequence((t2.c)::rowid) = t1.rowno)), (Expression Flatten Optimized)
   ->  Seq Scan ON public.t2
         Output: t2.a, t2.c
   ->  Hash
         Output: t1.a, t1.c, t1.rowid, t1.tableoid, t1.rowno
         ->  Seq Scan ON public.t1
               Output: t1.a, t1.c, t1.rowid, t1.tableoid, t1.rowno, (Expression Flatten Optimized)
(11 rows)
gaussdb=# SELECT *,t1.rowid FROM t1 RIGHT JOIN t2 ON t1.rowid = t2.c;
 a | c | a |               c               |             rowid
---+---+---+-------------------------------+-------------------------------
 1 | 1 | 1 | AAAAAAAAAP//AAAEF4AAAAAAAAAAB | AAAAAAAAAP//AAAEF4AAAAAAAAAAB
 1 | 1 | 2 | AAAAAAAAAP//AAAEF4AAAAAAAAAAB | AAAAAAAAAP//AAAEF4AAAAAAAAAAB
 1 | 1 | 3 | AAAAAAAAAP//AAAEF4AAAAAAAAAAB | AAAAAAAAAP//AAAEF4AAAAAAAAAAB
 1 | 1 | 3 | AAAAAAAAAP//AAAEF4AAAAAAAAAAB | AAAAAAAAAP//AAAEF4AAAAAAAAAAB
 1 | 1 | 1 | AAAAAAAAAP//AAAEF4AAAAAAAAAAB | AAAAAAAAAP//AAAEF4AAAAAAAAAAB
 1 | 1 | 2 | AAAAAAAAAP//AAAEF4AAAAAAAAAAB | AAAAAAAAAP//AAAEF4AAAAAAAAAAB
 1 | 1 | 3 | AAAAAAAAAP//AAAEF4AAAAAAAAAAB | AAAAAAAAAP//AAAEF4AAAAAAAAAAB
 1 | 1 | 3 | AAAAAAAAAP//AAAEF4AAAAAAAAAAB | AAAAAAAAAP//AAAEF4AAAAAAAAAAB
 1 | 1 | 1 | AAAAAAAAAP//AAAEF4AAAAAAAAAAB | AAAAAAAAAP//AAAEF4AAAAAAAAAAB
 1 | 1 | 2 | AAAAAAAAAP//AAAEF4AAAAAAAAAAB | AAAAAAAAAP//AAAEF4AAAAAAAAAAB
(10 rows)
gaussdb=# EXPLAIN (COSTS OFF, VERBOSE) SELECT *,t1.rowid FROM t1 full JOIN t2 ON t1.rowid = t2.c;
                                                                 QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
 [Parameterized]
 Hash Full Join
   Output: t1.a, t1.c, t2.a, t2.c, t1.rowid, (Expression Flatten Optimized)
   Inner Unique: true
   Hash Cond: ((rowid_tableoid((t2.c)::rowid) = t1.tableoid) AND (rowid_sequence((t2.c)::rowid) = t1.rowno)), (Expression Flatten Optimized)
   ->  Seq Scan ON public.t2
         Output: t2.a, t2.c
   ->  Hash
         Output: t1.a, t1.c, t1.rowid, t1.tableoid, t1.rowno
         ->  Seq Scan ON public.t1
               Output: t1.a, t1.c, t1.rowid, t1.tableoid, t1.rowno, (Expression Flatten Optimized)
(11 rows)
gaussdb=# SELECT *,t1.rowid FROM t1 full JOIN t2 ON t1.rowid = t2.c;
 a | c  | a |               c               |             rowid
---+----+---+-------------------------------+-------------------------------
 1 |  1 | 1 | AAAAAAAAAP//AAAEF4AAAAAAAAAAB | AAAAAAAAAP//AAAEF4AAAAAAAAAAB
 1 |  1 | 2 | AAAAAAAAAP//AAAEF4AAAAAAAAAAB | AAAAAAAAAP//AAAEF4AAAAAAAAAAB
 1 |  1 | 3 | AAAAAAAAAP//AAAEF4AAAAAAAAAAB | AAAAAAAAAP//AAAEF4AAAAAAAAAAB
 1 |  1 | 3 | AAAAAAAAAP//AAAEF4AAAAAAAAAAB | AAAAAAAAAP//AAAEF4AAAAAAAAAAB
 1 |  1 | 1 | AAAAAAAAAP//AAAEF4AAAAAAAAAAB | AAAAAAAAAP//AAAEF4AAAAAAAAAAB
 1 |  1 | 2 | AAAAAAAAAP//AAAEF4AAAAAAAAAAB | AAAAAAAAAP//AAAEF4AAAAAAAAAAB
 1 |  1 | 3 | AAAAAAAAAP//AAAEF4AAAAAAAAAAB | AAAAAAAAAP//AAAEF4AAAAAAAAAAB
 1 |  1 | 3 | AAAAAAAAAP//AAAEF4AAAAAAAAAAB | AAAAAAAAAP//AAAEF4AAAAAAAAAAB
 1 |  1 | 1 | AAAAAAAAAP//AAAEF4AAAAAAAAAAB | AAAAAAAAAP//AAAEF4AAAAAAAAAAB
 1 |  1 | 2 | AAAAAAAAAP//AAAEF4AAAAAAAAAAB | AAAAAAAAAP//AAAEF4AAAAAAAAAAB
 3 |  4 |   |                               | AAAAAAAAAP//AAAEF4AAAAAAAAAAE
 2 | 10 |   |                               | AAAAAAAAAP//AAAEF4AAAAAAAAAAK
 1 |  9 |   |                               | AAAAAAAAAP//AAAEF4AAAAAAAAAAJ
 3 |  8 |   |                               | AAAAAAAAAP//AAAEF4AAAAAAAAAAI
 3 |  3 |   |                               | AAAAAAAAAP//AAAEF4AAAAAAAAAAD
 1 |  5 |   |                               | AAAAAAAAAP//AAAEF4AAAAAAAAAAF
 2 |  2 |   |                               | AAAAAAAAAP//AAAEF4AAAAAAAAAAC
 3 |  7 |   |                               | AAAAAAAAAP//AAAEF4AAAAAAAAAAH
 2 |  6 |   |                               | AAAAAAAAAP//AAAEF4AAAAAAAAAAG
(19 rows)
-- 清理数据
gaussdb=# DROP TABLE t1;
DROP TABLE
gaussdb=# DROP TABLE t2;
DROP TABLE
gaussdb=# DROP FUNCTION func;
DROP FUNCTION

ROWID系统列ORDER BY语句改写

对于ORDER BY rowid的场景，可改写为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语句改写遵从如下规格约束：

  • 普通表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个。
  • 普通表或者分区表剪枝到单分区。

示例：

由于不同场景下tableoid字段值不同，以下示例仅作参考，执行时以实际输出结果为准。

以GUC参数explain_perf_mode设置为normal为例。

-- 普通表
gaussdb=# CREATE TABLE t1 (
    c1 INTEGER,
    c2 INTEGER,
    c3 INTEGER
) with (storage_type = astore, hasrowid);
CREATE TABLE
gaussdb=#
gaussdb=# INSERT INTO t1 VALUES(generate_series(1,100),generate_series(1,200),generate_series(1,400));
INSERT 0 400
gaussdb=# EXPLAIN  (VERBOSE, COSTS FALSE) SELECT /*+ indexscan(t1) */ * FROM t1 ORDER BY rowid;
                                  QUERY PLAN
------------------------------------------------------------------------------
 [Bypass]
 [Parameterized]
 Index Scan using t1_rowno_idx on public.t1
   Output: c1, c2, c3, rowid, tableoid, rowno, (Expression Flatten Optimized)
(4 rows)
gaussdb=# SELECT /*+ indexscan(t1) */ * FROM t1 ORDER BY rowid LIMIT 1;
 c1 | c2 | c3
----+----+----
  1 |  1 |  1
(1 row)
gaussdb=# SELECT /*+ indexscan(t1) */ * FROM t1 WHERE c1 = 50 ORDER BY rowid;
 c1 | c2  | c3
----+-----+-----
 50 |  50 |  50
 50 | 150 | 150
 50 |  50 | 250
 50 | 150 | 350
(4 rows)
gaussdb=# SELECT /*+ indexscan(t1) */ * FROM t1 WHERE c2 = 50 ORDER BY rowid;
 c1 | c2 | c3
----+----+-----
 50 | 50 |  50
 50 | 50 | 250
(2 rows)
gaussdb=# EXPLAIN  (VERBOSE, COSTS FALSE) SELECT /*+ indexscan(t1) */ rowid,c1 FROM t1 WHERE c1 = 50 ORDER BY 1;
                              QUERY PLAN
----------------------------------------------------------------------
 [Parameterized]
 Index Scan using t1_rowno_idx on public.t1
   Output: rowid, c1, tableoid, rowno, (Expression Flatten Optimized)
   Filter: (t1.c1 = 50), (Expression Flatten Optimized)
(4 rows)
gaussdb=# EXPLAIN  (VERBOSE, COSTS FALSE) SELECT /*+ indexscan(t1) */ rowid,rowno FROM t1 ORDER BY rowid;
                               QUERY PLAN
-------------------------------------------------------------------------
 [Parameterized]
 Index Scan using t1_rowno_idx on public.t1
   Output: rowid, rowno, tableoid, rowno, (Expression Flatten Optimized)
(3 rows)
-- ORDER BY多列场景
gaussdb=# EXPLAIN  (VERBOSE, COSTS FALSE) SELECT /*+ indexscan(t1) */ rowid,rowno FROM t1 ORDER BY rowid,c1;
WARNING:  unused hint: IndexScan(t1)
                            QUERY PLAN
------------------------------------------------------------------
 [Parameterized]
 Sort
   Output: rowid, rowno, c1
   Sort Key: t1.rowid, t1.c1
   ->  Seq Scan on public.t1
         Output: rowid, rowno, c1, (Expression Flatten Optimized)
(6 rows)
gaussdb=# EXPLAIN  (VERBOSE, COSTS FALSE) SELECT /*+ indexscan(t1) */ rowid,rowno FROM t1 ORDER BY c1,rowid;
WARNING:  unused hint: IndexScan(t1)
                            QUERY PLAN
------------------------------------------------------------------
 [Parameterized]
 Sort
   Output: rowid, rowno, c1
   Sort Key: t1.c1, t1.rowid
   ->  Seq Scan on public.t1
         Output: rowid, rowno, c1, (Expression Flatten Optimized)
(6 rows)
-- 分区表
gaussdb=# CREATE TABLE t1_p (
gaussdb(#     c1 INTEGER,
gaussdb(#     c2 INTEGER,
gaussdb(# c3 INTEGER
gaussdb(# ) with (storage_type = astore, hasrowid)
gaussdb-# PARTITION BY RANGE (c1)
gaussdb-# (
gaussdb(#     PARTITION p2023q1 VALUES LESS THAN (30),
gaussdb(#     PARTITION p2023q2 VALUES LESS THAN (60),
gaussdb(# PARTITION p2023q3 VALUES LESS THAN (MAXVALUE)
gaussdb(# );
CREATE TABLE
gaussdb=# INSERT INTO t1_p VALUES(generate_series(1,100),generate_series(1,200),generate_series(1,400));
INSERT 0 400
gaussdb=# EXPLAIN  (VERBOSE, COSTS FALSE) SELECT /*+ indexscan(t1_p) */ * FROM t1_p ORDER BY rowid;
WARNING:  unused hint: IndexScan(t1_p)
                                        QUERY PLAN
------------------------------------------------------------------------------------------
 [Parameterized]
 Sort
   Output: c1, c2, c3, rowid, tableoid, rowno
   Sort Key: t1_p.tableoid, t1_p.rowno
   ->  Partition Iterator
         Output: c1, c2, c3, rowid, tableoid, rowno
         Iterations: 3
         ->  Partitioned Seq Scan on public.t1_p
               Output: c1, c2, c3, rowid, tableoid, rowno, (Expression Flatten Optimized)
               Selected Partitions:  1..3
(10 rows)
gaussdb=# SELECT /*+ indexscan(t1_p) */ * FROM t1_p ORDER BY rowid LIMIT 1;
 c1 | c2 | c3
----+----+----
  1 |  1 |  1
(1 row)
-- 静态剪枝
aussdb=# EXPLAIN  (VERBOSE, COSTS FALSE) SELECT /*+ indexscan(t1_p) */ * FROM t1_p WHERE c1 = 90 ORDER BY rowid;
                                  QUERY PLAN
------------------------------------------------------------------------------
 [Parameterized]
 Partitioned Index Scan using t1_p_rowno_idx on public.t1_p
   Output: c1, c2, c3, rowid, tableoid, rowno, (Expression Flatten Optimized)
   Filter: (t1_p.c1 = 90), (Expression Flatten Optimized)
   Selected Partitions:  3
(5 rows)
gaussdb=# SELECT /*+ indexscan(t1_p) */ * FROM t1_p WHERE c1 = 90 ORDER BY rowid;
 c1 | c2  | c3
----+-----+-----
 90 |  90 |  90
 90 | 190 | 190
 90 |  90 | 290
 90 | 190 | 390
(4 rows)
-- 动态剪枝
gaussdb=# SET plan_cache_mode = force_generic_plan;
SET
gaussdb=# PREPARE p2(int) AS SELECT /*+ indexscan(t1_p) */ * FROM t1_p WHERE c1 = $1 ORDER BY rowid;
PREPARE
gaussdb=# EXPLAIN  (VERBOSE, COSTS FALSE) EXECUTE p2(10);
                                  QUERY PLAN
------------------------------------------------------------------------------
 Partitioned Index Scan using t1_p_rowno_idx on public.t1_p
   Output: c1, c2, c3, rowid, tableoid, rowno, (Expression Flatten Optimized)
   Filter: (t1_p.c1 = $1), (Expression Flatten Optimized)
   Selected Partitions:  1 (pbe-pruning)
(4 rows)
gaussdb=# EXECUTE p2(10);
 c1 | c2  | c3
----+-----+-----
 10 |  10 |  10
 10 | 110 | 110
 10 |  10 | 210
 10 | 110 | 310
(4 rows)
gaussdb=# RESET plan_cache_mode;
RESET
-- CTE不改写场景
gaussdb=# EXPLAIN  (VERBOSE, COSTS FALSE)  WITH cte AS (SELECT rowid,* FROM t1) SELECT * FROM cte ORDER BY rowid;
                                  QUERY PLAN
-------------------------------------------------------------------------------
 [Parameterized]
 Sort
   Output: t1.rowid, t1.c1, t1.c2, t1.c3
   Sort Key: t1.rowid
   ->  Seq Scan on public.t1
         Output: t1.rowid, t1.c1, t1.c2, t1.c3, (Expression Flatten Optimized)
(6 rows)
-- 子查询不改写场景
gaussdb=# EXPLAIN  (VERBOSE, COSTS FALSE) SELECT * FROM (SELECT /*+ indexscan(t1) */ rowid,* FROM t1) ORDER BY rowid;
WARNING:  unused hint: IndexScan(t1)
                                  QUERY PLAN
-------------------------------------------------------------------------------
 [Parameterized]
 Sort
   Output: t1.rowid, t1.c1, t1.c2, t1.c3
   Sort Key: t1.rowid
   ->  Seq Scan on public.t1
         Output: t1.rowid, t1.c1, t1.c2, t1.c3, (Expression Flatten Optimized)
(6 rows)
-- 聚集函数不改写场景
gaussdb=# EXPLAIN (VERBOSE, COSTS FALSE)  SELECT /*+ indexscan(t1) */ max(rowid) FROM t1 ORDER BY rowid;
WARNING:  unused hint: IndexScan(t1)
                                        QUERY PLAN
------------------------------------------------------------------------------------------
 [Parameterized]
 Result
   Output: $0, (Expression Flatten Optimized)
   InitPlan 1 (returns $0)
     ->  Limit
           Output: public.t1.rowid, public.t1.rowno
           ->  Index Scan Backward using t1_rowno_idx on public.t1
                 Output: public.t1.rowid, public.t1.rowno, (Expression Flatten Optimized)
(8 rows)
-- GROUP不改写场景
gaussdb=# EXPLAIN (VERBOSE, COSTS FALSE)  SELECT /*+ indexscan(t1) */ count(*), rowid AS min_rowid FROM t1 group BY rowid ORDER BY rowid;
WARNING:  unused hint: IndexScan(t1)
                           QUERY PLAN
-----------------------------------------------------------------
 [Parameterized]
 Sort
   Output: (count(*)), rowid
   Sort Key: t1.rowid
   ->  HashAggregate
         Output: count(*), rowid, (Expression Flatten Optimized)
         Group By Key: t1.rowid
         ->  Seq Scan on public.t1
               Output: rowid, (Expression Flatten Optimized)
(9 rows)
-- DISTINCT不改写场景
gaussdb=# EXPLAIN (VERBOSE, COSTS FALSE)  SELECT /*+ indexscan(t1) */ distinct rowid FROM t1 ORDER BY rowid;
WARNING:  unused hint: IndexScan(t1)
                         QUERY PLAN
-------------------------------------------------------------
 [Parameterized]
 Sort
   Output: rowid
   Sort Key: t1.rowid
   ->  HashAggregate
         Output: rowid, (Expression Flatten Optimized)
         Group By Key: t1.rowid
         ->  Seq Scan on public.t1
               Output: rowid, (Expression Flatten Optimized)
(9 rows)
-- tableoid排序去除
gaussdb=# EXPLAIN  (VERBOSE, COSTS FALSE) SELECT /*+ indexscan(t1) */ rowid,rowno FROM t1 ORDER BY tableoid,c1;
WARNING:  unused hint: IndexScan(t1)
                                 QUERY PLAN
----------------------------------------------------------------------------
 [Parameterized]
 Sort
   Output: rowid, rowno, tableoid, c1
   Sort Key: t1.c1
   ->  Seq Scan on public.t1
         Output: rowid, rowno, tableoid, c1, (Expression Flatten Optimized)
(6 rows)
gaussdb=# EXPLAIN  (VERBOSE, COSTS FALSE) SELECT /*+ indexscan(t1_p) */ * FROM t1_p WHERE c1 = 90 ORDER BY tableoid,c2;
WARNING:  unused hint: IndexScan(t1_p)
                              QUERY PLAN
----------------------------------------------------------------------
 [Parameterized]
 Sort
   Output: c1, c2, c3, tableoid
   Sort Key: t1_p.c2
   ->  Partitioned Seq Scan on public.t1_p
         Output: c1, c2, c3, tableoid, (Expression Flatten Optimized)
         Filter: (t1_p.c1 = 90), (Expression Flatten Optimized)
         Selected Partitions:  3
(8 rows)
gaussdb=# EXPLAIN  (VERBOSE, COSTS FALSE) SELECT * FROM (SELECT /*+ indexscan(t1) */ rowid,* FROM t1 ORDER BY c2,tableoid);
WARNING:  unused hint: IndexScan(t1)
                                                                   QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
 [Parameterized]
 Subquery Scan on __unnamed_subquery__
   Output: __unnamed_subquery__.rowid, __unnamed_subquery__.c1, __unnamed_subquery__.c2, __unnamed_subquery__.c3, (Expression Flatten Optimized)
   ->  Sort
         Output: t1.rowid, t1.c1, t1.c2, t1.c3, t1.tableoid
         Sort Key: t1.c2
         ->  Seq Scan on public.t1
               Output: t1.rowid, t1.c1, t1.c2, t1.c3, t1.tableoid, (Expression Flatten Optimized)
(8 rows)


-- 清理数据
gaussdb=# DROP TABLE t1;
DROP TABLE
gaussdb=# DROP TABLE t1_p;
DROP TABLE

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优化遵从如下规格约束：

  • 仅支持普通表场景，不支持分区表等。
  • 当关闭GUC参数enable_indexscan时或者有其他不能进行rowno列索引扫描的场景时，该优化默认不生效。enable_indexscan参数详情可见《参考》中“数据库运行参数说明 > GUC参数说明 > 查询规划 > 优化器方法配置”章节内容。

示例：

由于不同场景下tableoid字段值不同，以下示例仅作参考，执行时以实际输出结果为准。

以GUC参数explain_perf_mode设置为normal为例。

-- 数据准备
gaussdb=# CREATE TABLE t1 (a INT, b INT) WITH (STORAGE_TYPE=Astore, hasrowid=ON);
CREATE TABLE
gaussdb=# INSERT INTO t1 VALUES ( generate_series(1,1000), generate_series(3001,4000));
INSERT 0 1000
gaussdb=# INSERT INTO t1 VALUES ( generate_series(1001,2000), generate_series(3001,4000));
INSERT 0 1000
gaussdb=# CREATE INDEX idx_t1_b ON t1(b);
CREATE INDEX
gaussdb=# ANALYZE t1;
ANALYZE
-- max/min优化场景
gaussdb=# EXPLAIN  (VERBOSE, COSTS FALSE) SELECT /*+ indexscan(t1) */ max(rowid) FROM t1;
WARNING:  unused hint: IndexScan(t1)
                                        QUERY PLAN
------------------------------------------------------------------------------------------
 [Parameterized]
 Result
   Output: $0, (Expression Flatten Optimized)
   InitPlan 1 (returns $0)
     ->  Limit
           Output: public.t1.rowid, public.t1.rowno
           ->  Index Scan Backward using t1_rowno_idx ON public.t1
                 Output: public.t1.rowid, public.t1.rowno, (Expression Flatten Optimized)
(8 rows)
gaussdb=# SELECT /*+ indexscan(t1) */ rowid_sequence(max(rowid)) FROM t1; -- 2000
 rowid_sequence
----------------
           2000
(1 row)
gaussdb=# EXPLAIN  (VERBOSE, COSTS FALSE) SELECT /*+ indexscan(t1) */ min(rowid) FROM t1;
WARNING:  unused hint: IndexScan(t1)
                                        QUERY PLAN
------------------------------------------------------------------------------------------
 [Parameterized]
 Result
   Output: $0, (Expression Flatten Optimized)
   InitPlan 1 (returns $0)
     ->  Limit
           Output: public.t1.rowid, public.t1.rowno
           ->  Index Scan using t1_rowno_idx ON public.t1
                 Output: public.t1.rowid, public.t1.rowno, (Expression Flatten Optimized)
(8 rows)
gaussdb=# SELECT /*+ indexscan(t1) */ rowid_sequence(min(rowid)) FROM t1; -- 1
 rowid_sequence
----------------
              1
(1 row)
-- 多个max/min使用场景，支持优化
gaussdb=# EXPLAIN  (VERBOSE, COSTS FALSE) SELECT /*+ indexscan(t1) */ rowid_sequence(max(rowid)),max(b) FROM t1;
WARNING:  unused hint: IndexScan(t1)
                                        QUERY PLAN
------------------------------------------------------------------------------------------
 [Parameterized]
 Result
   Output: rowid_sequence($0), $1, (Expression Flatten Optimized)
   InitPlan 1 (returns $0)
     ->  Limit
           Output: public.t1.rowid, public.t1.rowno
           ->  Index Scan Backward using t1_rowno_idx ON public.t1
                 Output: public.t1.rowid, public.t1.rowno, (Expression Flatten Optimized)
   InitPlan 2 (returns $1)
     ->  Limit
           Output: public.t1.b
           ->  Index Only Scan Backward using idx_t1_b ON public.t1
                 Output: public.t1.b
                 Index Cond: (public.t1.b IS NOT NULL), (Expression Flatten Optimized)
(14 rows)
gaussdb=# SELECT /*+ indexscan(t1) */ rowid_sequence(max(rowid)),max(b) FROM t1; --2000, 4000
 rowid_sequence | max
----------------+------
           2000 | 4000
(1 row)
gaussdb=# EXPLAIN  (VERBOSE, COSTS FALSE) SELECT /*+ indexscan(t1) */ rowid_sequence(max(rowid)),max(rowno) FROM t1;
WARNING:  unused hint: IndexScan(t1)
                                        QUERY PLAN
------------------------------------------------------------------------------------------
 [Parameterized]
 Result
   Output: rowid_sequence($0), $1, (Expression Flatten Optimized)
   InitPlan 1 (returns $0)
     ->  Limit
           Output: public.t1.rowid, public.t1.rowno
           ->  Index Scan Backward using t1_rowno_idx ON public.t1
                 Output: public.t1.rowid, public.t1.rowno, (Expression Flatten Optimized)
   InitPlan 2 (returns $1)
     ->  Limit
           Output: public.t1.rowno
           ->  Index Only Scan Backward using t1_rowno_idx ON public.t1
                 Output: public.t1.rowno
(13 rows)
gaussdb=# SELECT /*+ indexscan(t1) */ rowid_sequence(max(rowid)),max(rowno) FROM t1; --2000, 2000
 rowid_sequence | max
----------------+------
           2000 | 2000
(1 row)
-- 关闭enable_indexscan参数场景，不支持max/min优化
gaussdb=# SET enable_indexscan = off;
SET
gaussdb=# EXPLAIN  (VERBOSE, COSTS FALSE) SELECT /*+ indexscan(t1) */ max(rowid) FROM t1;
WARNING:  unused hint: IndexScan(t1)
                      QUERY PLAN
-------------------------------------------------------
 [Parameterized]
 Aggregate
   Output: max(rowid), (Expression Flatten Optimized)
   ->  Seq Scan ON public.t1
         Output: rowid, (Expression Flatten Optimized)
(5 rows)
gaussdb=# SET enable_indexscan = ON;
SET
-- complex SQL，不支持max/min优化
gaussdb=# EXPLAIN  (VERBOSE, COSTS FALSE) SELECT /*+ indexscan(t1) */ rowid_sequence(max(rowid)) FROM (SELECT rowid,a FROM t1 WHERE a = 1);
EXPLAIN  (VERBOSE, COSTS FALSE) SELECT /*+ indexscan(t1) */ rowid_sequence(max(rowid)) FROM t1 WHERE rownum < 100;WARNING:  unused hint: IndexScan(t1)
                               QUERY PLAN
-------------------------------------------------------------------------
 [Parameterized]
 Aggregate
   Output: rowid_sequence(max(t1.rowid)), (Expression Flatten Optimized)
   ->  Seq Scan ON public.t1
         Output: t1.rowid, (Expression Flatten Optimized)
         Filter: (t1.a = 1), (Expression Flatten Optimized)
(6 rows)
-- 分区表场景，不支持max/min优化
gaussdb=# -- PARTITION TABLE
gaussdb=# CREATE TABLE t2
gaussdb-# ( a INT,
gaussdb(# b INT,
gaussdb(# c INT)
gaussdb-# WITH (STORAGE_TYPE=Astore, hasrowid=ON)
gaussdb-# PARTITION BY RANGE (a)
gaussdb-# (
gaussdb(# PARTITION P1 VALUES LESS THAN(100),
gaussdb(# PARTITION P2 VALUES LESS THAN(200),
gaussdb(# PARTITION P3 VALUES LESS THAN(300),
gaussdb(# PARTITION P8 VALUES LESS THAN(MAXVALUE)
gaussdb(# ) ;
CREATE TABLE
gaussdb=# INSERT INTO t2 VALUES ( generate_series(1,1000), generate_series(1001,2000));
INSERT 0 1000
gaussdb=# ANALYZE t2;
ANALYZE
gaussdb=# EXPLAIN  (VERBOSE, COSTS FALSE) SELECT /*+ indexscan(t2) */ max(rowid) FROM t2;
WARNING:  unused hint: IndexScan(t2)
                         QUERY PLAN
-------------------------------------------------------------
 [Parameterized]
 Aggregate
   Output: max(rowid), (Expression Flatten Optimized)
   ->  Partition Iterator
         Output: rowid
         Iterations: 4
         ->  Partitioned Seq Scan ON public.t2
               Output: rowid, (Expression Flatten Optimized)
               Selected Partitions:  1..4
(9 rows)
gaussdb=# EXPLAIN  (VERBOSE, COSTS FALSE) SELECT /*+ indexscan(t2) */ max(rowid) FROM t2 WHERE a = 1;
WARNING:  unused hint: IndexScan(t2)
                         QUERY PLAN
------------------------------------------------------------
 [Parameterized]
 Aggregate
   Output: max(rowid), (Expression Flatten Optimized)
   ->  Partitioned Seq Scan ON public.t2
         Output: rowid, (Expression Flatten Optimized)
         Filter: (t2.a = 1), (Expression Flatten Optimized)
         Selected Partitions:  1
(7 rows)


-- 清理数据
gaussdb=# DROP TABLE t2;
DROP TABLE
gaussdb=# DROP TABLE t1;
DROP TABLE

ROWID系统列分区剪枝

分区剪枝需要在ROWID系统列谓词条件被改写为包含tableoid等值条件的场景下触发，从而将查询裁剪至唯一的单个分区。该机制同时支持常量和参数两种条件：当为ROWID常量条件时触发静态剪枝，当为ROWID参数条件时触发动态剪枝。更多分区剪枝详情可见分区剪枝章节内容。

示例：

由于不同场景下tableoid字段值不同，以下示例仅作参考，执行时以实际输出结果为准。

以GUC参数explain_perf_mode设置为normal为例。

-- 数据准备
gaussdb=# SET auto_explain_level=notice;
SET
gaussdb=# CREATE TABLE t_part (c1 int, c2 int) WITH (storage_type=astore, hasrowid=on) partition by hash(c1) partitions 4;
CREATE TABLE
gaussdb=# INSERT INTO t_part SELECT generate_series(1,3), generate_series(1,4);
INSERT 0 12
gaussdb=# ANALYZE t_part;
ANALYZE


-- cplan计划
gaussdb=# SET plan_cache_mode = force_custom_plan;
SET
-- 常量条件静态剪枝至p2分区
gaussdb=# EXPLAIN (costs off, verbose on) SELECT * FROM t_part WHERE rowid = 'AAAAAAAAAP//AAAETGAAAAAAAAAAM';
                                QUERY PLAN
---------------------------------------------------------------------------
 [Parameterized]
 Partitioned Index Scan using t_part_rowno_idx on public.t_part
   Output: c1, c2, (Expression Flatten Optimized)
   Index Cond: (t_part.rowno = 12::bigint), (Expression Flatten Optimized)
   Filter: (t_part.tableoid = 17606::oid), (Expression Flatten Optimized)
   Selected Partitions:  2
(6 rows)
-- gplan计划
gaussdb=# SET plan_cache_mode = force_generic_plan;
SET
-- 参数条件动态剪枝至p2分区
gaussdb=# EXPLAIN (costs off, verbose on) SELECT * FROM t_part WHERE rowid = 'AAAAAAAAAP//AAAETGAAAAAAAAAAM';
                                       QUERY PLAN
-----------------------------------------------------------------------------------------
 [Parameterized]
 Partition Iterator
   Output: c1, c2
   Iterations: PART
   ->  Partitioned Index Scan using t_part_rowno_idx on public.t_part
         Output: c1, c2, (Expression Flatten Optimized)
         Index Cond: (t_part.rowno = rowid_sequence($1)), (Expression Flatten Optimized)
         Filter: (t_part.tableoid = rowid_tableoid($1)), (Expression Flatten Optimized)
         Selected Partitions:  2 (pbe-pruning)
(9 rows)


-- 清理数据
gaussdb=# DROP TABLE t_part;
DROP TABLE
gaussdb=# RESET plan_cache_mode;
RESET
gaussdb=# RESET auto_explain_level;
RESET
父主题： 使用指导

相关文档