索引与分区表

索引类型

Halo 数据库支持 PostgreSQL 的全部索引类型。

B-Tree 索引

默认的索引类型，适用于等值查询和范围查询。

postgres=# CREATE TABLE t_btree (id INT, info TEXT);
CREATE TABLE
postgres=# INSERT INTO t_btree SELECT generate_series(1,10000), md5(random()::text);
INSERT 0 10000
postgres=# CREATE INDEX idx_t_btree_1 ON t_btree USING btree (id);
CREATE INDEX
postgres=# EXPLAIN (ANALYZE, VERBOSE, COSTS, BUFFERS) SELECT * FROM t_btree WHERE id = 1;
                                                      QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on public.t_btree  (cost=1.77..42.64 rows=50 width=36) (actual time=0.059..0.060 rows=1 loops=1)
   Output: id, info
   Recheck Cond: (t_btree.id = 1)
   Heap Blocks: exact=1
   Buffers: shared hit=1 read=2
   ->  Bitmap Index Scan on idx_t_btree_1  (cost=0.00..1.76 rows=50 width=0) (actual time=0.051..0.051 rows=1 loops=1)
         Index Cond: (t_btree.id = 1)
         Buffers: shared read=2
 Query Identifier: -2279879673129888102
 Planning:
   Buffers: shared hit=24 read=1
 Planning Time: 0.477 ms
 Execution Time: 0.117 ms
(13 行记录)

Hash 索引

适用于简单的等值查询。

postgres=# CREATE TABLE t_hash (id INT, info TEXT);
CREATE TABLE
postgres=# INSERT INTO t_hash SELECT generate_series(1,1000), repeat(md5(random()::text), 10000);
INSERT 0 1000
postgres=# CREATE INDEX idx_t_hash_1 ON t_hash USING hash (info);
CREATE INDEX

组合索引 / 唯一索引 / 表达式索引 / 部分索引

-- 组合索引（多字段）
postgres=# CREATE TABLE t_compound (
    id INT,
    date_created DATE,
    info TEXT,
    PRIMARY KEY (id, date_created)
);
CREATE TABLE
postgres=# CREATE INDEX idx_t_compound ON t_compound USING btree (id, date_created);
CREATE INDEX

-- 唯一索引
postgres=# CREATE TABLE t_unique (id INT PRIMARY KEY, info TEXT);
CREATE TABLE
postgres=# CREATE UNIQUE INDEX idx_t_unique_1 ON t_unique USING btree (id);
CREATE INDEX

-- 表达式索引（基于函数/表达式）
postgres=# CREATE TABLE halo_test (a SERIAL, b NUMERIC);
CREATE TABLE
postgres=# CREATE INDEX b_expr ON halo_test (trunc(b));
CREATE INDEX

-- 部分索引（只对满足条件的行建索引）
postgres=# CREATE TABLE t_part (id SERIAL PRIMARY KEY, status TEXT, amount NUMERIC);
CREATE TABLE
postgres=# CREATE INDEX idx_t_part_active ON t_part (amount) WHERE status = 'ACTIVE';
CREATE INDEX

GIN 索引

适用于全文搜索和 JSONB 等复合数据类型。

postgres=# CREATE TABLE t_gin (id SERIAL, data JSONB);
CREATE TABLE
postgres=# INSERT INTO t_gin (data) VALUES ('{"tags": ["database", "halo"]}');
INSERT 0 1
postgres=# CREATE INDEX idx_t_gin ON t_gin USING gin (data);
CREATE INDEX

分区表

Halo 支持 RANGE、LIST、HASH 三种分区方式，并支持多级组合分区。

RANGE 范围分区

按值的范围分区，适合按时间或数值范围组织数据。

postgres=# CREATE TABLE test_range (
    city_id NUMERIC(38,0) NOT NULL,
    logdate DATE NOT NULL,
    peaktemp NUMERIC(38,0),
    unitsales NUMERIC(38,0)
) PARTITION BY RANGE (logdate);
CREATE TABLE
postgres=# CREATE TABLE test_range_default PARTITION OF test_range DEFAULT;
CREATE TABLE
postgres=# CREATE TABLE test_range_y2006m02 PARTITION OF test_range
postgres-#     FOR VALUES FROM ('2006-02-01') TO ('2006-03-01');
CREATE TABLE
postgres=# CREATE TABLE test_range_y2007m11 PARTITION OF test_range
postgres-#     FOR VALUES FROM ('2007-11-01') TO ('2007-12-01');
CREATE TABLE
postgres=# CREATE INDEX idx_test_range_1 ON test_range USING btree (logdate);
CREATE INDEX
postgres=# INSERT INTO test_range VALUES (1, '2006-02-07', 20, 30);
INSERT 0 1
postgres=# INSERT INTO test_range VALUES (3, '2007-11-07', 40, 50);
INSERT 0 1

分区裁剪查询（只扫描目标分区）：

postgres=# EXPLAIN ANALYZE SELECT * FROM test_range WHERE logdate = '2006-02-07';
                                                               QUERY PLAN                                                               
