57fbc1e0b4
* perf(literaryworks): cut Postgres load on ebook↔audiobook auto-linking Rescans and candidate matching were driving high Postgres CPU on book libraries. - AutoLinkContent now checks literary_work_items with a cheap indexed lookup before GetMatchItem, so unchanged already-linked books skip the heavy triple-lateral query on every rescan. - ListMatchCandidates is split into an indexable ID-selection phase (title / provider EXISTS / series EXISTS) and a hydration phase, so the per-row lateral aggregates run only for the LIMIT candidates kept rather than for every opposite-format book. - Add a partial LOWER(title) index scoped to ebook/audiobook so the OR of title/provider/series filters can be driven entirely by indexes. Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com> * fix(migrations): drop invalid leftover index before concurrent rebuild An interrupted CREATE INDEX CONCURRENTLY (cancel/restart) can leave an INVALID idx_media_items_books_title_lower; IF NOT EXISTS would then skip the rebuild while Goose records success. Add the preflight invalid-index drop used by the repo's other concurrent-index migrations. Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com> --------- Co-authored-by: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
39 lines
1.4 KiB
SQL
39 lines
1.4 KiB
SQL
-- +goose NO TRANSACTION
|
|
|
|
-- +goose Up
|
|
-- literaryworks.ListMatchCandidates matches ebooks against audiobooks (and vice
|
|
-- versa) by exact case-insensitive title. Combined with the provider-id and
|
|
-- series EXISTS predicates it forms an OR, so the planner can only bitmap-OR the
|
|
-- whole filter through indexes when every branch is indexable; without a
|
|
-- LOWER(title) index the title branch forces a full media_items scan and negates
|
|
-- the provider/series indexes. Scoped to books so it stays small on large
|
|
-- movie/show catalogs.
|
|
--
|
|
-- Preflight: a canceled build or server restart can leave an INVALID index of
|
|
-- this name. IF NOT EXISTS would then skip the rebuild while Goose records
|
|
-- success, so drop any invalid leftover before retrying.
|
|
-- +goose StatementBegin
|
|
DO $$
|
|
BEGIN
|
|
IF EXISTS (
|
|
SELECT 1
|
|
FROM pg_class c
|
|
JOIN pg_namespace n ON n.oid = c.relnamespace
|
|
JOIN pg_index i ON i.indexrelid = c.oid
|
|
WHERE n.nspname = 'public'
|
|
AND c.relname = 'idx_media_items_books_title_lower'
|
|
AND NOT i.indisvalid
|
|
) THEN
|
|
DROP INDEX public.idx_media_items_books_title_lower;
|
|
END IF;
|
|
END;
|
|
$$;
|
|
-- +goose StatementEnd
|
|
|
|
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_media_items_books_title_lower
|
|
ON media_items (LOWER(title))
|
|
WHERE type IN ('ebook', 'audiobook');
|
|
|
|
-- +goose Down
|
|
DROP INDEX CONCURRENTLY IF EXISTS idx_media_items_books_title_lower;
|