Liangzu Liu, Yiyan Wang, Yinjun Wu, Runze Su, Zhuo Chang, Peizhi Wu, Jianjun Chen, Fuxin Jiang, Rui Shi, Bin Cui, Tieying Zhang
9 min
Abstract
Cardinality estimation (CardEst) still remains a challenging problem for DBMS. Recent years have witnessed the success of ML-based cardinality estimators in outperforming traditional methods. However, these solutions suffer from poor generalizability to new data or query distribution, inability to handle complex queries, and substantial data preparation overhead, thus preventing their wide adoption in the real-world DBMS. Some recent efforts have been dedicated to addressing some but not all of these issues. We notice that the recent emerging Large Language Models (LLMs) have shown their remarkable generalizability to unseen tasks, capabilities to understand complex programs, and power to perform data-efficient fine-tuning. In light of this, we propose to leverage LLMs to mitigate the above issues. Specifically, we carefully craft prompts, and subsequently perform fine-tuning and self-correction during inference with LLMs for CardEst task. We then extensively evaluate LLMs' in-distribution and out-of-distribution generalizability, feasibility to support complex queries, and training data efficiency during fine-tuning LLMs on pre-training datasets. The results suggest that LLMs outperform the state-of-the-art in almost all settings, thus indicating their potential for the CardEst task. We further measure the end-to-end query execution time in DBMS by using the estimated cardinalities of LLMs in some practical settings, which suggests that the inference overhead of LLMs can be outweighed by the benefits brought by LLMs for CardEst.
Sam: The model builds the number one digit at a time, like spelling out a word letter by letter. It predicts the first digit based on the prompt, adds it back in, predicts the next, and stops when it hits a special end signal. This ensures it handles any size number without special training for huge ones. During training, they adjust the model to favor the exact sequence of digits from real answers, step by step.
Alex: That sidesteps just dumping a whole number, which might confuse it. What if it still goes off track?
Sam: That's where self-correction kicks in. If the model's number strays too far from the guiding estimates—beyond a tuned threshold—they feed the mismatch back into the prompt as feedback, letting the model refine itself in a few rounds. The paper suggests this cuts errors notably on tough cases, without needing extra data.
Alex: How did these models stack up against the usual tools on standard tests?
Sam: The study tested them on four common datasets with straightforward queries involving selections, projections, and joins—basic operations like picking rows, choosing columns, and linking tables. Fine-tuned versions of the language models beat all compared methods across nearly every measure, especially on the toughest cases. For instance, on one dataset, they cut errors by up to 74 percent compared to the prior best approach.
Alex: That's a clear edge on hard ones. Did they need way more training data to pull that off?
Sam: No, quite the opposite. They first pre-fine-tuned on 26 varied datasets using just 8000 queries each—a one-time step done offline, taking about 12 hours total. That alone let the model handle unseen databases better than many baselines, without any extra work per database. Adapting to a specific one took only 50 to 60 minutes, on par with or faster than most others.
Alex: Even the pre-tuned version generalizes to new data without tweaking. Does that hold when queries change in ways the training didn't cover?
Sam: Yes, they checked shifts to new query patterns, like training on simple joins under three tables but testing on those with more. The models still outperformed baselines, reducing errors up to 76 percent on the hardest shifted cases versus the next best.
Alex: Those database twists—like data changing over time—sound tricky for any model. How do the language models handle actual updates to the data itself, say adding or changing rows?
Sam: Real databases often change data with operations that add new rows, remove some, or edit existing ones—think of it as constantly rewriting parts of your spreadsheet while still answering questions about it. The study tests this with write-intensive workloads on the STATS dataset, creating insert-heavy ones with more adds and update-heavy with more edits. Fine-tuned language models beat baselines like Neurocard or MSCN, cutting errors up to 20 percent at the 95th percentile on insert-heavy cases.
Alex: Why does the pre-fine-tuned version struggle more here than on static data?
Sam: The initial pre-fine-tuning used only read-only queries, missing the patterns of changes like inserts or deletes. Fine-tuning directly on mixed workloads fixes that, outperforming the pre-fine-tuned one even after extra steps.
Alex: Baking in changes early helps. What about when the data's shape shifts entirely, like getting more uneven?
Sam: They tested on TPC-H-Skew, where data bunches up unnaturally compared to even training sets, mimicking real evolution. Pre-fine-tuned models matched or beat PG and PRICE at median and 90th percentile errors, holding steady on extremes thanks to prompts blending baseline guesses.
Alex: And for totally new databases never seen in training?
Sam: Applied straight after pre-fine-tuning to unseen instances like IMDB, they reduced errors up to 67 percent at the 90th percentile versus PG and PRICE.
Alex: One more thing—those complex queries we touched on earlier, like pattern matching or uniques?
Sam: For LIKE queries—searching strings with wildcards, like partial names—they beat LPLM by up to 43 percent at median error on DBLP, even on rare patterns missing from top stats, using internal query knowledge from training. DISTINCT ones, counting unique values per column, topped EstNDV; JOB's real-world multi-join messes dropped errors versus ASM. Handles what specialized tools can't, natively.
Alex: What if you strip away parts of the setup—does that reveal why the language models work so well mechanically?
Sam: Researchers tested that with an ablation study—basically, turning off one piece at a time to see its role, like removing ingredients from a recipe to check what each adds. One key test swapped the digit-by-digit prediction for a simple added layer that guesses numbers directly from query patterns. That failed badly, with errors over 200 times worse, because it couldn't link query details tightly to actual row counts.
Alex: The step-by-step number building aligns the model's understanding better with the data. Does model size matter much here?
Sam: Larger models within the same family did show steady gains as size grew from hundreds of millions to billions of parameters—think of parameters as the adjustable knobs that let the model grasp nuances. This stems from stronger grasp of query logic and data patterns.
Alex: Those checks show the pieces fit tightly. In full database runs, does it speed up actual queries?
Sam: End-to-end tests injected estimates into Postgres to measure full execution time on sub-parts of queries. They used it selectively on high-cost sub-queries—ones the optimizer flags as potentially slow—falling back to basics for quick ones. This sped plans notably where baselines faltered on expensive joins. Pre-fine-tuned models slashed execution time versus baselines like PG, PRICE, and ASM on most workloads. Fine-tuned ones trimmed total time up to 33 percent on several datasets. The selective approach cut inference overhead by about two-thirds while keeping execution gains.
Alex: It balances the slowdown from running the models. Doesn't the extra thinking time still add up?
Sam: The models' inference latency is higher, so full use exceeds baselines on some datasets like IMDB. Self-correction helps: 85 percent of queries need zero iterations, and more rounds trade added inference for bigger execution savings. Deploying locally on one GPU keeps costs low.
Alex: A solid, balanced look at where language models fit in database optimization. The paper shows LLMs bringing versatility across shifts, complex patterns, and low-data setups, with end-to-end wins outweighing overhead via smart selection. Still, accelerating inference or diverse pre-fine-tuning for writes remains key for broader use. This positions them as a practical step toward more adaptive query planning. Thanks for listening to ResearchPod.