Início / dba / Perguntas / 228485
Accepted
Hassan Baig
Asked: 2019-01-31 12:26:32 +0800 CST

Otimizando a consulta SELECT consistentemente aparecendo no log lento

  • 772

Em um aplicativo chamado 'Links', os usuários postam links e fotos de conteúdo interessante que descobriram recentemente (e outros comentam sobre as referidas postagens).

Esses comentários postados sob as fotos são salvos em uma links_photocommenttabela no meu banco de dados postgresql 9.6.5.

Uma SELECTconsulta na links_photocommenttabela está aparecendo consistentemente no slow_log. Está demorando mais de 500 ms e é ~ 10 vezes 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:

LOG: duração: 5071,112 ms declaração:

SELECT "links_photocomment"."abuse",
       "links_photocomment"."text",
       "links_photocomment"."id",
       "links_photocomment"."submitted_by_id",
       "links_photocomment"."submitted_on",
       "auth_user"."username",
       "links_userprofile"."score"
FROM   "links_photocomment"
       INNER JOIN "auth_user"
               ON ( "links_photocomment"."submitted_by_id" = "auth_user"."id" )
       LEFT OUTER JOIN "links_userprofile"
                    ON ( "auth_user"."id" = "links_userprofile"."user_id" )
WHERE  "links_photocomment"."which_photo_id" = 3115087
ORDER  BY "links_photocomment"."id" DESC
LIMIT  25;

Veja os explain analyzeresultados: https://explain.depesz.com/s/UuCk

A consulta acaba filtrando 19.100.179 linhas de acordo com isso!

O que eu tentei:

Minha intuição é que o Postgres baseia esse plano de consulta em estatísticas enganosas. Assim, eu corri VACUUM ANALYZEna referida mesa. No entanto, isso não mudou nada.

Sendo uma espécie de DBA acidental, estou procurando uma orientação rápida de especialistas sobre o assunto. Desde já agradeço e peço desculpas pela pergunta noob (se for).

Termo aditivo:

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

                                      Table "public.links_photocomment"
     Column      |           Type           |                            Modifiers                            
-----------------+--------------------------+-----------------------------------------------------------------
 id              | integer                  | not null default nextval('links_photocomment_id_seq'::regclass)
 which_photo_id  | integer                  | not null
 text            | text                     | not null
 device          | character varying(10)    | not null
 submitted_by_id | integer                  | not null
 submitted_on    | timestamp with time zone | not null
 image_comment   | character varying(100)   | not null
 has_image       | boolean                  | not null
 abuse           | boolean                  | default false
Indexes:
    "links_photocomment_pkey" PRIMARY KEY, btree (id)
    "links_photocomment_submitted_by_id" btree (submitted_by_id)
    "links_photocomment_which_photo_id" btree (which_photo_id)
Foreign-key constraints:
    "links_photocomment_submitted_by_id_fkey" FOREIGN KEY (submitted_by_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
    "links_photocomment_which_photo_id_fkey" FOREIGN KEY (which_photo_id) REFERENCES links_photo(id) DEFERRABLE INITIALLY DEFERRED
Referenced by:
    TABLE "links_photo" CONSTRAINT "latest_comment_id_refs_id_f2566197" FOREIGN KEY (latest_comment_id) REFERENCES links_photocomment(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_report" CONSTRAINT "links_report_which_photocomment_id_fkey" FOREIGN KEY (which_photocomment_id) REFERENCES links_photocomment(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_photo" CONSTRAINT "second_latest_comment_id_refs_id_f2566197" FOREIGN KEY (second_latest_comment_id) REFERENCES links_photocomment(id) DEFERRABLE INITIALLY DEFERRED
postgresql performance

1 respostas

  1. Best Answer

    O plano não está usando o índice, (which_photo_id)mas o índice PK (id), portanto, ele deve ler uma parte muito grande do índice (e tudo se houver menos de 25 linhas que correspondam ao filtro). Isso leva cerca de 4,4 segundos na execução específica (e encontra as 25 linhas depois de ler e rejeitar 19 milhões de linhas):

    -> Index Scan Backward using links_photocomment_pkey on links_photocomment  
   (cost=0.57..2,819,246.22 rows=7,195 width=41)
   (actual time=555.830..4,929.154 rows=25 loops=1)

   Filter: (which_photo_id = 3115087)
   Rows Removed by Filter: 19100179

    Eu tentaria estes:

    • substituindo o index on (which_photo_id)por um index on (which_photo_id, id).

    • reescreva a INNERjunção para uma LEFTjunção (há uma FOREIGN KEYrestrição que garante que as duas consultas produzirão os mesmos resultados).

    • reescreva com uma subconsulta (tabela derivada ou CTE) movendo o WHEREfiltro para dentro) para primeiro obter os 25 ids (esperamos com uma varredura apenas de índice) e depois juntar as outras 2 tabelas.

    Consulta (com tabela derivada):

    SELECT "links_photocomment"."abuse",
       "links_photocomment"."text",
       "links_photocomment"."id",
       "links_photocomment"."submitted_by_id",
       "links_photocomment"."submitted_on",
       "auth_user"."username",
       "links_userprofile"."score"
FROM   
       (
         SELECT id
         FROM links_photocomment
         WHERE which_photo_id = 3115087
         ORDER BY id DESC
         LIMIT 25
       ) AS lim
       INNER JOIN "links_photocomment"
           ON ( "links_photocomment"."id" = lim.id )
       LEFT OUTER JOIN "auth_user"
           ON ( "links_photocomment"."submitted_by_id" = "auth_user"."id" )
       LEFT OUTER JOIN "links_userprofile"
           ON ( "auth_user"."id" = "links_userprofile"."user_id" )
ORDER  BY lim.id DESC
LIMIT  25;

    Consulta (com CTE):

    WITH lim AS
       (
         SELECT id
         FROM links_photocomment
         WHERE which_photo_id = 3115087
         ORDER BY id DESC
         LIMIT 25
       )
SELECT "links_photocomment"."abuse",
       "links_photocomment"."text",
       "links_photocomment"."id",
       "links_photocomment"."submitted_by_id",
       "links_photocomment"."submitted_on",
       "auth_user"."username",
       "links_userprofile"."score"
FROM   
       lim
       INNER JOIN "links_photocomment"
           ON ( "links_photocomment"."id" = lim.id )
       LEFT OUTER JOIN "auth_user"
           ON ( "links_photocomment"."submitted_by_id" = "auth_user"."id" )
       LEFT OUTER JOIN "links_userprofile"
           ON ( "auth_user"."id" = "links_userprofile"."user_id" )
ORDER  BY lim.id DESC
LIMIT  25;
    • 1

relate perguntas