为什么Postgresql不对IN查询使用索引？

本文介绍了为什么Postgresql不对IN查询使用索引？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个表 social_accounts ，在列 facebook_id 上有部分索引，其中 user_id IS NULL 。

I have a table social_accounts with a partial index on column facebook_id where user_id IS NULL.

如果我做一个简单查询 WHERE facebook_id ='123'，

If I do a simple query WHERE facebook_id = '123', the index is used:

=> EXPLAIN for: SELECT "social_accounts".* FROM "social_accounts" WHERE (user_id IS NOT NULL) AND "social_accounts"."facebook_id" = '123' QUERY PLAN -------------------------------------------------------------------------------------------------------------- Index Scan using index_social_accounts_on_facebook_id on social_accounts (cost=0.00..8.28 rows=1 width=345) Index Cond: ((facebook_id)::text = '123'::text) Filter: (user_id IS NOT NULL)

但是如果我使用 IN 不使用索引：

but if I do a query using IN it does not use the index:

=> EXPLAIN for: SELECT "social_accounts".* FROM "social_accounts" WHERE (user_id IS NOT NULL) AND "social_accounts"."facebook_id" IN ('123', '456') QUERY PLAN --------------------------------------------------------------------------------------------------- Bitmap Heap Scan on social_accounts (cost=8.53..16.36 rows=2 width=345) Recheck Cond: ((facebook_id)::text = ANY ('{123,456}'::text[])) Filter: (user_id IS NOT NULL) -> Bitmap Index Scan on index_social_accounts_on_facebook_id (cost=0.00..8.52 rows=2 width=0) Index Cond: ((facebook_id)::text = ANY ('{123,456}'::text[])) (5 rows)

为什么在第二种情况下不使用索引？有什么方法可以加快查询速度？

why doesn't it use the index in the second case? any way to speed up this query?

（请注意，在此示例中，我已经截断了数组，并且测试了更多的元素，但，速度慢，结果）

推荐答案

实际上，它是使用索引。

Actually, it is using an index. Just doing so differently.

索引扫描逐行访问，以随机顺序从一个磁盘页面到下一个磁盘页面。

An index scan visit rows one by one, going back and forth from one disk page to the next in random order.

位图索引扫描首先过滤要访问的磁盘页面，然后依次访问后者。重新检查条件是因为在每个页面中都需要过滤掉无效的行。

A bitmap index scan starts by filtering disk pages to visit, and then visits the latter one by one sequentially. The recheck cond is because, in each page, you then need to filter out invalid rows.

对于少量行，索引扫描是最便宜的。对于更多行，位图索引扫描变得最便宜。对于更大的行数，seq扫描最终将变得最便宜。

For tiny numbers of rows, index scan is cheapest. For more rows, bitmap index scan becomes cheapest. For even larger numbers of rows, a seq scan eventually becomes cheapest.