PostgreSQL分区剪枝和btree_gin索引存在冲突

在PostgreSQL中，分区表是一种将庞大数据集分解为更小和更可管理数据块的方法。分区表可在相同的表结构中存储大量数据并获得更优秀的性能。常见的分区策略包括按范围、按列表和按哈希函数。有时，使用btree_gin索引时会出现分区剪枝失败的情况。

以下代码是一个示例表的定义，它使用按范围的分区策略：

CREATE TABLE order_lineitems ( id SERIAL, order_id INT NOT NULL, item_id INT NOT NULL, quantity INT NOT NULL, total_price NUMERIC(10,2) NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT now() ) PARTITION BY RANGE(created_at);

-- 创建两个分区 CREATE TABLE order_lineitems_2019 ( CHECK(created_at >= '2019-01-01' AND created_at < '2020-01-01') ) INHERITS (order_lineitems);

CREATE TABLE order_lineitems_2020 ( CHECK(created_at >= '2020-01-01' AND created_at < '2021-01-01') ) INHERITS (order_lineitems);

现在，我们使用btree_gin索引来加速查询，例如：

CREATE INDEX ix_order_lineitems_order_id ON order_lineitems USING GIN (order_id);

然后，我们尝试执行以下查询：

SELECT * FROM order_lineitems WHERE order_id = 123 AND created_at BETWEEN '2020-01-01' AND '2021-01-01';

我们期望PostgreSQL只扫描order_lineitems_2020分区，但实际上PostgreSQL扫描了整个表。

解决方法是在btree_gin索引上添加created_at列作为它的第二列，例如：

CREATE INDEX ix_order_lineitems_order_id_created_at ON order_lineitems (order_id, created_at) USING GIN;

这将修复分区剪枝问题，查询现在只扫描order_lineitems_2020分区，执行速度更快。同时，它也适用于其他类似的查询。