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.447..0.453 rows=10 loops=1)

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

        Index Cond: (a = 50)

Planning Time: 8.182 ms

Execution Time: 0.506 ms

Peak Memory Usage: 8 kB

EXPLAIN

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

QUERY PLAN

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

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

        Index Cond: (b = 5000)

Planning Time: 0.059 ms

Execution Time: 235.371 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.406..0.406 rows=0 loops=1)

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

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

Planning Time: 0.102 ms

Execution Time: 0.438 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.326..0.327 rows=0 loops=1)

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

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

Planning Time: 0.693 ms

Execution Time: 0.358 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.764..0.764 rows=0 loops=1)

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

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

Planning Time: 0.047 ms

Execution Time: 0.791 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.691..0.691 rows=0 loops=1)

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

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

Planning Time: 0.048 ms

Execution Time: 0.718 ms

Peak Memory Usage: 8 kB

EXPLAIN