CTE reuse

CTE reuse optimizes queries by identifying common table expressions and subqueries that appear multiple times within a query and executing them only once.

When you enable CTE reuse, Starburst Enterprise platform (SEP) executes matching query fragments once, saves the intermediate results, and reuses them at different points in the query plan. CTE reuse also detects similar but non-identical query fragments.

CTE reuse operates within the scope of a single SQL query. Results are not reused across different queries.

Note

CTE reuse is a best effort optimization and Starburst does not guarantee that it will merge and reuse all table scans, common blocks, or subqueries for every query.

Supported connectors and operators

The following object storage connectors support CTE reuse:

• Great Lakes connector

• Iceberg connector

• Hive connector

• Delta Lake connector

The following non-object storage connectors support CTE reuse:

• ClickHouse connector

• Starburst Greenplum connector

• Starburst IBM Db2 connector

• MySQL connector

• Starburst Netezza connector

• Oracle connector

• PostgreSQL connector

• Redshift connector

• Starburst SAP HANA connector

• SingleStore connector

• SQL Server connector

• Starburst Teradata connectors

• Starburst Synapse connector

• Vertica connector

CTE reuse supports most basic query operators, including:

• Table scans, including filters and projections

• Aggregations

• Joins

• Unions

Configuration

To enable CTE reuse globally, add the following property to your coordinator’s config.properties file:

optimizer.reuse-common-subqueries=true

To enable CTE reuse on a per-query basis, use the reuse_common_subqueries session property:

SET SESSION reuse_common_subqueries = true;

Exchange manager

CTE reuse requires an exchange manager to transfer intermediate results between query stages. Use one of the following options:

• Filesystem exchange (only when used with FTE mode)

• Embedded buffer service

Embedded buffer service

The embedded buffer service runs within your SEP cluster and temporarily stores intermediate query results in memory. When the buffer service runs out of memory, it writes data to object storage.

Enable the embedded buffer service

To enable the embedded buffer service, add the following property to the config.properties file on all nodes, including the coordinator:

embedded-buffer-service-enabled=true

Configure the exchange manager to use the embedded buffer service by adding the following properties to the exchange-manager.properties file on all nodes:

exchange-manager.name=buffer
exchange.use-embedded-buffer-service=true

Configure buffer service memory

To control how much memory the buffer service uses, configure the following property in the config.properties files on worker nodes:

Embedded buffer service memory configuration properties

Property name	Description	Default
buffer.memory.base	The total memory assigned to the buffer service subprocess as a fraction of heap memory. Do not set higher than 30%.	10%

Configure chunks spooling

When the embedded buffer service runs out of memory, it writes data to an external object storage location. Configure this location in the config.properties on coordinator and worker nodes using the buffer.spooling.directory and the appropriate storage-specific properties.

The buffer service connects to object storage using the NATIVE storage driver by default. To use the Trino native file system instead, set buffer.spooling.storage-driver=TRINO_FS.

Note

Ensure all nodes (both coordinator and workers) have direct read access to the spooling location. Workers inherit the object storage configuration from the embedded buffer service.

AWS S3

To configure AWS S3 as the spooling location, use the following configuration properties:

S3 spooling configuration properties

Property name	Description
buffer.spooling.directory	The object storage location for spooled data. Use an s3:// URI.
buffer.spooling.s3.aws-access-key	The access key for the S3 API.
buffer.spooling.s3.aws-secret-key	The secret key for the S3 API.
buffer.spooling.s3.region	The AWS region of the S3 spooling bucket.
buffer.spooling.s3.endpoint	The S3 API endpoint. Required when using an S3-compatible service such as MinIO.

Google Cloud Storage

To configure Google Cloud Storage as the spooling location, use the following configuration properties:

GCS spooling configuration properties

Property name	Description
buffer.spooling.directory	The object storage location for spooled data. Use a gs:// URI.
buffer.spooling.s3.aws-access-key	The GCS HMAC access key.
buffer.spooling.s3.aws-secret-key	The GCS HMAC secret key.
buffer.spooling.s3.endpoint	The S3 API endpoint. Set to https://storage.googleapis.com.
buffer.spooling.gcs.json-key	The GCS service account credentials in JSON format.
buffer.spooling.gcs.json-key-file-path	Path to a JSON file containing the GCS service account credentials.

Azure Blob Storage

To configure Azure Blob Storage as the spooling location, use the following configuration properties:

