本页尚未翻译

本页内容仍为英文原文，源自官方文档 basics/sql。中文翻译正在分批进行中。

SQL

Query LanceDB Enterprise with SQL from Python or an Arrow Flight SQL client.

Use SQL in LanceDB Enterprise to filter records, join tables, calculate aggregates, and prepare datasets. SQL queries work with the same Lance tables you create and update through the SDKs, without exporting the data to another system.

This guide starts with when to reach for SQL instead of SDK table operations, then covers the three ways to run SQL, connection settings, a first query, parameterized queries, and how to read and manage the results.

Choose SQL or SDK table operations

An SDK gives you a programming-language-specific API for invoking LanceDB's functionality: create a table, insert rows, build an index, run a vector search, each its own method call. Every one of those functionalities also has a corresponding SQL operation. SQL covers a wider surface than any one SDK method and lets you chain operations together in a single statement -- running a vector search and then joining the results with another table, for example -- where SDK methods stay separate calls.

Use SQL for joins, aggregations, window functions, analytical queries, and whenever you want to chain operations together. Use SDK table operations to build applications around table management, ingestion, filtering, and search, including applications that run with LanceDB Embedded, where SQL is not available.

You can combine them. For example, create and populate a table with an SDK, then query it with SQL to prepare a training dataset. In Python, both workflows can use the same Enterprise connection.

Both interfaces work with the same Lance tables, through the same Arrow schema. Applications in different languages can work with the same tables: connect to the same database and namespace, use the appropriate credentials, and account for read consistency when another process writes data. A fixed-size vector column stores vectors of a consistent dimension; see the data types reference for the supported element types and other column types.

Common SQL operations

Several tasks are available through both interfaces, but the methods and SQL statements do not have identical coverage. SDK support also differs by language. These SQL reference pages describe the supported statements:

Task	SQL reference
Create a table	CREATE TABLE
Insert or change rows	INSERT, UPDATE, DELETE
Build an index	CREATE INDEX
Join and aggregate data	SELECT
Save a query as a view	CREATE VIEW

Computed columns store SQL expressions whose values are populated by a refresh. They are separate from registered Python Functions. See the computed-column and materialized-view guides for their SDK and SQL operations and Enterprise setup requirements.

Access LanceDB with SQL

There are three ways to run SQL against LanceDB Enterprise, all against the same Arrow Flight SQL endpoint, returning results as Arrow record batches:

1. db.execute_query() on the Python SDK's Enterprise connection, shown below, or Connection::execute_query_async() on the Rust SDK's. These are LanceDB's native SQL execution methods, and we recommend them for the best performance -- see Adaptive query execution.
2. An Arrow Flight SQL client. Use this from TypeScript, whose LanceDB SDK does not expose a SQL execution method, or from any tool with its own Flight SQL integration. A LanceDB-specific SDK is not required to submit SQL.
3. The LanceDB CLI. Open an interactive SQL shell, or run a statement or file, directly from the command line with no code at all.

The SQL reference shows a query from Python. See SDKs for the LanceDB language clients.

Adaptive query execution

A standard Flight SQL client calls GetFlightInfo to plan a query and then DoGet to fetch rows -- two round trips every time, even when the server could stream results back immediately.

execute_query() skips that extra round trip when it can. It calls DoGet directly for a query with no parameters, or DoExchange when binding parameters, and sends a lancedb-sql-protocol: adaptive-v1 header. The server decides per query whether to stream results on that same call or defer execution and return a poll ticket; when it defers, execute_query() follows up with PollFlightInfo automatically, with no extra code on your side. A generic Flight SQL client does not send that header, so it always takes the slower GetFlightInfo path.

Connection settings

Get the following values from your Enterprise administrator:

• Database URI, such as db://your-database.
• API key and region.
• API endpoint for host_override.
• Flight SQL endpoint for sql_host_override, including its scheme and port.

The Flight SQL endpoint must be enabled and reachable from your client. Pass it explicitly as sql_host_override in Python, as in the example below. The API and SQL endpoints may use different hosts or ports.

See Connect to Enterprise for other connection options.

SQL runs in Enterprise. Connections to a local directory or an object-storage bucket use LanceDB Embedded and cannot execute SQL statements. Use SDK table operations to query those tables.

