优化查询速度：在大型PostgreSQL表中进行简单SELECT操作

Question

我在Postgres数据库的SELECT查询速度方面遇到了一些问题。

我的表格有两个整数列作为关键字：(int1,int2) 这个表格大约有7000万行数据。

我需要在这个环境中进行两种简单的SELECT查询:

SELECT * FROM table WHERE int1=X;
SELECT * FROM table WHERE int2=X;

这两个选择器从 7 千万行中各返回约 1 万行。为了让它尽可能快地工作，我考虑使用两个哈希索引，一个针对每列。不幸的是，结果并不那么好：

                                                               QUERY PLAN                                                               
----------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on lec_sim  (cost=232.21..25054.38 rows=6565 width=36) (actual time=14.759..23339.545 rows=7871 loops=1)
   Recheck Cond: (lec2_id = 11782)
   ->  Bitmap Index Scan on lec_sim_lec2_hash_ind  (cost=0.00..230.56 rows=6565 width=0) (actual time=13.495..13.495 rows=7871 loops=1)
         Index Cond: (lec2_id = 11782)
 Total runtime: 23342.534 ms
(5 rows)

这是一个 EXPLAIN ANALYZE 示例查询。它需要大约 23 秒。我的期望是在不到一秒的时间内获取此信息。

以下是 postgres 数据库配置的一些参数：

work_mem = 128MB
shared_buffers = 2GB
maintenance_work_mem = 512MB
fsync = off
synchronous_commit = off
effective_cache_size = 4GB

任何帮助、评论或想法都将不胜感激。

提前感谢您。

- alexdemartos

---

@JustBob：在解释输出中报告的时间是准备查询在服务器上所需的时间（不包括客户端往返）。 - user330315

哈希索引在PostgreSQL中效率不高。您尝试过常规的B-Tree索引吗？每个列都有一个索引，还是两者都有一个组合索引？这两个语句中哪一个是发布的执行计划？ - user330315

这里的索引查找非常快 - 所有时间都花在检索实际行上。23秒/ 7871行=每行2.9毫秒，这对于检索分散在磁盘子系统中的数据是合理的。搜索很慢；您可以a）将数据集放入RAM中，b）购买SSD，或c）提前组织数据以最小化搜索。 - willglynn

@willglynn：非常感谢，我没有意识到时间被花费在寻找数据而不是寻找索引上。因此，忘记关于索引的疑问，我想看看如何组织这些数据以便从硬盘中更快地获取它们。也许我可以在一个表中按(int1)排序行，并且有另一个按(int2)排序的表的副本，并根据所寻找的键索引在这两个表上执行SELECT查询。这样会更好吗？速度会更快吗？非常感谢。 - alexdemartos

两个表是一个选项，特别是如果您将它们都进行了CLUSTER。然而，PostgreSQL 9.2增加了一项称为仅索引扫描的功能，对于这里特别有帮助 - 对感兴趣的所有列创建一个btree索引（PostgreSQL自动保持有序），查询可以（可能）仅使用索引回答，不需要额外的搜索。 - willglynn

Answer 1

将我的评论提取为答案：这里的索引查找非常快 - 所有时间都花在检索实际行上。 23秒/7871行 = 每行2.9毫秒，对于检索分散在磁盘子系统中的数据而言是合理的。查找很慢；您可以a）将数据集放入RAM中，b）购买SSD或c）提前组织数据以最小化查找。

PostgreSQL 9.2具有称为index-only scans的功能，允许它（通常）回答查询而无需访问表格。 您可以将此与自动维护顺序的btree索引属性结合使用，使此查询快速。 您提到了int1，int2和两个浮点数：

CREATE INDEX sometable_int1_floats_key ON sometable (int1, float1, float2);
CREATE INDEX sometable_int2_floats_key ON sometable (int2, float1, float2);

