[YSQL] Primary Index scan cost higher than Sequential cost in small tables

[gauravk-in]

Jira Link: DB-15582

Description

For small tables, a primary index scan is being assigned higher cost than sequential scan, even though it appears to be slightly faster.

This seems to be caused by the assumption that disk pages will be fetched randomly for primary index scan in the index scan cost model.

yugabyte=# create table test (k1 INT, v1 INT, PRIMARY KEY (k1 ASC));
CREATE TABLE
yugabyte=# INSERT INTO test (SELECT s, s FROM generate_series(1, 10) s);
INSERT 0 10
yugabyte=# ANALYZE test;
ANALYZE
yugabyte=# SET yb_enable_base_scans_cost_model = ON;
SET

yugabyte=# /*+ IndexScan(test test_pkey) */ EXPLAIN ANALYZE SELECT * FROM test WHERE k1 < 5;
                                                     QUERY PLAN                                                     
--------------------------------------------------------------------------------------------------------------------
 Index Scan using test_pkey on test  (cost=180.00..552.88 rows=3 width=8) (actual time=0.467..0.469 rows=4 loops=1)
   Index Cond: (k1 < 5)
 Planning Time: 0.068 ms
 Execution Time: 0.497 ms
 Peak Memory Usage: 24 kB
(5 rows)

yugabyte=# EXPLAIN ANALYZE SELECT * FROM test WHERE k1 < 5;
                                            QUERY PLAN                                            
--------------------------------------------------------------------------------------------------
 Seq Scan on test  (cost=180.00..550.53 rows=3 width=8) (actual time=1.313..1.316 rows=4 loops=1)
   Storage Filter: (k1 < 5)
 Planning Time: 0.139 ms
 Execution Time: 1.393 ms
 Peak Memory Usage: 24 kB
(5 rows)

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.