Re: Postgres Query Plan using wrong index

On Thu, 3 Apr 2025 at 16:24, Manikandan Swaminathan
<maniswami23(at)gmail(dot)com> wrote:
> Since you mentioned the planner not knowing about the correlation between the columns, I’m curious, why doesn’t making a multivariate statistic make a difference?
>
>
> CREATE STATISTICS col_a_col_b_stats (dependencies) ON col_a, col_b FROM test_table;
> ANALYZE test_table;

Extended statistics won't help you here. "dependencies" just estimates
functional dependencies between the columns mentioned in the ON
clause. What we'd need to store to do better in your example query is
positional information of where certain values are within indexes
according to an ordered scan of the index. I don't quite know how we'd
represent that exactly, but if we knew that a row matching col_a >
4996 wasn't until somewhere near the end of idx_col_a_btree index,
then we'd likely not want to use that index for this query.

David