Aumentar work_mem e shared_buffers no Postgres 9.2 diminui significativamente as consultas

Em primeiro lugar, lembre-se de que work_mem é por operação e, portanto, pode ficar excessivo rapidamente. Em geral, se você não está tendo problemas com a lentidão das classificações, eu deixaria o work_mem em paz até que você precise.

Observando seus planos de consulta, uma coisa que me impressiona é que os acertos de buffer são muito diferentes olhando para os dois planos, e que mesmo as varreduras sequenciais são mais lentas. Suspeito que o problema tenha a ver com o cache de leitura antecipada e com menos espaço para isso. O que isso significa é que você está polarizando a memória para reutilização de índices e contra a leitura de tabelas no disco.

Meu entendimento é que o PostgreSQL procurará no cache por uma página antes de lê-la do disco porque não sabe realmente se o cache do SO conterá essa página. Como as páginas permanecem no cache e porque esse cache é mais lento que o cache do sistema operacional, isso altera os tipos de consultas que são rápidas versus as que são lentas. De fato, lendo os planos, além dos problemas de work_mem, parece que todas as informações da sua consulta vêm do cache, mas é uma questão de qual cache.

work_mem : quanta memória podemos alocar para uma operação de classificação ou junção relacionada. Isso é por operação, não por instrução ou por back-end, portanto, uma única consulta complexa pode usar muitas vezes essa quantidade de memória. Não está claro que você está atingindo esse limite, mas vale a pena notar e estar ciente. se você aumentar demais, perderá memória que pode estar disponível para o cache de leitura e os buffers compartilhados.

shared_buffers : quanta memória alocar para a fila de páginas real do PostgreSQL. Agora, idealmente, o conjunto interessante do seu banco de dados ficará na memória armazenada em cache aqui e nos buffers de leitura. No entanto, o que isso faz é garantir que as informações usadas com mais frequência em todos os back-ends sejam armazenadas em cache e não liberadas no disco. No Linux, esse cache é significativamente mais lento do que o cache de disco do SO, mas oferece garantias de que o cache de disco do SO não é e é transparente para o PostgreSQL. Este é claramente onde está o seu problema.

Então, o que acontece é que quando temos uma requisição, verificamos primeiro os buffers compartilhados, pois o PostgreSQL tem profundo conhecimento desse cache, e procuramos as páginas. Se eles não estiverem lá, pedimos ao sistema operacional para abri-los a partir do arquivo, e se o sistema operacional tiver armazenado em cache o resultado, ele retornará a cópia em cache (isso é mais rápido que os buffers compartilhados, mas o Pg não pode dizer se está armazenado em cache ou em disk, e disk é muito mais lento, então o PostgreSQL normalmente não vai correr esse risco). Lembre-se de que isso também afeta o acesso à página aleatório versus sequencial. Assim, você pode obter um melhor desempenho com configurações de shared_buffers mais baixas.

Minha intuição é que você provavelmente terá um desempenho melhor, ou pelo menos mais consistente, em ambientes de alta simultaneidade com configurações maiores de shared_buffer. Lembre-se também de que o PostgreSQL pega essa memória e a mantém, portanto, se você tiver outras coisas em execução no sistema, os buffers de leitura conterão arquivos lidos por outros processos. É um tema muito grande e complexo. Configurações maiores de buffer compartilhado fornecem melhores garantias de desempenho, mas podem oferecer menos desempenho em alguns casos.

Além do efeito aparentemente paradoxal de que aumentar work_memdiminui o desempenho ( @Chris pode ter uma explicação), você pode melhorar sua função de pelo menos duas maneiras.

• Reescreva dois falsos LEFT JOINcom JOIN. Isso pode confundir o planejador de consultas e levar a planos inferiores.

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%')

• Supondo que seus padrões de pesquisa reais sejam mais seletivos, use índices de trigramas em pi.firstnamee pi.lastnamepara dar suporte a pesquisas não ancoradas LIKE. (Padrões mais curtos como '%a%'também são suportados, mas um índice provavelmente não ajudará para predicados não seletivos.):

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);

Ou um índice de várias colunas:

CREATE INDEX personinfo_name_gin_idx ON personinfo USING gin (firstname gin_trgm_ops, lastname gin_trgm_ops);

Deve tornar sua consulta um pouco mais rápida. Você precisa instalar o módulo adicional pg_trgm para isso. Detalhes nestas perguntas relacionadas:

• Como o LIKE é implementado?
• Correspondência de padrões com LIKE, SIMILAR TO ou expressões regulares no PostgreSQL

Além disso, você tentou definir work_mem localmente - apenas para a transação atual ?

SET LOCAL work_mem = '96MB';

Isso evita que transações simultâneas também consumam mais RAM, possivelmente deixando umas às outras de fome.