Postgres Query Plan using wrong index - Mailing list pgsql-general

Hello,

I'm running on the docker postgres:17.0 image and trying to test out the behavior of adding a new index to a table. Specifically, I wanted to verify that my new index is actually used by looking at the output of "EXPLAIN ANALYZE". However, I found that my index is often not being used and wanted to see the rationale of the query planner when choosing the index.

Reproduction steps
postgres=# select version();
                                                          version                                                          
---------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 17.0 (Debian 17.0-1.pgdg120+1) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
(1 row)

1. Create database

CREATE DATABASE test LOCALE_PROVIDER icu ICU_LOCALE "en-US-x-icu" LOCALE "en_US.utf8" TEMPLATE template0;

2. Create table and indices
CREATE TABLE test_table (
    col_a int,
    col_b INT NOT NULL    
);
CREATE INDEX IF NOT EXISTS idx_col_a_btree ON test_table(col_b);
CREATE INDEX IF NOT EXISTS idx_col_a_brin ON test_table USING brin (col_b);
CREATE INDEX IF NOT EXISTS idx_col_b_a ON test_table(col_a, col_b);

3. Load 10 million rows into table

DO $$
DECLARE
    batch_count INT := 0;
    b_var INT := 0;
    a_var INT := 1;
    prev_a INT := 1;
    a_null BOOLEAN := FALSE;
    batch_size INT := 1000;
BEGIN
    FOR i IN 1..10000000 LOOP
        IF batch_count = batch_size THEN
            b_var := b_var + 1;
            a_null := NOT a_null;
            IF NOT a_null THEN
                a_var := prev_a + 1;
            ELSE
                prev_a := a_var;
                a_var := NULL;
            END IF;
            batch_count := 0;
        END IF;
        INSERT INTO test_table (col_a, col_b) VALUES (a_var, b_var);
        batch_count := batch_count + 1;
    END LOOP;
END $$;

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". However, explain analyze indicates otherwise:

postgres=# explain analyze select min(col_b) from test_table  where col_a > 4996;
                                                                      QUERY PLAN                                                                      
------------------------------------------------------------------------------------------------------------------------------------------------------
 Result  (cost=63.86..63.87 rows=1 width=4) (actual time=587.550..587.550 rows=1 loops=1)
   InitPlan 1
     ->  Limit  (cost=0.43..63.86 rows=1 width=4) (actual time=587.542..587.543 rows=1 loops=1)
           ->  Index Scan using idx_col_a_btree on test_table  (cost=0.43..259400.27 rows=4090 width=4) (actual time=587.541..587.541 rows=1 loops=1)
                 Filter: (col_a > 4996)
                 Rows Removed by Filter: 9992000
 Planning Time: 0.305 ms
 Execution Time: 587.579 ms
(8 rows)

Instead of using idx_col_b_a, it does an index scan on idx_col_a_btree. This is a problem because of the way how data is structured in my table. The higher col_a values are associated with higher col_b values. As a result, the index scan ends up having to scan through most of the index before finding the first record that matches the critieria "col_a > 4996".

When I DROP the idx_col_a_btree index, the resulting query plan looks much better because it's using the correct index on col_b:
postgres=# explain analyze select min(col_b) from test_table  where col_a > 4996;
                                                                QUERY PLAN                                                                
------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=102.23..102.24 rows=1 width=4) (actual time=0.591..0.592 rows=1 loops=1)
   ->  Index Only Scan using idx_col_b_a on test_table  (cost=0.43..92.01 rows=4090 width=4) (actual time=0.021..0.341 rows=4000 loops=1)
         Index Cond: (col_a > 4996)
         Heap Fetches: 0
 Planning Time: 0.283 ms
 Execution Time: 0.613 ms
(6 rows)

I tried fiddling with the table statistics and the random_page_cost but neither seemed to make a difference. Is there some nuance here that I'm missing? Why is the query planner using an index that drastically worsens the performance of the query?