I have a Postgres table with 350M records. I have 3x indexes on it:
historical_offers(recovery_date, uprn)
historical_offers(recovery_date, account_id)
historical_offers(recovery_date, individual_id)
If I run a query for a date older than 24hrs it’s fast. But if I run it for today (and sometimes yesterday) then it’s way too slow (0.05ms vs 300ms).
My query is on all 3x fields and it uses the 3x indexes then blends the results nice and fast for dates > 24hrs~. So I don’t think it’s an issue to do with the OR condition on the 3x fields needing to use 3x indexes. Further: if I modify the query to JUST run on 1 field I hit the same issue.
Current theories:
- there is lag writing to the indexes (but I thought indexes are updated at the same time the table is updated)
- query planning is screwing up and using the smallest index (I read something that this is a known Postgres practice). Perhaps I need to add hints to "force" it to use the correct indexes?
Slow response (today):
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
Fast query (date 2 days ago):
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
2
Answers
PostgreSQL automatically collects table statistics when a certain percentage (default 10%) of the table changes. That means that you may not have good statistics for the most recent data, which can lead to bad query plans.
The solution would be to tell autovacuum at
ANALYZEthe table more often:This would collect statistics whenever 1% of the table changes. If you want an absolute value of modified rows rather than a percantage (perhaps because your change rate remains constant), you could
It expects to find one row, and actually finds zero. But that expectation is based on post-filter. The plan does not tell us explicitly how many rows it expected the index scan to return pre-filter.
But based on the cost estimate and if default cost parameters are in use, we can assume it expected to find only one row where recovery_date = ‘2025-01-02’. This would give one index leaf page access and one table page access, each at 4 units. If that were true, the plan it choose would be a good one, but since there are really 1470748 rows, it is a poor plan choice.
So the table stats have not been updated since rows for ‘2025-01-02’ started being added. Also, pg_stats.histogram_bounds must be NULL for that column, because the stats system thinks all values present have been included in pg_stats.most_common_vals, which is what gives it the confidence to predict only one row has the unobserved value. This means that the stats target for the table column (or for the system itself, if the table column doesn’t have one) must be about as high as the number of distinct values in the column, or higher.
So you could fix this either by analyzing the table, or by lowering the stats target. The first would make the stats be more accurate, the 2nd would make the system less confident in the stats it already has.