postgresql - PostgREST 在子查询或 CTE 中使用限制和偏移量

Question

我们在我们的项目中使用 PostgREST 来处理一些非常复杂的数据库 View 。

从某个时候开始，当我们将限制和偏移量(x 范围 header 或查询参数)与子选择一起使用时，我们会获得非常长的响应时间。

从我们读到的内容来看，这似乎是一个已知问题，即 postgresql 甚至对未请求的记录执行子选择。解决方案是稍微调整偏移量和限制，将其放入子选择或 CTE 表中。

我们可以在数据库 View 中使用内部 GUC 值或类似的值来优化响应时间吗？有人对如何实现这一目标有任何提示吗？

编辑: 这里建议的是一些更多的细节。假设我们在产品和零件之间存在关系。我想知道每个产品的零件数(这是我们公开的数据库 View 的简化版本)。

有两种方法可以做到这一点

A.子选择:

    SELECT products.id
            ,(
                    SELECT count(part_id) AS total
                    FROM parts
                    WHERE product_id = products.id
                    )
    FROM products limit 1000 OFFSET 99000

B. CTE:

    WITH parts_count
    AS (
            SELECT product_id
                    ,count(part_id) AS total
            FROM parts
            GROUP BY product_id
            ORDER BY product_id
            )
    SELECT products.id
            ,parts_count.total
    FROM products
    LEFT JOIN parts_count ON parts_count.product_id = product.id 
    LIMIT 1000     
    OFFSET 99000

A 的问题是对每一行执行子选择，所以即使我只读取 1000 条记录，也有 100 000 个子选择。

B 的问题是与 parts_count 表的连接需要很长时间，因为那里有 100 0000 条记录(尽管 with 查询只需要 200 毫秒!对于 2000 条记录)。理想情况下，我想使用与主查询相同的限制和偏移量来限制 parts_count 表，但我不能在 PostgREST 中执行此操作，因为它只是在末尾附加限制和偏移量，我无法访问其中的那些参数WITH 查询

Answer 1

高OFFSET导致性能不佳是不可避免的。

没有其他方法可以计算 OFFSET，只能扫描并丢弃所有行，直到达到偏移量，如果 OFFSET，世界上没有任何数据库会很快高。

这是一个概念性问题，避免它的唯一方法是避免 OFFSET。

如果您的目标是分页，那么通常 keyset pagination是更好的解决方案:

您添加一个符合您要求的 ORDER BY 子句，确保 ORDER BY 子句中有一个唯一键并记住您找到的最后一个值。要获取下一页，请添加具有该值的 WHERE 条件。有了适当的索引支持，这会非常快。

对于您的查询，更有效的版本可能是:

SELECT p.id
       count(parts.part_id) AS total
FROM (SELECT id FROM products
      LIMIT 1000 OFFSET 99000) p
   LEFT JOIN parts ON parts.product_id = p.id
GROUP BY p.id;

很奇怪，您没有ORDER BY，而是LIMIT 和OFFSET。这没有多大意义。