Início / dba / Perguntas / 228244
Accepted
Hassan Baig
Asked: 2019-01-29 04:51:41 +0800 CST

Otimizando a consulta SELECT lenta (postgresql 9.6.5)

  • 772

Em um aplicativo chamado Links, os usuários postam conteúdo interessante em uma interface semelhante a um fórum, e outros postam respostas ou comentários sob esse conteúdo postado publicamente.

Essas respostas postadas são salvas em uma tabela postgresql chamada links_publicreply(postgresql 9.6.5 DB).

Uma consulta executada na tabela continua surgindo no slow_log(maior que 500ms). Isso está relacionado à exibição das 25 respostas públicas mais recentes acumuladas em um determinado conteúdo compartilhado.

Aqui está uma amostra do log lento:

LOG: duração: 1614,030 ms declaração:

 SELECT "links_publicreply"."id",
       "links_publicreply"."submitted_by_id",
       "links_publicreply"."answer_to_id",
       "links_publicreply"."submitted_on",
       "links_publicreply"."description",
       "links_publicreply"."abuse",
       "auth_user"."id",
       "auth_user"."username",
       "links_userprofile"."id",
       "links_userprofile"."user_id",
       "links_userprofile"."score",
       "links_userprofile"."avatar",
       "links_link"."id",
       "links_link"."description",
       "links_link"."submitter_id",
       "links_link"."submitted_on",
       "links_link"."reply_count",
       "links_link"."latest_reply_id"
FROM   "links_publicreply"
       INNER JOIN "links_link"
               ON ( "links_publicreply"."answer_to_id" = "links_link"."id" )
       INNER JOIN "auth_user"
               ON ( "links_publicreply"."submitted_by_id" = "auth_user"."id" )
       LEFT OUTER JOIN "links_userprofile"
                    ON ( "auth_user"."id" = "links_userprofile"."user_id" )
WHERE  "links_publicreply"."answer_to_id" = 8936203
ORDER  BY "links_publicreply"."id" DESC
LIMIT  25

Aqui estão os explain analyzeresultados da referida consulta: https://explain.depesz.com/s/pVZ5

De acordo com isso, cerca de 70% do tempo de consulta parece ser ocupado pela verificação do índice. Mas para um DBA acidental como eu, não é imediatamente óbvio qual otimização posso fazer para tornar isso mais eficiente. Talvez um índice composto em links_publicreply.answer_to_id, links_publicreply.id?

Isso me ajudaria muito a saber se um especialista em domínio pode fornecer orientação + intuição para resolver esse tipo de problema.

P \d links_publicreplyé:

                                      Table "public.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
 device          | character varying(10)    | default '1'::character varying
Indexes:
    "links_publicreply_pkey" PRIMARY KEY, btree (id)
    "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_submitted_by_id_fkey" FOREIGN KEY (submitted_by_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
Referenced by:
    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

    Em primeiro lugar, tentando reduzir o número de chamadas do index. Este filtro answer_to_id = 8936203retorna as 14740linhas que costumavam verificar nas outras tabelas. No entanto, você precisa apenas 25do topo id. E se você LIMIT 25, então, JOINas outras tabelas.

    WITH tmp_links_publicreply AS (
   SELECT ...
   FROM links_publicreply
   WHERE  "links_publicreply"."answer_to_id" = 8936203
   ORDER  BY "links_publicreply"."id" DESC
   LIMIT  25  
)
SELECT 
FROM tmp_links_publicreply t 
JOIN ... 
JOIN ...

    A consulta acima funcionaria corretamente se você tivesse a restrição entre links_publicreplye 2 tabelas links_linke auth_user. Por quê? Suponho que você LIMIT 25, então, não encontrou nada quando JOINporque não há linhas links_linkrelacionadas a answer_to_id = 8936203.

    Em seguida, criando o novo índice em answer_to_id, id DESC.

    Nota : WITH( consulta acima ) chamado Common Table Expressions

    • 2

relate perguntas