主页 / dba / 问题 / 187959
Accepted
Andrew Wei
Asked: 2017-10-08 16:46:01 +0800 CST

查询任何（列）时不搜索 GIN 索引

  • 772

在 Postgres 9.4.11 数据库中，我有一个很大的书籍表，它与作者有一对多的关系。理论上为了提高性能，我在电子书记录中缓存了作者姓名以便快速搜索。列的属性authors_cache：

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 秒是不可接受的时间量。我对设计更改持开放态度，因为我还没有“嫁给”这个专栏。

postgresql index

1 个回答

  1. Best Answer

    使用GIN 索引支持的数组运算符。= ANY ()结构不是。

    SELECT *
FROM   ebooks
WHERE  authors_cache @> '{Charles Bukowski}'  -- array literal
LIMIT  60;

    详细解释：

    不确定你的去规范化会给你带来多少好处。具有简单 btree 索引的标准 1:n 实现也应该表现良好。

    如果您要使用这种方法，我会考虑存储一个整数 (int[]) 数组和author_id's。大大缩小数组列和 GIN 索引。参照完整性的潜在问题大致相同。（数组元素目前不能有 FK 约束。到目前为止，在 Postgres 中实现这个的尝试是不成功的。）

    哦，考虑升级。自 Postgres 9.4 以来，GIN 索引的性能得到了显着改善。

    • 7

相关问题