Indexes

Halo database supports all PostgreSQL index types.

B-Tree Index

The default index type, suitable for equality queries and range queries.

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 Index

Suitable for simple equality queries.

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);

Composite Index

Creating an index on multiple columns.

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';

Unique Index

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

Expression Index

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

Partial Index

Creates an index only for rows that satisfy a condition.

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

GIN Index

Suitable for full-text search and composite data types such as 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);