---
title: 'SQL+VS: Integrating Vector Search with SQL'
url: https://www.emergentmind.com/topics/sql-vs-queries
type: topic
---

# SQL+VS: Integrating Vector Search with SQL

SQL+VS queries are composite database workloads in which vector search is embedded inside relational query plans rather than exposed as an isolated nearest-neighbor primitive. In recent systems work, they are treated as **full analytical SQL plans with vector search embedded**, combining semantic retrieval with joins, grouping, sorting, anti/semi joins, and lateral execution [2605.15957]. Adjacent literature addresses the surrounding lifecycle of such queries: natural-language specification, diagrammatic interpretation, semantic equivalence, data generation for non-equivalence witnesses, automated rewriting, and educational use [2510.25997] [2003.07438] [2004.11375] [1802.02229] [2409.18821] [2408.08401].

## 1. Relational–vector composition as a query model

The principal motivation for SQL+VS is that many modern workloads require **semantic retrieval plus structured filtering or aggregation**. Representative examples include finding the most visually similar products and then computing supplier statistics, or finding reviews semantically similar to a complaint and then excluding suppliers associated with them [2605.15957]. This workload model differs from stand-alone vector search because the semantic-search stage is only one operator inside a larger relational plan.

A central systems observation is that relational engines already support rich plan composition, whereas most vector databases expose a narrower API. The resulting design question is therefore not only how to accelerate ANN or ENN, but how to optimize entire plans in which vector search interacts with join shape, post-filters, grouping, and lateral execution [2605.15957]. This suggests that SQL+VS should be understood as a database-engine problem rather than solely an information-retrieval problem.

The same broad pattern appears in text-to-SQL and agentic analytics systems. For realistic spatio-temporal workloads, natural-language interfaces must map vague phrasing to schema terms, generate executable SQL, handle temporal and spatial reasoning, and sometimes select a visualization rather than a raw table as the appropriate output [2510.25997]. A plausible implication is that SQL+VS querying is increasingly part of a larger analytical workflow in which query generation, execution, and presentation are jointly optimized.

## 2. Benchmark structure and representative query patterns

A concrete benchmark formulation is provided by **Vec-H**, which extends TPC-H with vector data by adding `REVIEWS` and `IMAGES` tables, each with an `EMBEDDING` column [2605.15957]. The vector data is derived from the Amazon Reviews dataset, covering **48M unique products**, **571M reviews**, **133M images**, and **34 product categories**. Vec-H creates a one-to-one mapping from TPC-H parts to Amazon products, attaches all reviews and images for that product to the corresponding `PART`, and maps reviews to `CUSTOMER` using Amazon user IDs with random reassignment when needed to preserve TPC-H customer cardinality [2605.15957].

The benchmark uses an approximate embedding-size model

$$
\text{Vec\_bytes} \approx SF \times 200{,}000 \times (\overline{R}\times d_r + \overline{I}\times d_i)\times b
$$

with $\overline{R}\approx 12$ average reviews per product and $\overline{I}\approx 4$ average images per product [2605.15957]. For $SF=1$, the reported instantiation uses **Qwen 0.6B** text embeddings for reviews with $d=1024$ and size about **9.8 GB**, and **SigLIP2** image embeddings with $d=1152$ and size about **3.4 GB**, yielding a **Rel:VS ratio of about 1:12** [2605.15957].

The query templates are organized around where vector search enters the plan.

| Pattern family | Example queries | Characteristic role |
|---|---|---|
| **VS@Start** | Q2, Q16, Q19 | Vector search drives the plan |
| **VS@Mid** | Q10, Q13, Q18 | Vector search enriches SQL output |
| **VS@End** | Q11, Q15 | SQL produces vectors used by later search |

These templates collectively cover **five VS integration patterns**: **inner**, **left**, **lateral**, **anti**, and **semi** joins [2605.15957]. That is significant because it moves beyond the common “filter + ANN” abstraction and treats vector search as an operator that participates in optimizer-visible join structure.

Because the vector component can be approximate, evaluation is performed at the **query level** rather than solely at the operator level. For ordinary queries the metric is recall, defined as the fraction of true output rows returned by ANN. For **Q19**, which returns a scalar revenue sum, the paper uses relative error,

$$
\mathit{rel\_err} = \frac{|\mathit{rev}_{\text{ANN}} - \mathit{rev}_{\text{ENN}}|}{\mathit{rev}_{\text{ENN}}}
$$

with a target of at least **95% recall** per query and at most **1% relative error** for Q19 [2605.15957]. Some queries require oversampling because post-filters can discard vector-search hits; on GPU, FAISS top-$k$ limits become a constraint for workloads such as Q15 [2605.15957].

## 3. Operator semantics and execution architecture

