为什么 Postgres 多列分区修剪没有比这更聪明？

Question

在Postgres (v12)中，我们有一些大的表是按"国家"分区的。和"扇区"，但是我发现剪枝行为只在为每个查询标识一个精确值的查询中是最优的，当为其中一个指定了2个或更多的值时，剪枝行为就变得次优了。此外还有"国家"。列在某种程度上更有利于剪枝逻辑，大概是因为它是分区键中的第一列…

例子

CREATE TABLE part.partitioned_table
(
country character varying NOT NULL,
sector character varying NOT NULL,
a_value integer NOT NULL,
other_value integer,
CONSTRAINT partitioned_table_pkey PRIMARY KEY (country, sector, a_value)
) PARTITION BY RANGE (country, sector, a_value);
CREATE TABLE part.partitioned_table_gb_alpha PARTITION OF part.partitioned_table
FOR VALUES FROM ('GB', 'ALPHA', MINVALUE) TO ('GB', 'ALPHA', MAXVALUE);
CREATE TABLE part.partitioned_table_gb_beta PARTITION OF part.partitioned_table
FOR VALUES FROM ('GB', 'BETA', MINVALUE) TO ('GB', 'BETA', MAXVALUE);
CREATE TABLE part.partitioned_table_gb_gamma PARTITION OF part.partitioned_table
FOR VALUES FROM ('GB', 'GAMMA', MINVALUE) TO ('GB', 'GAMMA', MAXVALUE);
CREATE TABLE part.partitioned_table_fr_alpha PARTITION OF part.partitioned_table
FOR VALUES FROM ('FR', 'ALPHA', MINVALUE) TO ('FR', 'ALPHA', MAXVALUE);
CREATE TABLE part.partitioned_table_fr_beta PARTITION OF part.partitioned_table
FOR VALUES FROM ('FR', 'BETA', MINVALUE) TO ('FR', 'BETA', MAXVALUE);
CREATE TABLE part.partitioned_table_fr_gamma PARTITION OF part.partitioned_table
FOR VALUES FROM ('FR', 'GAMMA', MINVALUE) TO ('FR', 'GAMMA', MAXVALUE);
CREATE TABLE part.partitioned_table_de_alpha PARTITION OF part.partitioned_table
FOR VALUES FROM ('DE', 'ALPHA', MINVALUE) TO ('DE', 'ALPHA', MAXVALUE);
CREATE TABLE part.partitioned_table_de_beta PARTITION OF part.partitioned_table
FOR VALUES FROM ('DE', 'BETA', MINVALUE) TO ('DE', 'BETA', MAXVALUE);
CREATE TABLE part.partitioned_table_de_gamma PARTITION OF part.partitioned_table
FOR VALUES FROM ('DE', 'GAMMA', MINVALUE) TO ('DE', 'GAMMA', MAXVALUE);
INSERT INTO part.partitioned_table(country, sector, a_value, other_value) VALUES
('GB', 'ALPHA', 10, 1000),
('GB', 'BETA', 10, 1000),
('GB', 'GAMMA', 10, 1000),
('FR', 'ALPHA', 10, 1000),
('FR', 'BETA', 10, 1000),
('FR', 'GAMMA', 10, 1000),
('DE', 'ALPHA', 10, 1000),
('DE', 'BETA', 10, 1000),
('DE', 'GAMMA', 10, 1000);
-- query plan for this statement shows that only a single partition is scanned as expected (partitioned_table_gb_beta)
EXPLAIN ANALYSE SELECT * FROM part.partitioned_table WHERE country = 'GB' and sector = 'BETA';
-- adding a sector to the where clause causes ALL 'GB' partitions to be scanned
EXPLAIN ANALYSE SELECT * FROM part.partitioned_table WHERE country = 'GB' and sector in ('BETA', 'GAMMA');
-- instead adding a country to the where clause causes ALL 'GB' and 'FR' partitions to be scanned!
EXPLAIN ANALYSE SELECT * FROM part.partitioned_table WHERE country in ('GB', 'FR') and sector = 'BETA';

注意，即使我使用'OR'逻辑而不是'IN'逻辑，也会扫描相同的分区。如果需要，我可以添加explain analyse输出。

那么首先，为什么扫描所有'GB'分区只是因为多个扇区被指定?

其次，也许更奇怪的是，如果我坚持一个单一扇区(在我的例子中是'BETA')并添加第二个国家，它现在扫描每个指定国家的所有分区，而不是扫描所有'BETA'分区。

