postgre如何有效地对复杂 SQL 查询的结果进行分页?

Question

我有一个相当复杂的SQL查询，它首先将一些数据提取到CTE中，然后在CTE上执行几次自连接以计算值。下面是一个简化的示例，简化了我们应用程序的一些复杂性：

WITH subset AS (
SELECT time, value, device_id FROM raw_data
WHERE device_id IN (1, 2, 3)
AND time BETWEEN '2019-01-01 00:00:00'::timestamp AND '2019-01-15 00:00:00'::timestamp
)
SELECT
time,
(("device_1".value + "device_2".value) / "device_3".value) as value
FROM 
(
SELECT * FROM subset 
WHERE device_id = 1
) "device_1"
INNER JOIN
(
SELECT * FROM subset 
WHERE device_id = 2
) "device_2"
ON "device_1".time = "device_2".time
INNER JOIN
(
SELECT * FROM subset 
WHERE device_id = 3
) "device_3"
ON "device_3".time = "device_2".time

查询是自动生成的，可以扩展到针对可能数十个设备的值的复杂计算。出于性能原因，我们希望对此查询的结果进行分页，因为使用的时间范围可能很大。一个关键的约束是数据可能有时间上的间隙，但我们希望每页返回恒定的行数。

我们已经考虑在查询结束时使用LIMIT per_page OFFSET start，这将是标准方法，但这并没有为我们带来任何速度，并且查询执行相同。这是有道理的，因为在这种情况下，LIMIT/OFFSET 是在获取、连接和计算完所有数据之后执行的，它只返回已经计算的数据切片。这不会明显降低查询的运行速度。

我们考虑对提取到 CTE 中的数据进行分页，即计算与感兴趣的页面相对应的时间范围，然后在 CTE 的 BETWEEN 子句中使用该时间范围。这将起作用，但问题是我们无法可靠地计算这个时间范围，因为某些变量可能存在间隙。因此，如果我们将 100 行计算为 2 天的窗口，而我们获取 2 天，则如果device_2在该窗口的某个时间点没有记录数据，则可能会得到少于 100 行。对于计算，这些数据点将放在内部连接中。

问题是，鉴于这些约束，是否有一种有效的方法来分页此查询或重组它以实现快速分页？例如，是否有某种方法可以指示查询计划器"联接，直到匹配与联接条件匹配的 100 个结果，然后停止到此为止"。我们正在PostgreSQL上运行它，如果这有什么不同的话。

Answer 1

假设您的用例可以容忍没有始终拥有绝对的最新数据，您可以考虑创建一个物化视图：

WITH subset AS ( ... )
CREATE MATERIALIZED VIEW yourView AS SELECT ...

使用LIMIT和OFFSET对实例化视图进行分页应该比每次从头开始运行完整查询更快。 这里的缺点是您将从视图中返回数据，该视图必须以满足您要求的某种频率进行更新。

作为替代方法/除了实例化视图之外，您可以考虑使用索引来调整查询。 例如，可以加快 CTE 查询速度subset的索引可能是：

CREATE INDEX idx1 ON raw_data (time, device_id, value);

或者也许：

CREATE INDEX idx2 ON raw_data (device_id, time, value);

Answer 2

1( 使用以下顺序创建复合索引 device_id 和时间说明。

2(尝试以这种方式生成查询

select device_1.time,
(("device_1".value + "device_2".value) / "device_3".value) as value 
from raw_data as device_1 ,raw_data as device_2 ,raw_data as device_3 
where device_1.devise_id = 1 
and device_2.devise_id = 2 
and device_3.devise_id = 3 
and device_1.time BETWEEN '2019-01-01 00:00:00'::timestamp AND '2019-01-15 00:00:00'::timestamp 
and device_2.time BETWEEN '2019-01-01 00:00:00'::timestamp AND '2019-01-15 00:00:00'::timestamp 
and device_3.time BETWEEN '2019-01-01 00:00:00'::timestamp AND '2019-01-15 00:00:00'::timestamp 
and device_1.time = device_2.time 
and device_2.time = device_3.time