The execution substrate described for SQL+VS is **MaxVec**, an extension of the heterogeneous query engine **Maximus** [2605.15957]. Operator placement is explicit and heterogeneous, with operators independently assigned to CPU or GPU through names such as `cpu::acero::filter` and `gpu::cudf::hash_join`. MaxVec promotes vector search to a **first-class operator**, backed by **FAISS** on CPU and **FAISS/cuVS** on GPU [2605.15957].

The logical vector-search operator is binary:

$$
\text{vector\_search(query vectors, embedding column, k)}
$$

One input supplies the query-vector side and the other supplies the embedding corpus [2605.15957]. When the query side has cardinality 1, this behaves as ordinary top-$k$ search; with batched query vectors, it behaves as a similarity join. This operatorization is important because it lets vector search participate in plan construction as a relationally composable node rather than as an external RPC.

Embeddings are stored using `embeddings_type`, implemented as `arrow::ListType` on CPU and `cudf::ListDType` on GPU, with contiguous storage intended to enable zero-copy interoperability with FAISS where possible [2605.15957]. Physically, the operator has two ports. The **data port** is always blocking because the full search corpus must be materialized before search begins. The **query port** is streaming on CPU but blocking/batched on GPU, where query vectors are accumulated so the search can be expressed as a large GEMM over an $N \times d \times M$ workload [2605.15957]. This batching behavior is central to GPU utilization.

The architecture supports both **ENN** and **ANN**. ANN uses IVF or graph indexes, while ENN performs exact exhaustive search [2605.15957]. The physical deployment space includes all-CPU execution, all-GPU execution, and heterogeneous strategies in which vector search and relational operators are placed differently; it also distinguishes whether data and indexes reside initially in host or device memory, and whether GPU execution must copy the index per query [2605.15957].

## 4. CPU/GPU tradeoffs and index organization

A major empirical result is that, for SQL+VS workloads, **the relational components benefit much more from running on the GPU than the vector search part** [2605.15957]. The paper quantifies this with the share of total speedup attributable to relational acceleration,

$$
\mathrm{share}_{\mathrm{rel}} = 
\frac{\mathrm{rel}_{\mathrm{CPU}}-\mathrm{rel}_{\mathrm{GPU}}}
{\mathrm{total}_{\mathrm{CPU}}-\mathrm{total}_{\mathrm{GPU}}}
$$

and reports median relational shares of **87%** for CAGRA, **77%** for IVF1024, **84%** for IVF4096, and **44%** for ENN [2605.15957]. Under ANN, relational operators on CPU were reported as **12× to 150× slower** than the vector-search portion for several queries [2605.15957].

The most important negative result concerns conventional **data-owning vector indexes**. When the index stores the embeddings themselves, moving the index to GPU is often prohibitively expensive; for ANN, index transfer frequently accounts for **95%+** of wall time [2605.15957]. Faster interconnects help but do not remove the bottleneck. Even under **NVLink-C2C**, transfer and transformation costs can dominate when the index is data-owning [2605.15957]. ENN behaves differently because its higher compute intensity amortizes transfer better, and GPU ENN on NVLink is reported as **4×–19× faster** than CPU [2605.15957].

To address this, the paper introduces a **non-data-owning vector index**. In this organization, the index stores only the search structure—such as IVF centroids or graph structure—while the embeddings remain in the base table [2605.15957]. The design is compared to a secondary B-tree index that points to table rows rather than duplicating them. On coherent NVLink-C2C systems with ATS, the GPU can access host memory directly, so only the compact search structure must be resident or transferred [2605.15957].

Three transfer optimizations are reported: **pinning**, **caching**, and **host-residency** [2605.15957]. Their measured effect is large. IVF1024H metadata transfer is reduced from about **1266 ms** to **4 ms**, and CAGRAC+H metadata transfer from about **112 ms** to **0.8 ms** [2605.15957]. For CAGRA, total transfer improves from **853 ms** to **425 ms** with caching alone, from **578 ms** to **187 ms** with pinning on PCIe, and from **112 ms** to **27.4 ms** on NVLink with caching plus pinning [2605.15957]. The paper also identifies a structural inefficiency in IVF movement: **5121** `cudaMemcpy` calls for IVF1024 and **20481** for IVF4096, driving effective throughput below **2% of theoretical bandwidth** [2605.15957].

The practical recommendations are correspondingly conditional. If all data and the index fit on GPU, **use `gpu`**. If only the index fits, use **`gpu-i` for IVF** and **`hybrid` for CAGRA/HNSW**. If neither fits, **`hybrid`** is usually best, while `copy-i` can be an alternative for IVF at large batch sizes [2605.15957]. On unified-memory systems such as **DGX Spark**, where placement asymmetry largely disappears, GPU execution wins consistently for both ENN and ANN [2605.15957].

