Editing Corpus/Schema (section)

== Indexes ==

<syntaxhighlight lang="sql">
-- B-tree indexes on promoted columns
CREATE INDEX idx_doc_kind          ON corpus.documents (kind);
CREATE INDEX idx_doc_jurisdiction   ON corpus.documents (jurisdiction);
CREATE INDEX idx_doc_source        ON corpus.documents (source);
CREATE INDEX idx_doc_date          ON corpus.documents (date);
CREATE INDEX idx_doc_date_end      ON corpus.documents (date_end)   WHERE date_end IS NOT NULL;
CREATE INDEX idx_doc_parent        ON corpus.documents (parent_id)  WHERE parent_id IS NOT NULL;
CREATE INDEX idx_doc_kind_juris    ON corpus.documents (kind, jurisdiction);
CREATE INDEX idx_doc_kind_source   ON corpus.documents (kind, source);

-- GIN index on tags
CREATE INDEX idx_doc_tags          ON corpus.documents USING GIN (tags jsonb_path_ops);

-- Partial GIN indexes per kind (FTS isolation)
CREATE INDEX idx_doc_fts_legislation ON corpus.documents USING GIN (content_fts) WHERE kind = 'legislation';
CREATE INDEX idx_doc_fts_decision  ON corpus.documents USING GIN (content_fts) WHERE kind = 'decision';
CREATE INDEX idx_doc_fts_record    ON corpus.documents USING GIN (content_fts) WHERE kind = 'record';
CREATE INDEX idx_doc_fts_notice    ON corpus.documents USING GIN (content_fts) WHERE kind = 'notice';
</syntaxhighlight>

=== Why partial GIN indexes per kind instead of table partitioning ===

Partitioning <code>documents</code> by <code>kind</code> would require <code>kind</code> in the primary key (PostgreSQL requires the partition key in the PK). That means <code>id</code> alone can no longer be a PK -- every FK, every edge reference, every external link would need to carry <code>(id, kind)</code> instead of just <code>id</code>. The complexity cascades through the entire system.

Partial GIN indexes achieve 80% of the performance benefit with 0% of the complexity. When a query filters <code>WHERE kind = 'legislation' AND content_fts @@ ...</code>, PostgreSQL uses the partial index <code>idx_doc_fts_legislation</code> which contains only legislation tsvectors. The planner skips the other kinds entirely. Index size is smaller, scans are faster, and the PK stays a simple <code>text</code> column.

No partial index for <code>section</code> and <code>chunk</code> because they rarely have <code>body</code>/<code>body_search</code> content worth full-text indexing.

----