Run a query in Python

Install the SDK with pip install lancedb. Replace the connection placeholders with your Enterprise settings, including the region and both endpoint URLs. This example reads an existing frames table, with a video_id and a caption column describing each frame. Change the query to match your table.

db = lancedb.connect(
    "db://your-database",
    api_key="your-api-key",
    region="us-east-1",
    host_override="https://your-api-endpoint",
    sql_host_override="https://your-sql-endpoint:443",
)

reader = db.execute_query("SELECT frame_id, video_id, caption FROM frames LIMIT 10")
table = reader.read_all()
print(table.to_pylist())

In application code, load credentials from environment variables or a secrets manager. The connection guide shows that setup.

execute_query() returns a blocking Arrow reader; see Adaptive query execution for how it starts streaming. The example above uses read_all() to collect the small result in memory. For larger results, or to track and cancel a running query, see Read and manage results. To submit a query and check its status separately, use execute_query_async().

Parameters in SQL queries

Pass a parameters argument to bind values into a query, instead of formatting them into the SQL string:

import numpy as np

reader = db.execute_query(
    "SELECT id FROM docs ORDER BY distance(vector, $1) LIMIT $2",
    parameters=[np.array([0.1, 0.2, 0.3], dtype=np.float32), 10],
)

Values travel as Arrow, not interpolated SQL text, so a vector binds as a fixed-size list with no manual formatting and no injection risk. Reference a parameter with $1, $2, ... for positional binding, or $name for named binding:

• A list or tuple binds positionally, in order, to $1, $2, ...
• A dict binds by key to $name.
• A one-row pyarrow.RecordBatch or Table binds its columns, by name and by position.
• A 1-D NumPy or Arrow array becomes a single fixed-size-list value -- use this form for a vector argument, as shown above.

说明

parameters is a LanceDB-specific mechanism, separate from the Flight SQL protocol's PREPARE/bind step, which a deployment does not implement; see Prepared statements below. Bind values with parameters from the Python SDK; a generic Flight SQL client has no equivalent.

Prepared statements

A Flight SQL client can prepare a statement before it runs it. A deployment accepts that, but it keeps no plan between runs and does not accept the parameter values described above at this protocol layer.

注意

There is no SQL PREPARE / EXECUTE here. Preparation is part of the Arrow Flight SQL protocol, so you reach it through your client rather than by sending a statement. A script containing PREPARE name AS SELECT ... will not run.

A Flight SQL client takes these steps, whatever it calls them:

1. Prepare: send the SQL and get back a handle and the result schema. The statement is parsed and planned at this point, so a syntax or name error surfaces here rather than at execution.
2. Execute: run the handle and read the result. The deployment plans the statement again each time it runs, so reusing a handle does not save planning time.
3. Close: release the handle.

A client that binds values to placeholders such as $1 and $2 at this protocol layer gets a "not implemented" error when it executes the statement. Use parameters above instead; do not put untrusted input directly into prepared statement text, since quoting it does not protect the statement the way binding a parameter would.

Prepare a statement when your client requires it, or to get a statement's result schema and catch syntax and name errors without running it. Preparing does not make a repeated statement faster. To give a reusable query a name, create a view. To store its result for later queries, create a materialized view.

Read and manage results

• Collect the results when you want a list, Arrow table or DataFrame that fits in memory. Use a result limit and select only the columns you need.
• Read batches when you want to process a large result incrementally. Reading batches avoids loading the entire result into application memory. It does not guarantee that the query can return its first batch immediately.

Asynchronous clients

An async client lets your application do other work while a request is waiting. That is separate from whether you collect results or read them in batches. Python offers synchronous and asynchronous clients; TypeScript uses async APIs. Check the SDK reference for the methods available in your client.

Query handles and background jobs

An Enterprise SQL query handle lets you inspect query execution and request cancellation. Background operations such as index builds return a job handle instead. Use the API for the returned handle to check status and retrieve results.

Query plans

Use query plans to understand how LanceDB reads data, applies filters and uses indexes. explain_plan() shows the planned operations. analyze_plan() runs the query and adds measurements, including execution time and rows processed.

