- 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.
- Query performance tuning — Techniques to improve query performance at execution time.
- 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:- Apply TimeKeys — Use the to partition by time and enable efficient pruning for time-bounded filters and rollups. For details, see TimeKeys.
- Apply clustering keys and indexes:
- Use the
CLUSTERING KEYkeywords to co-locate rows with commonly filtered or joined columns to reduce input and output (I/O). For details, see Clustering Key. - Use the
CLUSTERING INDEXkeywords to boost performance on any additional clustered column combinations. For details, see Clustering Indexes.
- Use the
- 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.
- 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.
- Consider column cardinality and skew. Leverage dictionary compression for repeated values. For details, see Global Dictionary Compression.
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:- Adhere to query best practices:
- Filter and aggregate early. Apply the most selective predicates as soon as possible in a query.
- Avoid unnecessary use of the
DISTINCTkeyword. Also, avoid the use of many columns in aSELECTSQL statement. - 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
SELECTstatement where possible. Use theJOINkeyword to join queries or use window functions when possible instead. - If you have a column with no duplicate values, use the
UNION ALLkeywords instead of theUNIONkeyword to avoid deduplication overhead.
- 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.
- Use result set caching — For recurring, identical queries, leverage caching to store query results and bypass execution. For details, see Result Set Caching.
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 (
EXPLAINtooling):- Inspect the logical and execution plans of your query. Focus on the largest operators by time and rows. For details, see Query Analysis.
- 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.
- Statistics monitoring and cache management — Monitor key performance metrics and statistics. For details, see Set Up System Monitoring with the TIG Stack and Kapacitor.