Skip to content

codeintel: add missing API docs FTS indexes (use GIN, not btree)

Warren Gifford requested to merge sg/apidocs-missing-indexes into main

Created by: slimsag

I totally typo'd the indexes here and made these btree, not GIN, by accident. GIN indexes are FAR better at indexing tsvector columns / queries.

Before merging, I will execute the nearly identical, but CONCURRENT, commands on Sourcegraph.com's codeintel-db directly. I have tested on a clone of prod's codeintel-db, and each CREATE INDEX statement (without CONCURRENT) takes ~24m to complete.

Other deployments should be fine, as they do not have any data in these tables at present.

DROP INDEX IF EXISTS lsif_data_docs_search_public_search_key_tsv_idx;
DROP INDEX IF EXISTS lsif_data_docs_search_public_search_key_reverse_tsv_idx;
DROP INDEX IF EXISTS lsif_data_docs_search_public_label_tsv_idx;
DROP INDEX IF EXISTS lsif_data_docs_search_public_label_reverse_tsv_idx;

DROP INDEX IF EXISTS lsif_data_docs_search_private_search_key_tsv_idx;
DROP INDEX IF EXISTS lsif_data_docs_search_private_search_key_reverse_tsv_idx;
DROP INDEX IF EXISTS lsif_data_docs_search_private_label_tsv_idx;
DROP INDEX IF EXISTS lsif_data_docs_search_private_label_reverse_tsv_idx;

CREATE INDEX IF NOT EXISTS lsif_data_docs_search_public_search_key_tsv_idx ON lsif_data_docs_search_public USING GIN (search_key_tsv);
CREATE INDEX IF NOT EXISTS lsif_data_docs_search_public_search_key_reverse_tsv_idx ON lsif_data_docs_search_public USING GIN (search_key_reverse_tsv);
CREATE INDEX IF NOT EXISTS lsif_data_docs_search_public_label_tsv_idx ON lsif_data_docs_search_public USING GIN (label_tsv);
CREATE INDEX IF NOT EXISTS lsif_data_docs_search_public_label_reverse_tsv_idx ON lsif_data_docs_search_public USING GIN (label_reverse_tsv);

CREATE INDEX CONCURRENTLY IF NOT EXISTS lsif_data_docs_search_private_search_key_tsv_idx ON lsif_data_docs_search_private USING GIN (search_key_tsv);
CREATE INDEX CONCURRENTLY IF NOT EXISTS lsif_data_docs_search_private_search_key_reverse_tsv_idx ON lsif_data_docs_search_private USING GIN (search_key_reverse_tsv);
CREATE INDEX CONCURRENTLY IF NOT EXISTS lsif_data_docs_search_private_label_tsv_idx ON lsif_data_docs_search_private USING GIN (label_tsv);
CREATE INDEX CONCURRENTLY IF NOT EXISTS lsif_data_docs_search_private_label_reverse_tsv_idx ON lsif_data_docs_search_private USING GIN (label_reverse_tsv);

Signed-off-by: Stephen Gutekanst [email protected]

Merge request reports