-- Indexing inside a document: JSONB + GIN, DDIA 2e Ch. 3.
-- A relational database can query and index INSIDE a JSON document. A GIN index
-- on a jsonb column turns a containment query from a full table scan into an
-- index scan -- with the catch that GIN's default operator class serves the
-- @> containment operator, not ->> equality.
--
--   docker cp jsonb_gin_index.sql ddia-ch3:/tmp/
--   docker exec ddia-ch3 psql -U postgres -d study -f /tmp/jsonb_gin_index.sql

CREATE TABLE docs (id bigint PRIMARY KEY, data jsonb);
-- 2M documents; ~0.01% (200) are 'archived', the rest 'active'.
INSERT INTO docs
SELECT g, jsonb_build_object(
    'status', CASE WHEN g % 10000 = 0 THEN 'archived' ELSE 'active' END,
    'priority', g % 5,
    'author', 'user'||(g % 10000),
    'tags', to_jsonb(ARRAY['t'||(g%50), 't'||(g%80)]))
FROM generate_series(1,2000000) g;
VACUUM ANALYZE docs;

\echo '########## querying a nested field WITHOUT an index ##########'
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, SUMMARY OFF)
SELECT count(*) FROM docs WHERE data @> '{"status":"archived"}';

\echo '########## same query WITH a GIN index on the jsonb column ##########'
CREATE INDEX docs_gin ON docs USING gin (data);
ANALYZE docs;
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, SUMMARY OFF)
SELECT count(*) FROM docs WHERE data @> '{"status":"archived"}';

\echo '########## the catch: GIN jsonb_ops does NOT serve ->> equality ##########'
EXPLAIN (COSTS OFF)
SELECT count(*) FROM docs WHERE data->>'status' = 'archived';
