benchmark.sql

CREATE TABLE purchases_more (
  id          serial
, customer_id int  -- REFERENCES customer
, total       int  -- could be amount of money in Cent
, some_column text -- to make the row bigger, more realistic
);


INSERT INTO purchases_more (customer_id, total, some_column)    -- insert 8M rows
SELECT (random() * 40000)::int             AS customer_id  -- 40k customers
     , (random() * random() * 100000)::int AS total     
     , 'note: ' || repeat('x', (random()^2 * random() * random() * 500)::int)
FROM   generate_series(1,8000000) g;

ALTER TABLE purchases_more ADD CONSTRAINT purchases_more_id_pkey PRIMARY KEY (id);

DELETE FROM purchases_more WHERE random() > 0.9; -- some dead rows

INSERT INTO purchases_more (customer_id, total, some_column)
SELECT (random() * 40000)::int             AS customer_id  -- 40k customers
     , (random() * random() * 100000)::int AS total     
     , 'note: ' || repeat('x', (random()^2 * random() * random() * 500)::int)
FROM   generate_series(1,800000) g;  -- add 800k to make it ~ 8M

CREATE INDEX purchases_more_3c_idx ON purchases_more (customer_id, total DESC, id);

VACUUM ANALYZE purchases_more;