PostgreSQL SQL优化 使用rownum的Count Stop特性

1 背景知识

rownum 条件用于限制返回结果集数据量。

1.1 使用场景

1、对于两表连接的场景，如果没有rownum限制，则需要对两张表进行完整地关联，返回所有的行。
2、Rownum 关键字对于某些连接方式的影响。
（1）对于 mergejoin ，hashjoin 影响较小。

（2）而对于采用 nestloop 的场景影响是较大。

1.2 SQL 优化思路

1、rownum 与 nestloop 或索引相结合，可以有效地提升SQL效率。
2、对于包含 order by 的SQL，将含有排序列的表为驱动表，先进行排序，再与其他表进行关联。
5、本例场景是客户现场一个POC案例，涉及多张表关联，排序列也涉及多表的列。

2 环境准备

2.1 创建表

这里创建三张表用于本章示例测试。

DROP TABLE IF EXISTS t01,t02,t03;
CREATE TABLE t01(t01_id1 integer, t01_id2 integer, t01_name text);
CREATE TABLE t02(t02_id1 integer, t02_id2 integer, t02_name text);
CREATE TABLE t03(t03_id1 integer, t03_id2 integer, t03_name text);

2.2 t01 表数据准备

1、插入 1W 随机数据量。
2、t01 表自身重复插入8 次。

INSERT INTO t01 SELECT generate_series(1,10000),mod(generate_series(1,10000),500),repeat('a',1000);
DO LANGUAGE 'plpgsql'  -- 指定匿名块的语言，可省略默认为plpgsql
$BODY$ 
BEGIN
  for i in 1..8 loop
    INSERT INTO t01 SELECT * FROM t01;
  END loop;
END 
$BODY$;

3、在t01 表的 t01_id1 字段 上创建索引 idx_t01_t01_id01。

CREATE INDEX idx_t01_t01_id01 ON t01(t01_id1);

2.3 t02 表数据准备

插入1W 数据量。

O t02 SELECT generate_series(1,10000),mod(generate_series(1,10000),1000),repeat('a',1000);

2.4 t02 表数据准备

插入1W 数据量。

INSERT INTO t03 SELECT generate_series(1,10000),mod(generate_series(1,10000),100),repeat('a',1000);

3 未优化SQL

3.1 查看执行计划

EXPLAIN SELECT * FROM  (
SELECT t01_id2,t02_id2,t03_id2 FROM t01,t02,t03
WHERE t01_id1=t02_id1
AND t02_id1=t03_id1 
ORDER BY t02_id2 DESC NULLS LAST,t03_id2 ASC NULLS FIRST 
) AS foo
limit 100;

3.2 执行计划解读

1、代价最大的的部分是t01表的访问，优化的方向也集中在了如何避免t1表的全表扫描上。
2、由于语句只需要返回100条记录，自然就会考虑到用索引。
3、由于排序列不涉及t01表，因此，可以先对t2, t3 表连接结果进行排序，再以排序的结果集为驱动表，与t01进行nestloop，这样就可以避免对t1 表的全表访问。
可问题在于，本例的排序列涉及到两个表，靠优化器还无法这么智能地按以上思路执行，只能通过人为改写实现。

QUERY PLAN                                                 
----------------------------------
 Limit  (cost=242892950480.45..242892950493.12 rows=100 width=12)
   ->  Gather Merge  (cost=242892950480.45..422835645204.48 rows=1542258272054 width=12)
         Workers Planned: 2
         ->  Sort  (cost=242892949480.43..244820772320.49 rows=771129136027 width=12)
               Sort Key: t02.t02_id2 DESC NULLS LAST, t03.t03_id2 NULLS FIRST
               ->  Merge Join  (cost=538225.90..11569654289.98 rows=771129136027 width=12)
                     Merge Cond: (t01.t01_id1 = t02.t02_id1)
                     ->  Sort  (cost=497764.48..500431.15 rows=1066667 width=8)
                           Sort Key: t01.t01_id1
                           ->  Parallel Seq Scan on t01  (cost=0.00..376381.67 rows=1066667 width=8)
                     ->  Materialize  (cost=40461.42..2572004.28 rows=144586713 width=16)
                           ->  Merge Join  (cost=40461.42..2210537.49 rows=144586713 width=16)
                                 Merge Cond: (t02.t02_id1 = t03.t03_id1)
                                 ->  Sort  (cost=20230.71..20655.84 rows=170051 width=8)
                                       Sort Key: t02.t02_id1
                                       ->  Seq Scan on t02  (cost=0.00..3129.51 rows=170051 width=8)
                                 ->  Materialize  (cost=20230.71..21080.96 rows=170051 width=8)
                                       ->  Sort  (cost=20230.71..20655.84 rows=170051 width=8)
                                             Sort Key: t03.t03_id1
                                             ->  Seq Scan on t03  (cost=0.00..3129.51 rows=170051 width=8)
(20 行记录)

4 已优化SQL

4.1 查看执行计划

EXPLAIN WITH tmp_query1 AS 
(Select t02_id1,t02_id2,t03_id2 
 FROM t02,t03 
 WHERE t02_id1=t03_id1 
 ORDER BY t02_id2 DESC NULLS LAST,t03_id2 ASC NULLS FIRST)
 
SELECT t01_id2,t02_id2,t03_id2 
FROM t01, tmp_query1 
WHERE t01_id1=t02_id1 
ORDER BY t02_id2 DESC NULLS LAST,t03_id2 ASC NULLS FIRST 
limit 100;

4.2 执行计划解读

1、 tmp_query1 内部先对 t02, t03 的连接结果进行排序。
2、 tmp_query1 为驱动表与 t01 进行 nestloop 连接。
3、修改后的 SQL 有两个 order by，优化器实际会忽略外层的order by , 没必要，目的是为了保证语句结果的一致性，实际并不会执行两次排序。

5 小结

Count Stop + nestloop 是常见的优化方法，DBA 在遇到含有 rownum 的SQL时，需特别关注。