-- Random vs sequential writes into a B-tree, DDIA 2e Ch. 4.
-- Insert the same 10,000,000 rows into an indexed table two ways: keys in
-- ascending order (right-appends) vs. keys scattered across the key space
-- (random page inserts). Measure load time, index size, and leaf-page fill.
--
-- Needs the pgstattuple extension for leaf density:
--   docker exec ddia-ch4 psql -U postgres -d study -c "CREATE EXTENSION pgstattuple;"
-- Then:
--   docker exec ddia-ch4 psql -U postgres -d study -f /tmp/random_vs_sequential_writes.sql

\timing on

-- Sequential: an indexed key inserted in ascending order.
CREATE TABLE seq_t (k bigint, v int);
CREATE INDEX seq_idx ON seq_t (k);
\echo '=== sequential insert: keys 1..10,000,000 in ascending order ==='
INSERT INTO seq_t SELECT g, g % 1000 FROM generate_series(1, 10000000) g;

-- Random: same count, keys scattered across the key space.
CREATE TABLE rnd_t (k bigint, v int);
CREATE INDEX rnd_idx ON rnd_t (k);
\echo '=== random insert: same 10,000,000 rows, keys scattered across the key space ==='
INSERT INTO rnd_t SELECT (hashint8(g)::bigint + 3000000000), g % 1000 FROM generate_series(1, 10000000) g;

\timing off
\echo '=== index sizes: same key count, different insertion order ==='
SELECT pg_size_pretty(pg_relation_size('seq_idx')) AS seq_index,
       pg_size_pretty(pg_relation_size('rnd_idx')) AS rnd_index,
       round(pg_relation_size('rnd_idx')::numeric / pg_relation_size('seq_idx'), 2) AS ratio;

\echo '=== avg leaf fill (%): how densely packed the B-tree leaves are ==='
SELECT 'sequential' AS insert_order, round(avg_leaf_density::numeric, 1) AS avg_leaf_density_pct
FROM pgstatindex('seq_idx')
UNION ALL
SELECT 'random', round(avg_leaf_density::numeric, 1) FROM pgstatindex('rnd_idx');
