postgresql - 缓慢的 Postgres 9.3 查询，再次

Question

这是对 Slow Postgres 9.3 queries 问题的跟进.

新索引绝对有帮助。但是我们看到的是，有时查询在实践中比我们运行 EXPLAIN ANALYZE 时要慢得多。示例如下，在生产数据库上运行:

explain analyze SELECT * FROM messages WHERE groupid=957 ORDER BY id DESC LIMIT 20 OFFSET 31980;
                                                                       QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=127361.90..127441.55 rows=20 width=747) (actual time=152.036..152.143 rows=20 loops=1)
   ->  Index Scan Backward using idx_groupid_id on messages  (cost=0.43..158780.12 rows=39869 width=747) (actual time=0.080..150.484 rows=32000 loops=1)
         Index Cond: (groupid = 957)
 Total runtime: 152.186 ms
(4 rows)

在启用慢速查询日志记录的情况下，我们看到此查询的实例花费了 2 多秒。我们也有 log_lock_waits=true，大约在同一时间没有报告慢锁。什么可以解释执行时间的巨大差异？

Answer 1

LIMIT x OFFSET y 通常不会比 LIMIT x + y 快多少。较大的 OFFSET 总是 相对昂贵。建议索引in the linked question有帮助，但是虽然您无法从中获得仅索引扫描，但 Postgres 仍然必须检查堆(主要关系)中至少 x + y 行的可见性以确定正确的结果。

SELECT *
FROM   messages
WHERE  groupid = 957
ORDER  BY id DESC
LIMIT  20
OFFSET 31980;

索引 (groupid,id) 上的

CLUSTER 将有助于增加堆中数据的位置并减少每次查询要读取的数据页数。绝对是一场胜利。但是，如果所有 groupid 被查询的可能性相同，那将无法消除用于缓存的 RAM 太少的瓶颈。如果您有并发访问，请考虑使用 pg_repack 而不是 CLUSTER:

• Optimize Postgres timestamp query range

您真的需要返回所有列吗？ (SELECT *) 如果您只需要返回几个小列，则启用仅索引扫描 的覆盖索引可能会有所帮助。 (不过，autovacuum 必须足够强大才能处理对表的写入。只读表是理想的。)

此外，根据您的链接问题，您的表在磁盘上有 32 GB。 (通常在 RAM 中多一点)。 (groupid,id) 上的索引至少又增加了 308 MB(没有任何膨胀):

SELECT pg_size_pretty(7337880.0 * 44);  -- row count * tuple size

• Making sense of Postgres row sizes

您有 8 GB RAM，您预计其中约 4.5 GB 用于缓存(effective_cache_size = 4608MB)。这足以缓存索引以供重复使用，但还不足以缓存整个表。

如果您的查询恰好在缓存中找到数据页，则速度很快。否则，不是那么多。差别很大，即使使用 SSD 存储(使用 HDD 时差异更大)。

与此查询没有直接关系，但 8 MB 的 work_mem (work_mem = 7864kB) 对于您的设置来说似乎太小了。根据各种其他因素，我会将其设置为至少 64MB(除非您有许多带有排序/哈希操作的并发查询)。就像@Craig 评论的那样，EXPLAIN (BUFFERS, ANALYZE) 可能会告诉我们更多信息。

最佳查询计划还取决于值频率。如果只有几行通过过滤器，那么对于某些 groupid 的结果可能是空的，查询速度比较快。如果必须获取表的很大一部分，则可以使用简单的顺序扫描。您需要有效的表统计信息(再次是 autovacuum)。 groupid 可能还有一个更大的统计目标:

• Keep PostgreSQL from sometimes choosing a bad query plan