SELECT float1,float2 FROM sometable WHERE int1=<value>; -- uses int1 index
SELECT float1,float2 FROM sometable WHERE int2=<value>; -- uses int2 index

请注意，这并不会神奇地消除磁盘寻道，它只是将它们从查询时间移动到插入时间。此外，这也会耗费存储空间，因为您需要复制数据。然而，这很可能是您想要的权衡。

- willglynn

---

非常感谢@willglynn。我觉得那正是我在寻找的东西。明天我会试一试并发布它的表现。 - alexdemartos

我完全被这个惊人的成果所震撼，以及它对性能产生的巨大影响。在一个包含约2000万行数据的生产数据库中，创建一个简单的索引将查询时间从大约8秒降至约20毫秒。 - James Taylor

Answer 2

感谢您的建议，willglyn。正如您所注意到的一样，问题在于通过硬盘查找数据而不是查找索引。您提出了许多解决方案，例如将数据集加载到RAM中或购买SSD硬盘。但是，忘记这两个需要在数据库之外管理的解决方案后，您提出了两个想法：

1.重新组织数据以减少数据查找。

2.使用PostgreSQL 9.2功能“仅索引扫描”。

由于我正在使用PostgreSQL 9.1服务器，所以我选择了选项“1”。

我复制了表格。现在我有相同的表格和相同的数据两次。我为每个表格创建了一个索引，第一个被(int1)索引，第二个被(int2)索引。然后我对它们进行了聚簇处理 (CLUSTER table USING ind_intX)，分别使用它们各自的索引进行聚簇处理。

现在查找速度非常快。我从23秒降低到了约2毫秒，这是一个令人印象深刻的改进。我认为我的问题已经得到解决，希望这也对其他遇到同样问题的人有用。

非常感谢您，willglynn。

- alexdemartos

---

如果你有一个静态的数据集，那么就没问题了。如果没有，你需要使用触发器来维护已排序的表（以确保有一个真实的数据源），并且需要定期重新使用CLUSTER来维护磁盘上的顺序，以适应数据变化。 - willglynn

Answer 3

我曾经遇到了一个超级慢的查询案例，其中在一个拥有3300万行的表和一个尺寸为24亿行的子表之间进行了简单的一对多联接。我对子表的外键索引执行了CLUSTER，但发现这并没有解决我的查询超时问题，即使最简单的查询也是如此。运行ANALYZE也不能解决这个问题。

真正起到巨大作用的是手动清理父表和子表的VACUUM操作。即使在父表完成VACUUM过程时，我从10分钟的超时变成了1秒钟返回结果。

我从中得出的结论是，即使是v9.1，定期的VACUUM操作仍然至关重要。我这么做的原因是，我注意到自动VACUUM在两个表中至少两周没有运行过，并且自那以后发生了许多upserts和inserts。也许我需要改进自动VACUUM触发器来解决这个问题，但我可以说的是，如果清理干净所有东西，具有数十亿行的640GB表现得非常良好。我还没有必须将表分区以获得良好性能。

- Robert Casey

---

你应该调查一下为什么自动清理（autovacuum）没有运行。你可能有一些处于“事务空闲”模式下的会话。确保在你的代码中正确终止事务。你可能还应该将自动清理（autovacuum）设置得更为积极一些。 - user330315

Answer 4

如果您的Postgres机器上有快速的固态存储，可以尝试设置以下简单而有效的一行代码：

random_page_cost=1.0

在您的postgresql.conf文件中，默认设置为random_page_cost=4.0，这是针对旧式机械硬盘等具有高搜索时间的存储进行优化的。此更改会改变寻找的成本计算方式，并且不太依赖于内存（最终可能会进入交换分区）。仅通过此设置，我就将一个包含数百万条记录的长表格上的筛选查询时间从8秒减少到2秒。另一个主要的改进来自于在我的表格上使用所有布尔列创建索引。这将2秒的查询时间缩短到约1秒。希望这可以帮到你！

- Nick Woodhams