Query Performance Tuning

this guide summarizes tuning techniques for {{ocient}} sql querying these techniques fall into these stages docid 2d9qbr8v7my1wsmdiqe d — tools to optimize query filters and lookups, including segment keys, indexing, and compression options you must apply these tools before loading any data docid 2d9qbr8v7my1wsmdiqe d — techniques to improve query performance at execution time docid 2d9qbr8v7my1wsmdiqe d — 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 {{timekey}} to partition by time and enable efficient pruning for time bounded filters and rollups for details, see docid 7asr7i8qb0jlunvq9tzq 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 docid 7asr7i8qb0jlunvq9tzq use the clustering index keywords to boost performance on any additional clustered column combinations for details, see docid 7asr7i8qb0jlunvq9tzq 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 docid\ a5odjf1wlmeacsfrr8k4m table compression and encoding choose compression to lower storage and i/o while keeping cpu affordable for your workload for details, see docid\ dfaledfc0jdwsfsar3pav consider column cardinality and skew leverage dictionary compression for repeated values for details, see docid 3jftkb yrchqx3350odqp for examples of these different tools and use cases, see docid\ veeoxedu opmz9xm0qqac 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 docid\ sf8ojgyg8bqcetvtpj87c use result set caching — for recurring, identical queries, leverage caching to store query results and bypass execution for details, see docid\ tnfsbqqn4feyztn0f dbn 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 docid 5rvt6idm alkrqjsrm1em 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 docid\ rabiihlpjt8owglyqr1kd statistics monitoring and cache management — monitor key performance metrics and statistics for details, see docid 1za0jle7alhnsncimnmjo related links docid\ khdpfv6pnr1nk4lwgxe j docid 5rvt6idm alkrqjsrm1em