PostgreSQL优化查询

优化器行为#

通过一个例子展示优化器是如何工作的。现在有三张表：

CREATE TABLE t1(aid int, ...);  --1亿行
CREATE TABLE t2(bid int, ...);  --2亿行
CREATE TABLE t3(cid int, ...);  --3亿行

每张表对id字段建立了索引，并且有一张关于表1和表2的视图：

CREATE INDEX idx_a ON t1(aid);
CREATE INDEX idx_b ON t2(bid);
CREATE INDEX idx_c ON t3(cid);

CREATE VIEW v AS
SELECT * 
FROM t1, t2
WHERE aid = bid;

最后，我们希望执行下面的查询，来查看优化器会做些什么。

SELECT * FROM v, t3 WHERE v.aid = t3.cid AND cid = 4;

优化分析#

先来看看如果优化器是埋头硬干的话，可能会采用什么方法呢？

嵌套循环：这是最容易想到的方法，直接对两张表进行遍历，依次对表中的元组进行比较，总可以获得正确的结果，但可以想象的到，其开销会是巨大的，时间复杂度在O(n²);
哈希连接：哈希可以看作是对嵌套循环的优化，对两张表中的元组进行哈希运算，比较哈希值可以很快地得到两个元组是否相等，但这种方案也是有代价地，首先每个元组需要能够进行哈希运算，其次这些哈希值也需要地方存放；
归并连接：如果表中的元组是有序的，那么归并连接也是可以考虑的方案。于是需要先对表中的数据进行排序，然后再遍历，这样的时间复杂度也在O(n*logn)级别。

转换和等值约束#

显然，蛮干是无法获得理想的结果的，事实上优化器也不会采用上述的“笨办法”。优化器往往会使用转换和等值约束的操作先对查询语句进行优化。
转换可能有两步，内联视图和扁平化子查询，这两步也许没有明显的性能提升，但是为下一步的优化提供了条件。
比如上述的查询操作，经过内联视图得到下面的语句：

SELECT * 
FROM (SELECT * 
    FROM t1, t2
    WHERE aid = bid) AS v, t3
WHERE v.aid = t3.cid AND cid = 4;

然后扁平化查询语句：

SELECT * 
FROM t1,t2,t3
WHERE t1.aid = t3.cid AND aid = bid AND cid = 4;

现在的查询语句已经具备了优化的空间了，进一步使用等值约束可以推导出更严格的约束条件，不难看出根据查询条件，三个id的值都应该等于4，于是得出最终的查询语句：

SELECT * 
FROM t1,t2,t3
WHERE t1.aid = t3.cid
    AND aid = bid
    AND cid = 4
    AND aid = 4
    AND bid = 4

明显，在最终的查询语句中，已经可以充分地使用到表上建立的索引了，而这在原始的查询语句是无法使用的。

穷举搜索#

pg会按照代价模型将所有的可行计划都尝试一遍，然后选择代价较小的方案执行实际的查询。

                                    QUERY PLAN
----------------------------------------------------------------------------------
 Nested Loop  (cost=4.91..321.46 rows=3960 width=102)
   ->  Nested Loop  (cost=0.29..175.61 rows=90 width=8)
         ->  Index Only Scan using idx_b on t2  (cost=0.29..4.46 rows=10 width=4)
               Index Cond: (bid = 4)
         ->  Materialize  (cost=0.00..170.04 rows=9 width=4)
               ->  Seq Scan on t1  (cost=0.00..170.00 rows=9 width=4)
                     Filter: (aid = 4)
   ->  Materialize  (cost=4.63..96.46 rows=44 width=94)
         ->  Bitmap Heap Scan on t3  (cost=4.63..96.24 rows=44 width=94)
               Recheck Cond: (cid = 4)
               ->  Bitmap Index Scan on idx_c  (cost=0.00..4.62 rows=44 width=0)
                     Index Cond: (cid = 4)
(12 rows)

使用pg16的版本测试了下1万数据量的情况，产生的执行计划如上所示，和前面的分析稍有出入，可以看到在查找cid=4时，pg使用了位图索引来查找，在查找aid=4时直接进行了顺序遍历。大体上的思路是没错的，仍是将查询尽可能地使用到索引。

优化器设置#

无疑优化器在大多数情况下都会选择一种代价较小的高效查询，但也许在某些场景下我们并不希望它执行某种连接，或是期望其执行特定的行为，这当然是可以的，pg提供了很多运行时的变量，这些变量会影响查询时优化器的选择。

enable_bitmapscan = on
enable_hashagg = on
enable_hashjoin = on
enable_indexscan = on
enable_indexonlyscan = on
enable_material = on
enable_mergejoin = on
enable_nestloop = on
enable_seqscan = on
enable_sort = on
enable_tidscan = on
等等
例如当禁用掉归并连接SET enable_mergejoin TO off，会使得代价模型中的归并连接非常昂贵，从而迫使优化器选择其他的方法。

P.S.
将变量设置为off并不表示禁用这种执行方式，而是这种操作代价非常大，当所有的可行方案都被设置为off时，优化器仍会选择其中代价最小的方案执行。

分区数据#

理论上，pg默认的8000字节块，可以存放32TB的数据，但是管理一张这么大的表总是费力不讨好的事情。
将一张大表拆分管理可能是更好的方法：首先建立一张父表，然后通过表继承的方式，创建特定的子表。

ubuntu=> create table t_data(id serial, t date, payload text);
CREATE TABLE
ubuntu=> create table t_data_2016 () inherits (t_data);
CREATE TABLE
ubuntu=> \d t_data_2016
                          Table "public.t_data_2016"
 Column  |  Type   | Collation | Nullable |              Default
---------+---------+-----------+----------+------------------------------------
 id      | integer |           | not null | nextval('t_data_id_seq'::regclass)
 t       | date    |           |          |
 payload | text    |           |          |
Inherits: t_data

这里建立了一张名为t_data的父表，然后又建立了一张名为t_data_2016的子表，子表后的括号表示创建的这张子表不添加额外的列，所有的列都来源于父表。使用关键字INHERITS指定要继承的父表。

这样做之后，向子表添加的数据都可以在父表中查询到：

ubuntu=> insert into t_data_2016 values(1,'2016-1-1', 'some');
INSERT 0 1
ubuntu=> select * from t_data;
 id |     t      | payload
----+------------+---------
  1 | 2016-01-01 | some
(1 row)

表约束#

通过表继承的方式实现表的分区管理，往往还需要配合对子表的约束，例如我们创建的子表t_data_2016可以看作是只用来保存2016年以前的数据的，2017到2024年的数据可以再通过创建一张t_data_2024的子表来保存。
现在我们通过t这一日期类型的列为子表添加约束：

ubuntu=> alter table t_data_2016 add check (t < '2017-1-1');
ALTER TABLE

修改子表和修改普通表的方式没有不同，都是使用ALTER关键字来操作。
添加了约束后，再向子表中添加错误的日期数据时就会报错：

ubuntu=> insert into t_data_2016 values(4, '2024-1-1', 'error data');
ERROR:  new row for relation "t_data_2016" violates check constraint "t_data_2016_t_check"
DETAIL:  Failing row contains (4, 2024-01-01, error data).