| <div role="region" aria-label="Skip to main content"><a class="skipToContent_fXgn" href="#__docusaurus_skipToContent_fallback">Skip to main content</a></div><nav aria-label="Main" class="navbar navbar--fixed-top navbar--dark"><div class="navbar__inner"><div class="navbar__items"><button aria-label="Toggle navigation bar" aria-expanded="false" class="navbar__toggle clean-btn" type="button"><svg width="30" height="30" viewBox="0 0 30 30" aria-hidden="true"><path stroke="currentColor" stroke-linecap="round" stroke-miterlimit="10" stroke-width="2" d="M4 7h22M4 15h22M4 23h22"></path></svg></button><a class="navbar__brand" href="/"><div class="navbar__logo"><img src="/img/druid_nav.png" alt="Apache® Druid" class="themedImage_ToTc themedImage--light_HNdA"><img src="/img/druid_nav.png" alt="Apache® Druid" class="themedImage_ToTc themedImage--dark_i4oU"></div></a></div><div class="navbar__items navbar__items--right"><a class="navbar__item navbar__link" href="/technology">Technology</a><a class="navbar__item navbar__link" href="/use-cases">Use Cases</a><a class="navbar__item navbar__link" href="/druid-powered">Powered By</a><a class="navbar__item navbar__link" href="/docs/28.0.0/design/">Docs</a><a class="navbar__item navbar__link" href="/community/">Community</a><div class="navbar__item dropdown dropdown--hoverable dropdown--right"><a href="#" aria-haspopup="true" aria-expanded="false" role="button" class="navbar__link">Apache®</a><ul class="dropdown__menu"><li><a href="https://www.apache.org/" target="_blank" rel="noopener noreferrer" class="dropdown__link">Foundation<svg width="12" height="12" aria-hidden="true" viewBox="0 0 24 24" class="iconExternalLink_nPIU"><path fill="currentColor" d="M21 13v10h-21v-19h12v2h-10v15h17v-8h2zm3-12h-10.988l4.035 4-6.977 7.07 2.828 2.828 6.977-7.07 4.125 4.172v-11z"></path></svg></a></li><li><a href="https://apachecon.com/?ref=druid.apache.org" target="_blank" rel="noopener noreferrer" class="dropdown__link">Events<svg width="12" height="12" aria-hidden="true" viewBox="0 0 24 24" class="iconExternalLink_nPIU"><path fill="currentColor" d="M21 13v10h-21v-19h12v2h-10v15h17v-8h2zm3-12h-10.988l4.035 4-6.977 7.07 2.828 2.828 6.977-7.07 4.125 4.172v-11z"></path></svg></a></li><li><a href="https://www.apache.org/licenses/" target="_blank" rel="noopener noreferrer" class="dropdown__link">License<svg width="12" height="12" aria-hidden="true" viewBox="0 0 24 24" class="iconExternalLink_nPIU"><path fill="currentColor" d="M21 13v10h-21v-19h12v2h-10v15h17v-8h2zm3-12h-10.988l4.035 4-6.977 7.07 2.828 2.828 6.977-7.07 4.125 4.172v-11z"></path></svg></a></li><li><a href="https://www.apache.org/foundation/thanks.html" target="_blank" rel="noopener noreferrer" class="dropdown__link">Thanks<svg width="12" height="12" aria-hidden="true" viewBox="0 0 24 24" class="iconExternalLink_nPIU"><path fill="currentColor" d="M21 13v10h-21v-19h12v2h-10v15h17v-8h2zm3-12h-10.988l4.035 4-6.977 7.07 2.828 2.828 6.977-7.07 4.125 4.172v-11z"></path></svg></a></li><li><a href="https://www.apache.org/security/" target="_blank" rel="noopener noreferrer" class="dropdown__link">Security<svg width="12" height="12" aria-hidden="true" viewBox="0 0 24 24" class="iconExternalLink_nPIU"><path fill="currentColor" d="M21 13v10h-21v-19h12v2h-10v15h17v-8h2zm3-12h-10.988l4.035 4-6.977 7.07 2.828 2.828 6.977-7.07 4.125 4.172v-11z"></path></svg></a></li><li><a href="https://www.apache.org/foundation/sponsorship.html" target="_blank" rel="noopener noreferrer" class="dropdown__link">Sponsorship<svg width="12" height="12" aria-hidden="true" viewBox="0 0 24 24" class="iconExternalLink_nPIU"><path fill="currentColor" d="M21 13v10h-21v-19h12v2h-10v15h17v-8h2zm3-12h-10.988l4.035 4-6.977 7.07 2.828 2.828 6.977-7.07 4.125 4.172v-11z"></path></svg></a></li></ul></div><a class="navbar__item navbar__link" href="/downloads/">Download</a><div class="searchBox_ZlJk"><div class="navbar__search"><span aria-label="expand searchbar" role="button" class="search-icon" tabindex="0"></span><input type="search" id="search_input_react" placeholder="Loading..." aria-label="Search" class="navbar__search-input search-bar" disabled=""></div></div></div></div><div role="presentation" class="navbar-sidebar__backdrop"></div></nav><div id="__docusaurus_skipToContent_fallback" class="main-wrapper mainWrapper_z2l0 docsWrapper_BCFX"><button aria-label="Scroll back to top" class="clean-btn theme-back-to-top-button backToTopButton_sjWU" type="button"></button><div class="docPage__5DB"><aside class="theme-doc-sidebar-container docSidebarContainer_b6E3"><div class="sidebarViewport_Xe31"><div class="sidebar_njMd"><nav aria-label="Docs sidebar" class="menu thin-scrollbar menu_SIkG"><ul class="theme-doc-sidebar-menu menu__list"><li class="theme-doc-sidebar-item-category theme-doc-sidebar-item-category-level-1 menu__list-item menu__list-item--collapsed"><div class="menu__list-item-collapsible"><a class="menu__link menu__link--sublist menu__link--sublist-caret" aria-expanded="false" href="/docs/28.0.0/design/">Getting started</a></div></li><li class="theme-doc-sidebar-item-category theme-doc-sidebar-item-category-level-1 menu__list-item menu__list-item--collapsed"><div class="menu__list-item-collapsible"><a class="menu__link menu__link--sublist menu__link--sublist-caret" aria-expanded="false" href="/docs/28.0.0/tutorials/tutorial-msq-extern">Tutorials</a></div></li><li class="theme-doc-sidebar-item-category theme-doc-sidebar-item-category-level-1 menu__list-item menu__list-item--collapsed"><div class="menu__list-item-collapsible"><a class="menu__link menu__link--sublist menu__link--sublist-caret" aria-expanded="false" href="/docs/28.0.0/design/architecture">Design</a></div></li><li class="theme-doc-sidebar-item-category theme-doc-sidebar-item-category-level-1 menu__list-item menu__list-item--collapsed"><div class="menu__list-item-collapsible"><a class="menu__link menu__link--sublist menu__link--sublist-caret" aria-expanded="false" href="/docs/28.0.0/ingestion/">Ingestion</a></div></li><li class="theme-doc-sidebar-item-category theme-doc-sidebar-item-category-level-1 menu__list-item menu__list-item--collapsed"><div class="menu__list-item-collapsible"><a class="menu__link menu__link--sublist menu__link--sublist-caret" aria-expanded="false" href="/docs/28.0.0/data-management/">Data management</a></div></li><li class="theme-doc-sidebar-item-category theme-doc-sidebar-item-category-level-1 menu__list-item"><div class="menu__list-item-collapsible"><a class="menu__link menu__link--sublist menu__link--sublist-caret menu__link--active" aria-expanded="true" href="/docs/28.0.0/querying/sql">Querying</a></div><ul style="display:block;overflow:visible;height:auto" class="menu__list"><li class="theme-doc-sidebar-item-category theme-doc-sidebar-item-category-level-2 menu__list-item"><div class="menu__list-item-collapsible"><a class="menu__link menu__link--sublist menu__link--sublist-caret menu__link--active" aria-expanded="true" tabindex="0" href="/docs/28.0.0/querying/sql">Druid SQL</a></div><ul style="display:block;overflow:visible;height:auto" class="menu__list"><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-3 menu__list-item"><a class="menu__link" tabindex="0" href="/docs/28.0.0/querying/sql">Overview and syntax</a></li><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-3 menu__list-item"><a class="menu__link menu__link--active" aria-current="page" tabindex="0" href="/docs/28.0.0/querying/tips-good-queries">Tips for writing good queries</a></li><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-3 menu__list-item"><a class="menu__link" tabindex="0" href="/docs/28.0.0/querying/query-deep-storage">Query from deep storage</a></li><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-3 menu__list-item"><a class="menu__link" tabindex="0" href="/docs/28.0.0/querying/sql-data-types">SQL data types</a></li><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-3 menu__list-item"><a class="menu__link" tabindex="0" href="/docs/28.0.0/querying/sql-operators">Operators</a></li><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-3 menu__list-item"><a class="menu__link" tabindex="0" href="/docs/28.0.0/querying/sql-scalar">Scalar functions</a></li><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-3 menu__list-item"><a class="menu__link" tabindex="0" href="/docs/28.0.0/querying/sql-aggregations">Aggregation functions</a></li><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-3 menu__list-item"><a class="menu__link" tabindex="0" href="/docs/28.0.0/querying/sql-array-functions">Array functions</a></li><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-3 menu__list-item"><a class="menu__link" tabindex="0" href="/docs/28.0.0/querying/sql-multivalue-string-functions">Multi-value string functions</a></li><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-3 menu__list-item"><a class="menu__link" tabindex="0" href="/docs/28.0.0/querying/sql-json-functions">JSON functions</a></li><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-3 menu__list-item"><a class="menu__link" tabindex="0" href="/docs/28.0.0/querying/sql-functions">All functions</a></li><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-3 menu__list-item"><a class="menu__link" tabindex="0" href="/docs/28.0.0/querying/sql-query-context">SQL query context</a></li><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-3 menu__list-item"><a class="menu__link" tabindex="0" href="/docs/28.0.0/querying/sql-metadata-tables">SQL metadata tables</a></li><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-3 menu__list-item"><a class="menu__link" tabindex="0" href="/docs/28.0.0/querying/sql-translation">SQL query translation</a></li></ul></li><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-2 menu__list-item"><a class="menu__link" tabindex="0" href="/docs/28.0.0/querying/">Native queries</a></li><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-2 menu__list-item"><a class="menu__link" tabindex="0" href="/docs/28.0.0/querying/query-execution">Query execution</a></li><li class="theme-doc-sidebar-item-link theme-doc-sidebar-item-link-level-2 menu__list-item"><a class="menu__link" tabindex="0" href="/docs/28.0.0/querying/troubleshooting">Troubleshooting</a></li><li class="theme-doc-sidebar-item-category theme-doc-sidebar-item-category-level-2 menu__list-item menu__list-item--collapsed"><div class="menu__list-item-collapsible"><a class="menu__link menu__link--sublist menu__link--sublist-caret" aria-expanded="false" tabindex="0" href="/docs/28.0.0/querying/datasource">Concepts</a></div></li><li class="theme-doc-sidebar-item-category theme-doc-sidebar-item-category-level-2 menu__list-item menu__list-item--collapsed"><div class="menu__list-item-collapsible"><a class="menu__link menu__link--sublist menu__link--sublist-caret" aria-expanded="false" tabindex="0" href="/docs/28.0.0/querying/timeseriesquery">Native query types</a></div></li><li class="theme-doc-sidebar-item-category theme-doc-sidebar-item-category-level-2 menu__list-item menu__list-item--collapsed"><div class="menu__list-item-collapsible"><a class="menu__link menu__link--sublist menu__link--sublist-caret" aria-expanded="false" tabindex="0" href="/docs/28.0.0/querying/filters">Native query components</a></div></li></ul></li><li class="theme-doc-sidebar-item-category theme-doc-sidebar-item-category-level-1 menu__list-item menu__list-item--collapsed"><div class="menu__list-item-collapsible"><a class="menu__link menu__link--sublist menu__link--sublist-caret" aria-expanded="false" href="/docs/28.0.0/api-reference/">API reference</a></div></li><li class="theme-doc-sidebar-item-category theme-doc-sidebar-item-category-level-1 menu__list-item menu__list-item--collapsed"><div class="menu__list-item-collapsible"><a class="menu__link menu__link--sublist menu__link--sublist-caret" aria-expanded="false" href="/docs/28.0.0/configuration/">Configuration</a></div></li><li class="theme-doc-sidebar-item-category theme-doc-sidebar-item-category-level-1 menu__list-item menu__list-item--collapsed"><div class="menu__list-item-collapsible"><a class="menu__link menu__link--sublist menu__link--sublist-caret" aria-expanded="false" href="/docs/28.0.0/operations/web-console">Operations</a></div></li><li class="theme-doc-sidebar-item-category theme-doc-sidebar-item-category-level-1 menu__list-item menu__list-item--collapsed"><div class="menu__list-item-collapsible"><a class="menu__link menu__link--sublist menu__link--sublist-caret" aria-expanded="false" href="/docs/28.0.0/development/overview">Development</a></div></li><li class="theme-doc-sidebar-item-category theme-doc-sidebar-item-category-level-1 menu__list-item menu__list-item--collapsed"><div class="menu__list-item-collapsible"><a class="menu__link menu__link--sublist menu__link--sublist-caret" aria-expanded="false" href="/docs/28.0.0/misc/papers-and-talks">Misc</a></div></li></ul></nav></div></div></aside><main class="docMainContainer_gTbr"><div class="container padding-top--md padding-bottom--lg"><div class="row"><div class="col docItemCol_VOVn"><div class="docItemContainer_Djhp"><article><nav class="theme-doc-breadcrumbs breadcrumbsContainer_Z_bl" aria-label="Breadcrumbs"><ul class="breadcrumbs" itemscope="" itemtype="https://schema.org/BreadcrumbList"><li class="breadcrumbs__item"><a aria-label="Home page" class="breadcrumbs__link" href="/"><svg viewBox="0 0 24 24" class="breadcrumbHomeIcon_YNFT"><path d="M10 19v-5h4v5c0 .55.45 1 1 1h3c.55 0 1-.45 1-1v-7h1.7c.46 0 .68-.57.33-.87L12.67 3.6c-.38-.34-.96-.34-1.34 0l-8.36 7.53c-.34.3-.13.87.33.87H5v7c0 .55.45 1 1 1h3c.55 0 1-.45 1-1z" fill="currentColor"></path></svg></a></li><li class="breadcrumbs__item"><span class="breadcrumbs__link">Querying</span><meta itemprop="position" content="1"></li><li class="breadcrumbs__item"><span class="breadcrumbs__link">Druid SQL</span><meta itemprop="position" content="2"></li><li itemscope="" itemprop="itemListElement" itemtype="https://schema.org/ListItem" class="breadcrumbs__item breadcrumbs__item--active"><span class="breadcrumbs__link" itemprop="name">Tips for writing good queries</span><meta itemprop="position" content="3"></li></ul></nav><div class="tocCollapsible_ETCw theme-doc-toc-mobile tocMobile_ITEo"><button type="button" class="clean-btn tocCollapsibleButton_TO0P">On this page</button></div><div class="theme-doc-markdown markdown"><header><h1>Tips for writing good queries in Druid</h1></header><p>This topic includes tips and examples that can help you investigate and improve query performance and accuracy using <a href="/docs/28.0.0/querying/sql">Apache Druid SQL</a>. Use this topic as a companion to the Jupyter Notebook tutorial <a href="https://github.com/apache/druid/blob/master/examples/quickstart/jupyter-notebooks/notebooks/03-query/00-using-sql-with-druidapi.ipynb" target="_blank" rel="noopener noreferrer">Learn the basics of Druid SQL</a>.</p><p>Your ability to effectively query your data depends in large part on the way you've ingested and stored the data in Apache Druid. This document assumes that you've followed the best practices described in <a href="/docs/28.0.0/ingestion/schema-design#general-tips-and-best-practices">Schema design tips and best practices</a> when modeling your data. </p><h2 class="anchor anchorWithStickyNavbar_LWe7" id="investigate-query-performance">Investigate query performance<a href="#investigate-query-performance" class="hash-link" aria-label="Direct link to Investigate query performance" title="Direct link to Investigate query performance"></a></h2><p>If your queries run slower than anticipated, you can use the following tools to investigate query performance issues.</p><h3 class="anchor anchorWithStickyNavbar_LWe7" id="analyze-query-metrics">Analyze query metrics<a href="#analyze-query-metrics" class="hash-link" aria-label="Direct link to Analyze query metrics" title="Direct link to Analyze query metrics"></a></h3><p>You can configure Druid processes to emit metrics that are essential for monitoring query execution. See <a href="/docs/28.0.0/operations/metrics#query-metrics">Query metrics</a> for more information. </p><h3 class="anchor anchorWithStickyNavbar_LWe7" id="generate-an-explain-plan">Generate an explain plan<a href="#generate-an-explain-plan" class="hash-link" aria-label="Direct link to Generate an explain plan" title="Direct link to Generate an explain plan"></a></h3><p>An explain plan shows the full query details and all of the operations Druid performs to execute it. You can use the information in the plan to identify possible areas of query improvement.</p><p>See <a href="/docs/28.0.0/querying/sql#explain-plan">Explain plan</a> and <a href="/docs/28.0.0/querying/sql-translation#interpreting-explain-plan-output">Interpreting explain plan output</a> for more information.</p><p>You can follow the <a href="/docs/28.0.0/tutorials/tutorial-sql-query-view">Get to know Query view tutorial</a> to create an example explain plan in the Druid console.</p><h2 class="anchor anchorWithStickyNavbar_LWe7" id="improve-query-performance">Improve query performance<a href="#improve-query-performance" class="hash-link" aria-label="Direct link to Improve query performance" title="Direct link to Improve query performance"></a></h2><p>In most cases, you can improve query performance by adjusting Druid settings and by manually tuning your queries.</p><h3 class="anchor anchorWithStickyNavbar_LWe7" id="adjust-druid-settings">Adjust Druid settings<a href="#adjust-druid-settings" class="hash-link" aria-label="Direct link to Adjust Druid settings" title="Direct link to Adjust Druid settings"></a></h3><p>This section outlines Druid settings that can help to improve query performance.</p><h4 class="anchor anchorWithStickyNavbar_LWe7" id="turn-on-query-caching">Turn on query caching<a href="#turn-on-query-caching" class="hash-link" aria-label="Direct link to Turn on query caching" title="Direct link to Turn on query caching"></a></h4><p>You can enable caching in Druid to improve query times for frequently accessed data. Caching enables increased concurrency on the same system, leading to noticeable performance improvements for queries handling throughput for concurrent, mixed workloads.</p><p>The largest performance gains from caching tend to apply to TopN and timeseries queries. For GroupBy queries, if the bottleneck is in the merging phase on the Broker, enabling caching results in little noticeable query improvement. See <a href="/docs/28.0.0/querying/caching#performance-considerations-for-caching">Performance considerations for caching</a> for more information.</p><h4 class="anchor anchorWithStickyNavbar_LWe7" id="use-approximation">Use approximation<a href="#use-approximation" class="hash-link" aria-label="Direct link to Use approximation" title="Direct link to Use approximation"></a></h4><p>When possible, design your SQL queries in such a way that they match the rules for TopN approximation, so that Druid enables TopN by default. For Druid to automatically optimize for TopN, your SQL query must include the following:</p><ul><li><p>GROUP BY on one dimension, and</p></li><li><p>ORDER BY on one aggregate.</p><p>See <a href="/docs/28.0.0/querying/topnquery">TopN queries</a> for more information.</p></li></ul><p>Note that TopN queries are approximate in that each data process ranks its top K results and only returns those top K results to the Broker.</p><p>You can follow the tutorial <a href="https://github.com/apache/druid/blob/master/examples/quickstart/jupyter-notebooks/notebooks/03-query/02-approxRanking.ipynb" target="_blank" rel="noopener noreferrer">Using TopN approximation in Druid queries</a> to work through some examples with approximation turned on and off. The tutorial <a href="/docs/28.0.0/tutorials/tutorial-sql-query-view">Get to know Query view</a> demonstrates running aggregate queries in the Druid console.</p><h3 class="anchor anchorWithStickyNavbar_LWe7" id="manually-tune-your-queries">Manually tune your queries<a href="#manually-tune-your-queries" class="hash-link" aria-label="Direct link to Manually tune your queries" title="Direct link to Manually tune your queries"></a></h3><p>This section outlines techniques you can use to improve your query accuracy and performance.</p><h4 class="anchor anchorWithStickyNavbar_LWe7" id="query-one-table-at-a-time">Query one table at a time<a href="#query-one-table-at-a-time" class="hash-link" aria-label="Direct link to Query one table at a time" title="Direct link to Query one table at a time"></a></h4><p>Query a single table at a time to minimize the load on the Druid processor.</p><h4 class="anchor anchorWithStickyNavbar_LWe7" id="select-specific-columns">Select specific columns<a href="#select-specific-columns" class="hash-link" aria-label="Direct link to Select specific columns" title="Direct link to Select specific columns"></a></h4><p>Only select the columns needed for the query instead of retrieving all columns from the table. This reduces the amount of data retrieved from the database, which improves query performance.</p><h4 class="anchor anchorWithStickyNavbar_LWe7" id="use-filters">Use filters<a href="#use-filters" class="hash-link" aria-label="Direct link to Use filters" title="Direct link to Use filters"></a></h4><p>Use filters, for example the WHERE clause, and filter on time. Try to minimize the use of inequality filters, because they're very resource-intensive.</p><p>The following example query filters on <code>__time</code> and <code>product</code>:</p><div class="codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#bfc7d5;--prism-background-color:#292d3e"><div class="codeBlockContent_biex"><pre tabindex="0" class="prism-code language-text codeBlock_bY9V thin-scrollbar"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#bfc7d5"><span class="token plain">SELECT</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> FLOOR(__time to day),</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> product,</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> sum(quantity * price) as revenue</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain">FROM "orders"</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain">WHERE</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> __time > '2023-08-20' and product = 'product 1'</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain">GROUP BY 1, 2</span><br></span></code></pre><div class="buttonGroup__atx"><button type="button" aria-label="Copy code to clipboard" title="Copy" class="clean-btn"><span class="copyButtonIcons_eSgA" aria-hidden="true"><svg viewBox="0 0 24 24" class="copyButtonIcon_y97N"><path fill="currentColor" d="M19,21H8V7H19M19,5H8A2,2 0 0,0 6,7V21A2,2 0 0,0 8,23H19A2,2 0 0,0 21,21V7A2,2 0 0,0 19,5M16,1H4A2,2 0 0,0 2,3V17H4V3H16V1Z"></path></svg><svg viewBox="0 0 24 24" class="copyButtonSuccessIcon_LjdS"><path fill="currentColor" d="M21,7L9,19L3.5,13.5L4.91,12.09L9,16.17L19.59,5.59L21,7Z"></path></svg></span></button></div></div></div><p>The following example uses a wildcard filter on the <code>diffUrl</code> column:</p><div class="codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#bfc7d5;--prism-background-color:#292d3e"><div class="codeBlockContent_biex"><pre tabindex="0" class="prism-code language-text codeBlock_bY9V thin-scrollbar"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#bfc7d5"><span class="token plain">SELECT * from Wikipedia</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain">WHERE diffUrl LIKE 'https://en.wikipedia%'</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain">AND TIME_IN_INTERVAL(__time, '2016-06-27T01:00:00/2016-06-27T02:00:00')</span><br></span></code></pre><div class="buttonGroup__atx"><button type="button" aria-label="Copy code to clipboard" title="Copy" class="clean-btn"><span class="copyButtonIcons_eSgA" aria-hidden="true"><svg viewBox="0 0 24 24" class="copyButtonIcon_y97N"><path fill="currentColor" d="M19,21H8V7H19M19,5H8A2,2 0 0,0 6,7V21A2,2 0 0,0 8,23H19A2,2 0 0,0 21,21V7A2,2 0 0,0 19,5M16,1H4A2,2 0 0,0 2,3V17H4V3H16V1Z"></path></svg><svg viewBox="0 0 24 24" class="copyButtonSuccessIcon_LjdS"><path fill="currentColor" d="M21,7L9,19L3.5,13.5L4.91,12.09L9,16.17L19.59,5.59L21,7Z"></path></svg></span></button></div></div></div><h4 class="anchor anchorWithStickyNavbar_LWe7" id="shorten-your-queries">Shorten your queries<a href="#shorten-your-queries" class="hash-link" aria-label="Direct link to Shorten your queries" title="Direct link to Shorten your queries"></a></h4><p>Make your queries shorter where possible<!-- -->—<!-- -->Druid processes shorter queries faster. You might also be able to divide a single query into multiple queries.</p><p>For example, the following query aggregates over multiple datasources using UNION ALL:</p><div class="codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#bfc7d5;--prism-background-color:#292d3e"><div class="codeBlockContent_biex"><pre tabindex="0" class="prism-code language-text codeBlock_bY9V thin-scrollbar"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#bfc7d5"><span class="token plain">SELECT id, SUM(revenue) FROM</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> (SELECT id, revenue from datasource_1</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain">UNION ALL</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> SELECT id, revenue FROM datasource_2)</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain">...</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain">UNION ALL</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> SELECT id, revenue FROM datasource_n)</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain">GROUP BY id</span><br></span></code></pre><div class="buttonGroup__atx"><button type="button" aria-label="Copy code to clipboard" title="Copy" class="clean-btn"><span class="copyButtonIcons_eSgA" aria-hidden="true"><svg viewBox="0 0 24 24" class="copyButtonIcon_y97N"><path fill="currentColor" d="M19,21H8V7H19M19,5H8A2,2 0 0,0 6,7V21A2,2 0 0,0 8,23H19A2,2 0 0,0 21,21V7A2,2 0 0,0 19,5M16,1H4A2,2 0 0,0 2,3V17H4V3H16V1Z"></path></svg><svg viewBox="0 0 24 24" class="copyButtonSuccessIcon_LjdS"><path fill="currentColor" d="M21,7L9,19L3.5,13.5L4.91,12.09L9,16.17L19.59,5.59L21,7Z"></path></svg></span></button></div></div></div><p>To simplify this query, you could split it into several queries, for example:</p><div class="codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#bfc7d5;--prism-background-color:#292d3e"><div class="codeBlockContent_biex"><pre tabindex="0" class="prism-code language-text codeBlock_bY9V thin-scrollbar"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#bfc7d5"><span class="token plain">SELECT id, SUM(revenue) FROM datasource_1</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain" style="display:inline-block"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain">SELECT id, SUM(revenue) FROM datasource_2</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain">...</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain">SELECT id, SUM(revenue) FROM datasource_n</span><br></span></code></pre><div class="buttonGroup__atx"><button type="button" aria-label="Copy code to clipboard" title="Copy" class="clean-btn"><span class="copyButtonIcons_eSgA" aria-hidden="true"><svg viewBox="0 0 24 24" class="copyButtonIcon_y97N"><path fill="currentColor" d="M19,21H8V7H19M19,5H8A2,2 0 0,0 6,7V21A2,2 0 0,0 8,23H19A2,2 0 0,0 21,21V7A2,2 0 0,0 19,5M16,1H4A2,2 0 0,0 2,3V17H4V3H16V1Z"></path></svg><svg viewBox="0 0 24 24" class="copyButtonSuccessIcon_LjdS"><path fill="currentColor" d="M21,7L9,19L3.5,13.5L4.91,12.09L9,16.17L19.59,5.59L21,7Z"></path></svg></span></button></div></div></div><p>You could then manually aggregate the results of the individual queries.</p><h4 class="anchor anchorWithStickyNavbar_LWe7" id="minimize-or-remove-subqueries">Minimize or remove subqueries<a href="#minimize-or-remove-subqueries" class="hash-link" aria-label="Direct link to Minimize or remove subqueries" title="Direct link to Minimize or remove subqueries"></a></h4><p>Consider whether you can pre-compute a subquery task and store it as a join or make it a part of the datasource. See <a href="/docs/28.0.0/querying/datasource#join">Datasources: join</a> and <a href="/docs/28.0.0/querying/sql-translation#joins">SQL query translation: Joins</a> for more information and examples.</p><h4 class="anchor anchorWithStickyNavbar_LWe7" id="consider-alternatives-to-groupby">Consider alternatives to GroupBy<a href="#consider-alternatives-to-groupby" class="hash-link" aria-label="Direct link to Consider alternatives to GroupBy" title="Direct link to Consider alternatives to GroupBy"></a></h4><p>Consider using Timeseries and TopN as alternatives to GroupBy. See <a href="/docs/28.0.0/querying/groupbyquery#alternatives">GroupBy queries: alternatives</a> for more information.</p><p>Avoid grouping on high cardinality columns, for example user ID. Investigate whether you can apply a filter first, to reduce the number of results for grouping. </p><h4 class="anchor anchorWithStickyNavbar_LWe7" id="query-over-smaller-intervals">Query over smaller intervals<a href="#query-over-smaller-intervals" class="hash-link" aria-label="Direct link to Query over smaller intervals" title="Direct link to Query over smaller intervals"></a></h4><p>Consider whether you can query a smaller time interval to return a smaller results set.</p><p>For example, the following query doesn't limit on time and could be resource-intensive:</p><div class="codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#bfc7d5;--prism-background-color:#292d3e"><div class="codeBlockContent_biex"><pre tabindex="0" class="prism-code language-text codeBlock_bY9V thin-scrollbar"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#bfc7d5"><span class="token plain">SELECT cust_id, sum(revenue) FROM myDatasource</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain">GROUP BY cust_id</span><br></span></code></pre><div class="buttonGroup__atx"><button type="button" aria-label="Copy code to clipboard" title="Copy" class="clean-btn"><span class="copyButtonIcons_eSgA" aria-hidden="true"><svg viewBox="0 0 24 24" class="copyButtonIcon_y97N"><path fill="currentColor" d="M19,21H8V7H19M19,5H8A2,2 0 0,0 6,7V21A2,2 0 0,0 8,23H19A2,2 0 0,0 21,21V7A2,2 0 0,0 19,5M16,1H4A2,2 0 0,0 2,3V17H4V3H16V1Z"></path></svg><svg viewBox="0 0 24 24" class="copyButtonSuccessIcon_LjdS"><path fill="currentColor" d="M21,7L9,19L3.5,13.5L4.91,12.09L9,16.17L19.59,5.59L21,7Z"></path></svg></span></button></div></div></div><p>This query could be split into multiple queries over smaller time spans, with the results combined client-side. For example:</p><div class="codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#bfc7d5;--prism-background-color:#292d3e"><div class="codeBlockContent_biex"><pre tabindex="0" class="prism-code language-text codeBlock_bY9V thin-scrollbar"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#bfc7d5"><span class="token plain">SELECT cust_id, sum(revenue) FROM myDatasource</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain">GROUP BY cust_id</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain">WHERE __time BETWEEN '2023-07-01' AND '2023-07-31'</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain" style="display:inline-block"></span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain">SELECT cust_id, sum(revenue) FROM myDatasource</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain">GROUP BY cust_id</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain">WHERE __time BETWEEN '2023-08-01' AND '2023-08-31'</span><br></span></code></pre><div class="buttonGroup__atx"><button type="button" aria-label="Copy code to clipboard" title="Copy" class="clean-btn"><span class="copyButtonIcons_eSgA" aria-hidden="true"><svg viewBox="0 0 24 24" class="copyButtonIcon_y97N"><path fill="currentColor" d="M19,21H8V7H19M19,5H8A2,2 0 0,0 6,7V21A2,2 0 0,0 8,23H19A2,2 0 0,0 21,21V7A2,2 0 0,0 19,5M16,1H4A2,2 0 0,0 2,3V17H4V3H16V1Z"></path></svg><svg viewBox="0 0 24 24" class="copyButtonSuccessIcon_LjdS"><path fill="currentColor" d="M21,7L9,19L3.5,13.5L4.91,12.09L9,16.17L19.59,5.59L21,7Z"></path></svg></span></button></div></div></div><h4 class="anchor anchorWithStickyNavbar_LWe7" id="reduce-the-computation-in-your-queries">Reduce the computation in your queries<a href="#reduce-the-computation-in-your-queries" class="hash-link" aria-label="Direct link to Reduce the computation in your queries" title="Direct link to Reduce the computation in your queries"></a></h4><p>Examine your query to see if it uses a lot of transformations, functions, and expressions. Consider whether you could rewrite the query to reduce the level of computation.</p><h2 class="anchor anchorWithStickyNavbar_LWe7" id="druid-sql-query-example">Druid SQL query example<a href="#druid-sql-query-example" class="hash-link" aria-label="Direct link to Druid SQL query example" title="Direct link to Druid SQL query example"></a></h2><p>The following example query demonstrates many of the tips outlined in this topic. |
| The query:</p><ul><li>selects specific dimensions and metrics</li><li>uses approximation</li><li>selects from a single table</li><li>groups by low cardinality columns</li><li>filters on both dimensions and time</li><li>orders by a dimension and a measure</li><li>includes a limit</li></ul><div class="codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#bfc7d5;--prism-background-color:#292d3e"><div class="codeBlockContent_biex"><pre tabindex="0" class="prism-code language-text codeBlock_bY9V thin-scrollbar"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#bfc7d5"><span class="token plain">SELECT</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> FLOOR() AS month,</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> country,</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> SUM(price),</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain"> APPROX_COUNT_DISTINCT_DS_HLL(userid)</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain">FROM sales</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain">GROUP BY month, country</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain">WHERE artist = 'Madonna' AND TIME_IN_INTERVAL(__time, '2023-08-01/P1M')</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain">ORDER BY country, SUM(price) DESC</span><br></span><span class="token-line" style="color:#bfc7d5"><span class="token plain">LIMIT 100</span><br></span></code></pre><div class="buttonGroup__atx"><button type="button" aria-label="Copy code to clipboard" title="Copy" class="clean-btn"><span class="copyButtonIcons_eSgA" aria-hidden="true"><svg viewBox="0 0 24 24" class="copyButtonIcon_y97N"><path fill="currentColor" d="M19,21H8V7H19M19,5H8A2,2 0 0,0 6,7V21A2,2 0 0,0 8,23H19A2,2 0 0,0 21,21V7A2,2 0 0,0 19,5M16,1H4A2,2 0 0,0 2,3V17H4V3H16V1Z"></path></svg><svg viewBox="0 0 24 24" class="copyButtonSuccessIcon_LjdS"><path fill="currentColor" d="M21,7L9,19L3.5,13.5L4.91,12.09L9,16.17L19.59,5.59L21,7Z"></path></svg></span></button></div></div></div></div></article><nav class="pagination-nav docusaurus-mt-lg" aria-label="Docs pages"><a class="pagination-nav__link pagination-nav__link--prev" href="/docs/28.0.0/querying/sql"><div class="pagination-nav__sublabel">Previous</div><div class="pagination-nav__label">Overview and syntax</div></a><a class="pagination-nav__link pagination-nav__link--next" href="/docs/28.0.0/querying/query-deep-storage"><div class="pagination-nav__sublabel">Next</div><div class="pagination-nav__label">Query from deep storage</div></a></nav></div></div><div class="col col--3"><div class="tableOfContents_bqdL thin-scrollbar theme-doc-toc-desktop"><ul class="table-of-contents table-of-contents__left-border"><li><a href="#investigate-query-performance" class="table-of-contents__link toc-highlight">Investigate query performance</a><ul><li><a href="#analyze-query-metrics" class="table-of-contents__link toc-highlight">Analyze query metrics</a></li><li><a href="#generate-an-explain-plan" class="table-of-contents__link toc-highlight">Generate an explain plan</a></li></ul></li><li><a href="#improve-query-performance" class="table-of-contents__link toc-highlight">Improve query performance</a><ul><li><a href="#adjust-druid-settings" class="table-of-contents__link toc-highlight">Adjust Druid settings</a></li><li><a href="#manually-tune-your-queries" class="table-of-contents__link toc-highlight">Manually tune your queries</a></li></ul></li><li><a href="#druid-sql-query-example" class="table-of-contents__link toc-highlight">Druid SQL query example</a></li></ul></div></div></div></div></main></div></div><footer class="footer"><div class="container container-fluid"><div class="footer__bottom text--center"><div class="margin-bottom--sm"><img src="/img/favicon.png" class="themedImage_ToTc themedImage--light_HNdA footer__logo"><img src="/img/favicon.png" class="themedImage_ToTc themedImage--dark_i4oU footer__logo"></div><div class="footer__copyright">Copyright © 2023 Apache Software Foundation. Except where otherwise noted, licensed under CC BY-SA 4.0. Apache Druid, Druid, and the Druid logo are either registered trademarks or trademarks of The Apache Software Foundation in the United States and other countries.</div></div></div></footer></div> |