Inspect a plan or measure execution

Operation	Python / Rust	TypeScript
Inspect the plan without executing the query	explain_plan()	explainPlan()
Execute the query and report runtime measurements	analyze_plan()	analyzePlan()

Use the first to inspect query structure and the second to investigate work actually performed. Operator names and available measurements can differ between LanceDB Embedded and LanceDB Enterprise. SQL provides EXPLAIN and EXPLAIN ANALYZE.

Explain and analyze a query in Python, TypeScript or Rust

These examples use three sample records and hand-written vectors, so no embedding service is needed. Each program creates a temporary local table and removes it when finished.

• Python: Install with pip install lancedb, save as query_plan.py, then run python query_plan.py.
• TypeScript: Install with npm install @lancedb/lancedb, save as query_plan.mts, then run node query_plan.mts with Node.js 22.18 or later.
• Rust: Download and extract the complete Rust examples, then run cargo run --locked --bin query_plan from the extracted directory.

table = db.create_table(
    "documents",
    [
        {
            "id": 1,
            "text": "A puppy playing in the park",
            "category": "animals",
            "vector": [1.0, 0.0],
        },
        {
            "id": 2,
            "text": "A dog resting at home",
            "category": "animals",
            "vector": [0.9, 0.1],
        },
        {
            "id": 3,
            "text": "A guide to growing tomatoes",
            "category": "gardening",
            "vector": [0.0, 1.0],
        },
    ],
)
query = table.search([1.0, 0.0]).select(["id", "text", "_distance"]).limit(2)
print("Plan:", query.explain_plan())
print("Measured execution:", query.analyze_plan())
rows = query.to_list()
print(rows)

const lancedb = await import("@lancedb/lancedb");
const { mkdtemp, rm } = await import("node:fs/promises");
const { tmpdir } = await import("node:os");
const { join } = await import("node:path");
const uri = await mkdtemp(join(tmpdir(), "lancedb-search-"));
try {
  const db = await lancedb.connect(uri);
  const table = await db.createTable("documents", [
    {
      id: 1,
      text: "A puppy playing in the park",
      category: "animals",
      vector: [1.0, 0.0],
    },
    {
      id: 2,
      text: "A dog resting at home",
      category: "animals",
      vector: [0.9, 0.1],
    },
    {
      id: 3,
      text: "A guide to growing tomatoes",
      category: "gardening",
      vector: [0.0, 1.0],
    },
  ]);
  const query = table
    .vectorSearch([1.0, 0.0])
    .select(["id", "text", "_distance"])
    .limit(2);
  console.log("Plan:", await query.explainPlan());
  console.log("Measured execution:", await query.analyzePlan());
  const rows = await query.toArray();
  console.log(rows);

} finally {
  await rm(uri, { recursive: true, force: true });
}

use arrow_array::types::Float32Type;
use arrow_array::{FixedSizeListArray, Int64Array, RecordBatch, RecordBatchIterator, StringArray};
use arrow_schema::{DataType, Field, Schema};
use futures::TryStreamExt;
use lancedb::query::{ExecutableQuery, QueryBase, Select};
use std::sync::Arc;

#[tokio::main]
async fn main() -> Result<(), Box<dyn std::error::Error>> {
    let directory = tempfile::tempdir()?;
    let db = lancedb::connect(directory.path().to_str().unwrap())
        .execute()
        .await?;
    let schema = Arc::new(Schema::new(vec![
        Field::new("id", DataType::Int64, false),
        Field::new("text", DataType::Utf8, false),
        Field::new("category", DataType::Utf8, false),
        Field::new(
            "vector",
            DataType::FixedSizeList(Arc::new(Field::new("item", DataType::Float32, true)), 2),
            true,
        ),
    ]));
    let batch = RecordBatch::try_new(
        schema.clone(),
        vec![
            Arc::new(Int64Array::from(vec![1, 2, 3])),
            Arc::new(StringArray::from(vec![
                "A puppy playing in the park",
                "A dog resting at home",
                "A guide to growing tomatoes",
            ])),
            Arc::new(StringArray::from(vec!["animals", "animals", "gardening"])),
            Arc::new(
                FixedSizeListArray::from_iter_primitive::<Float32Type, _, _>(
                    [[1.0, 0.0], [0.9, 0.1], [0.0, 1.0]]
                        .into_iter()
                        .map(|v| Some(v.into_iter().map(Some).collect::<Vec<_>>())),
                    2,
                ),
            ),
        ],
    )?;
    let reader = RecordBatchIterator::new(vec![Ok(batch)].into_iter(), schema);
    let table = db
        .create_table(
            "documents",
            Box::new(reader) as Box<dyn arrow_array::RecordBatchReader + Send>,
        )
        .execute()
        .await?;
    let query = table
        .vector_search(&[1.0_f32, 0.0])?
        .select(Select::columns(&["id", "text", "_distance"]))
        .limit(2);
    println!("Plan: {}", query.explain_plan(false).await?);
    println!("Measured execution: {}", query.analyze_plan().await?);
    let rows: Vec<RecordBatch> = query.execute().await?.try_collect().await?;
    println!("{rows:?}");

    Ok(())
}

