支持创建分区表全局索引,目前仅支持btree索引类型
-- 建表
CREATE TABLE parent_range_tbl(
c1 int not null,
c2 int not null,
c3 int not null
) PARTITION BY RANGE (c1);
CREATE TABLE part_0_100 PARTITION OF parent_range_tbl FOR VALUES FROM (0) TO (100);
CREATE TABLE part_100_200 PARTITION OF parent_range_tbl FOR VALUES FROM (100) TO (200);
CREATE TABLE part_default PARTITION OF parent_range_tbl DEFAULT;
-- 插入数据
INSERT INTO parent_range_tbl(c1,c2,c3) SELECT generate_series(50, 100) AS c1, generate_series(50, 100) AS c2, generate_series(50, 100) AS c3;
-- 创建全局索引
CREATE INDEX c3_global_idx ON parent_range_tbl(c3) GLOBAL;
-- 设置不使用seq scan
set enable_seqscan TO off;
-- 通过全局索引查询数据
EXPLAIN SELECT c3 FROM parent_range_tbl WHERE c3 > 70 AND c3 < 75 ORDER BY c3;
QUERY PLAN
----------------------------------------------------------------------------------------
---------
Remote Subquery Scan on all (dn01,dn02) (cost=0.12..8.14 rows=9 width=4)
-> Index Only Scan using c3_global_idx on parent_range_tbl (cost=0.12..8.14 rows=9
width=4)
Index Cond: ((c3 > 70) AND (c3 < 75))
(3 rows)