PostgreSQL:为什么这个查询不使用我的索引？

Question

我在数据库上执行这个查询(所有的数字和列名都是由)组成的：

select * from t where a=1 and b=11 and c!=5 and d<8

T有一个索引：

create index i on t (a,b,c,d)

当我运行“解释分析”时，查询会执行顺序扫描，大约需要55 do才能完成。如果我像这样修改查询：

select * from t where a=1 and b=11 and c=5 and d<8
                                       ^

它使用索引并在0.5ms内完成。所以肯定是不平等的，对吧？不是这样，因为如果我执行这个查询：

select * from t where a=1 and b=11 and c=5 and d!=8
                                               ^

查询仍然使用索引。但如果我试一试，就没有索引：

select * from t where a=1 and b=11 and c<5 and d<8
                                       ^

那么，为什么Postgres的行为是这样的呢？这对我来说很奇怪。

Answer 1

正如您已经认识到的，这个问题与使用非等号运算符有关。索引只能最有效地用于最左边的列，这些列是由等于(加上一个范围条件)进行比较的。

在你的例子中：

create index i on t (a,b,c,d);
where a=1 and b=11 and c!=5 and d<8;

它只对a和b有效地使用索引。这意味着DB获取与a和b条件匹配的所有行，然后根据其余条件检查每一行。

当您将c上的筛选器更改为等于时，它会获取(可能)较少的行(只有匹配a、b和c的行)，然后根据d筛选器检查那些(较少)行。在这种情况下，使用索引更有效。

通常，PostgreSQL查询计划器评估两个选项：(1)使用索引；(2)执行SeqScan。对于这两种情况，它都计算成本值--越高，预期性能就越差。因此，它的成本价值较小。这是它如何决定是否使用索引的，没有固定的阈值。

最后，是上面写的“加一个范围条件”。这意味着，如果使用等号，它不仅可以以最有效的方式使用索引，而且还可以使用一个单一范围的条件。

考虑到查询中只有一个范围条件，我建议将索引更改如下：

create index i on t (a,b,d,c);

现在，它可以与索引一起有效地使用a、b和d上的过滤器，只需要过滤c!=5所在的行。尽管该索引可以作为原始索引更有效地用于您的查询，但它并不意味着PG将自动使用它。这取决于费用估计。但试试看。

最后，如果这不是快速索引，并且您在表达式5中使用的值c!=5是常量，则可以考虑使用一个部分索引：

 create index i on t (a,b,d)
        where c!=5;

如果您将所有其他列与常量进行比较，也可以这样做。

参考文献：

• Indexing >, < and BETWEEN
• 索引多个独立范围条件(不！)

Answer 2

我想说的是，它没有为第一个查询使用索引，因为索引实际上没有帮助，因为几乎整个表都匹配。在这种情况下，扫描整个表要快一些。最后两个查询的不同之处在于，只有当预期结果大小低于某一阈值时，才可能使用索引。与之完全匹配的查询产生的结果很可能比使用较少的查询产生的结果要少，但仍然产生的结果小于等于选择。

尽管如此，查询优化器是一个非常复杂的软件，常常会产生令人惊讶的结果。