在 Postgres 9.4.11 数据库中,我有一个很大的书籍表,它与作者有一对多的关系。理论上为了提高性能,我在电子书记录中缓存了作者姓名以便快速搜索。列的属性authors_cache
:
示例行:
我在此列上创建了一个 GIN 索引:
CREATE INDEX "index_ebooks_on_authors_cache" ON "ebooks" USING gin ("authors_cache")
它在搜索时不使用索引authors_cache
并且花费了不可接受的时间:
EXPLAIN ANALYZE
SELECT * FROM "ebooks"
WHERE ('Charles Bukowski' = ANY (authors_cache))
LIMIT 60 OFFSET 0;
结果:
Limit (cost=0.00..172334.67 rows=30 width=124) (actual time=71.962..10067.070 rows=59 loops=1)
-> Seq Scan on ebooks (cost=0.00..172334.67 rows=30 width=124) (actual time=71.960..10067.015 rows=59 loops=1)
Filter: ('Charles Bukowski'::text = ANY ((authors_cache)::text[]))
Rows Removed by Filter: 894504
Planning time: 0.188 ms
Execution time: 10067.112 ms
10 秒是不可接受的时间量。我对设计更改持开放态度,因为我还没有“嫁给”这个专栏。
使用GIN 索引支持的数组运算符。
= ANY ()
结构不是。详细解释:
不确定你的去规范化会给你带来多少好处。具有简单 btree 索引的标准 1:n 实现也应该表现良好。
如果您要使用这种方法,我会考虑存储一个整数 (
int[]
) 数组和author_id
's。大大缩小数组列和 GIN 索引。参照完整性的潜在问题大致相同。(数组元素目前不能有 FK 约束。到目前为止,在 Postgres 中实现这个的尝试是不成功的。)哦,考虑升级。自 Postgres 9.4 以来,GIN 索引的性能得到了显着改善。