HALO is a powerful database with many advanced features. One of the most commonly used features is indexing, which can significantly improve query efficiency.
What is an Index?
In a database, without an index, every query requires a full table scan. This significantly slows down query speed, especially when the data volume is large. Therefore, an index is a data structure used to accelerate data retrieval. Its principle is similar to a book's directory: by recording keywords and their physical locations, it enables rapid positioning.
HALO supports various types of indexes, each with specific application scenarios and performance characteristics. You need to select the appropriate index type based on actual query patterns and data characteristics.
The following are the index types supported by the HALO database:
1. B-tree Index
The B-tree index is the most common and universal index type in HALO, suitable for equality queries, range queries, and sorting operations.
-- Create table and insert data
CREATE TABLE student (
id SERIAL PRIMARY KEY,
name VARCHAR(50) NOT NULL,
age INTEGER NOT NULL,
grade VARCHAR(10) NOT NULL
);
INSERT INTO student (name, age, grade)
VALUES ('Alice', 18, 'High School 1'),
('Bob', 17, 'High School 1'),
('Cathy', 16, 'Junior High 3'),
('David', 15, 'Junior High 2'),
('Emily', 14, 'Junior High 1');
-- Create B-tree index
CREATE INDEX idx_student_id ON student (id);2. Hash Index
Hash indexes use a hash algorithm to achieve extremely fast equality lookups but do not support range queries, sorting, or fuzzy matching. Suitable for high-concurrency exact match scenarios, with high memory and storage overhead.
CREATE INDEX idx_student_id ON student USING hash (id);3. GIN Index
GIN (Generalized Inverted Index) is suitable for multi-value queries on complex data types such as arrays, JSON, and full-text search. Supports operators like ANY, @>, and &&, but has high construction and maintenance costs.
-- Create table and insert data
CREATE TABLE mytable1 (
id SERIAL PRIMARY KEY,
title TEXT NOT NULL,
tags TEXT[] NOT NULL
);
INSERT INTO mytable1 (title, tags) VALUES
('HALO GIN Index', ARRAY['HALO', 'Database', 'Index']),
('JavaScript Frameworks', ARRAY['JavaScript', 'Framework']),
('Python Web Development', ARRAY['Python', 'Web', 'Development']),
('Data Science with R', ARRAY['Data', 'Science', 'R']),
('Introduction to Docker', ARRAY['Docker']);
-- Create GIN index
CREATE INDEX mytable_tags_gin_idx ON mytable1 USING gin(tags);
-- Query example
SELECT * FROM mytable1 WHERE 'Database' = ANY(tags);4. GiST Index
GiST (Generalized Search Tree) is a generic index structure suitable for approximate or range queries on complex data types such as geometry, geography, text similarity, and network addresses.
CREATE TABLE locations (
id SERIAL PRIMARY KEY,
name VARCHAR(50),
point GEOMETRY(Point, 4326)
);
-- Create GiST index
CREATE INDEX idx_locations_point_gist ON locations USING gist(point);5. BRIN Index
BRIN (Block Range Index) is suitable for large-scale ordered data (such as time series). It records minimum/maximum values by data block, occupying extremely small space, suitable for filtering large ranges of invalid data blocks.
-- Create table and test data
CREATE TABLE sales (
id SERIAL PRIMARY KEY,
sale_date DATE NOT NULL,
amount NUMERIC(10, 2) NOT NULL
);
INSERT INTO sales (sale_date, amount)
VALUES ('2023-05-16', 100.00),
('2023-05-17', 200.00),
('2023-05-18', 300.00);
-- Create BRIN index
CREATE INDEX sales_date_brin_idx ON sales USING BRIN (sale_date);
-- Query example
SELECT sum(amount) FROM sales WHERE sale_date BETWEEN '2023-05-16' AND '2023-05-18';6. Bloom Index
Bloom indexes are based on Bloom filters, a probabilistic index used to quickly exclude rows that cannot possibly match. Suitable for equality queries on high-cardinality columns, it can significantly reduce I/O but has a very small false positive rate.
-- Create test table and data
CREATE TABLE test_bloom (id SERIAL PRIMARY KEY, name TEXT);
INSERT INTO test_bloom(name) SELECT 'name_' || i FROM generate_series(1, 1000000) AS i;
-- Create extension
CREATE EXTENSION bloom;
-- Create Bloom index
CREATE INDEX test_bloom_name_bloom_idx ON test_bloom USING bloom (name);
-- Note: Bloom does not support LIKE, this is just an example
SELECT * FROM test_bloom WHERE name = 'name_500000';7. RUM Index
RUM is an advanced index optimized for full-text search, supporting trigram similarity, distance calculation, and efficient sorting. Compared to GIN, RUM can provide relevance scores and position information while returning results.
-- Create table and data
CREATE TABLE t (
id SERIAL PRIMARY KEY,
content TEXT NOT NULL
);
INSERT INTO t (content)
VALUES ('The quick brown fox jumps over the lazy dog.'),
('How vexingly quick daft zebras jump!'),
('Jived fox nymph grabs quick waltz.'),
('Glib jocks quiz nymph to vex dwarf.'),
('Jackdaws love my big sphinx of quartz.'),
('Pack my box with five dozen liquor jugs.'),
('The five boxing wizards jump quickly.');
-- Create extension
CREATE EXTENSION IF NOT EXISTS pg_trgm;
-- Create RUM index
CREATE INDEX idx_t_content_rum ON t USING rum(content rum_trgm_ops);
-- Use % operator for similarity query (requires pg_trgm)
SELECT * FROM t WHERE content % 'search term';These are the main index types supported by the HALO database. Reasonably selecting and combining these indexes can significantly enhance query performance and system efficiency.