YugabyteDB 2.18

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
-- https://dev.to/yugabyte/index-skip-scan-in-yugabytedb-2ao2

create table demo ( A int, B int, primary key(A ASC, B ASC) )
split at values ( (0,0),(33,0),(66,0) ) ;

CREATE TABLE

insert into demo select a,b 
 from generate_series(1,100) a, generate_series(1,1000) b;

INSERT 0 100000

explain (costs off, analyze) select * from demo
 where A=50            limit 10;

QUERY PLAN

Limit (actual time=0.540..0.547 rows=10 loops=1)

  ->  Index Scan using demo_pkey on demo (actual time=0.536..0.541 rows=10 loops=1)

        Index Cond: (a = 50)

Planning Time: 10.459 ms

Execution Time: 0.608 ms

Peak Memory Usage: 8 kB

EXPLAIN

explain (costs off, analyze) select * from demo
 where B=5000          limit 10;

QUERY PLAN

Limit (actual time=217.694..217.694 rows=0 loops=1)

  ->  Index Scan using demo_pkey on demo (actual time=217.691..217.691 rows=0 loops=1)

        Index Cond: (b = 5000)

Planning Time: 0.102 ms

Execution Time: 217.740 ms

Peak Memory Usage: 8 kB

EXPLAIN

explain (costs off, analyze) select * from demo
 where A=50 and B=5000 limit 10;

QUERY PLAN

Limit (actual time=0.443..0.443 rows=0 loops=1)

  ->  Index Scan using demo_pkey on demo (actual time=0.442..0.442 rows=0 loops=1)

        Index Cond: ((a = 50) AND (b = 5000))

Planning Time: 0.098 ms

Execution Time: 0.474 ms

Peak Memory Usage: 8 kB

EXPLAIN

explain (costs off, analyze) select * from demo
 where A=50 and B>5000 limit 10;

QUERY PLAN

Limit (actual time=0.372..0.372 rows=0 loops=1)

  ->  Index Scan using demo_pkey on demo (actual time=0.371..0.371 rows=0 loops=1)

        Index Cond: ((a = 50) AND (b > 5000))

Planning Time: 0.627 ms

Execution Time: 0.408 ms

Peak Memory Usage: 8 kB

EXPLAIN

explain (costs off, analyze) select * from demo
 where A>50 and B=5000 limit 10;

QUERY PLAN

Limit (actual time=0.793..0.793 rows=0 loops=1)

  ->  Index Scan using demo_pkey on demo (actual time=0.792..0.792 rows=0 loops=1)

        Index Cond: ((a > 50) AND (b = 5000))

Planning Time: 0.045 ms

Execution Time: 0.818 ms

Peak Memory Usage: 8 kB

EXPLAIN

explain (costs off, analyze) select * from demo
 where A>50 and B>5000 limit 10;

QUERY PLAN

Limit (actual time=0.651..0.651 rows=0 loops=1)

  ->  Index Scan using demo_pkey on demo (actual time=0.650..0.650 rows=0 loops=1)

        Index Cond: ((a > 50) AND (b > 5000))

Planning Time: 0.044 ms

Execution Time: 0.676 ms

Peak Memory Usage: 8 kB

EXPLAIN