PostgreSQL单列索引与多列索引：选择最佳的SELECT性能

Question

我是新手PostgreSQL，特别是它的性能调整方面。基本上，我们有一些数据，通过查询3个整数值来访问：segmentSize（范围1...10），segmentX（范围+/- 100,000），segmentY（范围+/- 100,000）。

前瞻性考虑：当数据量增长时，可以将数据分成多个表，一个表对应一个单独的segmentSize，和/或segmentX和segmentY连续的范围。

目前的选择：我有一个架构选择，要么直接使用键（segmentSize，segmentX，segmentY），要么为了获得性能，在PostgreSQL之外创建一个合成键，将segmentX，segmentY组合成一个单一的整数值，成为键（或者更少可能，所有三个(segmentSize，segmentX，segmentY)）。

问题是：假设我们不太关心在Postgress之外从segmentX、segmentY派生这个“合并键”的成本，并且我们没有特别追求每行数据节省字节的空间（除非它会影响性能），那么查询范围segmentX * segmentY的单个int值，与查询segmentX和segmentY两个单独的int值相比，是否会有任何可衡量或有意义的性能提升？

非常感谢。请随意包含任何扩展适用数据和索引策略以最大化SELECT /读性能的链接。

- SashaK

---

使用EXPLAIN和EXPLAIN ANALYZE来查看和测量查询的执行情况和最佳性能。 - Frank Heikens

第一：什么是“自然”主键？第二：您的典型用法是在X或Y上进行范围查询，还是在{X，Y}或{Y，X}上进行范围查询？第三：查询中的关键字段集是否与“自然”PK的不同？它是否与插入操作中的关键字段集不同？第四：从三个关键字段集中，任何可能的一对都是候选键吗？第五：请添加关键字段含义的描述。“segment_id”对我们大多数人来说并不是很有信息量。 - wildplasser

@wildplasser 很有见地 - 谢谢。基本上，我们有一个类似于曼哈顿纽约市街区的网格，其中街道（从第1到第11）和街道（从第1到第160）都有编号。因此，您可以将某个餐厅称为“靠近第7大道和第34街的拐角处”，就像现实生活中的人们一样。或者您可以遵循东京方案，每个城市街区都被赋予一个数字，因此您可以将某个餐厅称为“926号街区”。在前一种情况下，我们将具有（7,34）的组合索引/键，而在后一种情况下，则是单个键926（因此来自更大的值集）。 - SashaK

Answer 1

将两个（或三个）列组合成单个键的性能优势可能非常小。对于某些用途，它实际上可能会损害性能；如果这些值在其他表中具有意义，则需要“导航”通过合成键防止计划被认为可能更快。当存在可用的自然键时使用合成键往往属于“过早优化”的范畴，并伴随着所有相关风险，包括高概率使事情变得更慢。

- kgrittn