-- Data locality: one document read vs the equivalent join, DDIA 2e Ch. 3.
-- The same profile data stored normalized (users + positions) and as one JSONB
-- document. Fetching the WHOLE entity favors the co-located document; fetching
-- ONE field favors the normalized narrow column. Measured in buffers read.
--
--   docker cp data_locality.sql ddia-ch3:/tmp/
--   docker exec ddia-ch3 psql -U postgres -d study -f /tmp/data_locality.sql


-- Normalized: a user and their positions in separate tables.
CREATE TABLE users_n (id bigint PRIMARY KEY, name text, headline text);
INSERT INTO users_n SELECT g, 'person '||g, repeat('h',60) FROM generate_series(1,200000) g;
CREATE TABLE positions_n (id bigint PRIMARY KEY, user_id bigint, title text, org text, descr text);
INSERT INTO positions_n
SELECT g, ((g-1)/5)+1, 'title '||g, 'org '||(g%500), repeat('d', 180)
FROM generate_series(1,1000000) g;                       -- 5 positions per user
CREATE INDEX ON positions_n(user_id);

-- Document: the whole profile as one JSONB row (same data, co-located).
CREATE TABLE users_doc (id bigint PRIMARY KEY, profile jsonb);
INSERT INTO users_doc
SELECT u.id, jsonb_build_object(
         'name', u.name, 'headline', u.headline,
         'positions', coalesce(jsonb_agg(jsonb_build_object(
             'title',p.title,'org',p.org,'descr',p.descr)),'[]'::jsonb))
FROM users_n u LEFT JOIN positions_n p ON p.user_id=u.id
GROUP BY u.id, u.name, u.headline;
VACUUM ANALYZE users_n; VACUUM ANALYZE positions_n; VACUUM ANALYZE users_doc;

\echo '########## A) fetch 2,000 FULL profiles (user + their positions) ##########'
\echo '--- normalized: join users to positions ---'
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT u.id, u.name, p.title, p.org, p.descr
FROM users_n u JOIN positions_n p ON p.user_id=u.id
WHERE u.id BETWEEN 1 AND 2000;
\echo '--- document: read the co-located JSONB ---'
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT profile FROM users_doc WHERE id BETWEEN 1 AND 2000;

\echo '########## B) the flip: fetch ONLY the name of 20,000 users ##########'
\echo '--- normalized: read one narrow column ---'
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT name FROM users_n WHERE id BETWEEN 1 AND 20000;
\echo '--- document: must read the whole (large) JSONB to pull one field ---'
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT profile->>'name' FROM users_doc WHERE id BETWEEN 1 AND 20000;
