Re: Odd (slow) plan choice with min/max

On Wed, 24 Mar 2021 at 00:07, Rick Otten <rottenwindfish(at)gmail(dot)com> wrote:

>
>> 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.

Paul