Tenho uma tabela Postgres com 350M registros. Tenho 3x índices nela:
historical_offers(recovery_date, uprn)
historical_offers(recovery_date, account_id)
historical_offers(recovery_date, individual_id)
Se eu executar uma consulta para uma data mais antiga que 24 horas, ela é rápida. Mas se eu executar para hoje (e às vezes ontem), ela é muito lenta (0,05 ms vs 300 ms).
Minha consulta está em todos os campos 3x e usa os índices 3x, então mistura os resultados de forma agradável e rápida para datas > 24 horas~. Então, não acho que seja um problema com a condição OR nos campos 3x que precisam usar índices 3x. Além disso: se eu modificar a consulta para executar APENAS em 1 campo, terei o mesmo problema.
Teorias atuais:
- há atraso na gravação dos índices (mas pensei que os índices fossem atualizados ao mesmo tempo que a tabela é atualizada)
- o planejamento de consulta está estragando e usando o menor índice (eu li algo que essa é uma prática conhecida do Postgres). Talvez eu precise adicionar dicas para "forçá-lo" a usar os índices corretos?
Resposta lenta (hoje):
EXPLAIN ANALYZE SELECT * FROM historical_offers.historical_offers WHERE (historical_offers.uprn = '1001005' OR historical_offers.account_id = 'SW1006' OR historical_offers.individual_id = '6752da6') AND (historical_offers.recovery_date = '2025-01-02');
+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| QUERY PLAN |
|------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| Index Scan using historical_offers_date_individual_id_idx on historical_offers (cost=0.57..8.56 rows=1 width=174) (actual time=346.467..346.467 rows=0 loops=1) |
| Index Cond: (recovery_date = '2025-01-02'::date) |
| Filter: (((uprn)::text = '1001005'::text) OR ((account_id)::text = 'SW1006'::text) OR ((individual_id)::text = '6752da6'::text)) |
| Rows Removed by Filter: 1470748 |
| Planning Time: 0.099 ms |
| Execution Time: 346.488 ms |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
EXPLAIN 6
Time: 0.383s
Consulta rápida (data 2 dias atrás):
EXPLAIN ANALYZE SELECT * FROM historical_offers.historical_offers WHERE (historical_offers.uprn = '1001005' OR historical_offers.account_id = 'SW1006' OR historical_offers.individual_id = '6752da6') AND (historical_offers.recovery_date = '2025-01-01');
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| QUERY PLAN |
|-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| Bitmap Heap Scan on historical_offers (cost=13.88..78.14 rows=16 width=174) (actual time=0.031..0.032 rows=0 loops=1) |
| Recheck Cond: (((recovery_date = '2025-01-01'::date) AND ((uprn)::text = '1001005'::text)) OR ((recovery_date = '2025-01-01'::date) AND ((account_id)::text = 'SW1006'::text)) OR ((recovery_date = '2025-01-01'::date) AND ((individual_id)::text = '6752da6'::text))) |
| -> BitmapOr (cost=13.88..13.88 rows=16 width=0) (actual time=0.030..0.030 rows=0 loops=1) |
| -> Bitmap Index Scan on historical_offers_date_uprn_idx (cost=0.00..4.62 rows=5 width=0) (actual time=0.013..0.013 rows=0 loops=1) |
| Index Cond: ((recovery_date = '2025-01-01'::date) AND ((uprn)::text = '1001005'::text)) |
| -> Bitmap Index Scan on historical_offers_date_account_id_idx (cost=0.00..4.62 rows=5 width=0) (actual time=0.008..0.008 rows=0 loops=1) |
| Index Cond: ((recovery_date = '2025-01-01'::date) AND ((account_id)::text = 'SW1006'::text)) |
| -> Bitmap Index Scan on historical_offers_date_individual_id_idx (cost=0.00..4.62 rows=5 width=0) (actual time=0.008..0.008 rows=0 loops=1) |
| Index Cond: ((recovery_date = '2025-01-01'::date) AND ((individual_id)::text = '6752da6'::text)) |
| Planning Time: 0.113 ms |
| Execution Time: 0.054 ms |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
EXPLAIN 11
Time: 0.026s
O PostgreSQL coleta estatísticas de tabela automaticamente quando uma certa porcentagem (padrão 10%) da tabela muda. Isso significa que você pode não ter boas estatísticas para os dados mais recentes, o que pode levar a planos de consulta ruins.
A solução seria dizer ao autovacuum na
ANALYZEmesa com mais frequência:Isso coletaria estatísticas sempre que 1% da tabela mudasse. Se você quiser um valor absoluto de linhas modificadas em vez de uma porcentagem (talvez porque sua taxa de mudança permaneça constante), você poderia
Ele espera encontrar uma linha e, na verdade, encontra zero. Mas essa expectativa é baseada no pós-filtro. O plano não nos diz explicitamente quantas linhas ele esperava que a varredura de índice retornasse antes do filtro.
Mas com base na estimativa de custo e se os parâmetros de custo padrão estiverem em uso, podemos assumir que ele esperava encontrar apenas uma linha onde recovery_date = '2025-01-02'. Isso daria acesso a uma página de folha de índice e a uma página de tabela, cada uma com 4 unidades. Se isso fosse verdade, o plano escolhido seria bom, mas como há realmente 1470748 linhas, é uma escolha de plano ruim.
Então, as estatísticas da tabela não foram atualizadas desde que as linhas para '2025-01-02' começaram a ser adicionadas. Além disso, pg_stats.histogram_bounds deve ser NULL para essa coluna, porque o sistema de estatísticas acha que todos os valores presentes foram incluídos em pg_stats.most_common_vals, que é o que lhe dá a confiança para prever que apenas uma linha tem o valor não observado. Isso significa que a meta de estatísticas para a coluna da tabela (ou para o próprio sistema, se a coluna da tabela não tiver uma) deve ser tão alta quanto o número de valores distintos na coluna, ou maior.
Então você pode consertar isso analisando a tabela ou diminuindo a meta de estatísticas. A primeira tornaria as estatísticas mais precisas, a segunda tornaria o sistema menos confiante nas estatísticas que ele já tem.