Missing index on `pages(updated_at DESC)` — "list pages" query takes 14.6s on 31k rows

[notjbg]

Environment

• gbrain 0.10.1
• Postgres engine on Supabase (pooler on :6543, us-west-1)
• 31,242 pages in public.pages
• Observed via extensions.pg_stat_statements

Symptom

SELECT * FROM pages ORDER BY updated_at DESC LIMIT $1 OFFSET $2
-- calls: 144, total_exec_time: 2,103,652 ms, mean: 14,608.69 ms/call

14.6 seconds per call on a modest 31k-row table, consuming ~35 minutes of cumulative wall time just on this one query.

EXPLAIN before adding an index: sequential scan + external sort on updated_at. Since pages is 48 MB heap + 133 MB indexes/toast, the sort spills to disk every call.

Root cause

src/schema.sql:24-26,201 creates these indexes on pages:

CREATE INDEX IF NOT EXISTS idx_pages_type       ON pages(type);
CREATE INDEX IF NOT EXISTS idx_pages_frontmatter ON pages USING GIN(frontmatter);
CREATE INDEX IF NOT EXISTS idx_pages_trgm      ON pages USING GIN(title gin_trgm_ops);
CREATE INDEX IF NOT EXISTS idx_pages_search    ON pages USING GIN(search_vector);

…but no covering index on updated_at. The "list pages newest-first" path (e.g. autopilot's health/dashboard queries, any listPages({ orderBy: 'updated_at' })) has nothing to fall back to.

Fix

Add to src/schema.sql:

CREATE INDEX IF NOT EXISTS idx_pages_updated_at_desc ON pages (updated_at DESC);

After applying locally via execute_sql:

EXPLAIN: Index Scan using idx_pages_updated_at_desc on pages
         (Total Cost: 6693, Limit cost: 11 — was sort over full heap)

Estimated ~1000× speedup on that query.

Related: while you're in there, idx_pages_frontmatter and idx_pages_trgm both show 0 scans in pg_stat_user_indexes on this brain — they may be candidates for removal from schema.sql if usage data on other installs agrees. Not part of this issue.