Eu tenho uma tabela de vídeos com 18 milhões de linhas. Quando procuro um vídeo específico por ID, leva até 6 segundos para ser concluído. Às vezes leva alguns milissegundos, às vezes até 6 segundos, mas em média é de cerca de 2 segundos.
O aplicativo está hospedado no heroku e estou usando o banco de dados Crane ( https://addons.heroku.com/heroku-postgresql ) com 410 MB de RAM.
Existe alguma maneira de acelerar isso? Estou consultando vídeos 50 vezes por segundo em média, e novos vídeos são inseridos/atualizados a uma taxa de 50 por segundo.
explain analyze SELECT * FROM videos WHERE id = 17841464 LIMIT 1;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..6.43 rows=1 width=119) (actual time=2337.892..2337.894 rows=1 loops=1)
-> Index Scan using videos_pkey on videos (cost=0.00..6.43 rows=1 width=119) (actual time=2337.888..2337.888 rows=1 loops=1)
Index Cond: (id = 17841464)
Total runtime: 2337.943 ms
Veja como fica a tabela:
\d+ videos;
Table "public.videos"
Column | Type | Modifiers | Storage | Stats target | Description
----------------+-----------------------------+-----------------------------------------------------+----------+--------------+-------------
id | integer | not null default nextval('videos_id_seq'::regclass) | plain | |
uuid | character(11) | not null | extended | |
channel_id | integer | not null | plain | |
category_id | integer | | plain | |
title | character varying(255) | | extended | |
published_at | timestamp without time zone | | plain | |
view_count | bigint | | plain | |
like_count | integer | | plain | |
dislike_count | integer | | plain | |
favorite_count | integer | | plain | |
comment_count | integer | | plain | |
disabled | boolean | default false | plain | |
created_at | timestamp without time zone | | plain | |
updated_at | timestamp without time zone | | plain | |
Indexes:
"videos_pkey" PRIMARY KEY, btree (id)
"videos_uuid_idx" UNIQUE, btree (uuid)
"videos_latest_by_channel_idx" btree (channel_id, published_at DESC)
"videos_top_by_channel_idx" btree (channel_id, view_count DESC)
Has OIDs: no
Honestamente, nunca vi uma pesquisa de btree de valor único demorar tanto. Não acho que seu problema esteja simplesmente na sua consulta. Acho que é em outro lugar.
Primeiro você diz que tem muitas gravações. Isso provavelmente significa que você está constantemente empurrando muitas coisas para fora dos buffers e fazendo muitas E/S de disco aleatórias. Eu não ficaria surpreso se grande parte do gargalo estivesse na E/S geral e na falta de RAM.
Para 18 milhões de linhas, elas não parecem muito grandes. Todos eles podem caber na memória.
De qualquer forma, a primeira coisa a fazer se tivesse acesso à linha de comando no servidor seria esperar CPU I/O vs usuário vs tempo do sistema (você pode fazer isso em outro lugar em seu código). Também execute a explicação com (ANALYSE, BUFFERS, VERBOSE) definido para que você possa ver muito mais sobre o que está acontecendo. Isso deve lhe dar uma ideia se o seu problema for atividade de CPU abaixo do esperado, RAM lenta ou muita E/S de disco, mas, do jeito que está, há muito pouco que pode ser feito.
Outra coisa que você pode fazer é isso:
Veja se isso acelera as coisas.