[YSQL] skip scan occurs on range but not hash #11881
Description
Jira Link: DB-894
Description
When the hash key is not isn't completely specified, all scan conditions are ignored. With low cardinality in first column, a condition on second column can still be useful
Example creating 2 indexes with a in hash and range:
yugabyte=# create table franck as select generate_series(1,10) a ,generate_series(1,100000) b; SELECT 100000 yugabyte=# create index franck_a_hash_b on franck(a hash,b); CREATE INDEX yugabyte=# create index franck_a_range_b on franck(a asc,b); CREATE INDEX With the hash index all rows are read:
yugabyte=# explain (costs off, analyze) /*+IndexOnlyScan(franck franck_a_hash_b)*/ select * from franck where b=42; QUERY PLAN --------------------------------------------------------------------------------------------- Index Only Scan using franck_a_hash_b on franck (actual time=3.635..370.456 rows=1 loops=1) Index Cond: (b = 42) Heap Fetches: 0 Planning Time: 0.092 ms Execution Time: 370.492 ms (5 rows) With the range index this is fast, seeking to each a value:
yugabyte=# explain (costs off, analyze) /*+IndexOnlyScan(franck franck_a_range_b)*/ select * from franck where b=42; QUERY PLAN -------------------------------------------------------------------------------------------- Index Only Scan using franck_a_range_b on franck (actual time=0.818..0.819 rows=1 loops=1) Index Cond: (b = 42) Heap Fetches: 0 Planning Time: 0.095 ms Execution Time: 0.848 ms (5 rows) case and fix discussed in:
https://yugabyte.slack.com/archives/C4K1838GL/p1648144593377149?thread_ts=1648028384.539129&cid=C4K1838GL