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