Postgres使用一个表的索引，而不是另一个表的索引

Question

我有三个表在我的应用程序，把它们称为tableA，tableB，和tableC。tableA有字段为tableB_id和tableC_id，两者都有索引。tableB有字段foo带索引，tableC有字段bar带索引。

当我执行以下查询时：

select * 
from tableA 
  left outer join tableB on tableB.id = tableA.tableB_id 
where lower(tableB.foo) = lower(my_input)

它真的很慢（约1秒）。

当我执行以下查询时：

select * 
from tableA 
   left outer join tableC on tableC.id = tabelA.tableC_id 
where lower(tableC.bar) = lower(my_input)

速度非常快（约20毫秒）。

据我所知，这些桌子大小差不多

关于这两个查询之间的巨大性能差异，有什么想法吗？

表大小：

>

tableA：2061392行

表B:175339行

TableC： 1888912行

postgresql-性能标签信息

博士后版本-9.3.5

以上是查询的全文。

解释计划-表B表C

表中的相关信息：

• 表

• “表id上的索引表A”B树（表id）

• id，整数，非空默认值nextval（'tableB_id_seq'：：regclass），存储平面
  • “tableB_pkey”主键，btree（id）
  • “index_tableB_on_lower_foo_tableD”唯一，btree（lower（foo:：text），tableD_id）
  • id，整数，非空默认值nextval（'tableC_id_seq'：：regclass），普通存储
  • “tableB_id_和_bar上的索引tableC_”唯一，b树（tableB_id，bar）
  • “索引表在下栏上”b树（下（栏：：文本））

  硬件：

  >

• OS X 10.10.2

  CPU：1.4 GHz英特尔酷睿i5

  内存：8 GB 1600 MHz DDR3

  图形：英特尔高清图形5000 1536 MB

  看起来运行真空，然后对所有三个表进行分析解决了这个问题。运行命令后，缓慢的查询开始使用“index_patients_on_foo_tableD”。

Answer 1

首先，您的左连接被左表上的谓词抵消，并被迫像[INNER]连接一样工作。替换为：

SELECT *
FROM   tableA a
JOIN   tableB b ON b.id = a.tableB_id
WHERE  lower(b.foo) = lower(my_input);

或者，如果您真的希望左连接包含表a中的所有行：

SELECT *
FROM   tableA a
LEFT   JOIN tableB b ON b.id = a.tableB_id
                    AND lower(b.foo) = lower(my_input);

我想你想要第一个。

关于的索引 <罢工> （低（foo::文本）） 您发布的在语法上是无效的。您最好在psql中发布\d tbl的逐字输出，就像我反复注释的那样。索引定义中的cast（foo::text）的简写语法需要更多的括号，或者使用标准语法：cast（foo AS text）：

• 在电话字段的前3个字符（区号）上创建索引

但这也是不必要的。您可以使用foo的数据类型（字符变化（255））。当然，数据类型字符变化（255）一开始在Postgres中几乎没有意义。255个字符的奇怪限制来自其他RDBMS中的限制，这些限制不适用于Postgres。详细信息：

• 重构字段的外键

尽管如此。这种查询的完美索引应该是B上的多列索引——如果（且仅当）您从中获得仅索引扫描：

CREATE INDEX "tableB_lower_foo_id" ON tableB (lower(foo), id);

然后，您可以删除大部分被取代的索引"index_tableB_on_lower_foo"。tableC也是如此。
其余部分由表A中的（更重要的！）索引覆盖，位于tableB_id和tableC_id上。

如果每个tableB_id/tableC_id在tableA中有多行，那么这些相互竞争的命令中的任何一个都可以通过物理地将相关行聚集在一起来改变性能，以支持相应的查询：

CLUSTER tableA USING "index_tableA_on_tableB_id";
CLUSTER tableA USING "index_tableA_on_tableC_id";

你不能两者兼得。要么是B要么是CCLUSTER还完成了真空吸尘器充满时所能做的一切。但一定要先阅读细节：

• 优化Postgres时间戳查询范围

不要使用混合大小写标识符，有时引用，有时不引用。这是非常令人困惑的，必然会导致错误。只使用合法的小写标识符——那么你是否重复引用它们就无关紧要了。

Answer 2

另一件事是将索引列作为lower（）进行查询，这也可以在查询运行时创建部分索引。

如果您总是以lower（）的形式查询该列，那么您的列应该以lower（column_name）的形式进行索引，如下所示：

create index idx_1 on tableb(lower(foo));

还有，你看过执行计划了吗？如果你能看到它是如何查询表格的，这将回答你所有的问题。

老实说，这有很多因素。最好的解决方案是研究索引，特别是在博士后，这样你就可以看到它们是如何工作的。这是一个有点整体性的主题，你不可能只对问题的工作原理有一点了解就回答所有的问题。

例如，Postgres在查询运行之前有一个首字母“让我们看看这些表，看看我们应该如何查询它们”。它查看所有表，每个表有多大，存在哪些索引，等等，然后找出查询应该如何运行。然后它执行它。通常情况下，这就是问题所在。引擎错误地决定了如何执行它。

这方面的很多计算都是在汇总统计表之外完成的。您可以通过以下操作重置任何表的汇总表统计信息：

vacuum [table_name];

（这有助于防止死排导致的腹胀）

然后：

analyze [table_name];

我并不总是看到这项工作，但经常会有所帮助。

不管怎样，最好的选择是：

a）研究Postgres索引（a简单的编写，而不是极其复杂的东西）b）研究查询的执行计划c）利用你对Postgres索引的理解以及查询计划是如何执行的，你不得不解决确切的问题。