PostgreSQL查询未使用索引

Question

我有一个非常简单的数据库模式，其中以下列上具有多列b-tree索引：

PersonId, Amount, Commission

现在，如果我尝试使用以下查询语句选择表：

explain select * from "Order" where "PersonId" = 2 AND "Commission" > 3

Pg正在扫描索引，查询速度非常快，但如果我尝试以下查询：

explain select * from "Order" where "PersonId" > 2 AND "Commission" > 3

即使索引存在，它也会执行顺序扫描。甚至这个查询也是如此

explain select * from "Order" where "Commission" > 3 

进行顺序扫描。

有没有人能解释一下为什么？:-)

非常感谢。

更新

表格包含1亿行。我创建它只是为了测试PostgreSQL在性能方面与MS SQL的比较。表已经被VACUUMED。我的CPU是Core I5 2500k四核，内存为8 GB。

这是该查询的explain analyze结果：

explain ANALYZE select * from "Order" where "Commission" BETWEEN 3000000 AND 3000010  LIMIT 20 


Limit  (cost=0.00..2218328.00 rows=1 width=24) (actual time=28043.249..28043.249 rows=0 loops=1)
  ->  Seq Scan on "Order"  (cost=0.00..2218328.00 rows=1 width=24) (actual time=28043.247..28043.247 rows=0 loops=1)
        Filter: (("Commission" >= 3000000::numeric) AND ("Commission" <= 3000010::numeric))
Total runtime: 28043.278 ms

- Davita

---

请看这里：https://dev59.com/2G435IYBdhLWcg3wyzaa，特别是 @Frank Heikens 的回答。 - Raphaël Althaus

因为索引列的顺序很重要。阅读此内容：http://use-the-index-luke.com/sql/where-clause/searching-for-ranges/greater-less-between-tuning-sql-access-filter-predicates - Markus Winand

你能展示一下表的 CREATE TABLE 和 CREATE INDEX 语句吗？如果可行的话，您使用的填充表的语句也是非常好的参考。（从 psql 输出的 \d "Order" 可以，但这会让人们更难复制您的结果并测试建议的更改。）我假设数据已经完全缓存，否则在28秒内通过1亿行将会非常困难。默认配置假定最小化缓存，因此将选择基于磁盘访问假设的计划，这会对随机索引访问产生影响。 - kgrittn

另外，如果您想要更好的性能，可以使用 int 或 bigint 代替 numeric。我曾经看到这样做可以提高高达5%的性能。http://www.postgresql.org/docs/current/interactive/datatype-numeric.html 如果您不需要 numeric 提供的小数位精度，那么基于整数的类型（9223372036854775807）的最大值可能对大多数情况都足够了。 - kgrittn

在您的更新中，查询谓词是佣金范围，但佣金是多列索引的最低顺序元素，因此索引将对选择所请求的行帮助不大。您需要单独在佣金上建立索引，或者建立一个多列索引，其中佣金作为关键字的第一列，以便使用索引扫描有用地辅助此查询。 - dbenhur

Answer 1

简单来说，在比较各种可用计划时，基于您配置的成本因素和最新的统计数据，顺序扫描预计是最快的。从您提供的少量信息来看，规划器很可能做出了正确的选择。如果您有三个单列索引，则可能能够使用位图索引扫描，特别是如果要选择的行少于表中行数的约10％。

请注意，对于您描述的索引，所有行都需要从中进行扫描，其中“PersonId”> 2;除非您有许多“PersonId”的负值，否则这很可能是表中的大部分。

还要注意，如果您有一个很小的表-例如仅有几千行或更少，通过索引访问行很少会比仅扫描这些少数行更快。计划对数据量敏感，具有少量行的计划很可能与具有大量行的计划不同。

如果实际上没有选择最快的计划，则很有可能需要调整成本因素以更好地模拟机器上的成本。另一个可能性是您需要更积极地设置自动真空设置，以确保可用最新的统计数据，或者您可能需要配置收集更细粒度的统计数据。

如果您显示表描述（包括索引），查询的解释分析输出以及硬件描述，则人们将能够提供更具体的建议。

- kgrittn

---

+1。感谢您的回答。我根据您的建议更新了我的帖子 :) - Davita