关于 random_page_cost 的更多想法

几个月前，我发布了有关调整 random_page_cost 以更好地反映当前存储如何处理随机和顺序访问的文章。从那时起我就这个话题进行了很多精彩的讨论，但最终我被其他事情分散了注意力。 POSETTE 上周发生了，我预先录制了关于这个主题的演讲（顺便说一句，还有许多其他精彩的演讲）。这提醒我，我开始以不同的方式思考 random_page_cost 。所以这里有一些更多想法的更新。我确实已经在旧帖子中触及了其中一些内容，然后还在 POSETTE 演讲中进行了更详细的讨论。在此之前，让我分享一张包含旋转 SATA 驱动器的 random_page_cost 结果的图表。我完全忘记了我的机器里有这些磁盘，直到我打开机箱进行一些维护。这些可能更接近 2000 年左右原始实验所使用的存储。这些驱动器可能是 PATA 或 SCSI 驱动器，但仍然是旋转的。也许这会给我们更接近 4.0 默认值的值？显然不是。事实上，估计的 random_page_cost 约为 125，大约是 SSD 存储估计值的 2-4 倍。因此，对于 SSD，它越来越接近默认值，但这只是巧合。也许旧实验的一些基本部分我们没能回忆起来？或者也许“原始”结果以某种方式进行了调整。但 4.0 的默认值似乎从来都不是随机 I/O 的“原始”成本。我从过去尝试增加 random_page_cost 的人那里得到了很多反馈。根据他们的经验，这绝对不会提高性能，反而会损害性能。如果这会使成本计算不太准确，那怎么可能呢？我相信 random_page_cost 是为了“补偿”不完整的成本模型。它没有考虑与执行大量随机 I/O 的计划相关的各种缓存效果和资源。每个成本模型都是一个近似值，而且是一个相对粗糙的模型。不可能有一个快速/廉价的成本模型来准确地模仿每一个微小的细节。你可以让它变得越来越详细，但在某些时候它会变得和原始系统一样大。那为什么要有模型呢？这毫无用处。我们的成本模型有一些我认为很重要的差距。成本模型（大部分）忽略内存 操作的成本是根据执行该操作所使用的 CPU 和 I/O 量来计算的。这是两个关键资源，但它忽略了内存另一个重要资源。我们有 work_mem ，但这更多的是安全限制，因为它限制了工作缓冲区的大小（例如用于排序或散列）。它可能会影响操作需要执行的 I/O 量（例如，散列连接中较小的缓冲区意味着更多的溢出到磁盘）。但它不会跟踪计划“使用”的其他内存，它会忽略内存的其他使用。考虑一个 100GB 的表，其中包含 1GB 的“有趣”数据（与我们的查询谓词匹配）。我们可以按顺序或通过索引扫描表。如果表是“冷”的，顺序扫描可能会从内存（共享缓冲区或页缓存）中推送约 100GB 的其他数据。当 work_mem=4MB 时，查询可能运行得很好，但它可能会有效地“使用”100GB 内存。另一方面，索引可能只需要访问表的约 1%（1GB 的感兴趣数据）。随机 I/O 可能很容易比顺序扫描花费更多时间，但另一方面它只会“使用”1GB 内存。它可能会从缓存中清除更少的其他数据。然而成本模型完全没有注意到这一点。访问局部性另一个原因是活动集的概念。您可能有一个 1TB 的数据库，但在大多数实际系统中，仅访问数据的一小部分。用户只看最近的订单等。这就是我们所说的数据库的“活动集”。关键是将活动集保留在内存中。执行大量随机 I/O 的计划往往更加本地化，​​即仅访问“有趣”的数据。索引扫描通常会比顺序扫描访问更小的数据部分。 I/O 可能会更昂贵（随机、多次访问页面），但这只是“有趣”的数据。顺序扫描可能会大量扩展活动集 - 可能扩展到整个数据库。这不太好，除非您实际上有足够的 RAM 来容纳整个数据库。索引扫描允许更小的活动集。但规划者完全不知道活动集。它的计划就好像所有查询都从冷数据开始。它仅在非常有限的情况下考虑缓存效果 - 例如在同一查询中进行缓存。计划者忽视了其他各种事情。它单独规划查询，就好像没有其他查询竞争相同的资源并且带宽是无限的。顺序扫描对于单个后端可能非常有用，但是当有 100 个后端都在执行 seqscan 时，您将占用存储带宽。结论 我现在将 random_page_cost 视为所有这些影响的代理。规划器不会以与 CPU/磁盘相同的方式消耗内存，也不了解缓存效果。降低 random_page_cost 值 pus