repro.sql

application/sql

Download

-- build the table
select setseed(0);
create unlogged table t (a bigint, b text) with (fillfactor = 20);

insert into t
select 1 * a, b from (
  select r, a, b, generate_series(0,2-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, 5000000) s(i) ORDER BY (i + 16 * (random() - 0.5))
    ) foo
  ) bar
) baz ORDER BY ((r * 2 + p) + 8 * (random() - 0.5));

create index idx on t(a ASC) with (deduplicate_items=false);

vacuum freeze;

analyze t;

checkpoint;

-- query to test
SELECT * FROM ( SELECT * FROM t WHERE a BETWEEN 16150 AND 4540437 ORDER BY a ASC) OFFSET 1000000000;