Início / dba / Perguntas / 228213
Accepted
Hassan Baig
Asked: 2019-01-28 23:34:31 +0800 CST

Acelerando a consulta SELECT lenta (Postgresql 9.6)

  • 772

Em um aplicativo chamado Links , os usuários postam fotos de conteúdo interessante que descobriram recentemente (e outros podem votar ou comentar nelas).

Essas fotos postadas são salvas em uma links_phototabela no meu banco de dados postgresql 9.6.5.

O perfil de cada usuário mostra suas fotos postadas - paginadas por 10objetos cada - ordenadas por upload time.

Uma SELECTconsulta na links_phototabela 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: 542,755 ms declaração:

SELECT "links_photo"."id",
       "links_photo"."owner_id",
       "links_photo"."image_file",
       "links_photo"."upload_time",
       "links_photo"."comment_count",
       "links_photo"."vote_score",
       "links_photo"."caption",
       "links_photo"."category",
       "auth_user"."id",
       "auth_user"."username",
       "auth_user"."date_joined",
       "links_userprofile"."id",
       "links_userprofile"."user_id",
       "links_userprofile"."score",
       "links_userprofile"."avatar"
FROM   "links_photo"
       INNER JOIN "auth_user"
               ON ( "links_photo"."owner_id" = "auth_user"."id" )
       LEFT OUTER JOIN "links_userprofile"
                    ON ( "auth_user"."id" = "links_userprofile"."user_id" )
WHERE  ( "links_photo"."owner_id" = 78689
         AND "links_photo"."category" = '1' )
ORDER  BY "links_photo"."upload_time" DESC
LIMIT  10 offset 10

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

De acordo com isso, o Index Scan Backward acaba filtrando 1.196.188 linhas e é a fonte da lentidão.

O que acho que devo tentar:

Sendo uma espécie de DBA acidental, estou procurando uma orientação rápida de especialistas sobre o assunto. Eu teria pensado que ter um índice upload_timeseria suficiente, mas não. Então, talvez um composto em (owner_id, upload_time DESC)?

Adição:

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

                                           Table "public.links_photo"
          Column          |           Type           |                        Modifiers                         
--------------------------+--------------------------+----------------------------------------------------------
 id                       | integer                  | not null default nextval('links_photo_id_seq'::regclass)
 owner_id                 | integer                  | not null
 image_file               | character varying(100)   | not null
 upload_time              | timestamp with time zone | not null
 comment_count            | integer                  | not null
 is_public                | boolean                  | not null
 vote_score               | integer                  | not null
 visible_score            | integer                  | not null
 invisible_score          | double precision         | not null
 caption                  | character varying(100)   | 
 avg_hash                 | character varying(16)    | not null
 latest_comment_id        | integer                  | 
 second_latest_comment_id | integer                  | 
 category                 | character varying(11)    | not null
 device                   | character varying(10)    | not null
Indexes:
    "links_photo_pkey" PRIMARY KEY, btree (id)
    "links_photo_latest_comment_id" btree (latest_comment_id)
    "links_photo_owner_id" btree (owner_id)
    "links_photo_second_latest_comment_id" btree (second_latest_comment_id)
    "links_photo_upload_time" btree (upload_time)
Foreign-key constraints:
    "latest_comment_id_refs_id_f2566197" FOREIGN KEY (latest_comment_id) REFERENCES links_photocomment(id) DEFERRABLE INITIALLY DEFERRED
    "links_photo_owner_id_fkey" FOREIGN KEY (owner_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
    "second_latest_comment_id_refs_id_f2566197" FOREIGN KEY (second_latest_comment_id) REFERENCES links_photocomment(id) DEFERRABLE INITIALLY DEFERRED
Referenced by:
    TABLE "links_photostream" CONSTRAINT "cover_id_refs_id_d62b783f" FOREIGN KEY (cover_id) REFERENCES links_photo(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_photocomment" CONSTRAINT "links_photocomment_which_photo_id_fkey" FOREIGN KEY (which_photo_id) REFERENCES links_photo(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_photoobjectsubscription" CONSTRAINT "links_photoobjectsubscription_which_photo_id_fkey" FOREIGN KEY (which_photo_id) REFERENCES links_photo(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_photovote" CONSTRAINT "links_photovote_photo_id_fkey" FOREIGN KEY (photo_id) REFERENCES links_photo(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_report" CONSTRAINT "links_report_which_photo_id_fkey" FOREIGN KEY (which_photo_id) REFERENCES links_photo(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_photo_which_stream" CONSTRAINT "photo_id_refs_id_916b4355" FOREIGN KEY (photo_id) REFERENCES links_photo(id) DEFERRABLE INITIALLY DEFERRED
postgresql performance

1 respostas

  1. Best Answer

    Tenho o mesmo caso que você. O problema é que Rows Removed by Filtera consulta demora para remover linhas pelo filtro. Dependendo de quantas linhas serão removidas, o tempo será equivalente.

    Ideia 1

    Sua abordagem é boa. O pequeno é que a consulta precisa remover linhas no arquivo category. No entanto, não é um grande problema se sua cardinalidade nãocategory for muito.

    Ideia 2

    Sugiro criar um índice em owner_id, category, upload_time DESC. Vai evitar the filter. Mas, você deve considerar o tamanho do índice no caso de uma tabela grande.

    Digamos que temos a tabela ( db<>fiddle )

    CREATE TABLE links_photo 
(
  id serial, 
  owner_id int, 
  category int, 
  upload_time timestamp without time zone
);

    E a consulta

    SELECT id
FROM links_photo 
WHERE owner_id = 100 and category = 2 
ORDER BY upload_time desc 
LIMIT 10 
OFFSET 0;

    A explicação do usoupload_time DESC

    A explicação do usoowner_id, upload_time DESC

    A explicação do usoowner_id, category, upload_time DESC

    • 2

relate perguntas