ILIKE on a trigram-indexed column was spending ~40ms in the query planner for every ~3ms of execution. The fix was two lines of SQL and one line of Python — but finding it required understanding what the planner actually does with ILIKE.
Search results need two distinct scoring concerns: which printing of a card to surface, and which cards to rank first. Both are numeric ORDER BY expressions baked into the card rows.
The magic.cards table carries indexes of every type PostgreSQL offers. A tour of trigram GIN for substring search, GIN for JSONB containment, B-tree for numerics, hash for exact-match fields, and an expression index that unlocks a color identity query that otherwise requires a full table scan.
Switching from row-by-row inserts to PostgreSQL’s COPY protocol meaningfully cut import time. Why COPY is fast, how to stream data into it from Python, and two approaches to expanding a JSON blob into a typed row.
A search endpoint needs paginated results and a total count. A single CTE with UNION ALL answers both — and for deduplicated searches it runs in roughly half the time. For non-deduplicated searches it matches two separate queries while using one round trip.
Each AST node emits a SQL fragment and bound parameters. How the node hierarchy works, how different field types generate different SQL, and why user input never touches the query string. Covers text/regex/arithmetic; JSONB operators and the count+results CTE are in separate posts.
Motivation for building Sylvan Librarian: owning the query language, and the killer feature Scryfall can’t do — arithmetic comparisons across card attributes.