-- Index-only scans and the visibility-map catch, DDIA 2e Ch. 4.
-- A query that filters on an indexed column but also needs a second column.
-- Watch (A) a plain index still read the heap for that column, (B) a covering
-- index NOT help until the table is vacuumed, and (C) a true index-only scan
-- with zero heap access after VACUUM sets the visibility map.
--
--   docker cp index_only_scan.sql ddia-ch4:/tmp/
--   docker exec ddia-ch4 psql -U postgres -d study -f /tmp/index_only_scan.sql

CREATE TABLE sales (id bigint, category int, amount int, filler text);
INSERT INTO sales SELECT g, (g % 10000) + 1, g % 100, repeat('x', 40)
FROM generate_series(1, 10000000) g;
ANALYZE sales;   -- planner stats only; does NOT set the visibility map

\echo '############ A) plain index on (category); query also needs amount ############'
CREATE INDEX sales_cat ON sales (category);
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT category, sum(amount) FROM sales WHERE category BETWEEN 1 AND 50 GROUP BY category;

\echo '############ B) covering index (category) INCLUDE (amount), BEFORE any VACUUM ############'
DROP INDEX sales_cat;
CREATE INDEX sales_cov ON sales (category) INCLUDE (amount);
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT category, sum(amount) FROM sales WHERE category BETWEEN 1 AND 50 GROUP BY category;

\echo '############ C) same covering index, AFTER VACUUM (sets the visibility map) ############'
VACUUM sales;
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT category, sum(amount) FROM sales WHERE category BETWEEN 1 AND 50 GROUP BY category;
