Existem alguns outros tópicos sobre o ajuste de instâncias grandes. Os princípios de executar um PostgreSQL grande são os mesmos do MySQL, mas o diabo está nos detalhes.
Já estamos tentando pesquisar sobre o assunto na internet e aqui no Meta. Há algum tópico que você possa nos indicar que ache que possa ser útil?
Seu arquivo app.yml atual tem essas linhas comentadas:
Descomente essas linhas e aumente os valores de acordo. Depois disso, você precisará reconstruir.
3 curtidas
Fiz isso e reconstruí o container. Estou tentando executar o reindex novamente, mas parece que ele está travando de novo. Aqui está o que tenho para esses dois:
Há alguma maneira de saber se o work_mem pode precisar ser maior? Vejo que o disco está sendo acessado com bastante intensidade, então estou pensando que pode estar fazendo uma ordenação no sistema de arquivos ou algo similar, mas não tenho certeza de como confirmar se esse é o caso ou se algo mais está acontecendo.
1 curtida
Use as consultas que você listou no OP. Execute-as em um shell psql precedidas por EXPLAIN ANALYZE e cole a saída aqui.
2 curtidas
Looks like there’s some caching at work so subsequent loads tend to be faster, but > 5 seconds is still brutal when the cache expires.
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=26396.41..26397.10 rows=6 width=1278) (actual time=90.879..114.499 rows=6 loops=1)
-> Gather Merge (cost=26396.41..26448.85 rows=456 width=1278) (actual time=90.877..114.496 rows=6 loops=1)
Workers Planned: 1
Workers Launched: 1
-> Sort (cost=25396.40..25397.54 rows=456 width=1278) (actual time=87.195..87.197 rows=5 loops=2)
Sort Key: posts.like_count DESC, posts.created_at DESC
Sort Method: top-N heapsort Memory: 38kB
Worker 0: Sort Method: top-N heapsort Memory: 39kB
-> Nested Loop (cost=103.30..25388.23 rows=456 width=1278) (actual time=3.200..83.497 rows=3610 loops=2)
-> Parallel Bitmap Heap Scan on posts (cost=99.06..9509.95 rows=1356 width=783) (actual time=3.075..56.880 rows=5932 loops=2)
Recheck Cond: (user_id = 27510)
Filter: ((deleted_at IS NULL) AND (post_number > 1) AND (post_type = ANY ('{1,2,3,4}'::integer[])))
Rows Removed by Filter: 1071
Heap Blocks: exact=6272
-> Bitmap Index Scan on index_posts_on_user_id_and_created_at (cost=0.00..98.48 rows=2389 width=0) (actual time=3.916..3.917 rows=20157 loops=1)
Index Cond: (user_id = 27510)
-> Index Scan using topics_pkey on topics (cost=4.24..11.71 rows=1 width=495) (actual time=0.004..0.004 rows=1 loops=11864)
Index Cond: (id = posts.topic_id)
Filter: ((deleted_at IS NULL) AND (deleted_at IS NULL) AND visible AND ((archetype)::text <> 'private_message'::text) AND ((category_id IS NULL) OR (hashed SubPlan 1)))
Rows Removed by Filter: 0
SubPlan 1
-> Seq Scan on categories (cost=0.00..3.74 rows=28 width=4) (actual time=0.013..0.029 rows=35 loops=2)
Filter: ((NOT read_restricted) OR (id = ANY ('{3,4,5,17,19,25,26,27,28}'::integer[])))
Planning Time: 2.535 ms
Execution Time: 114.657 ms
(25 rows)
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=25004.87..25004.87 rows=1 width=1464) (actual time=98.136..121.987 rows=6 loops=1)
-> Sort (cost=25004.87..25004.87 rows=1 width=1464) (actual time=98.134..121.984 rows=6 loops=1)
Sort Key: topic_links.clicks DESC, topic_links.created_at DESC
Sort Method: top-N heapsort Memory: 55kB
-> Nested Loop (cost=1103.75..25004.86 rows=1 width=1464) (actual time=6.763..118.114 rows=3443 loops=1)
-> Gather (cost=1099.51..24993.34 rows=1 width=969) (actual time=6.464..88.294 rows=9130 loops=1)
Workers Planned: 1
Workers Launched: 1
-> Nested Loop (cost=99.51..23993.24 rows=1 width=969) (actual time=3.151..73.939 rows=4565 loops=2)
-> Parallel Bitmap Heap Scan on posts (cost=99.08..9506.46 rows=1405 width=783) (actual time=3.032..43.325 rows=7003 loops=2)
Recheck Cond: (user_id = 27510)
Filter: ((deleted_at IS NULL) AND (post_type = ANY ('{1,2,3,4}'::integer[])))
Heap Blocks: exact=5953
-> Bitmap Index Scan on index_posts_on_user_id_and_created_at (cost=0.00..98.48 rows=2389 width=0) (actual time=3.790..3.791 rows=20157 loops=1)
Index Cond: (user_id = 27510)
-> Index Scan using index_topic_links_on_post_id on topic_links (cost=0.43..10.30 rows=1 width=186) (actual time=0.003..0.004 rows=1 loops=14006)
Index Cond: (post_id = posts.id)
Filter: ((NOT internal) AND (NOT reflection) AND (NOT quote) AND (user_id = 27510))
Rows Removed by Filter: 0
-> Index Scan using topics_pkey on topics (cost=4.24..11.52 rows=1 width=495) (actual time=0.003..0.003 rows=0 loops=9130)
Index Cond: (id = topic_links.topic_id)
Filter: ((deleted_at IS NULL) AND (deleted_at IS NULL) AND visible AND ((archetype)::text <> 'private_message'::text) AND ((category_id IS NULL) OR (hashed SubPlan 1)))
Rows Removed by Filter: 1
SubPlan 1
-> Seq Scan on categories (cost=0.00..3.74 rows=28 width=4) (actual time=0.009..0.022 rows=35 loops=1)
Filter: ((NOT read_restricted) OR (id = ANY ('{3,4,5,17,19,25,26,27,28}'::integer[])))
Planning Time: 1.102 ms
Execution Time: 122.098 ms
(28 rows)
SELECT "posts"."id" AS t0_r0, "posts"."user_id" AS t0_r1, "posts"."topic_id" AS t0_r2, "posts"."post_number" AS t0_r3, "posts"."raw" AS t0_r4, "posts"."cooked" AS t0_r5, "posts"."created_at" AS t0_r6, "posts"."updated_at" AS t0_r7, "posts"."reply_to_post_number" AS t0_r8, "posts"."reply_count" AS t0_r9, "posts"."quote_count" AS t0_r10, "posts"."deleted_at" AS t0_r11, "posts"."off_topic_count" AS t0_r12, "posts"."like_count" AS t0_r13, "posts"."incoming_link_count" AS t0_r14, "posts"."bookmark_count" AS t0_r15, "posts"."score" AS t0_r16, "posts"."reads" AS t0_r17, "posts"."post_type" AS t0_r18, "posts"."sort_order" AS t0_r19, "posts"."last_editor_id" AS t0_r20, "posts"."hidden" AS t0_r21, "posts"."hidden_reason_id" AS t0_r22, "posts"."notify_moderators_count" AS t0_r23, "posts"."spam_count" AS t0_r24, "posts"."illegal_count" AS t0_r25, "posts"."inappropriate_count" AS t0_r26, "posts"."last_version_at" AS t0_r27, "posts"."user_deleted" AS t0_r28, "posts"."reply_to_user_id" AS t0_r29, "posts"."percent_rank" AS t0_r30, "posts"."notify_user_count" AS t0_r31, "posts"."like_score" AS t0_r32, "posts"."deleted_by_id" AS t0_r33, "posts"."edit_reason" AS t0_r34, "posts"."word_count" AS t0_r35, "posts"."version" AS t0_r36, "posts"."cook_method" AS t0_r37, "posts"."wiki" AS t0_r38, "posts"."baked_at" AS t0_r39, "posts"."baked_version" AS t0_r40, "posts"."hidden_at" AS t0_r41, "posts"."self_edits" AS t0_r42, "posts"."reply_quoted" AS t0_r43, "posts"."via_email" AS t0_r44, "posts"."raw_email" AS t0_r45, "posts"."public_version" AS t0_r46, "posts"."action_code" AS t0_r47, "posts"."locked_by_id" AS t0_r48, "posts"."image_upload_id" AS t0_r49, "topics"."id" AS t1_r0, "topics"."title" AS t1_r1, "topics"."last_posted_at" AS t1_r2, "topics"."created_at" AS t1_r3, "topics"."updated_at" AS t1_r4, "topics"."views" AS t1_r5, "topics"."posts_count" AS t1_r6, "topics"."user_id" AS t1_r7, "topics"."last_post_user_id" AS t1_r8, "topics"."reply_count" AS t1_r9, "topics"."featured_user1_id" AS t1_r10, "topics"."featured_user2_id" AS t1_r11, "topics"."featured_user3_id" AS t1_r12, "topics"."deleted_at" AS t1_r13, "topics"."highest_post_number" AS t1_r14, "topics"."like_count" AS t1_r15, "topics"."incoming_link_count" AS t1_r16, "topics"."category_id" AS t1_r17, "topics"."visible" AS t1_r18, "topics"."moderator_posts_count" AS t1_r19, "topics"."closed" AS t1_r20, "topics"."archived" AS t1_r21, "topics"."bumped_at" AS t1_r22, "topics"."has_summary" AS t1_r23, "topics"."archetype" AS t1_r24, "topics"."featured_user4_id" AS t1_r25, "topics"."notify_moderators_count" AS t1_r26, "topics"."spam_count" AS t1_r27, "topics"."pinned_at" AS t1_r28, "topics"."score" AS t1_r29, "topics"."percent_rank" AS t1_r30, "topics"."subtype" AS t1_r31, "topics"."slug" AS t1_r32, "topics"."deleted_by_id" AS t1_r33, "topics"."participant_count" AS t1_r34, "topics"."word_count" AS t1_r35, "topics"."excerpt" AS t1_r36, "topics"."pinned_globally" AS t1_r37, "topics"."pinned_until" AS t1_r38, "topics"."fancy_title" AS t1_r39, "topics"."highest_staff_post_number" AS t1_r40, "topics"."featured_link" AS t1_r41, "topics"."reviewable_score" AS t1_r42, "topics"."image_upload_id" AS t1_r43, "topics"."slow_mode_seconds" AS t1_r44 FROM "posts" INNER JOIN "topics" ON "topics"."id" = "posts"."topic_id" AND ("topics"."deleted_at" IS NULL) WHERE ("posts"."deleted_at" IS NULL) AND (posts.post_type IN (1,2,3,4)) AND ("topics"."deleted_at" IS NULL) AND (topics.archetype <> 'private_message') AND "topics"."visible" = TRUE AND (topics.category_id IS NULL OR topics.category_id IN (SELECT id FROM categories WHERE NOT read_restricted OR id IN (3,4,5,17,19,25,26,27,28))) AND "posts"."user_id" = 27510 AND (post_number > 1) ORDER BY posts.like_count DESC, posts.created_at DESC LIMIT 6;
SELECT "topic_links"."id" AS t0_r0, "topic_links"."topic_id" AS t0_r1, "topic_links"."post_id" AS t0_r2, "topic_links"."user_id" AS t0_r3, "topic_links"."url" AS t0_r4, "topic_links"."domain" AS t0_r5, "topic_links"."internal" AS t0_r6, "topic_links"."link_topic_id" AS t0_r7, "topic_links"."created_at" AS t0_r8, "topic_links"."updated_at" AS t0_r9, "topic_links"."reflection" AS t0_r10, "topic_links"."clicks" AS t0_r11, "topic_links"."link_post_id" AS t0_r12, "topic_links"."title" AS t0_r13, "topic_links"."crawled_at" AS t0_r14, "topic_links"."quote" AS t0_r15, "topic_links"."extension" AS t0_r16, "topics"."id" AS t1_r0, "topics"."title" AS t1_r1, "topics"."last_posted_at" AS t1_r2, "topics"."created_at" AS t1_r3, "topics"."updated_at" AS t1_r4, "topics"."views" AS t1_r5, "topics"."posts_count" AS t1_r6, "topics"."user_id" AS t1_r7, "topics"."last_post_user_id" AS t1_r8, "topics"."reply_count" AS t1_r9, "topics"."featured_user1_id" AS t1_r10, "topics"."featured_user2_id" AS t1_r11, "topics"."featured_user3_id" AS t1_r12, "topics"."deleted_at" AS t1_r13, "topics"."highest_post_number" AS t1_r14, "topics"."like_count" AS t1_r15, "topics"."incoming_link_count" AS t1_r16, "topics"."category_id" AS t1_r17, "topics"."visible" AS t1_r18, "topics"."moderator_posts_count" AS t1_r19, "topics"."closed" AS t1_r20, "topics"."archived" AS t1_r21, "topics"."bumped_at" AS t1_r22, "topics"."has_summary" AS t1_r23, "topics"."archetype" AS t1_r24, "topics"."featured_user4_id" AS t1_r25, "topics"."notify_moderators_count" AS t1_r26, "topics"."spam_count" AS t1_r27, "topics"."pinned_at" AS t1_r28, "topics"."score" AS t1_r29, "topics"."percent_rank" AS t1_r30, "topics"."subtype" AS t1_r31, "topics"."slug" AS t1_r32, "topics"."deleted_by_id" AS t1_r33, "topics"."participant_count" AS t1_r34, "topics"."word_count" AS t1_r35, "topics"."excerpt" AS t1_r36, "topics"."pinned_globally" AS t1_r37, "topics"."pinned_until" AS t1_r38, "topics"."fancy_title" AS t1_r39, "topics"."highest_staff_post_number" AS t1_r40, "topics"."featured_link" AS t1_r41, "topics"."reviewable_score" AS t1_r42, "topics"."image_upload_id" AS t1_r43, "topics"."slow_mode_seconds" AS t1_r44, "posts"."id" AS t2_r0, "posts"."user_id" AS t2_r1, "posts"."topic_id" AS t2_r2, "posts"."post_number" AS t2_r3, "posts"."raw" AS t2_r4, "posts"."cooked" AS t2_r5, "posts"."created_at" AS t2_r6, "posts"."updated_at" AS t2_r7, "posts"."reply_to_post_number" AS t2_r8, "posts"."reply_count" AS t2_r9, "posts"."quote_count" AS t2_r10, "posts"."deleted_at" AS t2_r11, "posts"."off_topic_count" AS t2_r12, "posts"."like_count" AS t2_r13, "posts"."incoming_link_count" AS t2_r14, "posts"."bookmark_count" AS t2_r15, "posts"."score" AS t2_r16, "posts"."reads" AS t2_r17, "posts"."post_type" AS t2_r18, "posts"."sort_order" AS t2_r19, "posts"."last_editor_id" AS t2_r20, "posts"."hidden" AS t2_r21, "posts"."hidden_reason_id" AS t2_r22, "posts"."notify_moderators_count" AS t2_r23, "posts"."spam_count" AS t2_r24, "posts"."illegal_count" AS t2_r25, "posts"."inappropriate_count" AS t2_r26, "posts"."last_version_at" AS t2_r27, "posts"."user_deleted" AS t2_r28, "posts"."reply_to_user_id" AS t2_r29, "posts"."percent_rank" AS t2_r30, "posts"."notify_user_count" AS t2_r31, "posts"."like_score" AS t2_r32, "posts"."deleted_by_id" AS t2_r33, "posts"."edit_reason" AS t2_r34, "posts"."word_count" AS t2_r35, "posts"."version" AS t2_r36, "posts"."cook_method" AS t2_r37, "posts"."wiki" AS t2_r38, "posts"."baked_at" AS t2_r39, "posts"."baked_version" AS t2_r40, "posts"."hidden_at" AS t2_r41, "posts"."self_edits" AS t2_r42, "posts"."reply_quoted" AS t2_r43, "posts"."via_email" AS t2_r44, "posts"."raw_email" AS t2_r45, "posts"."public_version" AS t2_r46, "posts"."action_code" AS t2_r47, "posts"."locked_by_id" AS t2_r48, "posts"."image_upload_id" AS t2_r49 FROM "topic_links" INNER JOIN "topics" ON "topics"."id" = "topic_links"."topic_id" AND ("topics"."deleted_at" IS NULL) INNER JOIN "posts" ON "posts"."id" = "topic_links"."post_id" AND ("posts"."deleted_at" IS NULL) WHERE "posts"."user_id" = 27510 AND (posts.post_type IN (1,2,3,4)) AND ("topics"."deleted_at" IS NULL) AND (topics.archetype <> 'private_message') AND "topics"."visible" = TRUE AND (topics.category_id IS NULL OR topics.category_id IN (SELECT id FROM categories WHERE NOT read_restricted OR id IN (3,4,5,17,19,25,26,27,28))) AND "topic_links"."user_id" = 27510 AND "topic_links"."internal" = FALSE AND "topic_links"."reflection" = FALSE AND "topic_links"."quote" = FALSE ORDER BY clicks DESC, topic_links.created_at DESC LIMIT 6;
Ambos estão usando memória para arquivos temporários, e os tempos de execução são bons o suficiente. Talvez esteja estabilizando?
Acho que essa análise pode estar puxando dados em cache. A página de resumo do site mostra tempos de execução muito maiores:
Vou tentar executar a análise novamente mais tarde, assim que o cache talvez tenha expirado.
O reindex só precisa ser executado uma vez — depois que os índices estão no formato de disco v13, eles permanecem nesse formato.
Sim, se seu VACUUM não incluiu ANALYZE, você está perdendo o uso dos índices.
2 curtidas
Certo, então essa consulta está buscando as postagens mais curtidas para um determinado usuário.
A estimativa aqui estava muito errada — o planejador estimou 2.389 linhas e obteve 20.157 linhas.
Um nível acima, outra discrepância de 5x.
São esse tipo de estimativas erradas que causam desempenho terrível nas consultas.
2 curtidas
Bem, pelo que sei, eles nunca deveriam ter estado em nenhum formato além do v13. Importamos os dados de outro software de fórum para uma instalação nova do Discourse que já estava rodando a versão 13. No momento, estou apenas tentando garantir que todos os índices estejam sendo reconstruídos corretamente após a importação.
Faz sentido. Estou tentando executar a reindexação novamente após aumentar o maintenance_work_mem. Parece que o postmaster está usando mais memória agora do que antes, mas ainda não sei se vai concluir em um tempo razoável.
2 curtidas
Parece que há um trabalho de badge em execução que está impedindo a reindexação.
Isso está em execução há mais de duas horas? Isso parece estranho.
Pode ser algo nesta tarefa semanal que também ainda está em execução:
Você pode executar
sv stop unicorn
para parar o servidor web durante a reindexação do banco de dados.
1 curtida
Ok, então o reindex finalmente foi concluído em um tempo razoável. Houve alguns índices que o sistema disse não conseguir reconstruir e pulou, mas sem detalhes adicionais sobre o motivo. Aqui estão as estatísticas das tabelas agora:
O problema de carregamento do perfil parece ter piorado:
Aqui estão os EXPLAINs dessas consultas:
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=625609.73..625610.43 rows=6 width=1278) (actual time=2058.211..2154.991 rows=6 loops=1)
-> Gather Merge (cost=625609.73..630928.70 rows=45588 width=1278) (actual time=1654.881..1751.658 rows=6 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=624609.70..624666.69 rows=22794 width=1278) (actual time=1619.464..1619.469 rows=5 loops=3)
Sort Key: posts.like_count DESC, posts.created_at DESC
Sort Method: top-N heapsort Memory: 37kB
Worker 0: Sort Method: top-N heapsort Memory: 37kB
Worker 1: Sort Method: top-N heapsort Memory: 38kB
-> Parallel Hash Join (cost=63000.65..624201.13 rows=22794 width=1278) (actual time=1383.820..1600.913 rows=20578 loops=3)
Hash Cond: (posts.topic_id = topics.id)
-> Parallel Bitmap Heap Scan on posts (cost=1875.97..562899.73 rows=67322 width=783) (actual time=63.310..264.627 rows=20579 loops=3)
Recheck Cond: ((user_id = 7237) AND (deleted_at IS NULL))
Filter: ((post_number > 1) AND (post_type = ANY ('{1,2,3,4}'::integer[])))
Rows Removed by Filter: 39566
Heap Blocks: exact=50390
-> Bitmap Index Scan on idx_posts_user_id_deleted_at (cost=0.00..1835.58 rows=167352 width=0) (actual time=36.507..36.508 rows=180435 loops=1)
Index Cond: (user_id = 7237)
-> Parallel Hash (cost=59504.20..59504.20 rows=129638 width=495) (actual time=1319.362..1319.364 rows=131785 loops=3)
Buckets: 524288 Batches: 1 Memory Usage: 190560kB
-> Parallel Seq Scan on topics (cost=3.81..59504.20 rows=129638 width=495) (actual time=316.007..1204.917 rows=131785 loops=3)
Filter: ((deleted_at IS NULL) AND (deleted_at IS NULL) AND visible AND ((archetype)::text <> 'private_message'::text) AND ((category_id IS NULL) OR (hashed SubPlan 1)))
Rows Removed by Filter: 174529
SubPlan 1
-> Seq Scan on categories (cost=0.00..3.74 rows=28 width=4) (actual time=17.841..17.887 rows=35 loops=3)
Filter: ((NOT read_restricted) OR (id = ANY ('{3,4,5,17,19,25,26,27,28}'::integer[])))
Planning Time: 0.751 ms
JIT:
Functions: 91
Options: Inlining true, Optimization true, Expressions true, Deforming true
Timing: Generation 11.057 ms, Inlining 134.342 ms, Optimization 762.277 ms, Emission 451.708 ms, Total 1359.384 ms
Execution Time: 2204.845 ms
(32 rows)
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=726143.96..726144.66 rows=6 width=1464) (actual time=5250.325..5305.144 rows=6 loops=1)
-> Gather Merge (cost=726143.96..726351.17 rows=1776 width=1464) (actual time=4800.064..4854.881 rows=6 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=725143.93..725146.15 rows=888 width=1464) (actual time=4762.610..4762.615 rows=5 loops=3)
Sort Key: topic_links.clicks DESC, topic_links.created_at DESC
Sort Method: top-N heapsort Memory: 36kB
Worker 0: Sort Method: top-N heapsort Memory: 37kB
Worker 1: Sort Method: top-N heapsort Memory: 32kB
-> Nested Loop (cost=574851.64..725128.02 rows=888 width=1464) (actual time=649.373..4710.853 rows=39385 loops=3)
-> Parallel Hash Join (cost=574847.40..695445.64 rows=2624 width=969) (actual time=630.974..3847.821 rows=336541 loops=3)
Hash Cond: (topic_links.post_id = posts.id)
-> Parallel Bitmap Heap Scan on topic_links (cost=11248.93..130753.84 rows=416505 width=186) (actual time=58.964..3095.540 rows=336541 loops=3)
Recheck Cond: (user_id = 7237)
Filter: ((NOT internal) AND (NOT reflection) AND (NOT quote))
Rows Removed by Filter: 8
Heap Blocks: exact=23544
-> Bitmap Index Scan on index_topic_links_on_user_id (cost=0.00..10999.02 rows=1000879 width=0) (actual time=45.320..45.322 rows=1009648 loops=1)
Index Cond: (user_id = 7237)
-> Parallel Hash (cost=562726.85..562726.85 rows=69730 width=783) (actual time=571.264..571.266 rows=60145 loops=3)
Buckets: 262144 Batches: 1 Memory Usage: 71264kB
-> Parallel Bitmap Heap Scan on posts (cost=1877.42..562726.85 rows=69730 width=783) (actual time=377.440..481.561 rows=60145 loops=3)
Recheck Cond: ((user_id = 7237) AND (deleted_at IS NULL))
Filter: (post_type = ANY ('{1,2,3,4}'::integer[]))
Heap Blocks: exact=141396
-> Bitmap Index Scan on idx_posts_user_id_deleted_at (cost=0.00..1835.58 rows=167352 width=0) (actual time=29.225..29.226 rows=180435 loops=1)
Index Cond: (user_id = 7237)
-> Index Scan using topics_pkey on topics (cost=4.24..11.31 rows=1 width=495) (actual time=0.002..0.002 rows=0 loops=1009624)
Index Cond: (id = topic_links.topic_id)
Filter: ((deleted_at IS NULL) AND (deleted_at IS NULL) AND visible AND ((archetype)::text <> 'private_message'::text) AND ((category_id IS NULL) OR (hashed SubPlan 1)))
Rows Removed by Filter: 1
SubPlan 1
-> Seq Scan on categories (cost=0.00..3.74 rows=28 width=4) (actual time=17.793..17.835 rows=35 loops=3)
Filter: ((NOT read_restricted) OR (id = ANY ('{3,4,5,17,19,25,26,27,28}'::integer[])))
Planning Time: 4.526 ms
JIT:
Functions: 118
Options: Inlining true, Optimization true, Expressions true, Deforming true
Timing: Generation 14.724 ms, Inlining 94.921 ms, Optimization 934.359 ms, Emission 525.383 ms, Total 1569.387 ms
Execution Time: 5309.478 ms
(40 rows)
Adicionei os índices que criei anteriormente para ajudar nisso (Slow Page Loads on User Profiles - #2 by Ghan), e isso reduziu bastante os tempos de carregamento.
Plano de consulta após adicionar os índices:
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=1004.13..25136.02 rows=6 width=1473) (actual time=84.592..96.171 rows=6 loops=1)
-> Gather Merge (cost=1004.13..9649737.42 rows=2399 width=1473) (actual time=84.591..96.168 rows=6 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Nested Loop (cost=4.11..9648460.49 rows=1000 width=1473) (actual time=60.452..60.471 rows=4 loops=3)
-> Nested Loop (cost=0.87..9619828.27 rows=2943 width=977) (actual time=0.115..47.798 rows=6058 loops=3)
-> Parallel Index Scan using index_topic_links_on_clicks_and_created_desc on topic_links (cost=0.43..6728256.06 rows=403188 width=186) (actual time=0.051..30.428 rows=6058 loops=3)
Filter: ((NOT internal) AND (NOT reflection) AND (NOT quote) AND (user_id = 7237))
Rows Removed by Filter: 54345
-> Index Scan using posts_pkey on posts (cost=0.44..7.17 rows=1 width=791) (actual time=0.002..0.002 rows=1 loops=18173)
Index Cond: (id = topic_links.post_id)
Filter: ((deleted_at IS NULL) AND (user_id = 7237) AND (post_type = ANY ('{1,2,3,4}'::integer[])))
-> Index Scan using topics_pkey on topics (cost=3.24..9.73 rows=1 width=496) (actual time=0.002..0.002 rows=0 loops=18173)
Index Cond: (id = topic_links.topic_id)
Filter: ((deleted_at IS NULL) AND (deleted_at IS NULL) AND visible AND ((archetype)::text <> 'private_message'::text) AND ((category_id IS NULL) OR (hashed SubPlan 1)))
Rows Removed by Filter: 1
SubPlan 1
-> Seq Scan on categories (cost=0.00..2.74 rows=28 width=4) (actual time=0.031..0.049 rows=35 loops=3)
Filter: ((NOT read_restricted) OR (id = ANY ('{3,4,5,17,19,25,26,27,28}'::integer[])))
Planning Time: 1.205 ms
Execution Time: 96.258 ms
(21 rows)
Aqui estão os índices caso alguém queira adicioná-los:
CREATE INDEX index_topic_links_on_clicks_and_created ON public.topic_links USING btree (clicks, created_at);
CREATE INDEX index_posts_on_like_count_and_created ON public.posts USING btree (like_count, created_at);
CREATE INDEX index_topic_links_on_clicks_and_created_desc ON public.topic_links USING btree (clicks DESC, created_at DESC);
CREATE INDEX index_posts_on_like_count_and_created_desc ON public.posts USING btree (like_count DESC, created_at DESC, user_id) WHERE deleted_at IS NULL AND post_number > 1 AND (post_type = ANY ('{1,2,3,4}'::integer[]));
1 curtida
Aqui e aqui, você perguntou se esses índices estavam faltando. Apenas adicioná-los faz uma diferença abismal em termos de velocidade de carregamento de perfis. Esses índices definitivamente devem ser considerados.
É verdade que o cache adicionado aqui: Slow Page Loads on User Profiles - #12 by codinghorror fez uma grande diferença, mas isso só ocorre nas cargas subsequentes. Os índices melhoram a experiência inicial de forma geral. Os usuários não devem ficar sujeitos a tempos de carregamento lentos, mesmo que melhorem devido ao cache durante o período em que ele estiver válido. O objetivo era melhorar o primeiro carregamento inicial (seja quando o cache expirar) e isso foi alcançado com grande sucesso.
4 curtidas
Não está claro para mim como um índice em cada linha única, ordenado pelo número de cliques/contagem de curtidas, é necessário para uma página de usuário que é escopada a um ID de usuário.
2 curtidas
No que diz respeito aos cliques, no nosso caso, parece ser aí que está o ganho:
-> Sort (cost=725143.93..725146.15 rows=888 width=1464) (actual time=4762.610..4762.615 rows=5 loops=3)
Sort Key: topic_links.clicks DESC, topic_links.created_at DESC
Essa ordenação consome 4 segundos, o que é parte do motivo pelo qual os perfis estão carregando tão lentamente. Esta é apenas uma de várias consultas na página do perfil (embora seja uma das mais custosas em termos de velocidade). Após adicionar o índice, o planejador de consultas o identifica aqui, reduzindo o tempo de execução dessa subárvore para cerca de 30 ms:
-> Parallel Index Scan usando index_topic_links_on_clicks_and_created_desc em topic_links (cost=0.43..6728256.06 rows=403188 width=186) (actual time=0.051..30.428 rows=6058 loops=3)
Obviamente, o plano de consulta também muda de outras formas, mas o índice está definitivamente sendo referenciado aqui e a consulta executa muito mais rápido.
1 curtida
Mas, conceitualmente, tudo o que nos importa são links com escopo limitado ao usuário. Por que os novos índices propostos não têm escopo limitado aos usuários?
1 curtida