Re: Postgres Query Plan using wrong index

Thanks so much for your help, Tom.

Sorry, I didn’t quite understand the answer — I have a few follow-up questions. Sorry, I'm new to Postgres so I am a bit ignorant here and would appreciate any tips on the query planner you could give.

1) Why is the query currently picking the poorly performing index? I already have an index on (col_a, col_b) that performs well. When I remove the separate index on (col_b), it correctly uses the (col_a, col_b) index and the query runs efficiently. But when both indexes are present, it chooses the slower (col_b) index instead.

2) Why would the index you suggested, (col_b, col_a), perform better than (col_a, col_b)? I would’ve expected the filter on col_a to come first, followed by the aggregate on col_b. In my mind, it needs to find rows matching the col_a condition before calculating the MIN(col_b), and I assumed it would traverse the B-tree accordingly. I'm more used to MySQL where I think it is called a "lose index scan". I must have a gap in my understanding of how Postgres approaches this. Thanks for your help!

3) Why does the planner choose the better-performing (col_a, col_b) index when the filter is col_a > 5000, but switch to the slower (col_b) index when the filter is not at the edge of the range, like col_a > 4996? For reference, here’s the query plan when filtering for col_a > 5000. It uses the correct index on (col_a, col_b).

postgres=# explain analyze select min(col_b) from test_table where col_a > 5000;

Aggregate (cost=4.46..4.46 rows=1 width=4) (actual time=0.008..0.008 rows=1 loops=1)
-> Index Only Scan using idx_col_b_a on test_table (cost=0.43..4.45 rows=1 width=4) (actual time=0.004..0.005 rows=0 loops=1)
Index Cond: (col_a > 5000)
Heap Fetches: 0
Planning Time: 2.279 ms
Execution Time: 0.028 ms
(6 rows)

>
> On Apr 1, 2025, at 5:30 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Manikandan Swaminathan <maniswami23(at)gmail(dot)com> writes:
>> 4. When running the following query, I would expect the index "idx_col_b_a"
>> to be used: select min(col_b) from test_table where col_a > 4996.
>> I have a range-based filter on col_a, and am aggregating the result with
>> min(col_b). Both columns are covered by "idx_col_b_a".
>
> They may be covered, but sort order matters, and that index has the
> wrong sort order to help with this query. Try
>
> create index on test_table(col_b, col_a);
>
> regards, tom lane