sql - Postgresql 规划器使用了错误的索引

Question

最近我将 Postgresql 从 9.1 升级到 9.2 版本。新规划器使用了错误的索引并且查询执行时间过长。

查询:

explain SELECT mentions.* FROM mentions WHERE (searches_id = 7646553) ORDER BY id ASC LIMIT 1000

9.1版本说明:

Limit  (cost=5762.99..5765.49 rows=1000 width=184)
->  Sort  (cost=5762.99..5842.38 rows=31755 width=184)
    Sort Key: id
    ->  Index Scan using mentions_searches_id_idx on mentions  (cost=0.00..4021.90 rows=31755 width=184)
          Index Cond: (searches_id = 7646553)

9.2版本的说明:

Limit  (cost=0.00..450245.54 rows=1000 width=244)
->  Index Scan using mentions_pk on mentions  (cost=0.00..110469543.02 rows=245354 width=244
    Index Cond: (id > 0)"
    Filter: (searches_id = 7646553)

正确的做法是在 9.1 版本中，planner 在 searches_id 上使用索引。在 9.2 版本中，规划器不使用该索引并按 searches_id 过滤行。

当我在没有 ORDER BY id 的情况下执行 9.2 版本查询时，planner 在 searches_id 上使用索引，但我需要按 id 排序。

我还尝试在子查询中选择行并在第二个查询中对其进行排序，但解释表明，规划器与普通查询中的操作相同。

select * from (
SELECT mentions.* FROM mentions WHERE (searches_id = 7646553))
AS q1
order by id asc

你会推荐什么？

Answer 1

如果 searches_id #7646553 行超过表的百分之几，那么该列上的索引将不会被使用，因为表扫描会更快。做一个

select count(*) from mentions where searches_id = 7646553 

并与总行数进行比较。

如果他们少于表格的百分之几，那么尝试

with m as (
    SELECT *
    FROM mentions
    WHERE searches_id = 7646553
)
select *
from m
order by id asc

(从 PostgreSQL v12 开始，您必须使用 with ... as materialized。)

或者创建复合索引:

create index index_name on mentions (searches_id, id)

如果 searches_id 低 cardinality然后以相反的顺序创建相同的索引

create index index_name on mentions (id, searches_id)

做

analyze mentions

创建索引后。