Início / dba / Perguntas / 269834
Accepted
Cerin
Asked: 2020-06-25 14:56:07 +0800 CST

Desempenho de consulta degradado após atualizar o Postgres

  • 772

Eu tenho uma tabela com vários milhões de registros em um banco de dados PostgreSQL 12 e, após uma atualização de 11 para 12, algumas consultas começaram a ter um desempenho horrível. Eles passaram de ~ 1 segundo para ~ 5 minutos. Tentei reconstruir todos os índices, aspirar e todas as frutas comuns do Postgres, mas o desempenho ainda é terrível.

Esta é a consulta:

SELECT id, activity_count
FROM user
WHERE (search_index) @@ (to_tsquery('pg_catalog.english', '''1234567890'':*') AND active = true
ORDER BY activity_count DESC LIMIT 101

Em outras palavras, encontre todos os usuários ativos correspondentes ao número de conta fornecido e classifique do mais ativo para o menos.

Essa consulta leva cerca de 5 minutos para retornar apenas 2 registros. Alguma coisa não está certa.

A coluna search_indexé um tsvector que armazena todas as palavras-chave dos vários campos de texto da tabela (apenas coisas como account_number, name, etc).

Eu tenho um índice GIN criado para esta coluna com:

CREATE INDEX user_search_index_gin
    ON public.user USING gin
    (search_index)
    TABLESPACE pg_default;

Eu também tenho um índice para a activecoluna com:

CREATE INDEX user_active
    ON public.user USING btree
    (active ASC NULLS LAST)
    TABLESPACE pg_default;

E eu tenho um índice ordenado para o activity_countcom:

CREATE INDEX user_activity_count
    ON public.user USING btree
    (activity_count ASC NULLS LAST)
    TABLESPACE pg_default;

No entanto, quando executo EXPLAIN, recebo:

"Limit  (cost=0.56..11443.66 rows=101 width=1552)"
"  ->  Index Scan Backward using user_activity_count on user  (cost=0.56..36010185.91 rows=317836 width=1552)"
"        Filter: (active AND (search_index @@ '''1234567890'':*'::tsquery))"

Por que está usando apenas o índice user_activity_count e não o índice GIN mais eficiente? Como faço para corrigir isso?

postgresql query-performance

1 respostas

  1. Best Answer

    Qual índice usar?

    Embora suas WHEREcondições não sejam muito seletivas, o plano de consulta atual faz muito sentido com o ORDER BY activity_count DESC LIMIT 101. Ver:

    No entanto, seus predicados me parecem bastante seletivos:

    WHERE (search_index) @@ (to_tsquery('pg_catalog.english', '''1234567890'':*') AND active = true

    Isso é, a menos que seu exemplo '1234567890'seja enganoso. (Tem certeza de que deseja as aspas simples no termo de pesquisa?) Obviamente, não é a consulta real que você usou. Parênteses sem correspondência. De qualquer forma, você provavelmente deseja a configuração de pesquisa de texto "simples" (além de algumas melhorias):

    WHERE  search_index @@ to_tsquery('pg_catalog.simple', '''1234567890'':*')
AND    active

    Ver:

    Estatisticas

    Portanto, parece que suas estatísticas não estão atualizadas.

    após uma atualização de 11 para 12, algumas consultas começaram a ter um desempenho horrível. Eles passaram de ~ 1 segundo para ~ 5 minutos. Tentei reconstruir todos os índices, aspirar e todas as frutas comuns do Postgres, mas o desempenho ainda é terrível.

    Por que está usando apenas o índice user_activity_count e não o índice GIN mais eficiente?

    Você também adicionou ANALYZEà sua cesta todas as frutas comuns do Postgres ? Você certamente não mencionou isso. Considere as instruções do manual :

    1. Estatisticas

      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 o seu banco de dados for grande , você pode estar interessado na vacuumdbopção --analyze-in-stages. (Esse navio pode ter navegado para o caso em questão.) O manual:

    Essa opção é útil para analisar um banco de dados recém-preenchido de um dump restaurado ou por arquivos pg_upgrade. Esta opção tentará criar algumas estatísticas o mais rápido possível, para tornar o banco de dados utilizável e, em seguida, produzir estatísticas completas nas etapas subsequentes.

    Relacionado:

    • 4

relate perguntas