[YSQL] Partial indexes not picked up with redundant column in key

[FranckPachot]

Jira Link: DB-11153

Description

With a partial index filtering on one single value for a column, the presence of that column in the index key is redundant. The query planner seems to ignore that fact costs the access like a non-fully qualified predicate.

Example:

-- partial index
drop table if exists demo;
create table demo ( id bigserial, account int, status int, created timestamptz);
insert into demo (account, status, created)
 select 42, trunc(1.1*random()), now() from generate_series(1,10000);

-- i1 is the ideal index for "status"=1, one "account", and a range of "created"
create index i1 on demo ( account, status, created ) where status=1;

-- i2 is not as good as it cannot filter on status
create index i2 on demo ( created desc, account asc );

The query planner doesn't choose i1 and then scans all status and filter later:

yugabyte=# explain (analyze, dist, summary off)
select * from demo where status=1 and account=42 and created > date'2024-01-01' limit 10;
                                                    QUERY PLAN
------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..5.30 rows=10 width=24) (actual time=1.980..6.585 rows=10 loops=1)
   ->  Index Scan using i2 on demo  (cost=0.00..5.30 rows=10 width=24) (actual time=1.978..6.578 rows=10 loops=1)
         Index Cond: ((created > '2024-01-01'::date) AND (account = 42))
         Remote Filter: (status = 1)
         Storage Table Read Requests: 2
         Storage Table Read Execution Time: 3.894 ms
         Storage Table Rows Scanned: 1034
         Storage Index Read Requests: 2
         Storage Index Read Execution Time: 2.082 ms
         Storage Index Rows Scanned: 1034

The index i1 is the best index

yugabyte=# explain (analyze, dist, summary off)
/*+ indexscan(demo i1) */
select * from demo where status=1 and account=42 and created > date'2024-01-01' limit 10;
                                                     QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..1.42 rows=10 width=24) (actual time=1.788..1.797 rows=10 loops=1)
   ->  Index Scan using i1 on demo  (cost=0.00..14.20 rows=100 width=24) (actual time=1.786..1.792 rows=10 loops=1)
         Index Cond: ((account = 42) AND (created > '2024-01-01'::date))
         Storage Table Read Requests: 1
         Storage Table Read Execution Time: 0.729 ms
         Storage Table Rows Scanned: 10
         Storage Index Read Requests: 1
         Storage Index Read Execution Time: 0.871 ms
         Storage Index Rows Scanned: 10

Workaround 1: remove the partial clause

yugabyte=# create index i3 on demo ( account, status, created );
CREATE INDEX

yugabyte=# explain (analyze, dist, summary off)
select * from demo where status=1 and account=42 and created > date'2024-01-01' limit 10;
                                                    QUERY PLAN
------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..5.27 rows=10 width=24) (actual time=1.578..1.587 rows=10 loops=1)
   ->  Index Scan using i3 on demo  (cost=0.00..5.27 rows=10 width=24) (actual time=1.576..1.581 rows=10 loops=1)
         Index Cond: ((account = 42) AND (status = 1) AND (created > '2024-01-01'::date))
         Storage Table Read Requests: 1
         Storage Table Read Execution Time: 0.584 ms
         Storage Table Rows Scanned: 10
         Storage Index Read Requests: 1
         Storage Index Read Execution Time: 0.801 ms
         Storage Index Rows Scanned: 10

Workaround 2: remove the redundant column in the index key

yugabyte=# create index i4 on demo ( account, created ) where status=1;
CREATE INDEX

yugabyte=# explain (analyze, dist, summary off)
select * from demo where status=1 and account=42 and created > date'2024-01-01' limit 10;
                                                    QUERY PLAN
------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..5.02 rows=10 width=24) (actual time=1.526..1.535 rows=10 loops=1)
   ->  Index Scan using i4 on demo  (cost=0.00..5.02 rows=10 width=24) (actual time=1.524..1.529 rows=10 loops=1)
         Index Cond: ((account = 42) AND (created > '2024-01-01'::date))
         Storage Table Read Requests: 1
         Storage Table Read Execution Time: 0.754 ms
         Storage Table Rows Scanned: 10
         Storage Index Read Requests: 1
         Storage Index Read Execution Time: 0.591 ms
         Storage Index Rows Scanned: 10

Workaround 3: add redundant predicate that is not removed by partial index pruning

yugabyte=# explain (analyze, dist, summary off)
select * from demo where status=1 and status>1 and account=42 and created > date'2024-01-01' limit 10;
                                                   QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..5.04 rows=10 width=24) (actual time=0.746..0.746 rows=0 loops=1)
   ->  Index Scan using i1 on demo  (cost=0.00..5.04 rows=10 width=24) (actual time=0.743..0.743 rows=0 loops=1)
         Index Cond: ((account = 42) AND (status > 1) AND (created > '2024-01-01'::date))
         Storage Index Read Requests: 1
         Storage Index Read Execution Time: 0.599 ms

Issue Type

kind/bug

Warning: Please confirm that this issue does not contain any sensitive information

• I confirm this issue does not contain any sensitive information.