我有一张包含 3.5 亿条记录的 Postgres 表。我在该表上有 3 个索引：
historical_offers(recovery_date, uprn)
historical_offers(recovery_date, account_id)
historical_offers(recovery_date, individual_id)
如果我针对 24 小时之前的日期运行查询，它很快。但如果我针对今天（有时是昨天）运行查询，它就太慢了（0.05 毫秒 vs 300 毫秒）。
我的查询针对所有 3x 字段，并使用 3x 索引，然后针对日期 > 24 小时~ 快速完美地混合结果。因此，我认为这与 3x 字段上的 OR 条件需要使用 3x 索引无关。此外：如果我将查询修改为仅在 1 个字段上运行，我会遇到同样的问题。
目前的理论：
- 索引的写入存在滞后（但我认为索引是在表更新的同时更新的）
- 查询规划搞砸了，并使用了最小的索引（我读到过一些文章说这是 Postgres 的已知做法）。也许我需要添加提示来“强制”它使用正确的索引？
响应缓慢（今天）：
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
快速查询（2天前）：
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