This tiny table demonstrates the API, not a performance benchmark. The plan uses a scan because the example has no vector index. The following larger example illustrates how to read scans, filters and index operations.

Read scans, filters and result retrieval

The recorded example below uses 1.2 million rows sampled from the Wikipedia dataset. The counts describe that run. The table initially has no indexes, so the plan shows a scan. This Enterprise plan includes RemoteTake and hides storage paths as uri=***. A local plan shows the dataset path instead, and fetches the result rows with Lance's Take or, depending on the library version, a second LanceRead whose source is stream(_rowid).

Let's examine a vector search query that:

• Filters rows where identifier is between 0 and 1,000,000
• Returns the top 100 matches
• Projects specific columns: chunk_index, title, and identifier

# explain_plan
query_explain_plan = (
    table.search(query_embed)
    .where("identifier > 0 AND identifier < 1000000")
    .select(["chunk_index", "title", "identifier"])
    .limit(100)
    .explain_plan(True)
)

// explain_plan
const explainPlan = await table
    .search(queryEmbed)
    .where("identifier > 0 AND identifier < 1000000")
    .select(["chunk_index", "title", "identifier"])
    .limit(100)
    .explainPlan(true);

Execution Plan Components

Read the recorded vector plan operator by operator

The execution plan reveals the sequence of operations performed to execute your query. Let's examine each component:

ProjectionExec: expr=[chunk_index@4 as chunk_index, title@5 as title, identifier@1 as identifier, _distance@3 as _distance]
  RemoteTake: columns="vector, identifier, _rowid, _distance, chunk_index, title"
    CoalesceBatchesExec: target_batch_size=1024
      GlobalLimitExec: skip=0, fetch=100
        FilterExec: _distance@3 IS NOT NULL
          SortExec: TopK(fetch=100), expr=[_distance@3 ASC NULLS LAST], preserve_partitioning=[false]
            KNNVectorDistance: metric=l2
              FilterExec: identifier@1 > 0 AND identifier@1 < 1000000
                LanceScan: uri=***, projection=[vector, identifier], row_id=true, row_addr=false, ordered=false

1. Base Layer (LanceScan)

• Initial data scan loading only specified columns to minimize I/O
• Unordered scan enabling parallel processing

LanceScan: 
- projection=[vector, identifier]
- row_id=true, row_addr=false, ordered=false

2. First Filter

• Apply requested filter on identifier column
• Reduces the number of vectors that need KNN computation

FilterExec: identifier@1 > 0 AND identifier@1 < 1000000

3. Vector Search

• Computes squared L2 distances between query vector and all vectors that passed the filter

KNNVectorDistance: metric=l2

4. Results Processing

• Filters out null distance results
• Sorts by distance and takes top 100 results
• Coalesces output into batches targeting 1,024 rows in this plan

SortExec: TopK(fetch=100)
- expr=[_distance@3 ASC NULLS LAST]
- preserve_partitioning=[false]
FilterExec: _distance@3 IS NOT NULL
GlobalLimitExec: skip=0, fetch=100
CoalesceBatchesExec: target_batch_size=1024

5. Data Retrieval

