create.sql

application/sql

Download

create unlogged table t (a bigint, b text) with (fillfactor = 20);
insert into t select a, b from (select r, a, b, generate_series(0,32-1) AS p from (select row_number() over () AS r, a, b from (select i AS a, md5(i::text) AS b from generate_series(1, 312500) s(i) ORDER BY (i + 1 * (random() - 0.5))) foo) bar) baz ORDER BY ((r * 32 + p) + 8 * (random() - 0.5));
create index idx on t(a ASC);
vacuum freeze;
analyze;
checkpoint;

-- EXPLAIN (ANALYZE) SELECT * FROM t WHERE a BETWEEN 16336 AND 49103 ORDER BY a ASC;
-- EXPLAIN (ANALYZE) SELECT * FROM t WHERE a BETWEEN 16336 AND 49103 ORDER BY a DESC;