buffer.spooling.directory=abfs://<FILESYSTEM_CONTAINER>@<STORAGE_ACCOUNT_NAME>.dfs.core.windows.net/<SPOOL_PATH>
buffer.spooling.azure.connection-string=DefaultEndpointsProtocol=https;AccountName=<STORAGE_ACCOUNT_NAME>;AccountKey=<STORAGE_ACCOUNT_KEY>

Azure Blob Storage spooling configuration properties

Property name	Description
buffer.spooling.directory	The object storage location for spooled data. Use an abfs:// URI.
buffer.spooling.azure.connection-string	The Azure storage connection string, including credentials.

Configure chunks spooling with Trino native file system

When buffer.spooling.storage-driver=TRINO_FS is set, the buffer service uses the Trino native file system to connect to object storage. Most configuration properties from the following pages apply, prefixed with buffer.spooling. For example, s3.region becomes buffer.spooling.s3.region:

• S3 file system

• GCS file system

• Azure Storage file system

The following properties do not apply:

• fs.s3.enabled

• fs.gcs.enabled

• fs.azure.enabled

Configure local disk tier

The local disk tier is available as a public preview feature.

The local disk tier adds an intermediate spooling step between memory and remote object storage. When memory crosses memory-high-watermark, the buffer service writes to local SSD instead of remote storage. When local disk crosses spooling-high-watermark, it falls through to remote object storage.

Before enabling the local disk tier:

• Each node must have local or instance-attached SSD storage. Network-attached volumes offer no latency advantage over spooling directly to remote storage.

• buffer.spooling.storage-driver=TRINO_FS must be set on all nodes.

• The embedded buffer service must be enabled.

To configure the local disk tier, use the following configuration properties:

Local disk tier configuration properties

Property name	Description	Default
buffer.local-disk.enabled	Enables the local disk tier. Requires buffer.spooling.storage-driver=TRINO_FS.	false
buffer.local-disk.directory	Local filesystem path for disk-tier data. Required.
buffer.local-disk.capacity	Maximum disk space the tier may use. Required.
buffer.local-disk.routing.memory-high-watermark	Fraction of memory capacity at which disk routing activates (0.0–1.0).	0.8
buffer.local-disk.routing.memory-low-watermark	Fraction of memory capacity at which disk routing deactivates after being triggered. Must be ≤ memory-high-watermark.	0.5
buffer.local-disk.spooling-high-watermark	Disk utilization ratio above which spooling to remote storage is triggered.	0.8
buffer.local-disk.spooling-low-watermark	Disk utilization ratio at which spooling to remote storage stops.	0.6
buffer.local-disk.io-threads	Threads dispatching disk-chunk writes.	128
buffer.local-disk.routing.max-open-disk-chunks	Maximum number of concurrently open disk chunks. Routing pauses when this limit is reached.	4096
buffer.local-disk.routing.exchange-memory-fraction	Once a single exchange’s allocated bytes exceed this fraction of total memory capacity, it is routed to disk regardless of watermarks. Set to 0.0 to disable.	0.3

The following example enables the local disk tier with a 200 GB capacity on a node with a local SSD mounted at /mnt/ssd0:

buffer.spooling.storage-driver=TRINO_FS
buffer.local-disk.enabled=true
buffer.local-disk.directory=/mnt/ssd0/trino-buffer-local-disk
buffer.local-disk.capacity=200GB

Examples

The following query defines a CTE named urgent that aggregates order counts and total prices for urgent orders, grouped by customer and clerk. The main query references urgent twice: once to filter results using a subquery that computes the average price from urgent, and once to aggregate the filtered results by clerk. With CTE reuse enabled, SEP computes the urgent result set once and reuses it for both references.

WITH urgent AS (
     SELECT custkey, clerk, COUNT(*) AS order_count, SUM(totalprice) AS price
     FROM orders
     WHERE orderpriority = '1-URGENT'
     GROUP BY custkey, clerk
 )
 SELECT clerk, COUNT(*), SUM(price)
 FROM urgent
 WHERE price > (SELECT avg(price) FROM urgent)
 GROUP BY clerk;

CTE reuse also optimizes repeated subqueries that are not explicitly defined as CTEs. In the following query, the same subquery appears in both branches of the UNION ALL:

SELECT max(name)
FROM nation
WHERE regionkey > 2
GROUP BY regionkey
UNION ALL
SELECT name
FROM region
WHERE name > ALL (
    SELECT min(name)
    FROM nation
    WHERE regionkey > 2
    GROUP BY regionkey
);