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

# Vectorized Query Execution

export const InternalLink = ({version, path = "", children, ...props}) => {
  let detectedVersion = version || "stable";
  if (typeof window !== 'undefined' && !version) {
    const match = window.location.pathname.match(/\/docs\/([^/]+)/);
    if (match) {
      detectedVersion = match[1];
    }
  }
  const normalizedPath = path.startsWith("/") ? path.slice(1) : path;
  return <a href={`/docs/${detectedVersion}/${normalizedPath}`} {...props}>
      {children}
    </a>;
};

CockroachDB supports [column-oriented](https://wikipedia.org/wiki/Column-oriented_DBMS#Column-oriented_systems) ("vectorized") query execution on all <InternalLink path="data-types">CockroachDB data types</InternalLink>.

Many SQL databases execute [query plans](https://wikipedia.org/wiki/Query_plan) one row of table data at a time. Row-oriented execution models can offer good performance for [online transaction processing (OLTP)](https://wikipedia.org/wiki/Online_transaction_processing) queries, but suboptimal performance for [online analytical processing (OLAP)](https://wikipedia.org/wiki/Online_analytical_processing) queries. The CockroachDB vectorized execution engine dramatically improves performance over [row-oriented execution](https://wikipedia.org/wiki/Column-oriented_DBMS#Row-oriented_systems) by processing each component of a query plan on type-specific batches of column data.

## Configure vectorized execution

By default, vectorized execution is enabled in CockroachDB.

You can configure vectorized execution with the `vectorize` <InternalLink path="set-vars">session variable</InternalLink>. The following options are supported:

| Option | Description                                                                    |
| ------ | ------------------------------------------------------------------------------ |
| `on`   | Turns on vectorized execution for all queries.     **Default:** `vectorize=on` |
| `off`  | Turns off vectorized execution for all queries.                                |

For information about setting session variables, see <InternalLink path="set-vars">`SET session variable`</InternalLink>.

<Tip>
  To see if CockroachDB will use the vectorized execution engine for a query, run a simple
  <InternalLink path="explain">`EXPLAIN`</InternalLink> statement on the query. If `vectorize` is `true`, the query will be executed with the
  vectorized engine. If it is `false`, the row-oriented execution engine is used instead.
</Tip>

## How vectorized execution works

When you issue a query, the gateway node (i.e., the node from which you issue the query) <InternalLink path="architecture/sql-layer#sql-parser-planner-executor">parses the query and creates a physical plan</InternalLink> for execution on each node that receives the plan. If vectorized execution is enabled, the physical plan is sent to each node to be executed by the vectorized execution engine.

To see a detailed view of the vectorized execution plan for a query, run the <InternalLink path="explain#vec-option">`EXPLAIN(VEC)`</InternalLink> statement on the query.

For information about vectorized execution in the context of the CockroachDB architecture, see <InternalLink path="architecture/sql-layer#query-execution">Query Execution</InternalLink>.

For detailed examples of vectorized query execution for hash and merge joins, see the blog posts [40x faster hash joiner with vectorized execution](https://www.cockroachlabs.com/blog/vectorized-hash-joiner) and [Vectorizing the merge joiner in CockroachDB](https://www.cockroachlabs.com/blog/vectorizing-the-merge-joiner-in-cockroachdb).

## Disk-spilling operations

The following disk-spilling operations require [memory buffering](https://wikipedia.org/wiki/Data_buffer) during execution. If there is not enough memory allocated for a disk-spilling operation, CockroachDB will spill the intermediate execution results to disk.

* Global <InternalLink path="order-by">sorts</InternalLink>
* <InternalLink path="order-by">Unordered aggregations</InternalLink>
* <InternalLink path="joins#hash-joins">Hash joins</InternalLink>
* <InternalLink path="joins#merge-joins">Merge joins</InternalLink> on non-unique columns. Merge joins on columns that are guaranteed to have one row per value, also known as "key columns", can execute entirely in-memory.
* <InternalLink path="window-functions">Window functions</InternalLink>.

By default, the memory limit allocated per disk-spilling operation is `64MiB`. This limit applies to a single operation within a single query, and is configured with the `sql.distsql.temp_storage.workmem` <InternalLink path="cluster-settings">cluster setting</InternalLink>.

To increase the limit, change the cluster setting:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SET CLUSTER SETTING sql.distsql.temp_storage.workmem = '100MiB';
```

<Note>
  Operations that do not support disk spilling ignore the `sql.distsql.temp_storage.workmem` limit.
</Note>

The <InternalLink path="cockroach-start#general">`--max-disk-temp-storage` flag</InternalLink> sets the maximum on-disk storage capacity for disk spilling. If the maximum on-disk storage capacity is reached, the query will return an error during execution.

You can also configure a node's total budget for in-memory query processing with the <InternalLink path="cockroach-start#general">`--max-sql-memory` flag</InternalLink> at node startup. This limit applies globally to all sessions at any point in time. When this limit is exceeded by an operation, it will result in an error instead of spilling to disk. For more details on `--max-sql-memory`, see <InternalLink path="recommended-production-settings#cache-and-sql-memory-size">Cache and SQL memory size</InternalLink>.

## Known limitations

* The vectorized engine does not support queries containing a join filtered with an <InternalLink path="joins#supported-join-conditions">`ON` expression</InternalLink>.
* The vectorized engine does not support <InternalLink path="export-spatial-data">working with spatial data</InternalLink>. Queries with <InternalLink path="functions-and-operators#spatial-functions">geospatial functions</InternalLink> or <InternalLink path="export-spatial-data">spatial data</InternalLink> will revert to the row-oriented execution engine.

## See also

* <InternalLink path="architecture/sql-layer">SQL Layer</InternalLink>
* <InternalLink path="set-vars">`SET session variable`</InternalLink>
* <InternalLink path="show-vars">`SHOW session variable`</InternalLink>