sql - 为什么 PostgreSQL 不*仅*根据其 IN() 子句的内容在此查询中使用覆盖索引？

Question

我有一个包含覆盖索引的表，它应该只使用索引来响应查询，而根本不检查表。事实上，如果 IN() 子句中有 1 个或 几个 元素，Postgres 确实会这样做。但是，如果 IN 子句有很多元素，看起来就像是在索引上进行搜索，然后去表中重新检查条件...

我不明白为什么 Postgres 会那样做。它可以直接从索引提供查询服务，也可以不提供服务，如果它(理论上)没有其他要添加的内容，为什么还要转到表？

表格:

CREATE TABLE phone_numbers
(
  id serial NOT NULL,
  phone_number character varying,
  hashed_phone_number character varying,
  user_id integer,
  created_at timestamp without time zone,
  updated_at timestamp without time zone,
  ghost boolean DEFAULT false,
  CONSTRAINT phone_numbers_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);

CREATE INDEX index_phone_numbers_covering_hashed_ghost_and_user
  ON phone_numbers
  USING btree
  (hashed_phone_number COLLATE pg_catalog."default", ghost, user_id);

我正在运行的查询是:

SELECT "phone_numbers"."user_id" 
FROM "phone_numbers" 
WHERE "phone_numbers"."hashed_phone_number" IN (*several numbers*) 
  AND "phone_numbers"."ghost" = 'f'

如您所见，索引具有回复该查询所需的所有字段。

如果我在 IN 子句中只有一个或几个数字，它会:

1 个号码:

Index Scan using index_phone_numbers_on_hashed_phone_number on phone_numbers (cost=0.41..8.43 rows=1 width=4)
  Index Cond: ((hashed_phone_number)::text = 'bebd43a6eb29b2fda3bcb63dcc7ffaf5433e78660ccd1a495c1180a3eaaf6b6a'::text)
  Filter: (NOT ghost)"

3 个数字:

Index Only Scan using index_phone_numbers_covering_hashed_ghost_and_user on phone_numbers (cost=0.42..17.29 rows=1 width=4)
  Index Cond: ((hashed_phone_number = ANY ('{8228a8116f1fdb12e243102cb85ecd859ebf7873d9332dce5f1343a481ec72e8,43ddeebdca2ea829d468d5debc84d475c8322cf4bf6edca286c918b04216387e,1578bf773eb6eb8a9b57a130922a28c9c91f1bda67202ef5936b39630ca4cfe4}'::text[])) AND (...)
  Filter: (NOT ghost)"

但是，当我在IN子句中有很多数字时，Postgres正在使用Index，但随后又打表了，我不知道为什么:

Bitmap Heap Scan on phone_numbers (cost=926.59..1255.81 rows=106 width=4)
  Recheck Cond: ((hashed_phone_number)::text = ANY ('{b6459ce58f21d99c462b132cce7adc9ea947fa522a3849321e9fb65893006a5e,8228a8116f1fdb12e243102cb85ecd859ebf7873d9332dce5f1343a481ec72e8,ab3554acc1f287bb2e22ff20bb855e19a4177ef552676689d217dbb2a1a6177b,7ec9f58 (...)
  Filter: (NOT ghost)
  -> Bitmap Index Scan on index_phone_numbers_covering_hashed_ghost_and_user (cost=0.00..926.56 rows=106 width=0)
        Index Cond: (((hashed_phone_number)::text = ANY ('{b6459ce58f21d99c462b132cce7adc9ea947fa522a3849321e9fb65893006a5e,8228a8116f1fdb12e243102cb85ecd859ebf7873d9332dce5f1343a481ec72e8,ab3554acc1f287bb2e22ff20bb855e19a4177ef552676689d217dbb2a1a6177b,7e (...)

当前正在执行此查询，它在一个总行数为 50k 的表中查找 250 条记录，大约是另一个表上类似查询的两倍，该查询在一个具有 500 万行的表中查找 250 条记录，没有多大意义。

任何想法可能会发生什么，我是否可以做任何事情来改进它？

---

更新:将覆盖索引中列的顺序更改为先是 ghost，然后是 hashed_phone_number 也没有解决问题:

Bitmap Heap Scan on phone_numbers (cost=926.59..1255.81 rows=106 width=4)
  Recheck Cond: ((hashed_phone_number)::text = ANY ('{b6459ce58f21d99c462b132cce7adc9ea947fa522a3849321e9fb65893006a5e,8228a8116f1fdb12e243102cb85ecd859ebf7873d9332dce5f1343a481ec72e8,ab3554acc1f287bb2e22ff20bb855e19a4177ef552676689d217dbb2a1a6177b,7ec9f58 (...)
  Filter: (NOT ghost)
  -> Bitmap Index Scan on index_phone_numbers_covering_ghost_hashed_and_user (cost=0.00..926.56 rows=106 width=0)
        Index Cond: ((ghost = false) AND ((hashed_phone_number)::text = ANY ('{b6459ce58f21d99c462b132cce7adc9ea947fa522a3849321e9fb65893006a5e,8228a8116f1fdb12e243102cb85ecd859ebf7873d9332dce5f1343a481ec72e8,ab3554acc1f287bb2e22ff20bb855e19a4177ef55267668 (...)

Answer 1

索引的选择基于优化器所说的查询的最佳解决方案。 Postgres 正在非常努力地使用您的索引，但它不是查询的最佳索引。

最好的索引有ghost在前:

CREATE INDEX index_phone_numbers_covering_hashed_ghost_and_user
  ON phone_numbers
  USING btree
  (ghost, hashed_phone_number COLLATE pg_catalog."default", user_id);

我碰巧认为 MySQL documentation很好地解释了如何使用复合索引。

本质上，发生的事情是 Postgres 需要对 in 列表中的每个元素进行索引查找。这可能会因使用字符串而变得复杂——因为归类/编码会影响比较。最终，Postgres 决定其他方法更有效。如果您将 ghost 放在首位，那么它只会跳转到索引的正确部分并在那里找到它需要的行。