在Postgres 9.2上增加work_mem和shared_buffers会大大减慢查询速度

default_statistics_target = 50
maintenance_work_mem = 960MB
constraint_exclusion = on
checkpoint_completion_target = 0.9
effective_cache_size = 11GB
work_mem = 96MB
wal_buffers = 8MB
checkpoint_segments = 16
shared_buffers = 3840MB
max_connections = 80

#1 楼

首先，请记住work_mem是针对每个操作的，因此它很快就会变得过多。通常，如果您对排序速度慢没什么麻烦，我将不理会work_mem直到您需要它。

看看您的查询计划，令我震惊的一件事是缓冲区命中率看起来很不一样在两个计划中，甚至连扫描都比较慢。我怀疑该问题与预读缓存有关，并且其空间较小。这意味着您正在使内存偏向于索引的重复使用和读取磁盘上的表。磁盘，因为它并不真正知道操作系统缓存是否将包含该页面。因为页面然后保留在缓存中，并且因为该缓存比OS缓存慢，所以这改变了快速查询与慢速查询的排序。实际上，除了work_mem问题外，阅读计划，看起来您的所有查询信息都来自缓存，但这是哪个缓存的问题。

work_mem：我们可以为一个缓存分配多少内存？排序或相关的联接操作。这是针对每个操作，而不是针对每个语句或后端，因此单个复杂查询可以使用此内存量的很多倍。尚不清楚您是否达到了这个极限，但是值得注意和意识到。如果将其增加得太多，则会丢失可能用于读取缓存和共享缓冲区的内存。

shared_buffers：要分配给实际PostgreSQL页面队列的内存量。现在，理想情况下，数据库的有趣集合将保留在此处缓存的内存和读取缓冲区中。但是，这样做是为了确保缓存所有后端中最常用的信息，而不将其刷新到磁盘上。在Linux上，此缓存比OS磁盘缓存要慢得多，但是可以保证OS磁盘缓存不存在，并且对PostgreSQL透明。这很显然是您问题所在。如果它们不存在，我们会要求操作系统从文件中打开它们，并且如果操作系统已缓存结果，它将返回缓存的副本（这比共享缓冲区快，但是Pg无法确定是缓存还是打开磁盘，而磁盘要慢得多，因此PostgreSQL通常不会遇到这种情况。请记住，这也会影响随机和顺序页面访问。因此，在较低的shared_buffers设置下，您可能会获得更好的性能。还请记住，PostgreSQL会获取并保留该内存，因此，如果系统上正在运行其他内容，则读取缓冲区将保存其他进程读取的文件。这是一个非常大而复杂的话题。较大的共享缓冲区设置可提供更好的性能保证，但在某些情况下可能会降低性能。

#2 楼

除了看似矛盾的效果，增加work_mem会降低性能（@Chris可能会有解释），您还可以至少通过两种方式来改善功能。 LEFT JOIN。这可能会混淆查询计划程序，并导致劣等的计划。




，请在JOIN和pi.firstname上使用三字母组合索引来支持非锚定的pi.lastname搜索。 （也支持LIKE之类的更小模式，但索引不太可能对非选择谓词有所帮助。）：
或一个多列索引：

SELECT count(*) AS ct
FROM   contest            c
JOIN   contestparticipant cp ON cp.contestId = c.id
JOIN   personinfo         pi ON pi.id = cp.personinfoid
LEFT   JOIN teammember    tm ON tm.contestparticipantid = cp.id
LEFT   JOIN staffmember   sm ON sm.contestparticipantid = cp.id
LEFT   JOIN person        p  ON p.id = cp.personid
WHERE (pi.firstname LIKE '%a%'
OR     pi.lastname  LIKE '%b%')

应使您的查询快得多。您需要为此安装附加模块pg_trgm。这些相关问题下的详细信息：



如何实施LIKE？
与LIKE，SIMILAR TO或PostgreSQL中的正则表达式匹配的模式


此外，您是否尝试过在本地设置'%a%'-仅针对当前事务？

CREATE INDEX personinfo_firstname_gin_idx ON personinfo USING gin (firstname gin_trgm_ops);
CREATE INDEX personinfo_lastname_gin_idx  ON personinfo USING gin (lastname gin_trgm_ops);

这样可以防止并发事务占用更多RAM，甚至可能使彼此饿死。