Every text-to-SQL prompt carries a schema. Send all of it and you pay for every table on every call. Send a few and you risk leaving out the one the question needs. So how you pick the tables matters.
I tested four ways on the same questions, with no model calls at all:
- Full schema: no picking.
- BM25: keyword search over each table's definition.
-
Embeddings:
all-MiniLM-L6-v2similarity, the usual way LangChain, LlamaIndex or Vanna pick tables from a vector store. - schemagate, the open-source library I maintain, using the same MiniLM model.
Each method got the same number of tables: 12 on Spider 2.0's big BigQuery and Snowflake warehouses, 6 on BIRD.
| Warehouses: all needed tables picked | Warehouses: tokens | BIRD: all needed tables picked | BIRD: tokens | |
|---|---|---|---|---|
| Full schema | 93.8% | 56,237 | 100% | 579 |
| BM25 | 78.7% | 1,513 | 81.0% | 407 |
| Embeddings | 78.2% | 1,555 | 89.4% | 438 |
| schemagate | 85.3% | 1,677 | 98.7% | 468 |
On the same question, schemagate had every needed table where embeddings missed 17 times on the warehouses and was beaten once. On BIRD, 151 to 9.
What it costs: 8–11% more tokens than plain retrieval, mostly the tables needed to join the ones it picked. That join step is a big part of why it finds more.
What I didn't test: selectors that ask an LLM to pick tables. They cost a model call per question; this comparison is the free tier.
The script and the full results are in this pull request, if you want to run it on your own questions: https://github.com/ashishsinha1602/schemagate/pull/189
Top comments (0)