为什么Postgres不使用索引？

I have a table with an integer column called account_id. I have an index on that column.

我有一个名为account_id的整数列的表。我有一个关于该列的索引。

But seems Postgres doesn't want to use my index:

但似乎Postgres不想使用我的索引:

EXPLAIN ANALYZE SELECT "invoices".* FROM "invoices" WHERE "invoices"."account_id" = 1;

 Seq Scan on invoices  (cost=0.00..6504.61 rows=117654 width=186) (actual time=0.021..33.943 rows=118027 loops=1)
   Filter: (account_id = 1)
   Rows Removed by Filter: 51462
 Total runtime: 39.917 ms
(4 rows)

Any idea why that would be?

知道为什么会这样吗?

1 个解决方案

#1

Because of:

Seq Scan on invoices  (...) (actual ... rows=118027 <— this
   Filter: (account_id = 1)
   Rows Removed by Filter: 51462                    <— vs this
 Total runtime: 39.917 ms

You're selecting so many rows that it's cheaper to read the entire table.

你选择了这么多行,读取整个表会更便宜。

Related earlier questions and answers from today for further reading:

相关的早期问题和答案从今天开始进一步阅读:

Why doesn't Postgresql use index for IN query?

为什么Postgresql不使用索引进行IN查询?
Postgres using wrong index when querying a view of indexed expressions?

Postgres在查询索引表达式视图时使用了错误的索引?

(See also Craig's longer answer on the second one for additional notes on indexes subtleties.)

(另请参阅Craig关于第二个问题的更长答案,以获取有关索引细微差别的其他说明。)

#1