PostgreSQL中多个索引与在多列上的单个索引的比较

Question

PostgreSQL中多个索引与在多列上的单个索引的比较

51

我阅读了一些现有帖子，没有得出任何确定的答案。

我有100个位置过去10年的某些数据。表格大约有8亿行。我需要主要为每个位置生成年度统计数据。有时我还需要生成月度变化统计和小时变化统计。我想知道是否应该生成两个索引——一个用于位置，另一个用于年份，或者在位置和年份上生成一个索引。我的主键当前是序列号（可能我可以使用位置和时间戳作为主键）。

谢谢。

- let_there_be_light

2

对比的解释与类比：https://dev59.com/uGAf5IYBdhLWcg3wwk0V#24315527 - Caffeine Coder

3个回答

11

对于这种情况，没有一定的规则。建议在生产数据库的副本中进行实验，看看哪种方法最适合您：单个多列索引还是两个单列索引。

Postgres的一个不错的功能是可以拥有多个索引，并在同一查询中使用它们。请查看文档的此章节：

… PostgreSQL可以组合多个索引…处理无法通过单个索引扫描实现的情况…

…有时候最好使用多列索引，但有时创建单独的索引并依赖于索引组合功能更好…

甚至可以尝试同时创建单独索引和组合索引，并检查每个索引的大小，以确定是否值得同时拥有它们。

您还可以尝试以下内容:

如果您的表太大，请考虑分区。似乎可以按位置或日期对其进行分区。分区将您的表数据分割成较小的表，减少查询需要查找的位置数量。

如果您的数据按照日期排列（例如交易日期），请检查BRIN索引。

如果多个查询以类似方式处理您的数据（例如聚合同一时期内的所有交易），请检查materialized views，这样您只需进行一次昂贵的聚合。

关于如何放置多列索引的顺序，请先放相等操作的列，然后再放范围操作的列，例如>=或<=操作的列。

- Daniel

2

在您的情况下，(位置, 时间戳) 索引比两个单独的索引更有效。请注意，列的顺序很重要。

- redneb

谢谢。列的顺序如何影响性能？ - let_there_be_light

1

如果您的搜索条件类似于 location="something" AND timestamp BETWEEN something AND something，那么 (location,timestamp) 索引可以用于此目的，而 (timestamp,location) 索引则不能。 - redneb

1

不是真的。Where条件的顺序一点也不重要。在这里看 https://dba.stackexchange.com/a/115759 - T3rm1

8

@T3rm1 我指的是多列索引定义中列的顺序。这与您提供的链接完全不同，该链接涉及的是WHERE子句中条件的顺序。在前一种情况下，顺序很重要，在后一种情况下则不重要。 - redneb

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Ildar Musin · Accepted Answer

无论你在关系上创建了多少个索引，只有其中一个会在某个查询中被使用（哪个取决于查询、统计等因素）。因此，在您的情况下，创建两个单列索引不会带来累积优势。为了从索引中获得最佳性能，我建议您在（位置、时间戳）上使用复合索引。

请注意，像 ... WHERE timestamp BETWEEN smth AND smth 这样的查询将不会使用上述索引，而像 ... WHERE location = 'smth' 或 ... WHERE location = 'smth' AND timestamp BETWEEN smth AND smth 这样的查询将会使用。这是因为索引中的第一个属性对于搜索和排序非常重要。

不要忘记执行

ANALYZE;

在创建索引后，以便收集统计信息。

更新：正如@MondKin在评论中提到的，某些查询实际上可以在同一关系上使用多个索引。例如，带有OR子句的查询，如a = 123 OR b = 456（假设两列都有索引）。在这种情况下，PostgreSQL将为两个索引执行位图索引扫描，构建结果位图的并集，并将其用于位图堆扫描。在某些条件下，相同的方案可以用于AND查询，但是与运算会取代并运算。