Postgresql:按分区键排序的查询

Question

我在PostgreSQL中创建了一个按received列分区的表（见这里）。让我们举一个玩具的例子：

CREATE TABLE measurement (
    received        timestamp without timezone PRIMARY KEY,
    city_id         int not null,
    peaktemp        int,
    unitsales       int
);

几年来，我每个月都创建了一个分区（measurement_y2012m01... measurement_y2016m03 ）。

我注意到postgresql不知道分区的顺序，因此对于如下所示的查询：

select * from measurement where ... order by received desc limit 1000;

PostgreSQL 对所有分区执行索引扫描，即使前 1000 个结果很可能位于最新的分区（或前两个或三个）中。

您知道如何利用分区进行此类查询吗？我想强调的是，where子句可能会有所不同，我不想对其进行硬编码。

第一个想法是以正确的顺序迭代分区，直到获取 1000 条记录或访问所有分区。但是如何以灵活的方式实现它呢？我想避免在应用程序中实现上述迭代，但我不介意应用程序是否需要调用存储过程。

提前感谢您的帮助！

格热戈兹

Answer 1

如果您真的不知道要扫描多少个分区才能在输出中获得所需的 1000 行，则可以在存储过程中构建结果集，并获取遍历分区的结果，直到满足限制条件。

从最新的分区开始将是明智之举。

select * from measurement_y2016m03 where ... order by received desc limit 1000;

您可以将即时结果集存储在记录中并对其发出计数，并动态更改下一个扫描分区的限制，这样，例如，如果您在第一个分区中获取870行，则可以使用 limit 130 构建第二个查询，然后再次执行计数，如果它仍然不满足1000行条件，则增加计数器。

为什么Postgres在计划期间不知道何时停止？

计划程序不知道需要多少个分区才能满足您的LIMIT子句。因此，它必须通过附加每个分区的结果来对整个集进行排序，然后执行限制（除非它在运行时已经满足此条件）。在 SQL 语句中执行此操作的唯一方法是将查找限制为仅几个分区 - 但您可能并非如此。此外，如果您在查找期间点击磁盘，增加work_mem设置可能会加快速度。

基调

另外，要记住的一件事是，当您设置分区时，您应该有一个最常访问的分区的降序。这将加快您的插入速度，因为 Postgres 会逐个检查条件，并在满足条件的第一个停止。

Answer 2

您可以猜测满足查询的received范围，而不是迭代分区并扩展它，直到获得所需的行数。将范围添加到WHERE将排除不必要的分区（假设设置了排除约束）。

编辑

正确，这就是我的意思（可以更好地表达）。

简单性似乎是一个非常合理的优势。无论哪种方式，我都不认为性能有什么不同。如果您大部分时间猜测合理地接近所需范围，这实际上可能会更有效，但可能不会产生显着差异。

它也更灵活一些，因为您不依赖于查询代码中的特定分区方案。