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

我有大约一千万行的下表

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

我主要在相似性上进行查询，因为这是一个自动完成的终结点，因此我在名称上有一个trigram索引。我还在其他领域进行排序。我不确定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);

这是在220k行上使用以下索引运行的explain analyze的输出

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'

最佳答案

如果ORDER BY子句中的所有表达式都在索引中，则索引只会帮助您进行排序，而由于第二个表达式，您将无法进行排序。

另外，只有b树索引对于支持ORDER BY有用。现在，当您想使用ORDER BY时，您不能合并多个索引，并且您说$1 % name是您的最有选择性的标准，因此您可能希望在其上使用索引。

该查询可以采用两种方式:

在$1 % name上使用三字母GIN索引查找name条件。
这就是您问题中的执行计划。
然后，您将不得不使用Sort，因为您不能为其使用索引。这里的危险是，位图索引扫描将发现太多行，以至于位图堆扫描非常昂贵。

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

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

您可以扫描索引并按ORDER BY顺序获取行，直到有10个与过滤条件$1 % name相匹配的行。这里的危险是找到这10行的时间可能比预期的要长。

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

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

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

关于postgresql - 什么时候使用多列索引？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/53674323/