test.sql

text/plain

Filename: test.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);
-- 5x fast
-- INSERT INTO t (SELECT 1, i%101 FROM generate_series(1,1_000_000) s(i)); 
-- 4x slow
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;
SET skipscan_prefix_cols=32;
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT, SETTINGS, WAL, VERBOSE) SELECT * FROM t WHERE id2 = 100; -- cache
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT, SETTINGS, WAL, VERBOSE) SELECT * FROM t WHERE id2 = 100;
SET skipscan_prefix_cols=0;
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT, SETTINGS, WAL, VERBOSE) SELECT * FROM t WHERE id2 = 100;
DROP INDEX t_idx;