如何在大表上的Postgres中加快SUM查询

2024-04-23 • 问答

问题

我正在尝试在postgres数据库中的SQL视图上运行以下查询：

SELECT sum(value) FROM invoices_view;

invoices_view大约有4500万行，整个数据库的数据大小为40.5 GB，数据库具有61 GB的RAM。

此查询目前需要4.5秒，我希望它在1秒以内。

我尝试过的事情

我当然不能直接向SQL视图添加索引，但是在基础表上有索引：

CREATE INDEX invoices_on_value_idx ON invoices (value);

我还在发票表上运行了VACUUM ANALYZE。

EXPLAIN ANALYZE

EXPLAIN ANALYZE的输出如下：

EXPLAIN (ANALYZE,BUFFERS) SELECT sum(value) FROM invoices_view;
Finalize Aggregate  (cost=1514195.47..1514195.47 rows=1 width=32) (actual time=5102.805..5102.806 rows=1 loops=1)
  Buffers: shared hit=14996 read=1446679
  I/O Timings: read=3235.147
  ->  Gather  (cost=1514195.16..1514195.47 rows=3 width=32) (actual time=5102.716..5109.229 rows=4 loops=1)
        Workers Planned: 3
        Workers Launched: 3
        Buffers: shared hit=14996 read=1446679
        I/O Timings: read=3235.147
        ->  Partial Aggregate  (cost=1513195.16..1513195.17 rows=1 width=32) (actual time=5097.626..5097.626 rows=1 loops=4)
              Buffers: shared hit=14996 read=1446679
              I/O Timings: read=3235.147
              ->  Parallel Seq Scan on invoices  (cost=0.00..1505835.14 rows=14720046 width=6) (actual time=0.049..3734.495 rows=11408036 loops=4)
                    Buffers: shared hit=14996 read=1446679
                    I/O Timings: read=3235.147
Planning Time: 2.503 ms
Execution Time: 5109.327 ms

有人对我如何加快速度有任何想法吗？还是我现在应该寻找Postgres的替代方案？

更多详细信息

这是我要在数据集上运行的查询的最简单版本。

例如，我需要能够基于用户输入（即附加的WHERE子句和GROUP BY）进行求和。

仅保留最简单的情况即可。

如果表是仅INSERT的，则有多种方法可以使您的总和（快得多）。

假设存在一列具有单调递增值的列（例如ID或在您的示例中创建的列），请创建 MATERIALZED VIEW 来预先计算早于给定阈值的（最近）和。然后只需将最近添加的总和添加到其中即可。

您应该考虑使用触发器来跟踪滚动金额：

CREATE OR REPLACE FUNCTION func_sum_invoice()
RETURNS trigger AS
$BODY$
BEGIN
    UPDATE invoices_sum
    SET total = total + NEW.value;
RETURN NEW;
END;
$BODY$

然后使用此功能创建触发器：

CREATE TRIGGER sum_invoice
AFTER INSERT ON invoices
FOR EACH ROW
EXECUTE PROCEDURE func_sum_invoice();

现在，每个插入invoices表的插入都将触发一个触发器，该触发器可以计算滚动总和。要获得该总和，现在只需要选择一个即可，这应该非常快：

SELECT total
FROM invoices_sum;

如何在大表上的Postgres中加快SUM查询

hgp_11 回答：如何在大表上的Postgres中加快SUM查询

大家都在问