postgresql - 什么时候使用多列索引？

Question

我有下表，大约有 1000 万行

CREATE TABLE "autocomplete_books"
(
    id uuid PRIMARY KEY DEFAULT uuid_generate_v4 (),
    author_id uuid NOT NULL REFERENCES "author"(id) ON DELETE CASCADE,
    language VARCHAR(30) NOT NULL,
    name VARCHAR(100) NOT NULL,
    importance_rank SMALLINT NOT NULL DEFAULT 1
);

我有以下查询

SELECT DISTINCT ON (author_id)
    author_id,
    similarity(name, $1) as score,
    language, name, importance_rank
FROM
    "autocomplete_books"
WHERE
    $1 % name AND language IN ($2, $3, $4)
ORDER BY
    author_id, score DESC, importance_rank DESC
LIMIT
    10

我主要查询相似性，因为这是一个自动完成端点，所以我在名称上有一个三元组索引。我也在对其他一些领域进行排序。我不确定 score 字段将如何与我的其他索引混合使用，也不确定使用这样的复合索引是否更好

选项 1

CREATE INDEX ON "autocomplete_books" USING GIN (name gin_trgm_ops);
CREATE INDEX ON "autocomplete_books" USING BTREE (author_id, language, importance_rank DESC);

或者如果我应该像这样把它们分开

选项 2

CREATE INDEX ON "autocomplete_books" USING GIN (name gin_trgm_ops);
CREATE INDEX ON "autocomplete_books" USING BTREE (author_id, language, importance_rank DESC);
CREATE INDEX ON "autocomplete_books" USING BTREE (language);
CREATE INDEX ON "autocomplete_books" USING BTREE (importance_rank DESC);

---

这是 explain analyze 的输出，在 220k 行上运行，索引如下

CREATE INDEX ON "autocomplete_books" USING BTREE (author_id, language);
CREATE INDEX ON "autocomplete_books" USING BTREE (importance_rank DESC);

-

Limit  (cost=762.13..762.38 rows=50 width=82) (actual time=12.230..13.024 rows=50 loops=1)
  ->  Unique  (cost=762.13..763.23 rows=217 width=82) (actual time=12.223..12.686 rows=50 loops=1)
        ->  Sort  (cost=762.13..762.68 rows=220 width=82) (actual time=12.216..12.373 rows=50 loops=1)
              Sort Key: author_id, ((similarity((name)::text, \'sale\'::text)) DESC, importance_rank DESC
              Sort Method: quicksort  Memory: 45kB
              ->  Bitmap Heap Scan on "books_autocomplete" mat  (cost=45.71..753.57 rows=220 width=82) (actual time=1.905..11.610 rows=149 loops=1)
                    Recheck Cond: (\'sale\'::text % (name)::text)
                    Rows Removed by Index Recheck: 2837
                    Filter: ((language)::text = ANY (\'{language1,language2,language3}\'::text[]))
                    Heap Blocks: exact=2078
                    ->  Bitmap Index Scan on "books_autocomplete_name_idx"  (cost=0.00..45.65 rows=220 width=0) (actual time=1.551..1.557 rows=2986 loops=1)
                          Index Cond: (\'sale\'::text % (name)::text)
Planning time: 13.976 ms
Execution time: 13.545 ms'

Answer 1

只有当 ORDER BY 子句中的所有 表达式都在索引中时，索引才会帮助您进行排序，而您不能这样做，因为第二个表达式.

此外，只有 b 树索引可用于支持 ORDER BY。现在当你想使用 ORDER BY 时你不能组合多个索引，你说 $1 % name 是你最有选择的标准，所以你可能想在那个。

这个查询有两种方式:

1. 使用 name 上的 trigram GIN 索引寻找 $1 % name 条件。这就是您问题中的执行计划所做的。然后你将不得不忍受那个 Sort，因为你不能为它使用索引。这里的危险是位图索引扫描会找到太多的行，以至于位图堆扫描的开销非常大。

2. 如果存在与 ORDER BY 子句完全匹配的索引:

   CREATE INDEX ON autocomplete_books
      (author_id, score DESC, importance_rank DESC);
   

   您可以扫描索引并以 ORDER BY 顺序获取行，直到有 10 行符合过滤条件 $1 % name。这里的危险在于，找到 10 行所需的时间可能比预期的要长。

首先尝试仅使用一个索引，然后仅使用另一个索引，并在实际大小的数据集上使用不同的参数运行查询，以查看最佳效果。

除这两个索引外，您应该删除所有其他索引，因为它们对此查询没有任何好处。

如果两种策略中的一种明显胜出，则删除另一个索引，这样优化器就不会使用它。否则保留两者并希望优化器根据参数选择正确的一个。