Estou hospedando um banco de dados Postgresql no Heroku com um plano padrão 0.
Meu banco de dados tem uma tabela chamada transactionsque contém aproximadamente 18 milhões de linhas:
SELECT COUNT(*) FROM transactions;
count
----------
17927768
(1 row)
Nos últimos meses tenho notado que o banco de dados está ficando cada vez mais lento. Agora estou no ponto em que recebo tempos limite de meus aplicativos porque as consultas (mesmo simples) demoram mais de 30 segundos.
Ao tentar descobrir o que está acontecendo, percebi algo estranho:
No servidor hospedado, uma consulta simples como:
EXPLAIN ANALYZE SELECT COUNT(*) FROM transactions WHERE partner_id = 1;
---------------------------------------------------------------------------------
Finalize Aggregate (cost=405691.73..405691.74 rows=1 width=8) (actual time=34941.061..34961.256
rows=1 loops=1)
-> Gather (cost=405691.63..405691.73 rows=1 width=8) (actual time=34940.913..34961.247 rows=2
loops=1)
Workers Planned: 1
Workers Launched: 1
-> Partial Aggregate (cost=404691.63..404691.63 rows=1 width=8) (actual time=34924.080.
.34924.081 rows=1 loops=2)
-> Parallel Seq Scan on transactions (cost=0.00..400083.56 rows=9216145 width=0)
(actual time=77.981..34179.970 rows=7801236 loops=2)
Filter: (partner_id = 1)
Rows Removed by Filter: 1164606
Planning Time: 0.755 ms
JIT:
Functions: 10
Options: Inlining false, Optimization false, Expressions true, Deforming true
Timing: Generation 1.912 ms, Inlining 0.000 ms, Optimization 30.606 ms, Emission 119.538 ms, To
tal 152.057 ms
Execution Time: 35190.328 ms
(14 rows)
leva até 35 segundos .
Mas quando faço download do dump de produção para minha máquina (um thinkpad mais antigo), a consulta leva menos de um segundo :
EXPLAIN ANALYZE SELECT COUNT(*) FROM transactions WHERE partner_id = 1;
---------------------------------------------------------------------------------
Finalize Aggregate (cost=251757.89..251757.90 rows=1 width=8)
(actual time=669.234..674.362 rows=1 loops=1)
-> Gather (cost=251757.67..251757.88 rows=2 width=8) (actua
l time=669.008..674.348 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=250757.67..250757.68 rows=
1 width=8) (actual time=638.447..638.448 rows=1 loops=3)
-> Parallel Index Only Scan using index_transact
ions_on_partner_id on transactions (cost=0.44..234528.06 rows=6
491844 width=0) (actual time=0.061..405.148 rows=5199597 loops=3
)
Index Cond: (partner_id = 1)
Heap Fetches: 0
Planning Time: 0.231 ms
JIT:
Functions: 11
Options: Inlining false, Optimization false, Expressions true, Deforming true
Timing: Generation 3.109 ms, Inlining 0.000 ms, Optimization 0.826 ms, Emission 11.406 ms, Total 15.342 ms
Execution Time: 676.047 ms
(14 rows)
Também pode-se ver que o Postgresql hospedado usa uma varredura sequencial paralela, enquanto a instância local usa uma varredura de índice paralela.
Como isso é possível? O que preciso fazer para chegar perto desse desempenho no servidor hospedado?
Edição 1: Mais informações sobre 'inchaço'
Tentei investigar o possível inchaço e recebi isto para a tabela de transações:
type | schemaname | object_name | bloat | waste
-------+------------+--------------+-------+------------
table | public | transactions | 1.3 | 571 MB
E isto:
schema | table | last_vacuum | last_autovacuum | rowcount | dead_rowcount | autovacuum_threshold | expect_autovacuum
--------+--------------------------------+-------------+------------------+----------------+----------------+----------------------+-------------------
public | transactions | | | 17,949,072 | 600 | 3,589,864 |
Essas consultas são geradas pelas ferramentas integradas do Herokus para analisar o inchaço, conforme descrito aqui .
Um valor dead rowcountde 600 em comparação com os 17 milhões de linhas parece negligenciável - mas por que o desperdício é tão alto (570 MB)? Essa poderia ser a origem do problema? Parece que o vácuo nunca foi realizado.