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
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
Columns	Ask only for the columns you use. No `SELECT *` or bare `select()`	Result schema; bytes returned	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
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
Vector search	Use the index's query shape, and let the index catch up	`EXPLAIN` shows `VAST_VEC_INDEX_SCAN`	Vector search
Measure	Time execute and fetch together; record the query ID	Client timings, `EXPLAIN`, `EXPLAIN ANALYZE` in Trino, cluster metrics	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).

Sorted table: for queries that filter on the same one to four columns. Filter on the sort columns (data modeling).
Partitioned table: for filters, or deletes, by a period or a category. Filter on the partition columns, or every partition is scanned (data modeling). The VAST Query Engine can't read partitioned tables in 5.5 (SQL reference).
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). 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. Indexing and data skipping compares the mechanisms, and 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).

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
Trino	`LIKE` pushes down only as `'%substring%'`; `OR` across columns only with `vast.complex_predicate_pushdown`	Trino
Spark	No `LIKE`, `startswith()` or `contains()` push down	Spark
VAST Query Engine	Functions on a column, such as `upper()`, are evaluated by the engine	Profile a query

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).

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, performance). For vector search, don't return the embedding column unless you need it (query design).

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).

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, performance).
Trino: set num_of_splits in the catalog, and run ANALYZE so the connector knows a table's size (performance).
Spark: spark.ndb.num_of_splits and spark.ndb.num_of_sub_splits are connector properties (Spark).
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).

Ask your platform team how many CNodes and virtual IPs serve VAST DataBase (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). There's no GROUP BY: group in Trino, Spark or PyArrow (querying).
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, read path and pruning).

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). Other shapes still run, but compare the query with every row (query design). Until indexing catches up with a load, searches read more rows directly, so measure once it has (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).
Where did the time go? Time the query, including the fetch, and in Trino use EXPLAIN ANALYZE (profile a 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).