为什么Postgres不使用索引？ [英] Why isn't Postgres using the index?

问题描述

我有一个表，其中有一个整数列，名为 account_id 。我在该列上有一个索引。

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

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

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

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)

任何想法为什么会是？

推荐答案

因为：

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:

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

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

Postgres using wrong index when querying a view of indexed expressions?

（另请参阅Craig更长的答案，关于索引细节的附加注释。）

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

这篇关于为什么Postgres不使用索引？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！