由 John Doe 一月 15, 2025
摘要:在本文中,您将学习如何在 PostgreSQL 中优化分区表上的查询。
目录
示例
查询执行中的分区剪枝,需要在某些条件下才会起作用。您需要有分区键上的索引,并且规划器必须选择嵌套循环来连接表。
CREATE TABLE partitioned_table (
a int,
b int,
c int,
p int
) PARTITION BY RANGE (p);
CREATE TABLE p1 PARTITION OF partitioned_table FOR VALUES FROM (0) TO (10);
CREATE TABLE p2 PARTITION OF partitioned_table FOR VALUES FROM (10) TO (20);
INSERT INTO partitioned_table
SELECT g.x, g.x, g.x, g.x FROM generate_series (0,19) AS g(x);
CREATE INDEX ON partitioned_table(p);
CREATE TABLE other_table (
p int,
r int
);
INSERT INTO other_table
SELECT 1, g.x FROM generate_series(1,100) AS g(x);
VACUUM ANALYZE partitioned_table, other_table;
EXPLAIN (settings, analyze, costs off, timing off, summary off)
SELECT a, b, c
FROM partitioned_table
WHERE p IN (SELECT p FROM other_table WHERE r between 1 and 100);
QUERY PLAN
-----------------------------------------------------------------------------------------
Nested Loop (actual rows=1 loops=1)
-> HashAggregate (actual rows=1 loops=1)
Group Key: other_table.p
Batches: 1 Memory Usage: 24kB
-> Seq Scan on other_table (actual rows=100 loops=1)
Filter: ((r >= 1) AND (r <= 100))
-> Append (actual rows=1 loops=1)
-> Index Scan using p1_p_idx on p1 partitioned_table_1 (actual rows=1 loops=1)
Index Cond: (p = other_table.p)
-> Index Scan using p2_p_idx on p2 partitioned_table_2 (never executed)
Index Cond: (p = other_table.p)
(11 rows)
请注意,在分区 p2 上的索引扫描是不会执行的。
优化对分区表的访问
大多数时候,开发者会抱有不切实际的期望,即如果他们对一个巨大的表进行分区,“对其进行查询会更快”。而实际情况下,分区的目标和目的是方便维护,而不是查询优化,如果短查询的执行速度和分区前一样,他们应该认为是成功的。这是没有规则没有例外的,虽然也有性能提升的情况,但那些确实是例外。
为了在分区后使查询不变慢,开发者需要显式地包含用于范围分区的属性值。很多时候,这个要求并不明显,而且,它需要对正在使用的 SQL 语句进行重大改写。
从一个应用开发者的角度来看,他们会这样做:
SELECT a, b, c
FROM partitioned_table
WHERE p IN (SELECT p FROM other_table WHERE r between 1 and 100)
开发者可能会想当然地认为:我已经在查询访问特定的分区了!实际上,在执行开始之前,PostgreSQL 无法知道要查询的将会是哪个分区!
那么,到底应该怎么做,才能让 PostgreSQL 确切地知道要查询哪个分区呢?一个肯定不坏的方法是,先计算出分区键的值,然后在查询中使用它。