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