
-- Dimension tables
create table dim_customer (id int primary key, segment text, region text);
create table dim_product  (id int primary key, category_id int, brand text);
create table dim_category (id int primary key, name text);
create table dim_store    (id int primary key, region text, size_class text);
create table dim_promo    (id int primary key, kind text, active bool);
create table dim_date     (id int primary key, year int, month int, is_weekend bool);
create table dim_supplier (id int primary key, nation text, tier text);

insert into dim_category select i, 'cat_' || i from generate_series(1, 50) i;
insert into dim_customer select i, (array['A','B','C','D'])[1 + (i % 4)], (array['east','west','north','south'])[1 + (i % 4)]
  from generate_series(1, 5000) i;
insert into dim_product select i, 1 + (i % 50), 'brand_' || (i % 200)
  from generate_series(1, 2000) i;
insert into dim_store select i, (array['east','west','north','south'])[1 + (i % 4)], (array['small','medium','large'])[1 + (i % 3)]
  from generate_series(1, 200) i;
insert into dim_promo select i, (array['none','discount','clearance'])[1 + (i % 3)], (i % 10 = 0)
  from generate_series(1, 100) i;
insert into dim_date select i, 2018 + (i / 365), 1 + ((i / 30) % 12), (i % 7 in (0,6))
  from generate_series(1, 2000) i;
insert into dim_supplier select i, (array['US','DE','CN','BR','IN'])[1 + (i % 5)], (array['gold','silver','bronze'])[1 + (i % 3)]
  from generate_series(1, 300) i;

-- Fact table: 10M rows
create table fact_sales (
  id bigint,
  customer_id int,
  product_id int,
  store_id int,
  promo_id int,
  date_id int,
  supplier_id int,
  quantity int,
  amount numeric
);

insert into fact_sales
  select i,
         1 + (random() * 4999)::int,
         1 + (random() * 1999)::int,
         1 + (random() * 199)::int,
         1 + (random() * 99)::int,
         1 + (random() * 1999)::int,
         1 + (random() * 299)::int,
         1 + (random() * 9)::int,
         (random() * 1000)::numeric(10,2)
  from generate_series(1, 10000000) i;

alter table fact_sales add primary key (id);
create index on fact_sales (customer_id);
create index on fact_sales (product_id);
create index on fact_sales (store_id);
create index on fact_sales (promo_id);
create index on fact_sales (date_id);
create index on fact_sales (supplier_id);

vacuum analyze;

set max_parallel_workers_per_gather = 0;

-- Each query joins fact_sales to N dimension tables, all with a WHERE
-- filter of similar selectivity (~30-40% survival each -- near
-- bloom_filter_pushdown_threshold, so each is individually just barely
-- worth pushing, and combination_floor (10% surviving) isn't crossed until
-- several are combined). This is the worst case for the combination
-- search: nothing gets pruned early.

-- N=2 filtered dimensions
explain (analyze, timing off, summary on)
select count(*) from fact_sales f
  join dim_customer c on f.customer_id = c.id
  join dim_product  p on f.product_id  = p.id
 where c.segment = 'A'
   and p.brand like 'brand_1%';

-- N=3 filtered dimensions
explain (analyze, timing off, summary on)
select count(*) from fact_sales f
  join dim_customer c on f.customer_id = c.id
  join dim_product  p on f.product_id  = p.id
  join dim_store    s on f.store_id    = s.id
 where c.segment = 'A'
   and p.brand like 'brand_1%'
   and s.region = 'east';

-- N=4 filtered dimensions
explain (analyze, timing off, summary on)
select count(*) from fact_sales f
  join dim_customer c on f.customer_id = c.id
  join dim_product  p on f.product_id  = p.id
  join dim_store    s on f.store_id    = s.id
  join dim_promo    pr on f.promo_id   = pr.id
 where c.segment = 'A'
   and p.brand like 'brand_1%'
   and s.region = 'east'
   and pr.kind <> 'none';

-- N=5 filtered dimensions
explain (analyze, timing off, summary on)
select count(*) from fact_sales f
  join dim_customer c on f.customer_id = c.id
  join dim_product  p on f.product_id  = p.id
  join dim_store    s on f.store_id    = s.id
  join dim_promo    pr on f.promo_id   = pr.id
  join dim_date     d on f.date_id     = d.id
 where c.segment = 'A'
   and p.brand like 'brand_1%'
   and s.region = 'east'
   and pr.kind <> 'none'
   and d.month <= 4;

-- N=6 filtered dimensions
explain (analyze, timing off, summary on)
select count(*) from fact_sales f
  join dim_customer c on f.customer_id = c.id
  join dim_product  p on f.product_id  = p.id
  join dim_store    s on f.store_id    = s.id
  join dim_promo    pr on f.promo_id   = pr.id
  join dim_date     d on f.date_id     = d.id
  join dim_supplier su on f.supplier_id = su.id
 where c.segment = 'A'
   and p.brand like 'brand_1%'
   and s.region = 'east'
   and pr.kind <> 'none'
   and d.month <= 4
   and su.nation in ('US','DE');

-- Snowflake variant: product -> category is a second hop, so a filter on
-- dim_category has to propagate through dim_product before it can reach
-- fact_sales. Tests Ding et al.'s snowflake extension (ref [8]).
-- N=6, snowflake (category -> product -> fact)
explain (analyze, timing off, summary on)
select count(*) from fact_sales f
  join dim_customer c on f.customer_id = c.id
  join dim_product  p on f.product_id  = p.id
  join dim_category cat on p.category_id = cat.id
  join dim_store    s on f.store_id    = s.id
  join dim_promo    pr on f.promo_id   = pr.id
  join dim_date     d on f.date_id     = d.id
 where c.segment = 'A'
   and cat.id <= 25
   and s.region = 'east'
   and pr.kind <> 'none'
   and d.month <= 4;