显然，真实的世界表包含数百个国家和部门。我们用例查询多个国家的单个扇区(例如，假设20个)，并最终扫描数百个分区(这20个国家的所有扇区分区)，当只需要扫描20个分区时，这是"明确的";

我们是否需要创建一个主要按扇区分区的表的另一个版本来克服这个问题，或者我们在这里遗漏了什么?

Answer 1

使用子分区使查询规划器的行为更加合理。因为这也允许我们partition by list，所以不需要人为地将整数列包含在分区键to range on:

中。

CREATE TABLE part.sub_partitioned_table
(
country character varying NOT NULL,
sector character varying NOT NULL,
a_value integer NOT NULL,
other_value integer,
CONSTRAINT sub_partitioned_table_pkey PRIMARY KEY (country, sector, a_value)
) PARTITION BY LIST (country);
CREATE TABLE part.sub_partitioned_table_de PARTITION OF part.sub_partitioned_table
FOR VALUES IN ('DE')
PARTITION BY LIST (sector);
CREATE TABLE part.sub_partitioned_table_gb PARTITION OF part.sub_partitioned_table
FOR VALUES IN ('GB')
PARTITION BY LIST (sector);
CREATE TABLE part.sub_partitioned_table_fr PARTITION OF part.sub_partitioned_table
FOR VALUES IN ('FR')
PARTITION BY LIST (sector);
CREATE TABLE part.sub_partitioned_table_gb_alpha PARTITION OF part.sub_partitioned_table_gb
FOR VALUES IN ('ALPHA');
CREATE TABLE part.sub_partitioned_table_gb_beta PARTITION OF part.sub_partitioned_table_gb
FOR VALUES IN ('BETA');
CREATE TABLE part.sub_partitioned_table_gb_gamma PARTITION OF part.sub_partitioned_table_gb
FOR VALUES IN ('GAMMA');
CREATE TABLE part.sub_partitioned_table_fr_alpha PARTITION OF part.sub_partitioned_table_fr
FOR VALUES IN ('ALPHA');
CREATE TABLE part.sub_partitioned_table_fr_beta PARTITION OF part.sub_partitioned_table_fr
FOR VALUES IN ('BETA');
CREATE TABLE part.sub_partitioned_table_fr_gamma PARTITION OF part.sub_partitioned_table_fr
FOR VALUES IN ('GAMMA');    
CREATE TABLE part.sub_partitioned_table_de_alpha PARTITION OF part.sub_partitioned_table_de
FOR VALUES IN ('ALPHA');
CREATE TABLE part.sub_partitioned_table_de_beta PARTITION OF part.sub_partitioned_table_de
FOR VALUES IN ('BETA');
CREATE TABLE part.sub_partitioned_table_de_gamma PARTITION OF part.sub_partitioned_table_de
FOR VALUES IN ('GAMMA');
INSERT INTO part.sub_partitioned_table(country, sector, a_value, other_value) VALUES
('GB', 'ALPHA', 10, 1000),
('GB', 'BETA', 10, 1000),
('GB', 'GAMMA', 10, 1000),
('FR', 'ALPHA', 10, 1000),
('FR', 'BETA', 10, 1000),
('FR', 'GAMMA', 10, 1000),
('DE', 'ALPHA', 10, 1000),
('DE', 'BETA', 10, 1000),
('DE', 'GAMMA', 10, 1000);


EXPLAIN ANALYSE SELECT * FROM part.sub_partitioned_table WHERE country = 'GB' and sector = 'BETA';
EXPLAIN ANALYSE SELECT * FROM part.sub_partitioned_table WHERE country = 'GB' and sector in ('ALPHA', 'BETA');
EXPLAIN ANALYSE SELECT * FROM part.sub_partitioned_table WHERE country = 'GB' and (sector = 'ALPHA' or sector = 'BETA');
EXPLAIN ANALYSE SELECT * FROM part.sub_partitioned_table WHERE country in ('GB', 'FR') and sector = 'BETA';
EXPLAIN ANALYSE SELECT * FROM part.sub_partitioned_table WHERE (country = 'GB' or country = 'FR') and sector = 'BETA';
EXPLAIN ANALYSE SELECT * FROM part.sub_partitioned_table WHERE
((sector = 'BETA' AND country = 'GB') OR
(sector = 'BETA' AND country = 'FR'))

EXPLAIN ANALYSE SELECT * FROM part.sub_partitioned_table WHERE
((sector = 'BETA' AND country = 'GB') OR
(sector = 'ALPHA' AND country = 'GB'))

在所有情况下，查询计划显示只扫描相关(子)分区。