首页 文章

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

提问于
浏览 次
0

我的应用程序中有三个表，称为 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行

  • tableB：175339行

  • tableC：1888912行

postgresql-performance标签信息

Postgres版本 - 9.3.5

上面是查询的全文 .

解释计划 - tableB tableC

表中的相关信息：

  • tableA

  • tableB_id，整数，无修饰符，存储平原

  • "index_tableA_on_tableB_id" btree（tableB_id）

  • tableC_id，整数，无修饰符，存储平原，

  • "index_tableA_on_tableB_id" btree（tableC_id）

  • tableB

  • id，integer，not null default nextval（'tableB_id_seq' :: regclass），storage plain

  • "tableB_pkey" PRIMARY_KEY，btree（id）

  • foo，字符变化（255），无修饰符，存储扩展

  • "index_tableB_on_lower_foo_tableD" UNIQUE，btree（lower（foo :: text），tableD_id）

  • tableD是一个单独的表，否则无关紧要

  • tableC

  • id，integer，not null default nextval（'tableC_id_seq' :: regclass），storage plain

  • "tableC_pkey" PRIMARY_KEY，btree（id）

  • bar，字符变化（255），无修饰符，存储扩展

  • "index_tableC_on_tableB_id_and_bar" UNIQUE，btree（tableB_id，bar）

  • "index_tableC_on_lower_bar" btree（lower（bar :: text））

硬件：

  • OS X 10.10.2

  • CPU：1.4 GHz Intel Core i5

  • 内存：8 GB 1600 MHz DDR3

  • 图形：Intel HD Graphics 5000 1536 MB

解决方案

看起来像运行真空，然后分析所有三个表修复了问题 . 运行命令后，慢速查询使用“index_patients_on_foo_tableD”开始 .

postgresql indexing postgresql-performance

2 回答

  • 1

    另一件事是您将索引列查询为 lower() ，这也可以在查询运行时创建部分索引 .

    如果您始终将列查询为 lower() ，则应将您的列编入索引为 lower(column_name) ，如下所示：

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

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

    老实说，这有很多因素 . 最好的解决方案是研究INDEXES，特别是在Postgres中，这样你就可以看到它们是如何工作的 . 这是一个整体主题，你无法真正回答所有问题，只需要对它们的工作方式有一点了解 .

    例如，Postgres在查询运行之前有一个初始的“让我们查看这些表并查看我们应该如何查询它们” . 它查看所有表，每个表有多大，存在哪些索引等，然后确定查询应该如何运行 . 然后它执行它 . 通常情况下，这是错误的 . 引擎错误地确定了如何执行它 .

    很多计算是通过汇总表统计完成的 . 您可以通过执行以下操作重置任何表的汇总表统计信息：

    vacuum [table_name];

    （这有助于防止死行膨胀）

    然后：

    analyze [table_name];

    我并不总是看到这项工作，但往往有帮助 .

    ANYway，最好的选择是：

    a）研究Postgres索引（SIMPLE编写，而不是非常复杂的事情）b）研究查询的执行计划c）使用你对Postgres索引的理解以及查询计划的执行方式，你不得不解决确切的问题 .

    回复于
  • 1

    对于初学者来说，你的 LEFT JOIN 被左表中的谓词抵消，并被迫像 [INNER] JOIN 那样行事 . 用 . . . 来代替：

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

    或者，如果您确实希望 LEFT JOIN 包含 tableA 中的所有行：

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

    我想你想要第一个 .

    您发布的（lower（foo :: text））索引是 syntactically invalid . 你最好在psql中发布 \d tbl 的逐字输出，就像我反复评论一样 . 索引定义中强制转换（ foo::text ）的简写语法需要更多括号，或使用标准语法： cast(foo AS text) ：

    但那是 also unnecessary . 您只需使用 foo 的数据类型（ character varying(255) ）即可 . 当然，Postgres中的数据类型 character varying(255) rarely makes sense 一开始 . 对255个字符的奇怪限制来自其他RDBMS中的限制，这些限制不适用于Postgres . 细节：

    是因为它可能 . 这种查询的完美索引是 B 上的多列索引 - 如果（并且仅当）你得到index-only scans：

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

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

    如果 tableA / tableB_id / tableC_id 中有多行，则其中任何一行竞争命令可以通过将相关行物理聚类在一起来摆动性能以支持相应的查询：

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

    您可以't have both. It' s B 或 C . CLUSTER 也做 VACUUM FULL 会做的一切 . 但请务必先阅读详细信息：

    并且不要使用 mixed case identifiers ，有时引用，有时不引用 . 这非常令人困惑，必然会导致错误 . 仅使用合法的小写标识符 - 然后，如果您双重引用它们并不重要 .

    回复于

相关问题