分區表全局索引
更新時間 2025-02-05 09:36:27
最近更新時間: 2025-02-05 09:36:27
分享文章
本文為您介紹如何管理分區表全局索引。
支持創建分區表全局索引，目前僅支持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)
