我的 Postgresql 数据库中有一个包含 6000 万行的表。
表如下:
id varchar PK
cutomerId Integer
orderId Integer
modified timestamp.
现在我想创建特定日期的客户和订单数量的摘要
SELECT customerId, COUNT(orderId)
FROM orders o
WHERE DATE(o.modified) = '2023-04-27' GROUP by customerId
这最多需要 5 分钟才能在 SQL 查询 UI 中返回。
有什么办法可以加快这个速度吗?
这是我的解释分析:
Finalize GroupAggregate (cost=778305.85..778574.65 rows=1061 width=12) (actual time=43412.506..43435.712 rows=1089 loops=1)
Group Key: customerId
Buffers: shared hit=1414708 read=576689
I/O Timings: read=123468.547
-> Gather Merge (cost=778305.85..778553.43 rows=2122 width=12) (actual time=43412.472..43435.024 rows=3267 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=1414708 read=576689
I/O Timings: read=123468.547
-> Sort (cost=777305.82..777308.47 rows=1061 width=12) (actual time=43341.758..43341.892 rows=1089 loops=3)
Sort Key: customerId
Sort Method: quicksort Memory: 100kB
Worker 0: Sort Method: quicksort Memory: 100kB
Worker 1: Sort Method: quicksort Memory: 100kB
Buffers: shared hit=1414708 read=576689
I/O Timings: read=123468.547
-> Partial HashAggregate (cost=777241.89..777252.50 rows=1061 width=12) (actual time=43340.904..43341.102 rows=1089 loops=3)
Group Key: customerId
Buffers: shared hit=1414694 read=576689
I/O Timings: read=123468.547
-> Parallel Index Scan using orders_idx on orders o (cost=0.43..770040.97 rows=1440183 width=8) (actual time=0.205..42743.905 rows=1097188 loops=3)
Index Cond: ((modified >= '2022-02-16'::date) AND (modified < '2022-02-17'::date))
Buffers: shared hit=1414694 read=576689
I/O Timings: read=123468.547
Planning Time: 10.171 ms
Execution Time: 43436.656 ms
一种可能是调整 Postgres服务器,特别是处理数据库集群如何处理索引的内存配置设置。Postgres 配置文件开箱即用,允许软件在非常小的计算环境中运行。通过了解 Postgres 如何使用资源,您可以为您的硬件自定义设置并优化性能。
另一个更基本的步骤是对查询运行命令并查看查询规划器如何使用索引
EXPLAIN
。如果您在此处发布命令的输出,EXPLAIN
您可能会得到更多有用的回复。你需要
date(modified)
为此建立一个索引。更好的解决方案是建立索引
modified
并将查询更改为为了提高查询速度,可以创建覆盖索引:
这
VACUUM
将更新可见性图,以便仅索引扫描可以变得更快。确保桌子经常被吸尘。