Usage-based ranking for SQL completion (local, no AI service)

[tessarotto21]

Problem

SQL completion proposals are ordered by match score and then alphabetically. On large databases the table you
use every day is somewhere in a list of thousands, and when the tables live in another schema you have to type
the schema name first every time. The editor does not learn anything from how you work.

Proposal

Rank completion proposals by what the user actually uses on each connection, IntelliSense style, but fully local
and lightweight: no LLM, no network, no extra database queries.

What it learns from

• Queries the user writes in the editor (no need to execute them), once the cursor leaves the statement
• Executed queries (failed ones too: the intent is the same)
• Proposals chosen in the completion popup (count immediately)

What it uses to rank

• Frecency of tables, columns and schemas (decayed counts, 7-day half-life)
• Tables used together (after FROM orders JOIN, the tables joined with orders before come first)
• A small trigram model over the token stream (keywords and object keys)
• A tiny linear ranker with non-negative weights, trained on popup choices

Usage only breaks ties: it never moves a worse text match above a better one, and never-used objects keep the
current order.

What changes in the popup

• After FROM / JOIN : the schema used most comes first (unless it is the default one), then the 5 recently
  used tables already qualified (e.g. sales.orders o), one Enter away.
• Tables and columns used more rank higher among equal matches (e.g. after t.).

Storage and cost

• One small gzipped JSON file per connection in the workspace .metadata, pruned to its most used entries. It
  stores object names and keywords only, never literals or query results.
• Ranking 16,000 proposals takes about 2 ms.
• Option in Preferences → Editors → SQL Editor → Code Completion ("Show tables and columns you use most first").

Benchmark (synthetic)

Offline benchmark on a synthetic ERP-like schema (480 tables, 8,350 columns) and simulated users writing queries for
30 days. Each query is evaluated before the model learns it. Keystrokes = letters typed until the wanted item is
first, plus Enter.

Scenario	Keystrokes saved	Top-1 after 1 letter	MRR, empty popup
Focused user (2 modules)	53.3% → 67.0%	52.1% → 66.9%	0.158 → 0.440
Switches module mid-way	50.1% → 65.2%	48.1% → 66.0%	0.149 → 0.426
Broad user (60% anywhere)	52.2% → 56.2%	46.0% → 51.4%	0.155 → 0.248
Cold start (first 2 days)	51.2% → 58.6%	51.2% → 61.7%	0.168 → 0.311
Small schema (40 tables)	52.7% → 68.8%	47.4% → 71.5%	0.195 → 0.501
Flat preferences	51.9% → 59.6%	48.2% → 57.5%	0.166 → 0.324

• JOIN table MRR: 0.006 → 0.578; ON column MRR: 0.154 → 0.803 (focused user).
• Tables in another schema (59 schemas, Oracle-like): 13.8 → 7.4 keystrokes per table for the focused user;
  better than today in every scenario.
• Cost: objects never used before move below used ones (−1.7 to −4.5 points of keystrokes saved for them only);
  the net result is positive in every scenario.

Smoke-tested in the app on Oracle, PostgreSQL, SQL Server and SQLite (synthetic databases).

Questions

1. Does this fit the direction of the completion engine? It hooks into SQLQueryCompletionAnalyzer and the
   proposal comparator, so it only applies to the new engine (the default mode).
2. On by default, or opt-in first?
3. The change is about 2k lines including tests. I can split it into: (a) the usage model with unit tests,
   (b) completion integration, (c) learning from the editor. Would that be easier to review?

I have a working branch and can open the PRs if the approach makes sense.

This proposal and the code were prepared with AI assistance (Claude Code) and reviewed by me.