Horton

test_for_v15.sql

text/plain

Download
Filename: test_for_v15.sql
Type: text/plain
Part: 0
Message: Re: Adding skip scan (including MDAM style range skip scan) to nbtree 
DROP TABLE IF EXISTS t;
CREATE TABLE t (id1 int, id2 int);
INSERT INTO t (SELECT i, i FROM generate_series(1,1_000_000) s(i));
CREATE INDEX t_idx on t (id1, id2);
VACUUM FREEZE ANALYZE;
--fast
SET skipscan_prefix_cols=32;
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT, SETTINGS, WAL, VERBOSE) SELECT * FROM t WHERE id2 = 1; -- cache
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT, SETTINGS, WAL, VERBOSE) SELECT * FROM t WHERE id2 = 1;
SET skipscan_prefix_cols=0;
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT, SETTINGS, WAL, VERBOSE) SELECT * FROM t WHERE id2 = 1;

--slow
SET skipscan_prefix_cols=32;
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT, SETTINGS, WAL, VERBOSE) SELECT * FROM t WHERE id2 = 1_000_000; -- cache
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT, SETTINGS, WAL, VERBOSE) SELECT * FROM t WHERE id2 = 1_000_000;
SET skipscan_prefix_cols=0;
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT, SETTINGS, WAL, VERBOSE) SELECT * FROM t WHERE id2 = 1_000_000;
DROP INDEX t_idx;