## 5. Query specification, visualization, and agentic orchestration

SQL+VS workloads also raise an interface problem: composite analytical queries are difficult to specify directly. One response is **dual-specification query synthesis**, implemented in **Duoquest**, which combines a natural-language query with an optional **table sketch query (TSQ)** [2003.07438]. The TSQ contains optional type annotations, optional example tuples, a sorting flag, and a limit value, and supports partial tuples, exact cells, empty cells, and range cells [2003.07438]. Synthesis is performed by **guided partial query enumeration (GPQE)**, a best-first search over partial SQL programs with early pruning by TSQ verification. In user studies, Duoquest achieved **85.9%** success compared with **23.4%** for the NLI baseline, a **62.5% absolute increase** [2003.07438]. On Spider, reported top-1 accuracy was **63.5%** for Duoquest versus **30.2%** on dev and **31.2%** on test for the NLI baseline [2003.07438].

Interpretation of complex SQL is addressed by **QueryVis**, which renders SQL queries as logic-based diagrams grounded in FOL and tuple relational calculus [2004.11375]. Tables appear as composite marks, joins as lines, predicates as labels, and quantifiers such as `∃`, `¬∃`, and `∀` as bounding boxes [2004.11375]. The supported fragment includes `SELECT ... FROM ... WHERE ...`, conjunctions, join predicates, `[NOT] EXISTS`, `[NOT] IN`, and `CO {ALL | ANY}(Q)`, under **set semantics**, **two-valued logic**, **no NULLs**, **no aggregates**, and **no disjunctions** in the main formal development [2004.11375]. A within-subjects study with **42** participants reported that, for the 9 non-grouping questions, **QV vs SQL** was about **20% faster** with **$p < 0.001$**, while error reductions were described as weak evidence [2004.11375].

At the execution end, agentic systems can transform natural-language requests into SQL plus a downstream presentation choice. A Mistral-based ReAct agent orchestrated with **LangGraph** has been evaluated on **35** spatio-temporal questions over NYC and Tokyo check-in data, using tools for schema inspection, SQL generation, SQL execution, plotting, and mapping [2510.25997]. The agentic pipeline achieved **91.4%** accuracy versus **28.6%** for a naive baseline using the same SQL generator, with large gains on temporal, spatial, multi-step, external-knowledge, and cross-dataset categories [2510.25997]. The system can select line plots for temporal queries, bar charts for categorical summaries, maps or heatmaps for spatial queries, and then produce a concise natural-language explanation [2510.25997]. Although this work is not framed as vector search, it addresses the same orchestration problem: SQL generation is only one stage in an end-to-end analytical workflow.

## 6. Semantic equivalence, testing, and rewrite verification

Optimization of SQL+VS workloads inherits the classical SQL problem that rewrites must preserve semantics. One formal foundation is **U-semiring**, introduced for deciding semantic equivalences of SQL queries involving bags, sets, `DISTINCT`, subqueries, views, indexes, keys, and foreign keys [1802.02229]. Relations are modeled as functions $R : Tuple(\sigma) \to K$, and the formalism augments semiring semantics with unbounded summation, a squash operator for duplicate elimination, and a negation-like operator for `NOT EXISTS` [1802.02229]. Key proof principles include equality substitution,

$$
\sum_t [t=e]\times f(t)=f(e),
$$

and key constraints expressed equationally as