• RemoteTake is the deployment's operator for this step; a LanceDB Embedded plan shows Lance's Take or a LanceRead over the selected row IDs, depending on the library version
• Handles efficient data retrieval from remote storage locations
• Fetches specific rows and columns needed for the final output
• Optimizes network bandwidth by only retrieving required data

RemoteTake: columns="vector, identifier, _rowid, _distance, chunk_index, title"

6. Final Output

• Returns only requested columns and maintains column ordering

ProjectionExec: expr=[chunk_index@4 as chunk_index, title@5 as title, identifier@1 as identifier, _distance@3 as _distance]

This plan demonstrates a basic search without index optimizations: it performs a full scan and filter before vector search.

Non-vector queries

Query plans also cover filters and full-text search. Python can show both sub-plans of a hybrid query. Check where a filter runs and how many rows flow through each operator; a separate FilterExec does not by itself establish that the plan is inefficient or that an index is missing.

FTS queries

plan = (
    table.search("puppy", query_type="fts")
    .where("category = 'animals'", prefilter=True)
    .limit(10)
    .explain_plan(True)
)
print(plan)

const plan = await table
    .query()
    .fullTextSearch("puppy")
    .where("category = 'animals'")
    .limit(10)
    .explainPlan(true);

Look for the text-index operation and any scan of unindexed rows. A scan can be needed to include records written after the index was built. Use index coverage and execution measurements to determine whether that extra work matters. Check filter placement separately from text-index use.

Hybrid queries

For hybrid queries, explain_plan returns the reranker label followed by the vector and FTS sub-plans, indented for readability:

plan = (
    table.search("flower moon", query_type="hybrid")
    .where("category = 'film'", prefilter=True)
    .limit(10)
    .explain_plan(True)
)
print(plan)
# RRFReranker(...)
#   <vector sub-plan>
#   <FTS sub-plan>

analyze_plan executes both sub-queries and labels them Vector Search Plan: and FTS Search Plan:. Compare their filter placement and execution measurements.

analyze_plan executes the query and returns the physical plan annotated with runtime metrics such as elapsed time, rows produced, bytes read and index comparisons. Where explain_plan tells you what a query intends to do, analysis shows the work performed.

说明

The SQL equivalent is EXPLAIN ANALYZE.

Read execution measurements

Executes the query and provides detailed runtime metrics, including:

• Operation duration (_elapsed_compute_)
• Data processing statistics (_output_rows_, _bytes_read_)
• Index effectiveness (_index_comparisons_, _indices_loaded_)
• Resource utilization (_iops_, _requests_)

Measure the example query

Let's use analyze_plan to run the query and analyze the query performance, which will help us identify potential bottlenecks:

# analyze_plan
query_analyze_plan = (
    table.search(query_embed)
    .where("identifier > 0 AND identifier < 1000000")
    .select(["chunk_index", "title", "identifier"])
    .limit(100)
    .analyze_plan()
)

// analyze_plan
const analyzePlan = await table
    .search(queryEmbed)
    .where("identifier > 0 AND identifier < 1000000")
    .select(["chunk_index", "title", "identifier"])
    .limit(100)
    .analyzePlan();

Performance Metrics Analysis

Inspect the recorded execution measurements

ProjectionExec: expr=[chunk_index@4 as chunk_index, title@5 as title, identifier@1 as identifier, _distance@3 as _distance], metrics=[output_rows=100, elapsed_compute=1.424µs]
    RemoteTake: columns="vector, identifier, _rowid, _distance, chunk_index, title", metrics=[output_rows=100, elapsed_compute=175.53097ms, output_batches=1, remote_takes=100]
      CoalesceBatchesExec: target_batch_size=1024, metrics=[output_rows=100, elapsed_compute=2.748µs]
        GlobalLimitExec: skip=0, fetch=100, metrics=[output_rows=100, elapsed_compute=1.819µs]
          FilterExec: _distance@3 IS NOT NULL, metrics=[output_rows=100, elapsed_compute=10.275µs]
            SortExec: TopK(fetch=100), expr=[_distance@3 ASC NULLS LAST], preserve_partitioning=[false], metrics=[output_rows=100, elapsed_compute=39.259451ms, row_replacements=546]
              KNNVectorDistance: metric=l2, metrics=[output_rows=1099508, elapsed_compute=56.783526ms, output_batches=1076]
                FilterExec: identifier@1 > 0 AND identifier@1 < 1000000, metrics=[output_rows=1099508, elapsed_compute=17.136819ms]
                  LanceScan: uri=***, projection=[vector, identifier], row_id=true, row_addr=false, ordered=false, metrics=[output_rows=1200000, elapsed_compute=21.348178ms, bytes_read=1852931072, iops=78, requests=78]

