add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
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