我有一张表,其中有大约 100,000,000 个条目,大部分按创建日期排序(没有时区,并且一些条目<0.01%,主键和正确顺序是相同的)。
一个常见的用例是通过一些任意的 where 语句来选择最新的 n 个条目,但是这很慢。
一个主要的瓶颈是维持顺序,按以下方式排序:
date=>x1, id => x10, not at all => x100
有没有办法可以利用我的表几乎已排序的事实?
我有一张表,其中有大约 100,000,000 个条目,大部分按创建日期排序(没有时区,并且一些条目<0.01%,主键和正确顺序是相同的)。
一个常见的用例是通过一些任意的 where 语句来选择最新的 n 个条目,但是这很慢。
一个主要的瓶颈是维持顺序,按以下方式排序:
date=>x1, id => x10, not at all => x100
有没有办法可以利用我的表几乎已排序的事实?
为了优化选择最后 n 个条目的查询,您可以考虑按日期范围对表进行分区。这允许 PostgreSQL 仅扫描相关分区,从而提高速度。
根据回复更新
这里有几点需要提及:
首先,重要的是根据具体情况定义“慢”。例如,您是否认为查询耗时超过 X 毫秒就属于慢?确定此阈值将有助于关注最关键的查询。
然后,您可以通过记录慢查询及其相应的 EXPLAIN 计划来获取慢查询。这将帮助您了解 PostgreSQL 所采用的执行路径以及瓶颈可能在哪里。
对于这些查询,请检查 WHERE 子句中最常用的条件。这将有助于定义有效的索引策略。
根据之前的分析,考虑创建符合查询模式的索引。带有 id、date 的索引可以在这里发挥作用。
分区可以通过创建较小的表和索引来提供帮助。这使 PostgreSQL 能够利用分区修剪来仅扫描相关分区,从而提高查询性能。