索引

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

B-Tree 索引

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

CREATE TABLE t_btree (id INT, info TEXT);
INSERT INTO t_btree SELECT generate_series(1,10000), md5(random()::text);
CREATE INDEX idx_t_btree_1 ON t_btree USING btree (id);

EXPLAIN (ANALYZE, VERBOSE, COSTS, BUFFERS)
SELECT * FROM t_btree WHERE id = 1;

Hash 索引

适用于简单的等值查询。

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

组合索引

在多个字段上创建索引。

CREATE TABLE t_compound (
    id INT,
    date_created DATE,
    info TEXT,
    PRIMARY KEY (id, date_created)
);
CREATE INDEX idx_t_compound ON t_compound USING btree (id, date_created);

EXPLAIN (ANALYZE, VERBOSE)
SELECT * FROM t_compound WHERE id = 555 AND date_created = '2025-10-16';

唯一索引

CREATE TABLE t_unique (id INT PRIMARY KEY, info TEXT);
CREATE UNIQUE INDEX idx_t_unique_1 ON t_unique USING btree (id);

表达式索引

CREATE TABLE halo_test (a SERIAL, b NUMERIC);
CREATE INDEX b_expr ON halo_test (trunc(b));

部分索引

只对满足条件的行创建索引。

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    status TEXT,
    amount NUMERIC
);
CREATE INDEX idx_orders_active ON orders (amount) WHERE status = 'ACTIVE';

GIN 索引

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

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