Início / dba / Perguntas / 252955
Accepted
Hassan Baig
Asked: 2019-11-12 04:02:06 +0800 CST

Acelerando a consulta UPDATE muito lenta (Postgresql 9.6)

  • 772

Em um aplicativo chamado Links, os usuários publicam conteúdo interessante que descobriram recentemente e outros podem comentar essas postagens.

Há também uma funcionalidade adicional, que permite aos usuários deixar um comentário em um comentário.

Todos os comentários (e comentários sobre comentários) são salvos em uma tabela chamada links_publicreplyno meu banco de dados postgresql 9.6.5. Esta tabela contém uma chave estrangeira auto-referenciada para dar suporte ao comments on comments. É uma tabela grande, com quase 200 milhões de linhas.

Uma UPDATEconsulta na links_publicreplytabela está aparecendo consistentemente no slow_log. Está demorando mais de 5000ms e é ~100X mais lento do que estou experimentando na maioria das outras operações do postgresql.

Aqui está um exemplo do SQL correspondente do meu log lento:

UPDATE "links_publicreply"
SET "direct_reply_tgt_text_prefix"='',
    "direct_reply_tgt_text_postfix"=''
WHERE "links_publicreply"."direct_reply_id"=175054159;

Veja os resultados da análise explicativa: https://explain.depesz.com/s/G8wX

De acordo com isso, o Seq Scan acaba filtrando 47.535.365 linhas e é a fonte da lentidão.

O que faço para reduzir esse tempo de execução exorbitante?

Sendo um DBA acidental, não sou especialista no assunto. Minha intuição é que estou filtrando a chave estrangeira auto-referencial, então isso já deveria ser um índice (portanto otimizado para pesquisas)? Estou um pouco perplexo aqui.

Adição:

Aqui está toda a saída para \d links_publicreply:

            Column             |           Type           |                           Modifiers                            
-------------------------------+--------------------------+----------------------------------------------------------------
 id                            | integer                  | not null default nextval('links_publicreply_id_seq'::regclass)
 submitted_by_id               | integer                  | not null
 answer_to_id                  | integer                  | not null
 submitted_on                  | timestamp with time zone | not null
 description                   | text                     | not null
 category                      | character varying(20)    | not null
 seen                          | boolean                  | not null
 abuse                         | boolean                  | not null
 direct_reply_tgt_uname        | text                     | 
 direct_reply_tgt_text_prefix  | text                     | 
 direct_reply_tgt_text_postfix | text                     | 
 direct_reply_id               | integer                  | 
 level                         | integer                  | 
Indexes:
    "links_publicreply_pkey" PRIMARY KEY, btree (id)
    "id_answer_to_id" btree (answer_to_id, id DESC)
    "links_publicreply_answer_to_id" btree (answer_to_id)
    "links_publicreply_submitted_by_id" btree (submitted_by_id)
Foreign-key constraints:
    "links_publicreply_answer_to_id_fkey" FOREIGN KEY (answer_to_id) REFERENCES links_link(id) DEFERRABLE INITIALLY DEFERRED
    "links_publicreply_direct_reply_id_fkey" FOREIGN KEY (direct_reply_id) REFERENCES links_publicreply(id)
    "links_publicreply_submitted_by_id_fkey" FOREIGN KEY (submitted_by_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
Referenced by:
    TABLE "links_publicreply" CONSTRAINT "links_publicreply_direct_reply_id_fkey" FOREIGN KEY (direct_reply_id) REFERENCES links_publicreply(id)
    TABLE "links_report" CONSTRAINT "links_report_which_publicreply_id_fkey" FOREIGN KEY (which_publicreply_id) REFERENCES links_publicreply(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_seen" CONSTRAINT "links_seen_which_reply_id_fkey" FOREIGN KEY (which_reply_id) REFERENCES links_publicreply(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_link" CONSTRAINT "publicreplyposter_link_fkey" FOREIGN KEY (latest_reply_id) REFERENCES links_publicreply(id) ON UPDATE CASCADE ON DELETE CASCADE
postgresql performance

1 respostas

  1. Best Answer

    Você deve criar um índice de árvore B:

    CREATE INDEX ON links_publicreply (direct_reply_id);

    Nota: seu índice links_publicreply_answer_to_idé inútil e deve ser descartado.

    • 3

relate perguntas