历史： PostgreSQL使用基于成本的优化器，为I/O操作分配抽象的货币单位。早期的数据库系统主要针对旋转磁盘，在这些系统中，寻道惩罚使得随机I/O的成本大约是顺序读取的40倍。为了缓解这种不对称，引入了位图索引扫描，通过构建匹配元组位置的内存位图来摊销随机页面提取，并以近似的物理顺序访问堆。
问题： 核心问题出现在过滤中等选择性谓词时，这些谓词匹配数千行分散在许多数据页面上。索引扫描对于每个匹配的元组指针执行一次随机I/O，这导致机械磁盘抖动或SSD上的过多I/O请求。相反，位图索引扫描构建位图结构会产生开销，并且如果因work_mem约束而导致位图变得有损，则可能处理不相关的行。
解决方案： 决策阈值位于cost_index()和cost_bitmap_heap_scan()函数内。规划者估算满足查询所需的不同堆页面数量（pages_fetched）。当pages_fetched超过比例random_page_cost / seq_page_cost时，优化器倾向于位图方法，因为排序页面检索的成本超过了随机访问的惩罚。减少random_page_cost（例如，将其从4.0降低到1.1用于SSD存储）降低了随机I/O的感知惩罚，从而推动规划者回到标准的索引扫描，适用于以前触发位图创建的选择性。
一个财务报表平台在当前财政季度按account_id聚合transactions的仪表板查询中遭受了严重的延迟。该表在带旋转磁盘的遗留SAN上包含了5亿行。谓词account_id = 12345大约匹配了分散在堆中的12%的行。执行计划显示，标准的索引扫描由于跨千个叶页面的随机I/O风暴消耗了14秒。
将 random_page_cost 从4.0 增加到8.0 明确向优化器传达了随机磁盘寻道的成本过高。这个立即的变化迫使规划者选择位图索引扫描，将执行时间减少到1.8秒，通过将页面请求批量处理成排序范围。然而，这个全局设置惩罚了应用中其他地方的OLTP点查找查询，导致它们转向效率较低的顺序扫描，增加了高峰交易时段的锁争用。
在 (account_id, transaction_date, amount) 上创建覆盖索引实现了索引仅扫描，完全绕过了堆，获得了80毫秒的响应时间。尽管对读取来说是最优的，但复合索引增加了表大小35%，并降低了22%的摄取吞吐量，因为每次插入现在需要维护两个大的B树结构，违反了实时交易记录的严格SLA。
我们选择通过范围来实现表分区 created_at 结合适中的 random_page_cost 为6.0。 这种混合方法将查询限制在当前季度的分区内，减少了绝对页面计数，低于位图阈值，同时提高的成本参数确保了跨分区的历史查询仍然使用位图，以防止随机I/O饱和。这个解决方案尊重了交易系统的写入性能约束，同时优化了读重的报告路径。
结果： 仪表板查询稳定在400毫秒内，没有降低OLTP插入性能，而且报告节点的磁盘I/O利用率在工作时间从95%降至30%。
effective_cache_size 如何与 random_page_cost 在规划者的成本模型中相互作用，且为何在具有大缓存的系统上降低 random_page_cost 可能降低某些连接类型的性能？
effective_cache_size量化了可用于磁盘缓存的内存。当设置为较高时，计划者假设许多页面存在于RAM中，有效地忽略了I/O成本，而不管random_page_cost的设置如何。如果您在保持较大的effective_cache_size的同时，激进地将random_page_cost降低到1.1（通常适用于NVMe SSD），优化器可能不理性地倾向于使用索引扫描的嵌套循环连接，而不是使用哈希连接。该模型假设内部关系的索引探测几乎是免费的，因为随机I/O便宜且被缓存，忽略了巨大的内部循环仍然占用CPU进行元组处理并触发缓存驱逐，导致墙钟时间比单个大批哈希操作差，后者仅需扫描一次内部表。
PostgreSQL的 位图索引扫描 与 位图堆扫描 有何不同，且为何规划者选择跨多个索引的 BitmapOr 操作，而不是使用单个复合索引？
位图索引扫描遍历索引结构以构建匹配元组指针（或页面范围，如果有损）。位图堆扫描随后使用该位图按顺序访问页面，从表中检索实际行数据。BitmapOr（或BitmapAnd）发生在查询像WHERE status = 'active' OR priority = 'high'这样的条件时，匹配不同的索引。由于PostgreSQL无法在单次通过中有效地同时遍历两个B树，因此它独立从每个索引生成位图，并通过按位操作组合它们。当查询仅在status上，priority上，或两者可变过滤时，这种技术比复合索引(status, priority)更受欢迎，因为维护两个独立索引的写放大低于多个覆盖复合变体。
当查询使用 LIMIT 子句时，为什么 PostgreSQL 仍可能选择 位图索引扫描 尽管早期终止更有利于标准 索引扫描，而滞后统计数据如何影响这种误判？
标准的索引扫描在获取LIMIT N行后可以立即终止，如果索引支持所需的排序，从而最小化I/O。然而，如果规划者低估了满足谓词的行数——由于陈旧的ANALYZE统计数据或相关列——它假设索引扫描会在找到匹配之前遍历过多的叶页面。因此，它选择位图索引扫描来摊销I/O成本。因为位图必须在访问堆之前完全物化，所以执行器无法提前停止；它构建包含数千行的位图，仅为丢弃前十行，导致与规划者乐观估计相比，延迟严重。