1. Data Loading (LanceScan)

• Scanned 1,200,000 rows from the LanceDB table
• Read 1.86GB of data in 78 I/O operations
• Only loaded necessary columns (vector and identifier)
• Unordered scan for parallel processing

2. Filtering and Search

• Applied prefilter condition (identifier > 0 AND identifier < 1000000)
• Reduced dataset from 1.2M to 1,099,508 rows
• KNN search used L2 (Euclidean) distance metric
• Vector comparisons processed in 1076 batches

3. Results Processing

• KNN results sorted by distance (TopK with fetch=100)
• Null distances filtered out
• Batches coalesced to target size of 1024 rows
• Additional columns fetched for final results
• Remote take operation for 100 results
• Final projection of required columns

Distributed metrics on LanceDB Enterprise

Enterprise

When you call analyze_plan against a LanceDB Enterprise deployment that distributes its queries across a pool of workers, the query runs on those workers. By default the returned plan aggregates each operator's metrics into a single value, matching the single-node output of LanceDB Embedded. Pass a distributed_metrics mode when you need to see how work was split across workers:

Mode	What it shows	When to use it
aggregate	One summary value per operator (default). Same shape as Embedded analyze_plan.	Everyday performance checks and comparisons against an Embedded plan.
per_worker	Metrics reported separately for each worker that participated in the query.	Diagnosing stragglers or uneven work distribution across the cluster.
full	Both the aggregate summary and the per-worker breakdown.	Deep investigations where you need the totals and the per-worker detail in a single output.

Python exposes this option through distributed_metrics. In TypeScript, pass the same mode to analyzePlan() as its distributedMetrics argument. Python queries on LanceDB Embedded ignore the distributed mode and return single-node measurements.

# Show a per-worker breakdown for a query on LanceDB Enterprise
plan = (
    table.search(query_embed)
    .where("identifier > 0 AND identifier < 1000000")
    .limit(100)
    .analyze_plan(distributed_metrics="per_worker")
)
print(plan)

说明

Use per_worker or full sparingly; the plan output grows with the size of your worker pool. Stick with the default aggregate mode for routine tuning.

Identify expensive operations

• The query computes distances for 1,099,508 vectors; inspect timings to assess its contribution to latency
• The scan reads 1.86 GB in this recorded run
• Full table scan due to lack of indices
• Compare this scan with an indexed plan using the same query

Compare the query after adding indexes

Compare the recorded indexed and unindexed plans

The recorded query after adding vector and scalar indexes produced this plan:

ProjectionExec: expr=[chunk_index@3 as chunk_index, title@4 as title, identifier@2 as identifier, _distance@0 as _distance]
  RemoteTake: columns="_distance, _rowid, identifier, chunk_index, title"
    CoalesceBatchesExec: target_batch_size=1024
      GlobalLimitExec: skip=0, fetch=100
        SortExec: TopK(fetch=100), expr=[_distance@0 ASC NULLS LAST], preserve_partitioning=[false]
          ANNSubIndex: name=vector_idx, k=100, deltas=1
            ANNIvfPartition: uuid=83916fd5-fc45-4977-bad9-1f0737539bb9, nprobes=20, deltas=1
            ScalarIndexQuery: query=AND(identifier > 0,identifier < 1000000)

Optimized Plan Analysis

1. Scalar Index Query

ScalarIndexQuery: query=AND(identifier > 0,identifier < 1000000)
metrics=[
    output_rows=2
    index_comparisons=2,301,824
    indices_loaded=2
    output_batches=1
    parts_loaded=562
    elapsed_compute=86.979354ms
]

• Range filter using scalar index
• Only 2 index files and 562 scalar index parts loaded
• 2.3M index comparisons for matches

2. Vector Search

