Yes, the columns are highly correlated, but that alone doesn't seem like it should be sufficient criteria to choose this plan. Ie the selection criteria (1 day of data about a year ago) has a year+ worth of data after it and probably a decade of data before it, so anything walking a correlated index from top or bottom is going to have to walk past a lot of data before it gets to data that fits the criteria.
I assume you have a statistic on the correlated columns, ie `create statistic` ?
I didn't, but adding ====== CREATE STATISTICS risk_risk_id_time_correlation_stats ON risk_id,time FROM risk; analyze risk; ====== doesn't seem to help. I get the same plan before/after. Second run was faster, but just because data was hot.
If you can't use partitions on your date column, can you use partial indexes instead? Or a functional index with min() over day and max() over day?
I don't particularly want to add more weird indexes to solve this one particular query. as the existing risk_id index should make it efficient enough if only the planner chose to use it. This is part of an archiving job, identifying sections of historical data, so not a query that needs to be super optimised, but essentially doing a full table scan backwards/forwards as it is now is doing a lot of unnecessary IO that would be best left free for more time sensitive queries.My count(() workaround works so we can use that. I'm more interested in understanding why the planner makes what seems to be an obviously bad choice.
