时间戳

首页 > 解决方案 > 对于 10 亿行，Postgres 查询非常慢

sql - 对于 10 亿行，Postgres 查询非常慢

问题描述

我的 Postgres 数据库表中有大约 10 亿行和 6 列（id、col1、col2..col5）。我有 id 作为我的 PRIMARY_KEY。我将其用作只读数据库，每次我只需要根据 10000 个 ID 列表检索/读取 10000 行。查询需要 40-60 秒才能检索 10000 行，这非常慢。以下是我的查询：

SELECT * 
FROM table_1b 
WHERE id IN (18792, 8718, 15010, 16671,.....*list of 10000 ids*....., 290224379, 192494270)

EXPLAIN ANALYZE 查询：

Index Scan using table_1b_pkey on table_1b 
(cost=0.57..46197.28 rows=10000 width=239) 
(actual time=13.305..55927.060 rows=10000 loops=1)
Index Cond: (id = ANY ('{18792, 8718, 15010, 16671,............,290224379,192494270}'::bigint[]))
Planning Time: 13.380 ms
Execution Time: 55935.614 ms

我想显着加快 10000 行的检索速度。我对 Postgres 相当陌生。我已阅读其他帖子并尝试了一些方法，包括将 work_mem 从 4 MB 修改为 256 MB，但没有帮助。Postgres 服务器在具有 384 GB RAM 的机器上运行。

提前感谢任何帮助。

编辑 1：解释 10 条记录的分析

Index Scan using table_1b_pkey on table_1b
(cost=0.57..49.95 rows=10 width=239) 
(actual time=17.204..123.489 rows=10 loops=1)
Planning Time: 0.378 ms
Execution Time: 123.540 ms

编辑 2：track_io_timing = ON；解释（分析，缓冲区）

Index Scan using table_1b_pkey on table_1b 
(cost=0.57..17243.33 rows=10000 width=239) 
(actual time=17.304..55371.511 rows=10000 loops=1)
Buffers: shared hit=29891 read=20108
I/O Timings: read=55032.422
Planning Time: 13.113 ms
Execution Time: 55380.644 ms

编辑 3：更新的服务器配置

max_connections = 20
shared_buffers = 25GB
effective_cache_size = 75GB
maintenance_work_mem = 2GB
checkpoint_completion_target = 0.7
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 640MB
min_wal_size = 1GB
max_wal_size = 4GB

标签： sqlpostgresqlquery-optimizationpostgresql-13

解决方案

所以基本上所有的时间都花在从磁盘读取数据上。每行需要两次读取，一次用于索引叶页，一次用于表页。（索引中更高级别的块可能主要在缓存中）。操作系统/文件系统缓存可能会满足其中的一些（未知）部分。

384GB 是很多内存。也许容纳整个表和索引就足够了，我不知道。（表和索引有多大？）但是仅仅有足够的 RAM 来保存数据集是不够的。您必须首先将数据放入 RAM（pg_prewarm 可以帮助您），并且您必须说服操作系统将其保留在那里而不是驱逐它以支持其他东西（您是否还有其他大型数据集哪些是经常使用的？）。

当然，“简单”的解决方案是获得更快的存储硬件。

如果您创建了一个包含表的所有列的覆盖索引（您可以使用 INCLUDE 语法，这样您仍然可以只有一个索引并将其用于此查询和唯一性强制），这将启用仅索引扫描. 这会将 IO 量减少一半，因为您只需要读取索引叶页而不是每一行的表页。

如果将 id 放入另一个表中，然后将查询写入联接，则可以获得并行查询。

SELECT table_1b.* 
FROM table_1b JOIN id_list using (id)

由于 id_list 表会很小（对于 IN 列表，10,000 行大，但对于表来说不是），您可能需要覆盖其设置才能实际使用并行查询。 ALTER TABLE id_list set (parallel_workers=8). 这有多有效取决于您的存储系统。单个旋转 HDD 可能会看到很少的改进，而 SSD 或 HDD RAID 会看到更多。

推荐阅读