Otimizando o desempenho lento da consulta SELECT simples

Principais problemas suspeitos (sinopse)

1. Você precisa executar ANALYZEapós uma atualização de versão principal com o pg_upgrade. As estatísticas da tabela não são copiadas. Possivelmente, ajuste as configurações de autovacuum também.

2. Um índice de várias colunas (which_group_id, submitted_on DESC)deve servir muito melhor a essa consulta.

Consulta

Consulta formatada sem ruído e com aliases de tabela para melhor legibilidade:

SELECT lr.id, lr.text, lr.which_group_id, lr.writer_id
     , lr.submitted_on, lr.image, lr.device, lr.category
     , au.id, au.username
     , lu.id, lu.user_id, lu.score, lu.avatar
FROM   links_reply            lr
JOIN   auth_user              au ON au.id = lr.writer_id
LEFT   JOIN links_userprofile lu ON lu.user_id = au.id
WHERE  lr.which_group_id = 119287
ORDER  BY lr.submitted_on DESC
LIMIT  25;

Não vejo problemas com a consulta em si.

Corrupção do índice?

(Eu não acho.)

Isso pode ser algum tipo de problema de indexação?

Se você suspeitar de corrupção, execute REINDEX. O manual sugere:

Se você suspeitar de corrupção de um índice em uma tabela de usuário, você pode simplesmente reconstruir esse índice ou todos os índices na tabela, usando REINDEX INDEX ou REINDEX TABLE.

No caso de acesso concorrente: o bloqueio difere de descartar e recriar índices do zero em vários aspectos. O manual:

REINDEXé semelhante a descartar e recriar o índice em que o conteúdo do índice é reconstruído do zero. No entanto, as considerações de bloqueio são bastante diferentes. REINDEXbloqueia as gravações, mas não as leituras da tabela pai do índice. Ele também faz um bloqueio exclusivo no índice específico que está sendo processado, o que bloqueará as leituras que tentarem usar esse índice. Em contraste, DROP INDEXmomentaneamente realiza um bloqueio exclusivo na tabela pai, bloqueando tanto as gravações quanto as leituras. O subsequente CREATE INDEXbloqueia as gravações, mas não as leituras; como o índice não está lá, nenhuma leitura tentará usá-lo, o que significa que não haverá bloqueio, mas as leituras podem ser forçadas a varreduras sequenciais caras.

Se isso ainda for um problema para operações simultâneas, considere CREATE INDEX CONCURRENTLYcriar novos índices duplicados e, em seguida, descarte os antigos em uma transação separada.

Estatísticas da tabela

No entanto , parece que as estatísticas da tabela são o problema real. Cotação do seu plano de consulta:

Varredura de índice para trás usando links_reply_submitted_on em links_reply
(custo=0,44..1.664.030,07 linhas=2.001 largura=50)
(tempo real=522.811..716.414 linhas=25 voltas=1)
    Filtro: (which_group_id = 119287)
    Linhas removidas pelo filtro: 1721320

Minha ênfase em negrito. Parece que o Postgres baseia esse plano de consulta em estatísticas enganosas. Ele espera muito mais acertos e provavelmente também subestima a seletividade do predicado which_group_id = 119287. Acaba filtrando 1,7 milhão de linhas. Isso cheira a estatísticas de tabela imprecisas. E há uma explicação provável, também:

Ao atualizar as versões principais pg_upgrade, não copia as estatísticas existentes para a nova versão do banco de dados. Recomenda-se executar VACUUM ANALYZEou pelo menos ANALYZEdepois pg_upgrade. A ferramenta ainda solicita para lembrá-lo. O manual:

Como as estatísticas do otimizador não são transferidas pelo pg_upgrade, você será instruído a executar um comando para gerar novamente essas informações no final da atualização. Pode ser necessário definir parâmetros de conexão para corresponder ao seu novo cluster.

Se você não fizer isso, as tabelas ficarão sem estatísticas atuais até que o autovacuum seja acionado por gravações suficientes na tabela (ou algum outro comando utilitário como CREATE INDEXou ALTER TABLEatualizar algumas estatísticas em tempo real).

O mesmo vale para qualquer ciclo de despejo/restauração (com pg_dump& pg_restoreno seu caso). As estatísticas da tabela não são incluídas no dump.

Sua mesa é muito grande (~25M rows). A configuração padrão para autovacuum define o limite como porcentagem do row_count mais o deslocamento fixo. Às vezes, isso não funciona bem para tabelas grandes, levará algum tempo até a próxima análise automática.

Execute ANALYZEo manual na mesa ou em todo o banco de dados.

Relacionado:

• Tiny table causa degradação extrema do desempenho, corrigida por VACUUM forçado. Por quê?
• Uso de índice em uma tabela temporária

Melhor índice

... questão de indexação, especificamente com o links_reply_submitted_oníndice?

Sim, isso também. O índice "links_reply_submitted_on" btree (submitted_on)não está otimizado para o padrão em sua consulta:

SELECT ...
FROM   links_reply            lr
JOIN   ...
WHERE  lr.which_group_id = 119287
ORDER  BY lr.submitted_on DESC
LIMIT  25

Como vimos no plano de consulta acima, o Postgres usa uma varredura de índice, lendo o índice de baixo para cima e filtra as não correspondências. Essa abordagem pode ser razoavelmente rápida se todos (poucos!) selecionados which_group_idtiverem 25 linhas no passado recente. Mas não funcionará tão bem para uma distribuição desigual de linhas ou muitos valores distintos para which_group_id.

Este índice de várias colunas é um ajuste melhor:

links_reply__which_group_id__submitted_on btree (which_group_id, submitted_on DESC)

Agora, o Postgres pode apenas escolher as 25 primeiras linhas para o selecionado which_group_id, independentemente da distribuição de dados.

Relacionado:

• Índice e desempenho de várias colunas

Mais explicações

Sobre sua observação:

Por fim, também notei (pela experiência do usuário) que para todos os objetos de grupo criados até a semana passada, a consulta ainda é executada rapidamente. Mas todos os novos objetos que estão sendo criados agora estão produzindo um log lento.

Por quê? Novos objetos podem ainda não ter 25 entradas, então o Postgres precisa continuar varrendo todo o grande índice na esperança de encontrar mais. Embora isso seja extremamente caro com seu antigo índice e plano de consulta, o mesmo é muito barato com o novo índice (e estatísticas de tabela atualizadas).

Além disso, com estatísticas de tabela precisas, o Postgres provavelmente teria usado seu outro índice "links_reply_which_group_id" btree (which_group_id)para pegar as poucas linhas existentes rapidamente (e classificar se houver mais de 25). Mas meu novo índice oferece um plano de consulta mais confiável em qualquer caso.

Coisas menores

Existem várias outras coisas (menores) que você pode fazer, como otimizar o layout da mesa ou ajustar as configurações de autovacuum, mas essa resposta já é longa o suficiente. Relacionado:

• Configurando o PostgreSQL para desempenho de leitura

E mais tarde você comentou:

Também removeu campos estranhos ...

Certamente ajuda a recuperar apenas as colunas que você realmente precisa. Faça isso, além disso. Mas não é a questão principal aqui.