heikki-testcase-variant.sql

-- Heikki's adversarial test case, and my (pgeoghegan's) new variants
--
-- Based on https://postgr.es/m/aa55adf3-6466-4324-92e6-5ef54e7c3918@iki.fi

-----------
-- Setup --
-----------
drop table if exists skiptest;
create table skiptest as
select
  g / 10 as a,
  g % 10 as b
from
  generate_series(1, 10_000_000) g;
vacuum freeze skiptest;
create index on skiptest (a, b);

------------------
-- Test Queries --
------------------
set enable_seqscan = off;
set max_parallel_workers_per_gather = 0;

-- Heikki's original test case (fixed by v23 of the skip scan patch series
-- back in January 2025):
select count(*) from skiptest where b=1;

-- My new variant, which is quite a lot slower than on HEAD on revision of the
-- patch prior to the latest (v32), but slightly faster than HEAD with v32:
select a, b from skiptest where b=-1_000_000; -- "b=-1_000_000" lower than any actual "b" value in the index

-- Another minor variant, identical performance characteristics to prior example:
select a, b from skiptest where b=1_000_000; -- "b=1_000_000" higher than any actual "b" value in the index

-- For context, these "range skip array" variants are now _much_ faster than
-- HEAD with v32 of the patch, partly due to the influence of the "Enhance
-- nbtree tuple scan key optimizations" commit, which makes them perform
-- almost identically to the prior pair of queries that each omit the "a" column:
select a, b from skiptest where a between 0 and 10_000_000 and b=-1_000_000;
select a, b from skiptest where a between 0 and 10_000_000 and b=1_000_000;