----------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on test_range_y2006m02 test_range  (cost=1.27..4.45 rows=3 width=94) (actual time=0.056..0.058 rows=1 loops=1)
   Recheck Cond: (logdate = '2006-02-07'::date)
   Heap Blocks: exact=1
   ->  Bitmap Index Scan on test_range_y2006m02_logdate_idx  (cost=0.00..1.27 rows=3 width=0) (actual time=0.005..0.005 rows=1 loops=1)
         Index Cond: (logdate = '2006-02-07'::date)
 Planning Time: 7.083 ms
 Execution Time: 0.255 ms
(7 行记录)

上例中查询计划只扫描了 test_range_y2006m02 一个分区，说明分区裁剪生效——查询条件命中的数据只存在于该分区，其他分区被自动跳过。

HASH 分区

按哈希值均匀分布数据，适合数据量大且无明显范围特征的场景。

postgres=# CREATE TABLE test_hash (
    order_id NUMERIC(38,0) NOT NULL,
    cust_id NUMERIC(38,0),
    status TEXT
) PARTITION BY HASH (order_id);
CREATE TABLE
postgres=# CREATE TABLE test_hash_01 PARTITION OF test_hash FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE
postgres=# CREATE TABLE test_hash_02 PARTITION OF test_hash FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE
postgres=# CREATE TABLE test_hash_03 PARTITION OF test_hash FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE
postgres=# CREATE TABLE test_hash_04 PARTITION OF test_hash FOR VALUES WITH (MODULUS 4, REMAINDER 3);
CREATE TABLE
postgres=# INSERT INTO test_hash SELECT id, id, md5(id::text) FROM generate_series(1,1000) AS id;
INSERT 0 1000

说明：HASH 分区按 MODULUS N 取模分布，需为每一个 REMAINDER（0 到 N-1）都建立对应分区，否则插入哈希到未建分区的行时会报错。上例 MODULUS 4 需建 4 个分区。

分区裁剪查询（只扫描哈希命中的目标分区）：

postgres=# EXPLAIN ANALYZE SELECT * FROM test_hash WHERE order_id = 123;
                                            QUERY PLAN                                            
--------------------------------------------------------------------------------------------------
 Seq Scan on test_hash_01 test_hash  (cost=0.00..18.50 rows=3 width=92) (actual time=0.041..0.078 rows=1 loops=1)
   Filter: (order_id = '123'::numeric)
   Rows Removed by Filter: 278
 Planning Time: 0.833 ms
 Execution Time: 0.127 ms
(5 行记录)

上例中查询计划只扫描了 test_hash_01 一个分区——order_id=123 哈希后命中该分区，其他分区被自动跳过，说明分区裁剪生效。

LIST 列表分区

按离散值列表分区，适合按类别、地区等固定值组织数据。

postgres=# CREATE TABLE test_list (
    id NUMERIC(38,0) NOT NULL,
    pname VARCHAR(30),
    sex VARCHAR(10) NOT NULL
) PARTITION BY LIST (sex);
CREATE TABLE
postgres=# CREATE TABLE test_list_default PARTITION OF test_list DEFAULT;
CREATE TABLE
postgres=# CREATE TABLE test_list_male PARTITION OF test_list FOR VALUES IN ('MAN', '男');
CREATE TABLE
postgres=# CREATE TABLE test_list_female PARTITION OF test_list FOR VALUES IN ('WOMAN', '女');
CREATE TABLE
postgres=# INSERT INTO test_list VALUES (1, '瑶瑶', '男');
INSERT 0 1
postgres=# INSERT INTO test_list VALUES (2, '倩倩', 'WOMAN');
INSERT 0 1
postgres=# INSERT INTO test_list VALUES (3, '优优', 'GOD');
INSERT 0 1
postgres=# EXPLAIN ANALYZE SELECT * FROM test_list WHERE sex = 'GOD';
-- 查询只扫描 test_list_default 分区（GOD 不在 male/female 列表中）

多级组合分区

支持 LIST + RANGE + HASH 等多级嵌套分区：

postgres=# CREATE TABLE test_lrh (
    id INTEGER,
    status TEXT,
    arr NUMERIC
) PARTITION BY LIST (status);

-- 第一级 LIST 分区
postgres=# CREATE TABLE test_lrh_active PARTITION OF test_lrh
postgres-#     FOR VALUES IN ('ACTIVE','RECURRING','REACTIVATED') PARTITION BY RANGE (arr);

-- 第二级 RANGE 分区
postgres=# CREATE TABLE test_lrh_active_arr_small PARTITION OF test_lrh_active
postgres-#     FOR VALUES FROM (MINVALUE) TO (101) PARTITION BY HASH (id);

-- 第三级 HASH 分区
postgres=# CREATE TABLE test_lrh_active_arr_small01 PARTITION OF test_lrh_active_arr_small
postgres-#     FOR VALUES WITH (MODULUS 2, REMAINDER 0);
postgres=# CREATE TABLE test_lrh_active_arr_small02 PARTITION OF test_lrh_active_arr_small
postgres-#     FOR VALUES WITH (MODULUS 2, REMAINDER 1);

分区维护

-- 分区下线（从主表分离为独立表）
ALTER TABLE test_lrh DETACH PARTITION test_lrh_other;

-- 分区上线（将独立表挂载回主表）
ALTER TABLE test_lrh ATTACH PARTITION test_lrh_other DEFAULT;

-- 分区改名
ALTER TABLE test_lrh_active RENAME TO test_lrh_status_active;

索引类型​

分区表​

索引类型

分区表