---
title: Query Performance Tuning
slug: query-performance-tuning
description: The Query Performance Tuning page explains how to optimize query execution in Ocient to achieve low latency and high throughput on large, complex workloads. 
docTags: 
createdAt: Wed Oct 22 2025 18:56:33 GMT+0000 (Coordinated Universal Time)
updatedAt: Mon Mar 30 2026 18:52:34 GMT+0000 (Coordinated Universal Time)
---

This guide summarizes tuning techniques for Ocient® SQL querying. These techniques fall into these stages:

- [Pre-query tuning](./#pre-query-tuning) — Tools to optimize query filters and lookups, including segment keys, indexing, and compression options. You must apply these tools before loading any data.&#x20;
- [Query performance tuning](./#query-performance-tuning) — Techniques to improve query performance at execution time.&#x20;
- [Query performance analysis](./#query-performance-analysis) — Tools and tips for monitoring queries and analyzing performance.

## Pre-Query Tuning

Carefully designing tables and storage with forethought can yield the largest query performance gains. Use these tools during table design:&#x20;

- Apply TimeKeys — Use the TimeKey® to partition by time and enable efficient pruning for time-bounded filters and rollups. For details, see [TimeKeys](<././Database Administration/Schema Design/TimeKeys and Clustering Keys.md>).
- Apply clustering keys and indexes:
  - Use the `CLUSTERING KEY` keywords to co-locate rows with commonly filtered or joined columns to reduce input and output (I/O). For details, see [Clustering Key](<././Database Administration/Schema Design/TimeKeys and Clustering Keys.md>).
  - Use the `CLUSTERING INDEX` keywords to boost performance on any additional clustered column combinations. For details, see [Clustering Indexes](<././Database Administration/Schema Design/TimeKeys and Clustering Keys.md>).
- Use secondary indexes — Create secondary indexes on columns that are frequently selected, especially for filtering predicates. These indexes can overlap with columns that already use segment keys or clustering indexes. For details, see [Secondary Indexes](<././Database Administration/Schema Design/Secondary Indexes.md>).
- Table compression and encoding:
  - Choose compression to lower storage and I/O while keeping CPU affordable for your workload. For details, see [Table Compression Options](<././Database Administration/Schema Design/Table Compression Options.md>).
  - Consider column cardinality and skew. Leverage dictionary compression for repeated values. For details, see [Global Dictionary Compression](<././Database Administration/Schema Design/Table Compression Options/Global Dictionary Compression.md>).

For examples of these different tools and use cases, see [CREATE TABLE SQL Statement Examples](<././Database Administration/Schema Design/CREATE TABLE SQL Statement Examples.md>).&#x20;

## Query Performance Tuning

Shape queries to minimize work at execution, align with indexes and data layout, and use runtime controls and caching to govern resources and reuse results. Use these methods while writing a query to tune performance:&#x20;

- Adhere to query best practices:
  - Filter and aggregate early. Apply the most selective predicates as soon as possible in a query.&#x20;
  - Avoid unnecessary use of the `DISTINCT` keyword. Also, avoid the use of many columns in a `SELECT` SQL statement.&#x20;
  - Avoid applying functions on indexed columns in predicates (e.g., `WHERE LOWER(city) = 'chicago'`) because it can impact index and join performance. Instead, create a normalized companion column (e.g., `city_lower`), index it, and use it for queries.
  - Avoid sub-queries in the `SELECT` statement where possible. Use the `JOIN` keyword to join queries or use window functions when possible instead.
  - If you have a column with no duplicate values, use the `UNION ALL` keywords instead of the `UNION` keyword to avoid deduplication overhead.&#x20;
- Apply workload management — Route heavy and long-running queries to batch classes and keep interactive queries in low-latency classes. For details, see [Workload Management Walkthrough](<././Database Administration/Manage Users_ Groups_ and Roles/Workload Management Walkthrough.md>).
- Use result set caching — For recurring, identical queries, leverage caching to store query results and bypass execution. For details, see [Result Set Caching](<././Database Administration/Manage Users_ Groups_ and Roles/Result Set Caching.md>).

## Query Performance Analysis

Measure, inspect, and iterate on query performance using analysis tools, system catalog tables and views, and statistics. Use these techniques to analyze the performance of a query:

- Query analysis (`EXPLAIN` tooling):
  - Inspect the logical and execution plans of your query. Focus on the largest operators by time and rows. For details, see [Query Analysis](<././SQL Reference/Data Definition Language _DDL_ Statement Reference/Query Analysis.md>).&#x20;
  - Compare the performance of alternative query structures and index choices.
- System catalog and information schema — Use system catalog tables and information schema views to understand table sizes, index usage, and the performance of queries during execution. For examples of capturing common query metrics, see [Discover Insights From System Catalog Tables](<././SQL Reference/System Catalog Reference/Discover Insights From System Catalog Tables.md>).&#x20;
- Statistics monitoring and cache management — Monitor key performance metrics and statistics. For details, see [Set Up System Monitoring with the TIG Stack and Kapacitor](<././System Administration/Monitoring/Set Up System Monitoring with the TIG Stack and Kapacitor.md>).

## Related Links

[Query Performance Optimizations](<././Ocient Architecture/Query Performance Optimizations.md>)&#x20;

[Query Analysis](<././SQL Reference/Data Definition Language _DDL_ Statement Reference/Query Analysis.md>)&#x20;

