我正在尝试优化在 PostgreSQL 15.4 中连接两个大表(40MM+ 行)的查询。
SELECT files.id, ARRAY_AGG(b.status)
FROM files
LEFT OUTER JOIN processing_tasks b
ON (files.id = b.file_id AND b.job_id = 113)
WHERE files.round_id = 591
GROUP BY files.id;
explain (analyze)
完全相同的查询的两个计划位于:
https://explain.depesz.com/s/cUXB需要 87 秒,使用并行 Seq 扫描
processing_tasks.job_id
(默认计划)https://explain.depesz.com/s/j39G需要 4 秒,使用位图索引扫描
processing_tasks.job_id
(当 时set local enable_seqscan = OFF
)
在 中files
,908,275 / 39,000,105 (2.3%) 个元组有round_id=591
; 它是静态的。
在 中processing_tasks
,4,026,364 / 60,780,802 (6.6%) 个元组有job_id=113
,并且随着行的插入,这个值将变得越来越常见,可能达到表的 15%。
这些链接上的“注释”选项卡包括表和索引定义,并显示数据pg_stats
包括这些最常见的值。
我对以下几个可能的目标中的任何一个都感到满意:
使用索引扫描时花费的 3-4 秒是可以接受的,如果这是我能做的最好的事情,那么
enable_seqscan
即使在生产中我也应该继续覆盖吗?(我猜是在交易中)但我宁愿进一步减少 3-4 秒,减少到 2 秒以下,并且随着
processing_tasks
时间的增长而保持不变。
这是一个奇怪的计划。奇怪的部分在于 depesz 搞砸了并行位图堆扫描的表示,仅显示其中一个工作人员的“缓冲区:”,就好像它是计划树的整个部分的“缓冲区:”一样。转到“来源”选项卡会提供真实信息。
这仍然很奇怪。
Buffers: shared hit=108093
没有读取。计划的这一部分需要近 850MB 的数据,而它的每一位恰好已经在共享缓冲区中?这是否只是因为您一遍又一遍地运行这个特定的查询参数化,但只向我们展示了它最快的运行?如果是这样,这似乎是一种非常不切实际的优化查询的方法。如果您从冷缓存运行它,或者如果您运行最近没有运行过的新参数化,会是什么样子?没有办法告诉 PostgreSQL 期望在缓存中找到某个特定查询所需的所有数据。还有 effective_cache_size,但这是针对单个查询执行预计会重复命中相同数据的情况,而不是针对不同执行都命中相同数据的情况。您可以修改 seq 和随机 page_cost 设置,但您提出的任何设置都不太可能全局适用。如果您需要仅针对这一查询的范围调整设置,那么调整 enable_seqscan 而不是其他事情会更直接。
或者,您可以使用查询提示。有一个第三方扩展可以实现它们,https://github.com/ossc-db/pg_hint_plan。它甚至可以在 RDS 中使用;我不知道其他托管数据库提供商。
或者,(job_id, file_id,status) 上的索引应该在此表上启用仅索引扫描,如果许多页面被标记为全部可见,那么对于规划器来说,这可能比 seq 扫描更好。
对此的一些其他评论:如果您经常运行如此大的查询,您的 work_mem 似乎太低了;提高它不会解决 seqscan 问题,但可能会使快速计划更快。你的 IO 看起来很糟糕,40MB/s 似乎来自一台 15 年的笔记本电脑,而不是现代服务器级硬件(尽管这显然是针对每个工人的,所以实际上 120MB/s 更好,但仍然不是很好)。
首先,您的查询中存在 42 MB/s 的可疑缓慢的 seq 扫描。正如 jjanes 所说,这是 5400rpm 笔记本电脑硬盘速度,除非有许多其他进程同时访问磁盘。如果是 SSD,那么您就有问题了。如果您没有在数据库上使用 SSD,那么您应该考虑一下。我从便宜的台式 SSD 上的 zstd 压缩 btrfs 中获得大约 3GB/s 的读取速度,因此在下面的示例中,seq 扫描大约需要一秒钟。
在你的两个计划中,它会拉动:
不幸的是,EXPLAIN 并没有说明这些行中有多少比例被提取,然后保留或丢弃,因为它们的 file_id 错误。您应该通过一些计数查询进行调查。有两种情况:
在这种情况下,您不能做太多事情,因为它必须获取这些行。您唯一的选择是要么不执行查询,要么加快获取行的速度:SSD/更多 RAM,或者通过对表进行聚类或使用覆盖索引来减少 IO 的随机性并减少执行次数。
在这种情况下,目标应该是不获取将被连接丢弃的行。也许处理任务上的多列索引会有所帮助。您已经在 (job_id, file_id, task_id) 上有一个唯一索引,并且它没有被使用,所以也许 (file_id,job_id) 会很有用。
我创建了具有大致相同分布和相关性的测试数据:
为了避免对 array_agg() 进行基准测试,我使用 max() 代替:
有了足够的work_mem(256MB),我得到了这个计划:(未缓存,缓存)这类似于您的快速计划,对两个表进行位图索引扫描加上散列连接。它非常慢,并且文件上的位图索引扫描不是很有用,因为它无论如何都会读取表的大部分内容,而 SSD 的随机读取速度可能很快,但这并不是灵丹妙药。请注意,您的表已在运行中缓存。
由于我故意插入了假的 url 列,从文件中读取的数据量非常大。因此,我创建了一个覆盖索引:
让我们重做相同的查询:结果计划要好得多,因为它在索引中查找 round_id 并直接获取 file_id。这避免了表文件上的大量 IO,但只有当您在查询中使用的所有列都在该覆盖索引中时,它才会起作用。否则它仍然需要从表中获取。
如果您的表包含一些短列和 URL 等大列,则可以使用垂直分区并将 URL 存储在辅助表中。这和TOAST的做法有点相似。这使得对辅助表中的列的访问变慢,但主表变得更小。或者您可以向覆盖索引添加更多列。
另一个解决方案是对表文件进行集群,在本例中是通过 (round_id,file_id) 进行集群,但这需要一段时间并锁定表。优点是具有相同 round_id 的文件在表中相邻,这意味着位图索引扫描将主要执行顺序 IO,而不是大量随机查找,因此速度更快(请注意,这是缓存的,因此人为地更快)。
这些都不能解决另一个表上的 IO 问题。所以我尝试了处理任务的一些索引...
最后一个效果很好,因为它允许合并连接在 400 毫秒内完成。然而,这个索引对于你的唯一约束是多余的,所以这个组合应该工作得更好:
IMO 这是最好的选择,除非您有许多相同类型的不同查询,这些查询都需要自己的特殊覆盖索引。在这种情况下,您最终会得到大量重复的数据。
另一种选择是仅进行反规范化并将 round_id 放入processing_tasks 表中。
这个效果很好,在未缓存的情况下运行大约 500 毫秒,在缓存的情况下运行 255 毫秒。
这允许两个仅索引扫描之间的合并连接,这是最快的选项。
不使用 files 表而仅从processing_tasks_2 获取行需要 16 毫秒,但在没有左连接的情况下会给出不同的结果。
所有这些优化都是有效的,但它们都是有针对性的:这些索引确实会占用空间和资源来保持最新状态。由您决定它们是否值得付出这个代价。
然而
Sure the query is slow, but if you want to optimize it, it must mean you're running it often. Otherwise it would be a 4AM batch job. If it needs to run under 4 seconds, maybe you run it every minute or even more frequent? So why do you need that 1 million row result so often?
This looks like a job management system which builds a todolist and assigns jobs to subprocesses or machines.
Are you actually using all the rows?
If you are not, and what you actually want is get the next batch of stuff that should be dispatched to workers, then there are simpler ways involving LIMIT or cursors, to only fetch a much smaller number of rows but do it more often.
If you want to poll worker status and are only looking for changes between two executions of this query, then postgres also has a notify mechanism, or you could also filter more precisely, etc.