ANNSubIndex: name=vector_idx, k=100, deltas=1
metrics=[
    output_rows=2,000
    index_comparisons=25,893
    indices_loaded=0
    output_batches=20
    parts_loaded=20
    elapsed_compute=111.849043ms
]

• IVF index with 20 probes
• Only 20 index parts loaded
• 25,893 vector comparisons
• 2,000 matching vectors

3. Results Processing

SortExec: TopK(fetch=100), expr=[_distance@0 ASC NULLS LAST], preserve_partitioning=[false]
GlobalLimitExec: skip=0, fetch=100
CoalesceBatchesExec: target_batch_size=1024

• Sorts by distance
• Limits to top 100 results
• Batches into groups of 1024

4. Data Fetching

RemoteTake: columns="_distance, _rowid, identifier, chunk_index, title"
metrics=[output_rows=100, elapsed_compute=113.491859ms, output_batches=1, remote_takes=100]

• Single output batch
• One remote take per row

5. Final Projection

ProjectionExec: expr=[chunk_index@3 as chunk_index, title@4 as title, identifier@2 as identifier, _distance@0 as _distance]

• Returns specified columns: chunk_index, title, identifier, and distance

Performance Improvements

1. Initial Data Access

ScalarIndexQuery metrics:
- indices_loaded=2
- parts_loaded=562
- output_batches=1

• Before: Full table scan of 1.2M rows, 1.86GB data
• After: Only 2 indices and 562 scalar index parts loaded
• Benefit: Eliminated table scans for prefilter

2. Vector Search Efficiency

ANNSubIndex:
- index_comparisons=25,893
- indices_loaded=0
- parts_loaded=20
- output_batches=20

• Before: L2 calculations on 1,099,508 vectors
• After:
  • About 97.6% fewer vector comparisons (25,893 versus 1,099,508)
  • Decreased output batches from 1,076 to 20

3. Data Retrieval Optimization

RemoteTake:
- remote_takes=100
- output_batches=1

• RemoteTake operation remains consistent

Choose a change to measure

1. Index Implementation

When to Create Indices

• Columns used in WHERE clauses
• Vector columns for similarity searches
• Join columns used in merge_insert

Index Type Selection

Data Type	Recommended Index	Use Case
Vector	IVF_PQ/IVF_HNSW_SQ/IVF_HNSW_FLAT	Approximate nearest neighbor search
Scalar	B-Tree	Equality and range filters
Categorical	Bitmap	Multi-value filters and set operations
List	Label_list	Multi-label classification and filtering

2. Query Plan Optimization

Common Patterns and Fixes

Plan Pattern	Optimization
Scan with high bytes read or I/O count	Check index coverage and whether the query can use an index
	Use select() to limit returned columns
	Check whether the dataset has been compacted
Multiple filters	Check their placement and row counts; compare the plan after changing the query

说明

Regularly analyze your query plans to identify and address performance bottlenecks. The analyze_plan output provides detailed metrics to guide optimization efforts.

3. Getting Started with Optimization

Keep the query, data and result limit fixed while changing one factor. Compare rows processed, bytes read, timings and returned results. For ANN changes, measure recall too: doing less work is useful only if retrieval quality meets your application's needs. See vector indexes and scalar indexes for index choices.

Vector and full-text search

SQL vector search support depends on the Enterprise server version and query form. Versions that support ORDER BY distance(vector, query_vector) LIMIT k over a table scan can plan indexed or flat vector search. Check that your server version supports this form, then use EXPLAIN to inspect the query plan.

The SDK vector search API is also available in LanceDB Enterprise and LanceDB Embedded. Use it when you prefer the SDK query builder or need to search embedded tables.

For full-text search in SQL, use the fts() table function or the contains_tokens predicate.

SQL syntax and API references

The SQL reference documents statement syntax, supported expressions, and data types. For SDK methods and their arguments, use the Python, TypeScript, or Rust API reference.

SQL ReferenceStatement syntax, expressions, and data types for LanceDB SQL.

Next steps

QuickstartRun a complete create, insert and search example.SearchChoose vector, full-text or hybrid retrieval.FilteringNarrow results with predicates.SDKsCompare language clients and find their API references.