The 14-second card page
A BinderDex card detail page was taking 14+ seconds to load on hot cards. Not sometimes. Every time, under normal evening traffic. The kind of latency where you start wondering if the whole service is down.
The culprit was a price-history query that looked perfectly reasonable in code review. It selected the latest graded-price rows for a card, ordered by source priority (a specific data source first, everything else second) and then by recency. Returns maybe a hundred rows. How bad could it be?
Bad. The sort used ORDER BY CASE WHEN source = 'primary' THEN 0 ELSE 1 END, and no index on Earth can serve that expression. Postgres had to read every single graded-price row for the card (18,787 on the hottest one), sort them in memory, and then throw away 99.5% of them to return the top 100.
Why CASE in ORDER BY is a trap
The instinct is reasonable: you want certain rows to appear first in the result, so you assign them a lower sort value. The problem is that CASE produces a computed column. Postgres can't walk an index to satisfy a computed sort key (unless you build a very specific expression index, which has its own maintenance cost). So it falls back to a sequential scan plus an in-memory sort of the full result set.
For a card with 200 graded-price rows, you'"'d never notice. For a card with 19k rows, you're reading 22 GB of table data to return 100 rows. Under I/O pressure from other queries, that's 14 seconds of wall time.
The fix: two index-served scans instead of one unindexable sort
The sort had two purposes: (1) put the primary source rows first, (2) within each group, order newest-first. Both of those can be served by the existing (card_id, recorded_at) index if you split the query into two scans.
First scan: get the top N the primary source rows, newest-first, using a backward index scan. Second scan: if you still need more rows to fill the limit, get the top (remaining) non-the primary source rows the same way. Concatenate. Done.
Here's the rewritten query shape (simplified, names changed):
// Scan 1: priority source, newest first\nconst priorityRows = await db.select(cols)\n .from(table)\n .where(and(...filters, eq(source, "priority")))\n .orderBy(desc(recordedAt))\n .limit(100);\n\n// Scan 2: everything else, only if we need more\nconst remaining = 100 - priorityRows.length;\nconst otherRows = remaining > 0\n ? await db.select(cols)\n .from(table)\n .where(and(...filters, sql\`${source} is distinct from 'priority'\`))\n .orderBy(desc(recordedAt))\n .limit(remaining)\n : [];\n\nconst result = [...priorityRows, ...otherRows];The IS DISTINCT FROM keeps NULL-source rows in the second branch, matching the old CASE's else-arm semantics. IS DISTINCT FROM is the NULL-safe inequality in Postgres; != would drop NULLs entirely and silently change the result set.
Result: 563 rows touched instead of 18,787. Cold query time dropped from 14.6 seconds to 49 milliseconds on the same saturated volume. Same 100 rows returned (99 of 100 identical IDs; the one difference was an arbitrary pick among 8 rows tied at the boundary timestamp, within the old query's own nondeterminism).
The broader campaign
This was step one of a performance push that ran through the night. The graded-price query was the worst offender, but the same table had a deeper problem: 67 million rows across two price-history tables, growing daily, with a legacy index diet of 13 and 11 indexes per table (most with zero lifetime scans). The fix was a partition migration kit that converted both tables to monthly range partitions on recorded_at, managed by pg_partman with a sub-second rename swap and a row-parity abort gate.
On the caching side, anonymous API GETs now hit CloudFront with viewer cookies stripped before the origin (auth'd routes pass through untouched with Cache-Control: private, no-store stamped by middleware as a second defense layer). The app cache backend moved from a hosted REST cache to an in-VPC Valkey instance, cutting cache GET p50 from a network round-trip to 1.3ms.
What I'd tell you to take away
If you're writing ORDER BY CASE on a table that grows, run EXPLAIN and look for a sequential scan. The sort key looks innocent in code but forces a full scan the moment the table has real volume. Splitting it into N filtered queries that each walk an existing index is almost always cheaper, and the result is identical.
The general pattern: if a sort encodes priority between groups, ask whether you can express that priority as separate filtered queries instead. Each one gets its own index-served LIMIT. You trade one query for two (or N), but each touches a few hundred rows instead of tens of thousands. On a hot path, that's 300x.