---
title: "Optimize queries"
slug: "dev-database-guides-optimize-queries"
updated: 2026-10-30T18:19:11Z
published: 2026-10-30T18:19:11Z
canonical: "docs.vastdata.com/dev-database-guides-optimize-queries"
---

> ## Documentation Index
> Fetch the complete documentation index at: https://docs.vastdata.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Optimize queries

*Applies to VAST Cluster 5.5.*

This page brings together the advice for making VAST DataBase queries cheaper, spread over the guides, concepts and reference pages. Each section says what to do, how to check it, and which page has the detail. It has no examples of its own: the pages it links to do.

## Checklist

| Check | What to do | How to check it | Detail |
| --- | --- | --- | --- |
| Table structure | Sort, partition or add semi-sorted projections to match the columns your queries filter on. Decide before you create the table. | `describe()` in data modeling; `table.sorting_done()` | [Choose the table structure](/develop/docs/dev-database-guides-optimize-queries#choose-the-table-structure) |
| Filters push down | Write each filter as an AND of conditions, each on one column, comparing with literals | SDK: no `NotImplementedError`. Trino, Spark, Query Engine: `EXPLAIN` | [Write filters that push down](/develop/docs/dev-database-guides-optimize-queries#write-filters-that-push-down) |
| Columns | Ask only for the columns you use. No `SELECT *` or bare `select()` | Result schema; bytes returned | [Project only the columns you need](/develop/docs/dev-database-guides-optimize-queries#project-only-the-columns-you-need) |
| Parallelism | Spread splits over several endpoints; analyze tables in Trino | SDK DEBUG logging; Trino split counts | [Splits, sub-splits and endpoints](/develop/docs/dev-database-guides-optimize-queries#splits-sub-splits-and-endpoints) |
| Top-N and limits | On the Query Engine, `ORDER BY` with `LIMIT`. In the SDK, `limit_rows` only for samples | The query runs; the plan has a `TOP_N` step | [Top-N and LIMIT](/develop/docs/dev-database-guides-optimize-queries#top-n-and-limit) |
| Vector search | Use the index's query shape, and let the index catch up | `EXPLAIN` shows `VAST_VEC_INDEX_SCAN` | [Vector search](/develop/docs/dev-database-guides-optimize-queries#vector-search) |
| Measure | Time execute and fetch together; record the query ID | Client timings, `EXPLAIN`, `EXPLAIN ANALYZE` in Trino, cluster metrics | [Check the result](/develop/docs/dev-database-guides-optimize-queries#check-the-result) |

## Choose the table structure

How much of a table a filter reads depends on how its rows are ordered for the filtered column. The cluster skips chunks whose minimum-to-maximum range can't match, so ordered data skips more ([read path and pruning](/develop/docs/dev-database-concepts-architecture-read-path-and-pruning#why-data-order-decides-the-cost)).

- **Sorted table:** for queries that filter on the same one to four columns. Filter on the sort columns ([data modeling](/develop/docs/dev-database-guides-data-modeling#sorted-tables)).
- **Partitioned table:** for filters, or deletes, by a period or a category. Filter on the partition columns, or every partition is scanned ([data modeling](/develop/docs/dev-database-guides-data-modeling#partitioned-tables)). The VAST Query Engine can't read partitioned tables in 5.5 ([SQL reference](/develop/docs/dev-database-reference-sql-not-supported)).
- **Semi-sorted projections:** for filters on different columns in different queries, each reading a few columns. A query uses one only if the projection holds every column it filters on and returns ([projections](/develop/docs/dev-database-guides-projections)). Don't force one by name in production queries: a forced projection leaves out rows it hasn't sorted yet.

Most of these choices are permanent and many can't be combined: see [limits](/develop/docs/dev-database-reference-limits#combining-table-structures). [Indexing and data skipping](/develop/docs/dev-database-guides-indexing-and-data-skipping) compares the mechanisms, and [table structures](/develop/docs/dev-database-concepts-architecture-table-structures) gives each one's costs.

## Write filters that push down

A filter pushes down when it's an AND of conditions, each one about a single column, compared with literal values. What doesn't push down is evaluated by the engine on the rows that come back, or, in the Python SDK, rejected before anything is sent ([pushdowns](/develop/docs/dev-database-reference-pushdowns)).

| Engine | What to know | Detail |
| --- | --- | --- |
| Python SDK | An unsupported predicate, such as an OR across two columns, raises `NotImplementedError`. Rewrite it and finish on the client. | [Querying](/develop/docs/dev-database-guides-querying#rewrite-filters-that-dont-push-down) |
| Trino | `LIKE` pushes down only as `'%substring%'`; `OR` across columns only with `vast.complex_predicate_pushdown` | [Trino](/develop/docs/dev-database-integrations-trino#what-vast-database-does-for-your-query) |
| Spark | No `LIKE`, `startswith()` or `contains()` push down | [Spark](/develop/docs/dev-database-integrations-spark#what-vast-database-does-for-your-query) |
| VAST Query Engine | Functions on a column, such as `upper()`, are evaluated by the engine | [Profile a query](/develop/docs/dev-database-guides-profile-a-query#read-the-plan-with-explain) |

On every engine, avoid functions and arithmetic on the filtered column, comparisons between two columns, and timestamps or numbers stored as strings ([read path and pruning](/develop/docs/dev-database-concepts-architecture-read-path-and-pruning#what-this-means-for-your-queries)).

## Project only the columns you need

Columns you neither filter on nor ask for are never read. In the SDK, pass `columns=`; in SQL, name the columns instead of `SELECT *`. On a test table of a few million rows, reading one column instead of all four took less than half the time, with or without a filter, in the SDK and in the Query Engine ([querying](/develop/docs/dev-database-guides-querying#ask-only-for-the-columns-you-need), [performance](/develop/docs/dev-database-operations-performance#ask-for-less)). For vector search, don't return the embedding column unless you need it ([query design](/develop/docs/dev-vector-search-guides-query-design#project-only-what-you-need)).

## Splits, sub-splits and endpoints

More splits and sub-splits add parallel work, but they don't reduce how much data is read ([read path and pruning](/develop/docs/dev-database-concepts-architecture-read-path-and-pruning#how-a-query-is-divided)).

- **Python SDK:** client parallelism comes from endpoints, not splits: one worker thread per entry in `data_endpoints`. Unless you set `data_endpoints`, the SDK discovers the addresses that serve the database, one per CNode (two on a two-CNode cluster). A small table runs as one split by default, so it uses only one of them; there, `num_splits` above one made a full scan faster, and listing the endpoints several times in `data_endpoints` added little more. Lowering `num_sub_splits` to 1 made it much slower ([querying](/develop/docs/dev-database-guides-querying#tune-a-query-with-queryconfig), [performance](/develop/docs/dev-database-operations-performance#spread-work-across-endpoints)).
- **Trino:** set `num_of_splits` in the catalog, and run `ANALYZE` so the connector knows a table's size ([performance](/develop/docs/dev-database-operations-performance#spread-work-across-endpoints)).
- **Spark:** `spark.ndb.num_of_splits` and `spark.ndb.num_of_sub_splits` are connector properties ([Spark](/develop/docs/dev-database-integrations-spark#connector-properties)).
- **VAST Query Engine:** one connection stays on the address you give it; open several connections over the pool's addresses for concurrent queries ([ADBC for Vector Search](/develop/docs/dev-vector-search-integrations-adbc#queries-per-second)).

Ask your platform team how many CNodes and virtual IPs serve VAST DataBase ([cluster settings to ask about](/develop/docs/dev-database-operations-performance#cluster-settings-to-ask-about)).

## Top-N and LIMIT

- **VAST Query Engine:** `ORDER BY` works only with a `LIMIT`, as a top-N query ([SQL reference](/develop/docs/dev-database-reference-sql-not-supported)). There's no `GROUP BY`: group in Trino, Spark or PyArrow ([querying](/develop/docs/dev-database-guides-querying#vast-query-engine)).
- **Python SDK:** `limit_rows` stops each sub-split once it has found that many rows. It isn't a top-N query, which rows come back isn't defined, and a small limit doesn't make a search for a rare value cheap: `limit_rows=1` on a value with four matches saved little, while on a common value it returned almost at once ([querying](/develop/docs/dev-database-guides-querying#limit-the-rows), [read path and pruning](/develop/docs/dev-database-concepts-architecture-read-path-and-pruning#what-limitrows-does)).

## Vector search

A search uses the vector index only in one shape: the metric's distance function, `ORDER BY` the distance ascending, one sort key, a `LIMIT` and no `OFFSET` ([the vector index](/develop/docs/dev-vector-search-concepts-index#rules-for-a-search-that-uses-the-index)). Other shapes still run, but compare the query with every row ([query design](/develop/docs/dev-vector-search-guides-query-design#queries-that-dont-use-the-index)). Until indexing catches up with a load, searches read more rows directly, so measure once it has ([check the index first](/develop/docs/dev-vector-search-operations-performance#check-the-index-first)).

## Check the result

- **Did the filter push down?** Read the plan: `EXPLAIN` in Trino and the Query Engine, `df.explain()` in Spark ([profile a query](/develop/docs/dev-database-guides-profile-a-query)).
- **Where did the time go?** Time the query, including the fetch, and in Trino use `EXPLAIN ANALYZE` ([profile a query](/develop/docs/dev-database-guides-profile-a-query#time-the-query)).
- **What did the cluster do?** Your platform team can read database metrics for the time the query ran ([what your platform team can see](/develop/docs/dev-database-operations-monitoring-your-workload#what-your-platform-team-can-see)).

## See also

- [Profile a query](/develop/docs/dev-database-guides-profile-a-query)
- [Indexing and data skipping](/develop/docs/dev-database-guides-indexing-and-data-skipping)
- [Querying](/develop/docs/dev-database-guides-querying) and [pushdowns](/develop/docs/dev-database-reference-pushdowns)
- [Performance](/develop/docs/dev-database-operations-performance)