$$
[t.k=t'.k]\times R(t)\times R(t') = [t=t']\times R(t).
$$

The implementation, in **Lean**, translates SQL to U-expressions, rewrites them into **SPNF**, canonizes them under constraints, and compares normalized terms [1802.02229]. The paper reports formal verification of **39** query rewrite rules drawn from classical work and real SQL engines [1802.02229].

For query testing and non-equivalence witnesses, a recent SMT-based approach represents base and intermediate results as symbolic tables with a count attribute `CNT` for bag semantics [2409.18821]. The system preprocesses and decorrelates subqueries, translates the query into constraints over base tables and result tables, solves them with **Z3**, and materializes concrete witness datasets [2409.18821]. It handles joins, outer joins, semijoins, anti-semijoins, aggregation, `HAVING`, `DISTINCT`, set operators, nested and correlated subqueries, and NULL-aware `IN` and `NOT IN`, while explicitly noting that it does **not** support full SQL three-valued logic because `unknown` is not modeled [2409.18821]. On the **XDataBM** benchmark of **84 original queries** and **407 non-equivalent mutants**, the new system killed **378/407** mutants, compared with **230/407** for VeriEQL and **244/407** for XDataO [2409.18821]. For correlated WHERE-clause subqueries, it reports **79/80** killed mutants versus **0** for VeriEQL [2409.18821].

LLM-based rewrite systems add a further layer of optimization methodology. **QUITE** is a training-free, feedback-aware SQL query rewrite system organized as a multi-agent workflow under an FSM with **Reasoning**, **Verification**, **Decision**, and **Termination** stages [2506.07675]. It uses real-time DB feedback such as **EXPLAIN**, syntax errors, equivalence checks via **SQLSolver**, execution metrics, and plan characteristics, and augments rewriting with a knowledge base, a hybrid SQL corrector, an agent memory buffer, and fine-grained hint injection through **pg_hint_plan** [2506.07675]. Reported results include up to **35.8%** execution-time reduction over prior approaches and **24.1%** more rewrites, with equivalence rates of **100%** on TPC-H, **96.8%** on DSB, and **98.3%** on Calcite [2506.07675]. This suggests that feedback-aware rewriting is becoming relevant wherever composite SQL plans are too diverse for fixed algebraic rule sets alone.

## 7. Null semantics, grading, and educational use

Formal SQL semantics remain relevant because composite queries are still interpreted under the core language. One line of work argues that SQL with nulls can be given an equally expressive **two-valued** semantics by conflating `unknown` with `false` during evaluation rather than only at the end of `WHERE` processing [2012.13198]. Under the proposed semantics, comparisons become

$$
{t \,\omega\, t'}_{D,\eta} :=
\begin{cases}
\text{true} & \text{if } t_\eta,t'_\eta \ne NULL \text{ and } t_\eta \,\omega\, t'_\eta \\
\text{false} & \text{otherwise}
\end{cases}
$$

and the paper proves that the two-valued semantics captures standard SQL efficiently for the SQL 1999 core, including `SELECT-FROM-WHERE`, `GROUP BY`, `HAVING`, subqueries, `IN / EXISTS / ANY / ALL`, set operations, and recursion [2012.13198]. It also reports that **120** of **121** benchmark queries across TPC-H and TPC-DS coincide under 2VL and 3VL, with only **TPC-H Q16** failing the sufficient condition [2012.13198].

In educational settings, the challenge is often not binary correctness but graded semantic proximity. A graph-based method for automated linear SQL grading models queries as nodes in an implicit graph and edits as weighted transitions, defining distance as the minimum-cost edit path

$$
d(q_s, q_t) = \min_{\text{paths } p:q_s \to q_t} \sum_{e \in p} w(e)
$$

[2403.14441]. The prototype, implemented in **TypeScript**, includes **181** edits, of which **94** are atomic [2403.14441]. In a survey over **11 scenarios** with **20** participants, the approach obtained mean fairness $\bar{f}=0.8473$ and comprehensibility $\bar{c}=0.8293$, compared with $\bar{f}=0.2186$ and $\bar{c}=0.4940$ for dynamic analysis, and $\bar{f}=0.8892$ and $\bar{c}=0.8743$ for manual grading [2403.14441]. The reported interpretation is that it approaches manual grading quality while remaining more explainable than purely dynamic methods.

Direct evidence on how learners seek help when writing SQL is provided by a randomized interview study in a database classroom [2408.08401]. In a **12-week introductory database systems course** with **226** students, **39** volunteered for a study comparing **web search**, **ChatGPT**, and an **instructor-tuned LLM** for two SQL-writing tasks over Zoom [2408.08401]. The strongest quantitative effect concerned interaction volume: a Kruskal-Wallis test found $\chi^2(2)=20.5$, $p<0.0001$, and post-hoc Dunn tests showed that the instructor-tuned LLM elicited more than twice as many interactions as ChatGPT and web search [2408.08401]. The number of edits to the final query did not differ significantly, with $\chi^2(2)=1.55$, $p=0.46$, and final query quality also showed no significant condition effect, $\chi^2(2)=3.85$, $p=0.14$, although correctness was directionally higher for the LLM conditions [2408.08401]. Mental demand was likewise non-significant overall, $\chi^2(2)=2.05$, $p=0.36$, but descriptively lower for the instructor-tuned LLM [2408.08401]. These findings position LLMs not merely as answer engines but as pedagogically structured supports for iterative SQL problem solving.

Taken together, the literature presents SQL+VS querying as both a systems problem and a workflow problem. At the systems level, the dominant issues are operator composition, approximate result quality, heterogeneous execution, and index organization. At the workflow level, the central concerns are specification, interpretability, semantic verification, rewrite safety, grading, and help-seeking. This suggests that the long-term development of SQL+VS will depend not only on faster ANN kernels or better GPU placement, but also on robust formalisms and interfaces for expressing, understanding, and validating increasingly composite analytical queries.

Source: https://www.emergentmind.com/topics/sql-vs-queries