PostgreSQL扩展bloom的具体使用

-- 安装扩展
CREATE EXTENSION bloom;

-- 验证安装
SELECT extname, extversion FROM pg_extension WHERE extname = 'bloom';

CREATE INDEX index_name ON table_name USING bloom (col1, col2, ...)
WITH (length=..., col1=..., col2=...);

-- 在用户表上创建多列bloom索引
CREATE INDEX users_bloom_idx ON users USING bloom 
(first_name, last_name, email, department)
WITH (length=100, first_name=5, last_name=5, email=6, department=3);

-- 多列组合查询
EXPLAIN ANALYZE SELECT * FROM users 
WHERE first_name = 'John' AND department = 'Engineering';

-- IN列表查询
EXPLAIN ANALYZE SELECT * FROM users 
WHERE email IN ('a@example.com', 'b@example.com');

-- 根据数据特征调整
CREATE INDEX optimized_bloom_idx ON large_table 
USING bloom (col1, col2, col3)
WITH (length=200, col1=4, col2=4, col3=4, false_positive=0.005);

位数选择 ≈ -n·ln(p) / (ln(2))²
其中：
n = 预计唯一值数量
p = 可接受的误报率

-- 实际查询应处理可能的误报
SELECT * FROM users 
WHERE first_name = 'John' AND department = 'Engineering'
AND first_name_bloom = 'John'  -- 使用索引列
AND department_bloom = 'Engineering';

REINDEX INDEX users_bloom_idx;

SELECT * FROM pg_stat_user_indexes 
WHERE indexrelname = 'users_bloom_idx';