PostgreSQL SQL优化 ROWNUM引发的全表扫描问题
1 背景知识
熟悉Oracle的DBA 应该很熟悉rownum伪列，不恰当地使用rownum，容易导致性能问题，包括影响索引使用，无法使用并行等。
同样的现象在KingbaseES 也是存在的。以下举例演示下。
2 无法使用索引的场景
2.1 环境准备
CREATE TABLE t01(id1 integer, id2 integer, name text);
INSERT INTO t01
SELECT GENERATE_SERIES(1,1000000),MOD(generate_series(1,1000000),500),REPEAT('a',1000);
CREATE INDEX idx_t01_id1 ON t01(id1);
2.2 构造查询
1、 (select id1, rownum from t01) 是一个 CTE 表达式，表达式中使用了rownum。
2、从下面结果得出rownum 为123，而不是1。
WITH tmp_query1 AS
(SELECT id1, rownum FROM t01)
SELECT * FROM tmp_query1 WHERE id1=123;
//屏幕输出：
id1 | rownum
-----+--------
123 | 123
(1 行记录)
2.3 查看执行计划
EXPLAIN WITH tmp_query1 AS
(SELECT id1, rownum FROM t01)
SELECT * FROM tmp_query1 WHERE id1=123;
//屏幕输出：
QUERY PLAN
--------------------
Subquery Scan on tmp_query1 (cost=0.00..162858.12 rows=1 width=12)
Filter: (tmp_query1.id1 = 123)
-> Count (cost=0.00..162858.12 rows=0 width=12)
-> Seq Scan on t01 (cost=0.00..152858.06 rows=1000006 width=4)
(4 行记录)
3 正常使用索引的场景
3.1 构造查询
从下面结果得出rownum 为1，是正常的输出的一种情况。
SELECT id1, rownum AS row_num FROM t01 WHERE id1=123;
3.2 查看执行计划
EXPLAIN SELECT id1, rownum AS row_num FROM t01 WHERE id1=123;
//屏幕输出：
QUERY PLAN
--------------
Count (cost=0.42..8.45 rows=0 width=12)
-> Index Only Scan using idx_t01_id1 on t01 (cost=0.42..8.44 rows=1 width=4)
Index Cond: (id1 = 123)
(3 行记录)
Note
Rownum 的结果是和执行计划无关，如果没有索引，也是1。
4 性能分析总结
1、 rownum 是当结果集查询出来之后，添加的一个伪列。
2、rownum 也会影响优化器对于子查询的提升。
（1）在不提升的情况下，优化器对于子查询先处理，访问表的全部数据，并添加伪列 rownum。所以使用 CTE 表达式的那个例子rownum 返回 123 。
（2）如果优化器对于语句进行了提升和合并，那么 rownum 则为1。所以不使用 CTE 表达式的那个例子rownum 返回 1 。