Query Performance Tuning

this guide summarizes tuning techniques for sql querying these techniques fall into these stages pre query tuning docid\ j0emntlrygy0vpzatoeff — 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 docid\ j0emntlrygy0vpzatoeff — techniques to improve query performance at execution time query performance analysis docid\ j0emntlrygy0vpzatoeff — 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 docid\ tfr hznzvabrm8wqf46lm 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 docid\ tfr hznzvabrm8wqf46lm use the clustering index keywords to boost performance on any additional clustered column combinations for details, see clustering indexes docid\ tfr hznzvabrm8wqf46lm 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 docid\ xmmylaxzqfci6ysnff5tg 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 docid 9dknfvlmwhmwtb3 pay09 consider column cardinality and skew leverage dictionary compression for repeated values for details, see global dictionary compression docid\ jgyc ya34xazt9dwvccfq for examples of these different tools and use cases, see create table sql statement examples docid m9x5vuwbb5i 09wfbmqv 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 distinct keyword also, avoid the use of many columns in a select sql 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 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 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 docid 9kxhilhqndr 2zpuqjx9x use result set caching — for recurring, identical queries, leverage caching to store query results and bypass execution for details, see result set caching docid 9fvbwkepgkiqpiz9vnejm 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 docid\ tu2oj1nn9zxenlht1nky3 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 docid\ sqn29gdq1fg jqaawgl9k statistics monitoring and cache management — monitor key performance metrics and statistics for details, see set up system monitoring with the tig stack and kapacitor docid\ kdg 95jlcpuvn6mzu pfs related links query performance optimizations docid\ das3yqqqhangqr7xusanl query analysis docid\ tu2oj1nn9zxenlht1nky3