Postgresql 不使用多列索引 (btree_gin)

Question

我在让 postgres 使用我的多列索引使用 btree_gin 扩展进行完整搜索时遇到问题。这是针对文章的搜索页面。使用btree_gin背后的想法是能够获取用于排序的"id"字段，并将magazine_id作为过滤器：

CREATE INDEX idx_gin_search ON article USING gin(id, magazine_id, search_vector_full) WITH (fastupdate = off);

Postgres决定在杂志上使用btree索引，然后过滤（=慢）：

Executed SQL
SELECT ••• FROM article WHERE (( (article.search_vector) @@    
(plainto_tsquery('pg_catalog.english', 'interesting'))) AND    
article.magazine_id = 7) ORDER BY article.id ASC LIMIT 36
Time 13.4780406952 ms
QUERY PLAN
Limit  (cost=2021.87..2021.96 rows=36 width=384) (actual time=9.782..9.787 rows=36 loops=1)
  ->  Sort  (cost=2021.87..2027.49 rows=2248 width=384) (actual time=9.781..9.784 rows=36 loops=1)
    Sort Key: id
    Sort Method: top-N heapsort  Memory: 53kB
    ->  Index Scan using idx_magazine_id on article (cost=0.29..1952.53 rows=2248 width=384) (actual time=0.035..8.924 rows=2249 loops=1)
          Index Cond: (magazine_id = 7)
          Filter: (search_vector @@ '''interesting'''::tsquery)
          Rows Removed by Filter: 11413
Planning time: 4.600 ms
Execution time: 9.860 ms

---

然后，我发现甚至不太了解的是，它也拒绝在文章的 LIST 页面上使用这个简单的 btree 索引，它们只是按降序每页列出 x：

CREATE INDEX idx_btree_listing ON article USING btree(id DESC, magazine_id);

同样，它不使用多列索引：

Executed SQL
SELECT ••• FROM article WHERE article.magazine_id = 7
ORDER BY article.id DESC LIMIT 36
Time 1.4750957489 ms
QUERY PLAN
Limit  (cost=0.29..7.48 rows=36 width=384) (actual time=0.034..0.115 rows=36 loops=1)
->  Index Scan Backward using idx_magazine_id on article  (cost=0.29..2729.56 rows=13662 width=384) (actual time=0.031..0.107 rows=36 loops=1)
    Filter: (magazine_id = 7)
    Planning time: 1.354 ms
    Execution time: 0.207 ms

编辑：以上是记录较少且只有 1 个杂志的开发设置，因此速度很快。下面是auto_explain在生产服务器上生成的日志：

duration: 230.629 ms  plan:
SELECT article.id, article.title, article.date, article.content FROM article WHERE article.magazine_id = 7 ORDER BY article.id DESC LIMIT 36
Limit  (cost=0.42..43.67 rows=36 width=306) (actual time=229.876..229.995 rows=36 loops=1)
    ->  Index Scan Backward using idx_magazine_id on article (cost=0.42..239539.22 rows=199379 width=306) (actual time=229.866..229.968 rows=36 loops=1)
    Filter: (article.magazine_id = 7)
    Rows Removed by Filter: 116414

我将不胜感激，任何人都可以给我进一步调试的提示。

Answer 1

多列索引中的第一列是 id。您不会根据 id 进行过滤，因此 postgres 不会使用该索引。您不必筛选索引中的所有列，但要筛选的列必须是索引中的前 n 列。

尝试

尝试使用索引的变体，例如将 id 移到末尾或从索引中省略 id。