| </span></code></pre></div><p><img alt="" src="https://www.datocms-assets.com/75153/1684246852-image9.png"/></p><p>Result: The query completed in 4 milliseconds! Notice in particular that the numDocsScanned came down from ~146M to 409!</p><h3 id="comparison"><a href="#comparison" aria-hidden="true" tabindex="-1"><span class="icon icon-link"></span></a>Comparison:</h3><p>Let’s take a closer look at the <a target="_blank" rel="noopener noreferrer" href="https://docs.pinot.apache.org/users/api/querying-pinot-using-standard-sql/response-format">query response stats</a> across all three iterations to understand the “how” part of this magic of indexing in Apache Pinot.</p><p><img alt="" src="https://www.datocms-assets.com/75153/1684246748-image3.png"/></p><ol><li><p>The dataset has 633,694,594 records (documents) spread across 130 segments.</p></li><li><p>Query Stats:</p><ol><li>w/o any index optimizations (Iteration #1), the query scanned ALL 633,694,594 records (check numEntriesScannedInFilter) during processing. Also, numEntriesScannedPostFilter was 584,147,312 (numDocsScanned = ~146M). All 130 segments were processed which was very inefficient.</li><li>w/ Inverted Index (Iteration #2), numEntriesScannedInFilter was 0; numEntriesScannedPostFilter was 584,147,312 (numDocsScanned = ~146M) which meant that the query selectivity was low (the query had to scan a lot of records during post filter phase; about 92% of overall records). This is an indication of when a star-tree index could help.</li><li>w/ Star-tree Index (Iteration #3), numEntriesScannedInFilter was 0; numEntriesScannedPostFilter was only 2,045 (numDocsScanned = 409). The star-tree index helped improve query performance tremendously by providing high query selectivity.</li></ol></li></ol><h2 id="5-impact-summary"><a href="#5-impact-summary" aria-hidden="true" tabindex="-1"><span class="icon icon-link"></span></a>5. Impact Summary:</h2><p><img alt="" src="https://www.datocms-assets.com/75153/1684246766-image4.png"/></p><ol><li>356,968x improvement (or 99.999% drop) in num docs scanned from ~146M to 409.</li><li>378.5x improvement (~99.736% drop) in query latency from 1,513 ms to 4 ms.</li></ol><h3 id="key-benefits-of-the-star-tree-index"><a href="#key-benefits-of-the-star-tree-index" aria-hidden="true" tabindex="-1"><span class="icon icon-link"></span></a>Key Benefits of the Star-Tree Index:</h3><ul><li><p>User controllable: Tune space vs. time overhead</p></li><li><p>Flexible: create any number of indexes. The right index is chosen based on the query structure.</p></li><li><p>Transparent: Unlike traditional MVs, users don’t need to know about the existence of a star-tree index. The same query will be accelerated with a star-tree index in place.</p></li><li><p>Dynamic: Very easy to generate a new index at any point of time.</p></li><li><p>Disk IO is the most expensive operation in query processing. Latency is linear to the number of disk reads a query has to perform. Star-Tree Index brings the number of disk reads down exponentially.</p><ul><li>In this example, star-tree Index reduced the disk reads by 99.999% from ~584 Million entries (~146 Million documents or records) in case of an inverted index to 2,045 entries (409 documents or records). Query latency came down from 1,513 ms to 4 ms!</li></ul></li></ul><p><a target="_blank" rel="noopener noreferrer" href="https://startree.ai/blog/star-tree-indexes-in-apache-pinot-part-2-understanding-the-impact-during-high-concurrency">In part 2 of this series,</a> we will perform throughput tests to measure the impact of star-tree index under high load.</p></div></main></div></div><aside class="mt-10 hidden border-l-2 pl-5 lg:sticky lg:top-1 lg:block lg:h-full"><section class="sticky top-0 mb-4 w-[15.375rem]"><div class="flex flex-col space-y-1.5 pb-3"><h3 class="text-sm font-semibold leading-snug text-neutral-500 dark:text-neutral-100">Table of Contents</h3></div><nav class="flex items-center self-start" aria-label="Table of Contents"><ol class="list-none space-y-3"><li class="text-sm leading-tight font-bold"><a href="#1-the-dataset">1. The Dataset:</a></li><li class="text-sm leading-tight ml-6 font-normal"><a href="#schema">Schema:</a></li><li class="text-sm font-normal leading-tight"><a href="#2-query-pattern">2. Query Pattern</a></li><li class="text-sm leading-tight ml-6 font-normal"><a href="#star-tree-index-config">Star-Tree Index Config:</a></li><li class="text-sm font-normal leading-tight"><a href="#4-query-results-and-stats">4. Query Results and Stats</a></li><li class="text-sm leading-tight ml-6 font-normal"><a href="#iteration-1-wo-any-apache-pinot-optimizations">Iteration #1: w/o any Apache Pinot optimizations:</a></li><li class="text-sm leading-tight ml-6 font-normal"><a href="#iteration-2-w-inverted-index">Iteration #2: w/ Inverted Index </a></li><li class="text-sm leading-tight ml-6 font-normal"><a href="#iteration-3-w-star-tree-index">Iteration #3: w/ Star-Tree Index: </a></li><li class="text-sm leading-tight ml-6 font-normal"><a href="#comparison">Comparison:</a></li><li class="text-sm font-normal leading-tight"><a href="#5-impact-summary">5. Impact Summary:</a></li><li class="text-sm leading-tight ml-6 font-normal"><a href="#key-benefits-of-the-star-tree-index">Key Benefits of the Star-Tree Index:</a></li></ol></nav></section></aside></div></article></section></main><footer class="border-t bg-sky-100 px-5 py-10 md:px-[6.75rem] md:pb-10 md:pt-16"><div class="mx-auto flex max-w-7xl flex-wrap justify-between"><div class="flex-shrink-0"><svg xmlns="http://www.w3.org/2000/svg" width="120" height="48" fill="none"><g fill="#C7154A" clip-path="url(#logo_svg__a)"><path d="M42.99 18.448c1.032-.553 2.21-.831 3.535-.831 1.542 0 2.938.38 4.187 1.14 1.248.76 2.236 1.841 2.965 3.241.728 1.402 1.091 3.025 1.091 4.872s-.363 3.482-1.091 4.903c-.729 1.424-1.717 2.525-2.965 3.307-1.25.782-2.645 1.173-4.187 1.173-1.325 0-2.493-.271-3.503-.815-1.01-.543-1.83-1.226-2.46-2.053v14.612H36V17.912h4.562v2.606c.586-.825 1.395-1.515 2.426-2.068l.002-.002m6.452 5.605c-.445-.793-1.032-1.395-1.76-1.808a4.72 4.72 0 0 0-2.362-.618c-.847 0-1.602.211-2.33.635-.728.423-1.315 1.038-1.76 1.841-.445.804-.668 1.749-.668 2.835 0 1.087.221 2.032.668 2.835.445.804 1.032 1.417 1.76 1.842a4.557 4.557 0 0 0 2.33.635 4.57 4.57 0 0 0 2.362-.652c.728-.435 1.313-1.053 1.76-1.856.445-.804.668-1.76.668-2.867s-.223-2.025-.668-2.818v-.004M62.947 17.912v18.051h-4.562V17.912h4.562m.551-6.079a2.833 2.833 0 1 1-5.666 0 2.833 2.833 0 0 1 5.666 0M82.954 19.687c1.325 1.358 1.988 3.253 1.988 5.685v10.59H80.38v-9.97c0-1.434-.358-2.537-1.075-3.307-.717-.772-1.695-1.157-2.933-1.157-1.239 0-2.254.387-2.982 1.157-.728.772-1.091 1.873-1.091 3.307v9.97h-4.562V17.91h4.562v2.248a6.322 6.322 0 0 1 2.33-1.841c.944-.445 1.981-.669 3.111-.669 2.15 0 3.889.68 5.214 2.037v.002M92.892 35.098c-1.39-.77-2.482-1.861-3.275-3.275-.794-1.411-1.19-3.041-1.19-4.888s.406-3.475 1.221-4.888a8.502 8.502 0 0 1 3.34-3.275c1.412-.772 2.987-1.157 4.725-1.157 1.739 0 3.312.387 4.725 1.157a8.5 8.5 0 0 1 3.34 3.275c.815 1.411 1.222 3.041 1.222 4.888s-.418 3.475-1.255 4.888a8.708 8.708 0 0 1-3.388 3.275c-1.424.772-3.014 1.157-4.774 1.157-1.76 0-3.301-.385-4.691-1.157m7.021-3.421c.729-.402 1.309-1.005 1.744-1.809.435-.803.651-1.781.651-2.933 0-1.715-.451-3.035-1.351-3.958-.902-.924-2.004-1.385-3.307-1.385s-2.395.461-3.275 1.385c-.88.923-1.32 2.243-1.32 3.958 0 1.715.428 3.035 1.287 3.958.858.924 1.938 1.385 3.241 1.385.825 0 1.602-.2 2.33-.603v.002M115.96 21.658v8.734c0 .608.147 1.048.44 1.32.293.271.787.406 1.482.406H120v3.845h-2.867c-3.845 0-5.766-1.868-5.766-5.605v-8.7h-2.15v-3.746h2.15V13l4.595-1v5.912h4.04v3.746h-4.042M20.03 46.757l-5.538-1.385A1.97 1.97 0 0 1 13 43.46v-5.462c0-.841.349-1.601.907-2.146a12.212 12.212 0 0 0 6.975-3.644c2.602-2.731 3.627-6.578 2.882-10.251L21 9h-4V4a1 1 0 0 0-2 0v7a1 1 0 0 1-2 0v-1a1 1 0 0 0-2 0v6.758a4.489 4.489 0 0 1 2.694-.755c2.278.095 4.156 1.934 4.297 4.21a4.501 4.501 0 0 1-6.992 4.029V29a1 1 0 0 1-2 0V7a1 1 0 0 0-2 0v2h-4L.237 21.957c-.745 3.675.279 7.52 2.882 10.251a12.202 12.202 0 0 0 6.975 3.644c.558.545.907 1.305.907 2.146V43.4c0 .938-.639 1.757-1.55 1.985l-5.48 1.37c-.57.143-.97.655-.97 1.243h18c0-.588-.4-1.1-.97-1.243v.002"></path><path d="M13.5 23a2.5 2.5 0 1 0 0-5 2.5 2.5 0 0 0 0 5M8 5a1 1 0 1 0 0-2 1 1 0 0 0 0 2M12 8a1 1 0 1 0 0-2 1 1 0 0 0 0 2M16 2a1 1 0 1 0 0-2 1 1 0 0 0 0 2"></path></g><defs><clipPath id="logo_svg__a"><path fill="#fff" d="M0 0h120v48H0z"></path></clipPath></defs></svg></div><div class="flex flex-wrap gap-x-16 gap-y-5 py-8 md:pl-24 md:pr-[21.625rem]"> <div><h5 class="mb-4 text-lg font-semibold">Resources</h5><div class="flex justify-between gap-x-10"><div class="flex flex-col"><a target="_blank" rel="noopener noreferrer" href="https://docs.pinot.apache.org/" class="block py-1 text-gray-600 hover:text-gray-900">Docs</a><a target="_blank" rel="noopener noreferrer" href="https://docs.pinot.apache.org/getting-started" class="block py-1 text-gray-600 hover:text-gray-900">Getting Started</a><a target="_blank" rel="noopener noreferrer" href="https://docs.pinot.apache.org/integrations/thirdeye" class="block py-1 text-gray-600 hover:text-gray-900">ThirdEye</a></div><div class="flex flex-col"><a class="block py-1 text-gray-600 hover:text-gray-900" href="/powered-by">Company Stories</a><a class="block py-1 text-gray-600 hover:text-gray-900" href="/download">Download</a><a class="block py-1 text-gray-600 hover:text-gray-900" href="/blog">Blog</a></div></div></div><div><h5 class="mb-4 text-lg font-semibold">Apache</h5><div class="flex justify-between gap-x-10"><div class="flex flex-col"><a target="_blank" rel="noopener noreferrer" href="https://www.apache.org" class="block py-1 text-gray-600 hover:text-gray-900">Foundation</a><a target="_blank" rel="noopener noreferrer" href="https://www.apache.org/licenses" class="block py-1 text-gray-600 hover:text-gray-900">License</a><a target="_blank" rel="noopener noreferrer" href="https://www.apache.org/security" class="block py-1 text-gray-600 hover:text-gray-900">Security</a></div><div class="flex flex-col"><a target="_blank" rel="noopener noreferrer" href="https://www.apache.org/foundation/sponsorship.html" class="block py-1 text-gray-600 hover:text-gray-900">Sponsorship</a><a target="_blank" rel="noopener noreferrer" href="https://www.apache.org/events/current-event" class="block py-1 text-gray-600 hover:text-gray-900">Events</a><a target="_blank" rel="noopener noreferrer" href="https://www.apache.org/foundation/thanks.html" class="block py-1 text-gray-600 hover:text-gray-900">Thanks</a></div></div></div></div><div class="mt-4 flex justify-center md:mt-0"><a target="_blank" rel="noopener noreferrer" href="https://join.slack.com/t/apache-pinot/shared_invite/zt-5z7pav2f-yYtjZdVA~EDmrGkho87Vzw" class="mr-4"><svg xmlns="http://www.w3.org/2000/svg" width="24" height="24" viewBox="0 0 24 24" fill="none" stroke="currentColor" stroke-width="2" stroke-linecap="round" stroke-linejoin="round" class="lucide lucide-slack fill-gray-900"><rect width="3" height="8" x="13" y="2" rx="1.5"></rect><path d="M19 8.5V10h1.5A1.5 1.5 0 1 0 19 8.5"></path><rect width="3" height="8" x="8" y="14" rx="1.5"></rect><path d="M5 15.5V14H3.5A1.5 1.5 0 1 0 5 15.5"></path><rect width="8" height="3" x="14" y="13" rx="1.5"></rect><path d="M15.5 19H14v1.5a1.5 1.5 0 1 0 1.5-1.5"></path><rect width="8" height="3" x="2" y="8" rx="1.5"></rect><path d="M8.5 5H10V3.5A1.5 1.5 0 1 0 8.5 5"></path></svg></a><a target="_blank" rel="noopener noreferrer" href="https://github.com/apache/pinot"><svg xmlns="http://www.w3.org/2000/svg" width="24" height="24" fill="currentColor" size="24"><g clip-path="url(#github_svg__a)"><path fill-rule="evenodd" d="M12.01 0C5.369 0 0 5.5 0 12.304c0 5.44 3.44 10.043 8.212 11.673.597.122.815-.265.815-.59 0-.286-.02-1.264-.02-2.283-3.34.734-4.036-1.466-4.036-1.466-.537-1.426-1.332-1.793-1.332-1.793-1.094-.754.08-.754.08-.754 1.212.082 1.849 1.263 1.849 1.263 1.073 1.874 2.803 1.345 3.5 1.019.098-.795.417-1.345.755-1.65-2.665-.285-5.468-1.345-5.468-6.07 0-1.345.477-2.445 1.232-3.3-.119-.306-.537-1.57.12-3.26 0 0 1.014-.326 3.3 1.263.98-.27 1.989-.407 3.003-.408 1.014 0 2.048.143 3.002.408 2.287-1.59 3.301-1.263 3.301-1.263.657 1.69.239 2.954.12 3.26.775.855 1.232 1.955 1.232 3.3 0 4.725-2.803 5.764-5.488 6.07.438.387.815 1.12.815 2.281 0 1.65-.02 2.975-.02 3.382 0 .326.22.713.816.59C20.56 22.347 24 17.744 24 12.305 24.02 5.5 18.63 0 12.01 0" clip-rule="evenodd"></path></g><defs><clipPath id="github_svg__a"><path fill="#fff" d="M0 0h24v24H0z"></path></clipPath></defs></svg></a></div></div><div class="mt-8 border-t border-neutral-300 pt-4 text-left text-sm text-gray-600">Copyright © <!-- -->2024<!-- --> The Apache Software Foundation. Apache Pinot, Pinot, Apache, the Apache feather logo, and the Apache Pinot project logo are registered trademarks of The Apache Software Foundation. This page has references to third party software - Presto, PrestoDB, ThirdEye, Trino, TrinoDB, that are not part of the Apache Software Foundation and are not covered under the Apache License.</div></footer></div><script src="/_next/static/chunks/webpack-dde39ac7c1b4eb4b.js" crossorigin="" async=""></script><script>(self.__next_f=self.__next_f||[]).push([0]);self.__next_f.push([2,null])</script><script>self.__next_f.push([1,"1:HL[\"/_next/static/media/6905431624c34d00-s.p.woff2\",\"font\",{\"crossOrigin\":\"\",\"type\":\"font/woff2\"}]\n2:HL[\"/_next/static/css/9e925a33b1acdac1.css\",\"style\",{\"crossOrigin\":\"\"}]\n0:\"$L3\"\n"])</script><script>self.__next_f.push([1,"4:HL[\"/_next/static/css/c130d1629644f070.css\",\"style\",{\"crossOrigin\":\"\"}]\n"])</script><script>self.__next_f.push([1,"5:I[3728,[],\"\"]\n7:I[9928,[],\"\"]\n8:I[7821,[\"326\",\"static/chunks/326-3a90a6443b9c824c.js\",\"980\",\"static/chunks/980-6e243f9cd384c7d2.js\",\"702\",\"static/chunks/702-a2bf9fe707814b79.js\",\"185\",\"static/chunks/app/layout-776a485845c720ef.js\"],\"ThemeProviders\"]\n9:I[3994,[\"326\",\"static/chunks/326-3a90a6443b9c824c.js\",\"980\",\"static/chunks/980-6e243f9cd384c7d2.js\",\"702\",\"static/chunks/702-a2bf9fe707814b79.js\",\"185\",\"static/chunks/app/layout-776a485845c720ef.js\"],\"\"]\na:I[9640,[\"326\",\"static/chunks/326-3a90a6443b9c824c.js"])</script><script>self.__next_f.push([1,"\",\"980\",\"static/chunks/980-6e243f9cd384c7d2.js\",\"702\",\"static/chunks/702-a2bf9fe707814b79.js\",\"185\",\"static/chunks/app/layout-776a485845c720ef.js\"],\"AlgoliaSearchProvider\"]\nb:I[7975,[\"326\",\"static/chunks/326-3a90a6443b9c824c.js\",\"980\",\"static/chunks/980-6e243f9cd384c7d2.js\",\"702\",\"static/chunks/702-a2bf9fe707814b79.js\",\"185\",\"static/chunks/app/layout-776a485845c720ef.js\"],\"\"]\nc:I[6954,[],\"\"]\nd:I[7264,[],\"\"]\ne:I[8326,[\"326\",\"static/chunks/326-3a90a6443b9c824c.js\",\"413\",\"static/chunks/413-f9f40b83f7bb3f22.js\""])</script><script>self.__next_f.push([1,",\"980\",\"static/chunks/980-6e243f9cd384c7d2.js\",\"797\",\"static/chunks/app/blog/%5B...slug%5D/page-502e08b6677b55da.js\"],\"\"]\n11:T9fe,"])</script><script>self.__next_f.push([1,"M42.99 18.448c1.032-.553 2.21-.831 3.535-.831 1.542 0 2.938.38 4.187 1.14 1.248.76 2.236 1.841 2.965 3.241.728 1.402 1.091 3.025 1.091 4.872s-.363 3.482-1.091 4.903c-.729 1.424-1.717 2.525-2.965 3.307-1.25.782-2.645 1.173-4.187 1.173-1.325 0-2.493-.271-3.503-.815-1.01-.543-1.83-1.226-2.46-2.053v14.612H36V17.912h4.562v2.606c.586-.825 1.395-1.515 2.426-2.068l.002-.002m6.452 5.605c-.445-.793-1.032-1.395-1.76-1.808a4.72 4.72 0 0 0-2.362-.618c-.847 0-1.602.211-2.33.635-.728.423-1.315 1.038-1.76 1.841-.445.804-.668 1.749-.668 2.835 0 1.087.221 2.032.668 2.835.445.804 1.032 1.417 1.76 1.842a4.557 4.557 0 0 0 2.33.635 4.57 4.57 0 0 0 2.362-.652c.728-.435 1.313-1.053 1.76-1.856.445-.804.668-1.76.668-2.867s-.223-2.025-.668-2.818v-.004M62.947 17.912v18.051h-4.562V17.912h4.562m.551-6.079a2.833 2.833 0 1 1-5.666 0 2.833 2.833 0 0 1 5.666 0M82.954 19.687c1.325 1.358 1.988 3.253 1.988 5.685v10.59H80.38v-9.97c0-1.434-.358-2.537-1.075-3.307-.717-.772-1.695-1.157-2.933-1.157-1.239 0-2.254.387-2.982 1.157-.728.772-1.091 1.873-1.091 3.307v9.97h-4.562V17.91h4.562v2.248a6.322 6.322 0 0 1 2.33-1.841c.944-.445 1.981-.669 3.111-.669 2.15 0 3.889.68 5.214 2.037v.002M92.892 35.098c-1.39-.77-2.482-1.861-3.275-3.275-.794-1.411-1.19-3.041-1.19-4.888s.406-3.475 1.221-4.888a8.502 8.502 0 0 1 3.34-3.275c1.412-.772 2.987-1.157 4.725-1.157 1.739 0 3.312.387 4.725 1.157a8.5 8.5 0 0 1 3.34 3.275c.815 1.411 1.222 3.041 1.222 4.888s-.418 3.475-1.255 4.888a8.708 8.708 0 0 1-3.388 3.275c-1.424.772-3.014 1.157-4.774 1.157-1.76 0-3.301-.385-4.691-1.157m7.021-3.421c.729-.402 1.309-1.005 1.744-1.809.435-.803.651-1.781.651-2.933 0-1.715-.451-3.035-1.351-3.958-.902-.924-2.004-1.385-3.307-1.385s-2.395.461-3.275 1.385c-.88.923-1.32 2.243-1.32 3.958 0 1.715.428 3.035 1.287 3.958.858.924 1.938 1.385 3.241 1.385.825 0 1.602-.2 2.33-.603v.002M115.96 21.658v8.734c0 .608.147 1.048.44 1.32.293.271.787.406 1.482.406H120v3.845h-2.867c-3.845 0-5.766-1.868-5.766-5.605v-8.7h-2.15v-3.746h2.15V13l4.595-1v5.912h4.04v3.746h-4.042M20.03 46.757l-5.538-1.385A1.97 1.97 0 0 1 13 43.46v-5.462c0-.841.349-1.601.907-2.146a12.212 12.212 0 0 0 6.975-3.644c2.602-2.731 3.627-6.578 2.882-10.251L21 9h-4V4a1 1 0 0 0-2 0v7a1 1 0 0 1-2 0v-1a1 1 0 0 0-2 0v6.758a4.489 4.489 0 0 1 2.694-.755c2.278.095 4.156 1.934 4.297 4.21a4.501 4.501 0 0 1-6.992 4.029V29a1 1 0 0 1-2 0V7a1 1 0 0 0-2 0v2h-4L.237 21.957c-.745 3.675.279 7.52 2.882 10.251a12.202 12.202 0 0 0 6.975 3.644c.558.545.907 1.305.907 2.146V43.4c0 .938-.639 1.757-1.55 1.985l-5.48 1.37c-.57.143-.97.655-.97 1.243h18c0-.588-.4-1.1-.97-1.243v.002"])</script><script>self.__next_f.push([1,"3:[[[\"$\",\"link\",\"0\",{\"rel\":\"stylesheet\",\"href\":\"/_next/static/css/9e925a33b1acdac1.css\",\"precedence\":\"next\",\"crossOrigin\":\"\"}]],[\"$\",\"$L5\",null,{\"buildId\":\"rmcKjFZ3e9kKdH1iJwCIQ\",\"assetPrefix\":\"\",\"initialCanonicalUrl\":\"/blog/2023/05/16/star-tree-indexes-in-apache-pinot-part-1-understanding-the-impact-on-query-performance\",\"initialTree\":[\"\",{\"children\":[\"blog\",{\"children\":[[\"slug\",\"2023/05/16/star-tree-indexes-in-apache-pinot-part-1-understanding-the-impact-on-query-performance\",\"c\"],{\"children\":[\"__PAGE__?{\\\"slug\\\":[\\\"2023\\\",\\\"05\\\",\\\"16\\\",\\\"star-tree-indexes-in-apache-pinot-part-1-understanding-the-impact-on-query-performance\\\"]}\",{}]}]}]},\"$undefined\",\"$undefined\",true],\"initialHead\":[false,\"$L6\"],\"globalErrorComponent\":\"$7\",\"children\":[null,[\"$\",\"html\",null,{\"lang\":\"en-us\",\"className\":\"__variable_1fc36d scroll-smooth\",\"suppressHydrationWarning\":true,\"children\":[[\"$\",\"head\",null,{\"children\":[[\"$\",\"meta\",null,{\"httpEquiv\":\"Content-Security-Policy\",\"content\":\"default-src 'self';script-src 'self' 'unsafe-eval' 'unsafe-inline' giscus.app analytics.umami.is www.youtube.com www.googletagmanager.com www.google-analytics.com;style-src 'self' 'unsafe-inline';img-src * blob: data:;media-src *.s3.amazonaws.com;connect-src *;font-src 'self';frame-src www.youtube.com youtube.com giscus.app youtu.be https://www.youtube.com https://youtube.com;\"}],[\"$\",\"link\",null,{\"rel\":\"apple-touch-icon\",\"sizes\":\"76x76\",\"href\":\"/static/favicons/apple-touch-icon.png\"}],[\"$\",\"link\",null,{\"rel\":\"icon\",\"type\":\"image/png\",\"sizes\":\"32x32\",\"href\":\"/static/favicons/favicon-32x32.png\"}],[\"$\",\"link\",null,{\"rel\":\"icon\",\"type\":\"image/png\",\"sizes\":\"16x16\",\"href\":\"/static/favicons/favicon-16x16.png\"}],[\"$\",\"link\",null,{\"rel\":\"manifest\",\"href\":\"/static/favicons/site.webmanifest\"}],[\"$\",\"link\",null,{\"rel\":\"mask-icon\",\"href\":\"/static/favicons/safari-pinned-tab.svg\",\"color\":\"#5bbad5\"}],[\"$\",\"meta\",null,{\"name\":\"msapplication-TileColor\",\"content\":\"#000000\"}],[\"$\",\"meta\",null,{\"name\":\"theme-color\",\"media\":\"(prefers-color-scheme: light)\",\"content\":\"#fff\"}],[\"$\",\"meta\",null,{\"name\":\"theme-color\",\"media\":\"(prefers-color-scheme: dark)\",\"content\":\"#000\"}],[\"$\",\"link\",null,{\"rel\":\"alternate\",\"type\":\"application/rss+xml\",\"href\":\"/feed.xml\"}]]}],[\"$\",\"body\",null,{\"className\":\"bg-white text-black antialiased dark:bg-gray-950 dark:text-white\",\"children\":[\"$\",\"$L8\",null,{\"children\":[[\"$undefined\",\"$undefined\",\"$undefined\",\"$undefined\",[[\"$\",\"$L9\",null,{\"strategy\":\"afterInteractive\",\"src\":\"https://www.googletagmanager.com/gtag/js?id=G-ZXG79NJEBY\"}],[\"$\",\"$L9\",null,{\"strategy\":\"afterInteractive\",\"id\":\"ga-script\",\"children\":\"\\n window.dataLayer = window.dataLayer || [];\\n function gtag(){dataLayer.push(arguments);}\\n gtag('js', new Date());\\n gtag('config', 'G-ZXG79NJEBY');\\n \"}]]],[\"$\",\"div\",null,{\"className\":\"mx-auto flex max-w-screen-customDesktop flex-col justify-between font-sans\",\"children\":[\"$\",\"$La\",null,{\"algoliaConfig\":{\"appId\":\"CKRA00L2X9\",\"apiKey\":\"6531f8f7783a88d76629190843f1801e\",\"indexName\":\"prod_apache_pinot_docs\"},\"children\":[[\"$\",\"$Lb\",null,{}],[\"$\",\"main\",null,{\"children\":[\"$\",\"$Lc\",null,{\"parallelRouterKey\":\"children\",\"segmentPath\":[\"children\"],\"loading\":\"$undefined\",\"loadingStyles\":\"$undefined\",\"loadingScripts\":\"$undefined\",\"hasLoading\":false,\"error\":\"$undefined\",\"errorStyles\":\"$undefined\",\"errorScripts\":\"$undefined\",\"template\":[\"$\",\"$Ld\",null,{}],\"templateStyles\":\"$undefined\",\"templateScripts\":\"$undefined\",\"notFound\":[\"$\",\"div\",null,{\"className\":\"flex flex-col items-start justify-start md:mt-24 md:flex-row md:items-center md:justify-center md:space-x-6\",\"children\":[[\"$\",\"div\",null,{\"className\":\"space-x-2 pb-8 pt-6 md:space-y-5\",\"children\":[\"$\",\"h1\",null,{\"className\":\"text-6xl font-extrabold leading-9 tracking-tight text-gray-900 dark:text-gray-100 md:border-r-2 md:px-6 md:text-8xl md:leading-14\",\"children\":\"404\"}]}],[\"$\",\"div\",null,{\"className\":\"max-w-md\",\"children\":[[\"$\",\"p\",null,{\"className\":\"mb-4 text-xl font-bold leading-normal md:text-2xl\",\"children\":\"Sorry we couldn't find this page.\"}],[\"$\",\"p\",null,{\"className\":\"mb-8\",\"children\":\"But dont worry, you can find plenty of other things on our homepage.\"}],[\"$\",\"$Le\",null,{\"href\":\"/\",\"className\":\"focus:shadow-outline-blue inline rounded-lg border border-transparent bg-blue-600 px-4 py-2 text-sm font-medium leading-5 text-white shadow transition-colors duration-150 hover:bg-blue-700 focus:outline-none dark:hover:bg-blue-500\",\"children\":\"Back to homepage\"}]]}]]}],\"notFoundStyles\":[],\"initialChildNode\":[\"$\",\"$Lc\",null,{\"parallelRouterKey\":\"children\",\"segmentPath\":[\"children\",\"blog\",\"children\"],\"loading\":\"$undefined\",\"loadingStyles\":\"$undefined\",\"loadingScripts\":\"$undefined\",\"hasLoading\":false,\"error\":\"$undefined\",\"errorStyles\":\"$undefined\",\"errorScripts\":\"$undefined\",\"template\":[\"$\",\"$Ld\",null,{}],\"templateStyles\":\"$undefined\",\"templateScripts\":\"$undefined\",\"notFound\":\"$undefined\",\"notFoundStyles\":\"$undefined\",\"initialChildNode\":[\"$\",\"$Lc\",null,{\"parallelRouterKey\":\"children\",\"segmentPath\":[\"children\",\"blog\",\"children\",[\"slug\",\"2023/05/16/star-tree-indexes-in-apache-pinot-part-1-understanding-the-impact-on-query-performance\",\"c\"],\"children\"],\"loading\":\"$undefined\",\"loadingStyles\":\"$undefined\",\"loadingScripts\":\"$undefined\",\"hasLoading\":false,\"error\":\"$undefined\",\"errorStyles\":\"$undefined\",\"errorScripts\":\"$undefined\",\"template\":[\"$\",\"$Ld\",null,{}],\"templateStyles\":\"$undefined\",\"templateScripts\":\"$undefined\",\"notFound\":\"$undefined\",\"notFoundStyles\":\"$undefined\",\"initialChildNode\":[\"$Lf\",\"$L10\",null],\"childPropSegment\":\"__PAGE__?{\\\"slug\\\":[\\\"2023\\\",\\\"05\\\",\\\"16\\\",\\\"star-tree-indexes-in-apache-pinot-part-1-understanding-the-impact-on-query-performance\\\"]}\",\"styles\":[[\"$\",\"link\",\"0\",{\"rel\":\"stylesheet\",\"href\":\"/_next/static/css/c130d1629644f070.css\",\"precedence\":\"next\",\"crossOrigin\":\"\"}]]}],\"childPropSegment\":[\"slug\",\"2023/05/16/star-tree-indexes-in-apache-pinot-part-1-understanding-the-impact-on-query-performance\",\"c\"],\"styles\":null}],\"childPropSegment\":\"blog\",\"styles\":null}]}],[\"$\",\"footer\",null,{\"className\":\"border-t bg-sky-100 px-5 py-10 md:px-[6.75rem] md:pb-10 md:pt-16\",\"children\":[[\"$\",\"div\",null,{\"className\":\"mx-auto flex max-w-7xl flex-wrap justify-between\",\"children\":[[\"$\",\"div\",null,{\"className\":\"flex-shrink-0\",\"children\":[\"$\",\"svg\",null,{\"xmlns\":\"http://www.w3.org/2000/svg\",\"width\":120,\"height\":48,\"fill\":\"none\",\"children\":[[\"$\",\"g\",null,{\"fill\":\"#C7154A\",\"clipPath\":\"url(#logo_svg__a)\",\"children\":[[\"$\",\"path\",null,{\"d\":\"$11\"}],[\"$\",\"path\",null,{\"d\":\"M13.5 23a2.5 2.5 0 1 0 0-5 2.5 2.5 0 0 0 0 5M8 5a1 1 0 1 0 0-2 1 1 0 0 0 0 2M12 8a1 1 0 1 0 0-2 1 1 0 0 0 0 2M16 2a1 1 0 1 0 0-2 1 1 0 0 0 0 2\"}]]}],[\"$\",\"defs\",null,{\"children\":[\"$\",\"clipPath\",null,{\"id\":\"logo_svg__a\",\"children\":[\"$\",\"path\",null,{\"fill\":\"#fff\",\"d\":\"M0 0h120v48H0z\"}]}]}]]}]}],[\"$\",\"div\",null,{\"className\":\"flex flex-wrap gap-x-16 gap-y-5 py-8 md:pl-24 md:pr-[21.625rem]\",\"children\":[\" \",[[\"$\",\"div\",\"Resources\",{\"children\":[[\"$\",\"h5\",null,{\"className\":\"mb-4 text-lg font-semibold\",\"children\":\"Resources\"}],[\"$\",\"div\",null,{\"className\":\"flex justify-between gap-x-10\",\"children\":[[\"$\",\"div\",null,{\"className\":\"flex flex-col\",\"children\":[[\"$\",\"a\",null,{\"target\":\"_blank\",\"rel\":\"noopener noreferrer\",\"href\":\"https://docs.pinot.apache.org/\",\"className\":\"block py-1 text-gray-600 hover:text-gray-900\",\"children\":\"Docs\"}],[\"$\",\"a\",null,{\"target\":\"_blank\",\"rel\":\"noopener noreferrer\",\"href\":\"https://docs.pinot.apache.org/getting-started\",\"className\":\"block py-1 text-gray-600 hover:text-gray-900\",\"children\":\"Getting Started\"}],[\"$\",\"a\",null,{\"target\":\"_blank\",\"rel\":\"noopener noreferrer\",\"href\":\"https://docs.pinot.apache.org/integrations/thirdeye\",\"className\":\"block py-1 text-gray-600 hover:text-gray-900\",\"children\":\"ThirdEye\"}]]}],[\"$\",\"div\",null,{\"className\":\"flex flex-col\",\"children\":[[\"$\",\"$Le\",null,{\"href\":\"/powered-by\",\"className\":\"block py-1 text-gray-600 hover:text-gray-900\",\"children\":\"Company Stories\"}],[\"$\",\"$Le\",null,{\"href\":\"/download\",\"className\":\"block py-1 text-gray-600 hover:text-gray-900\",\"children\":\"Download\"}],[\"$\",\"$Le\",null,{\"href\":\"/blog\",\"className\":\"block py-1 text-gray-600 hover:text-gray-900\",\"children\":\"Blog\"}]]}]]}]]}],[\"$\",\"div\",\"Apache\",{\"children\":[[\"$\",\"h5\",null,{\"className\":\"mb-4 text-lg font-semibold\",\"children\":\"Apache\"}],[\"$\",\"div\",null,{\"className\":\"flex justify-between gap-x-10\",\"children\":[[\"$\",\"div\",null,{\"className\":\"flex flex-col\",\"children\":[[\"$\",\"a\",null,{\"target\":\"_blank\",\"rel\":\"noopener noreferrer\",\"href\":\"https://www.apache.org\",\"className\":\"block py-1 text-gray-600 hover:text-gray-900\",\"children\":\"Foundation\"}],[\"$\",\"a\",null,{\"target\":\"_blank\",\"rel\":\"noopener noreferrer\",\"href\":\"https://www.apache.org/licenses\",\"className\":\"block py-1 text-gray-600 hover:text-gray-900\",\"children\":\"License\"}],[\"$\",\"a\",null,{\"target\":\"_blank\",\"rel\":\"noopener noreferrer\",\"href\":\"https://www.apache.org/security\",\"className\":\"block py-1 text-gray-600 hover:text-gray-900\",\"children\":\"Security\"}]]}],[\"$\",\"div\",null,{\"className\":\"flex flex-col\",\"children\":[[\"$\",\"a\",null,{\"target\":\"_blank\",\"rel\":\"noopener noreferrer\",\"href\":\"https://www.apache.org/foundation/sponsorship.html\",\"className\":\"block py-1 text-gray-600 hover:text-gray-900\",\"children\":\"Sponsorship\"}],[\"$\",\"a\",null,{\"target\":\"_blank\",\"rel\":\"noopener noreferrer\",\"href\":\"https://www.apache.org/events/current-event\",\"className\":\"block py-1 text-gray-600 hover:text-gray-900\",\"children\":\"Events\"}],[\"$\",\"a\",null,{\"target\":\"_blank\",\"rel\":\"noopener noreferrer\",\"href\":\"https://www.apache.org/foundation/thanks.html\",\"className\":\"block py-1 text-gray-600 hover:text-gray-900\",\"children\":\"Thanks\"}]]}]]}]]}]]]}],[\"$\",\"div\",null,{\"className\":\"mt-4 flex justify-center md:mt-0\",\"children\":[[\"$\",\"a\",null,{\"target\":\"_blank\",\"rel\":\"noopener noreferrer\",\"href\":\"https://join.slack.com/t/apache-pinot/shared_invite/zt-5z7pav2f-yYtjZdVA~EDmrGkho87Vzw\",\"className\":\"mr-4\",\"children\":[\"$\",\"svg\",null,{\"xmlns\":\"http://www.w3.org/2000/svg\",\"width\":24,\"height\":24,\"viewBox\":\"0 0 24 24\",\"fill\":\"none\",\"stroke\":\"currentColor\",\"strokeWidth\":2,\"strokeLinecap\":\"round\",\"strokeLinejoin\":\"round\",\"className\":\"lucide lucide-slack fill-gray-900\",\"children\":[[\"$\",\"rect\",\"diqz80\",{\"width\":\"3\",\"height\":\"8\",\"x\":\"13\",\"y\":\"2\",\"rx\":\"1.5\"}],[\"$\",\"path\",\"183iwg\",{\"d\":\"M19 8.5V10h1.5A1.5 1.5 0 1 0 19 8.5\"}],[\"$\",\"rect\",\"hqg7r1\",{\"width\":\"3\",\"height\":\"8\",\"x\":\"8\",\"y\":\"14\",\"rx\":\"1.5\"}],[\"$\",\"path\",\"76g71w\",{\"d\":\"M5 15.5V14H3.5A1.5 1.5 0 1 0 5 15.5\"}],[\"$\",\"rect\",\"1kmz0a\",{\"width\":\"8\",\"height\":\"3\",\"x\":\"14\",\"y\":\"13\",\"rx\":\"1.5\"}],[\"$\",\"path\",\"jc4sz0\",{\"d\":\"M15.5 19H14v1.5a1.5 1.5 0 1 0 1.5-1.5\"}],[\"$\",\"rect\",\"1omvl4\",{\"width\":\"8\",\"height\":\"3\",\"x\":\"2\",\"y\":\"8\",\"rx\":\"1.5\"}],[\"$\",\"path\",\"16f3cl\",{\"d\":\"M8.5 5H10V3.5A1.5 1.5 0 1 0 8.5 5\"}],\"$undefined\"]}]}],[\"$\",\"a\",null,{\"target\":\"_blank\",\"rel\":\"noopener noreferrer\",\"href\":\"https://github.com/apache/pinot\",\"children\":[\"$\",\"svg\",null,{\"xmlns\":\"http://www.w3.org/2000/svg\",\"width\":24,\"height\":24,\"fill\":\"currentColor\",\"size\":24,\"children\":[[\"$\",\"g\",null,{\"clipPath\":\"url(#github_svg__a)\",\"children\":[\"$\",\"path\",null,{\"fillRule\":\"evenodd\",\"d\":\"M12.01 0C5.369 0 0 5.5 0 12.304c0 5.44 3.44 10.043 8.212 11.673.597.122.815-.265.815-.59 0-.286-.02-1.264-.02-2.283-3.34.734-4.036-1.466-4.036-1.466-.537-1.426-1.332-1.793-1.332-1.793-1.094-.754.08-.754.08-.754 1.212.082 1.849 1.263 1.849 1.263 1.073 1.874 2.803 1.345 3.5 1.019.098-.795.417-1.345.755-1.65-2.665-.285-5.468-1.345-5.468-6.07 0-1.345.477-2.445 1.232-3.3-.119-.306-.537-1.57.12-3.26 0 0 1.014-.326 3.3 1.263.98-.27 1.989-.407 3.003-.408 1.014 0 2.048.143 3.002.408 2.287-1.59 3.301-1.263 3.301-1.263.657 1.69.239 2.954.12 3.26.775.855 1.232 1.955 1.232 3.3 0 4.725-2.803 5.764-5.488 6.07.438.387.815 1.12.815 2.281 0 1.65-.02 2.975-.02 3.382 0 .326.22.713.816.59C20.56 22.347 24 17.744 24 12.305 24.02 5.5 18.63 0 12.01 0\",\"clipRule\":\"evenodd\"}]}],[\"$\",\"defs\",null,{\"children\":[\"$\",\"clipPath\",null,{\"id\":\"github_svg__a\",\"children\":[\"$\",\"path\",null,{\"fill\":\"#fff\",\"d\":\"M0 0h24v24H0z\"}]}]}]]}]}]]}]]}],[\"$\",\"div\",null,{\"className\":\"mt-8 border-t border-neutral-300 pt-4 text-left text-sm text-gray-600\",\"children\":[\"Copyright © \",2024,\" The Apache Software Foundation. Apache Pinot, Pinot, Apache, the Apache feather logo, and the Apache Pinot project logo are registered trademarks of The Apache Software Foundation. This page has references to third party software - Presto, PrestoDB, ThirdEye, Trino, TrinoDB, that are not part of the Apache Software Foundation and are not covered under the Apache License.\"]}]]}]]}]}]]}]}]]}],null]}]]\n"])</script><script>self.__next_f.push([1,"12:I[1514,[\"326\",\"static/chunks/326-3a90a6443b9c824c.js\",\"413\",\"static/chunks/413-f9f40b83f7bb3f22.js\",\"980\",\"static/chunks/980-6e243f9cd384c7d2.js\",\"797\",\"static/chunks/app/blog/%5B...slug%5D/page-502e08b6677b55da.js\"],\"\"]\n13:I[2529,[\"326\",\"static/chunks/326-3a90a6443b9c824c.js\",\"413\",\"static/chunks/413-f9f40b83f7bb3f22.js\",\"980\",\"static/chunks/980-6e243f9cd384c7d2.js\",\"797\",\"static/chunks/app/blog/%5B...slug%5D/page-502e08b6677b55da.js\"],\"\"]\n14:I[5185,[\"326\",\"static/chunks/326-3a90a6443b9c824c.js\",\"413\",\""])</script><script>self.__next_f.push([1,"static/chunks/413-f9f40b83f7bb3f22.js\",\"980\",\"static/chunks/980-6e243f9cd384c7d2.js\",\"797\",\"static/chunks/app/blog/%5B...slug%5D/page-502e08b6677b55da.js\"],\"\"]\n"])</script><script>self.__next_f.push([1,"10:[[\"$\",\"script\",null,{\"type\":\"application/ld+json\",\"dangerouslySetInnerHTML\":{\"__html\":\"{\\\"@context\\\":\\\"https://schema.org\\\",\\\"@type\\\":\\\"BlogPosting\\\",\\\"headline\\\":\\\"StarTree Indexes in Apache Pinot Part-1 - Understanding the Impact on Query Performance\\\",\\\"datePublished\\\":\\\"2023-05-16T00:00:00.000Z\\\",\\\"dateModified\\\":\\\"2023-05-16T00:00:00.000Z\\\",\\\"description\\\":\\\"The blog post explains the star-tree index in Apache Pinot and its benefits compared to traditional materialized views. By implementing a star-tree index, query performance significantly improved, reducing query latency from 1,513 ms to just 4 ms and drastically reducing disk reads by 99.999%.\\\",\\\"image\\\":\\\"/static/images/twitter-card.png\\\",\\\"url\\\":\\\"https://pinot.apache.org/blog/2023-05-16-star-tree-indexes-in-apache-pinot-part-1-understanding-the-impact-on-query-performance\\\",\\\"author\\\":[{\\\"@type\\\":\\\"Person\\\",\\\"name\\\":\\\"Sandeep Dabade\\\"}]}\"}}],[\"$\",\"section\",null,{\"className\":\" px-5 pt-10 md:px-[13.313rem] md:py-16\",\"children\":[[\"$\",\"$L12\",null,{}],[\"$\",\"article\",null,{\"className\":\"\",\"children\":[\"$\",\"div\",null,{\"className\":\"mx-auto lg:flex\",\"children\":[[\"$\",\"div\",null,{\"className\":\"lg:pr-12\",\"children\":[[\"$\",\"header\",null,{\"className\":\"pt-6 md:pr-10\",\"children\":[[\"$\",\"h1\",null,{\"className\":\"text-4xl font-semibold\",\"children\":\"StarTree Indexes in Apache Pinot Part-1 - Understanding the Impact on Query Performance\"}],[\"$\",\"p\",null,{\"className\":\"pt-2 text-lg\",\"children\":[\"By: \",\"Sandeep Dabade\"]}],[\"$\",\"p\",null,{\"className\":\"py-2 text-sm\",\"children\":[\"May 16th, 2023\",\" • \",\"7 min read\"]}]]}],[\"$\",\"div\",null,{\"className\":\"flex flex-col lg:flex-row\",\"children\":[\"$\",\"main\",null,{\"className\":\"\",\"children\":[\"$\",\"div\",null,{\"className\":\"prose max-w-[45rem] pb-8 pt-10 dark:prose-invert\",\"children\":[[\"$\",\"p\",null,{\"children\":[\"Star-tree is a \",[\"$\",\"a\",null,{\"target\":\"_blank\",\"rel\":\"noopener noreferrer\",\"href\":\"https://docs.pinot.apache.org/basics/indexing/star-tree-index\",\"children\":\"specialized index\"}],\" in \",[\"$\",\"a\",null,{\"target\":\"_blank\",\"rel\":\"noopener noreferrer\",\"href\":\"https://startree.ai/resources/what-is-apache-pinot\",\"children\":\"Apache Pinot™\"}],\". This index dynamically builds a tree structure to maintain aggregates for a group of dimensions. With star-tree Index, the query latency becomes a function of just a tree traversal with computational complexity of log(\",[\"$\",\"em\",null,{\"children\":\"n\"}],\").\"]}],[\"$\",\"p\",null,{\"children\":[[\"$\",\"a\",null,{\"target\":\"_blank\",\"rel\":\"noopener noreferrer\",\"href\":\"https://startree.ai/blog/a-tale-of-three-real-time-olap-databases#query\",\"children\":\"This comprehensive blog\"}],\" explains in depth how the star-tree Index differs from traditional materialized views (MVs). In particular, read the section Star-Tree Index: Pinot’s intelligent materialized view. Particularly this one key passage:\"]}],[\"$\",\"p\",null,{\"children\":[\"$\",\"em\",null,{\"children\":\"Star-Tree Index: Pinot’s Intelligent Materialized View:\"}]}],[\"$\",\"p\",null,{\"children\":[\"$\",\"em\",null,{\"children\":\"The star-tree index provides an intelligent way to build materialized views within Pinot. Traditional MVs work by fully materializing the computation for each source record that matches the specified predicates. Although useful, this can result in non-trivial storage overhead. On the other hand, the star-tree index allows us to partially materialize the computations and provide the ability to tune the space-time tradeoff by providing a configurable threshold between pre-aggregation and data scans.\"}]}],[\"$\",\"p\",null,{\"children\":[\"$\",\"img\",null,{\"alt\":\"\",\"src\":\"https://www.datocms-assets.com/75153/1684246698-image5.png\"}]}],[\"$\",\"p\",null,{\"children\":\"In this three-part blog series, we will compare and contrast query performance of a star-tree index with an inverted index, something that most of the OLAP databases end up using for such queries.\"}],[\"$\",\"p\",null,{\"children\":\"In this first part, we will showcase how a star-tree index brought down standalone query latency on a sizable dataset of ~633M records from 1,513ms to 4ms! — nearly 380x faster.\"}],[\"$\",\"p\",null,{\"children\":[\"$\",\"img\",null,{\"alt\":\"\",\"src\":\"https://www.datocms-assets.com/75153/1684246806-image7.png\"}]}],[\"$\",\"h2\",null,{\"id\":\"1-the-dataset\",\"children\":[[\"$\",\"a\",null,{\"href\":\"#1-the-dataset\",\"aria-hidden\":\"true\",\"tabIndex\":\"-1\",\"children\":[\"$\",\"span\",null,{\"className\":\"icon icon-link\"}]}],\"1. The Dataset:\"]}],[\"$\",\"p\",null,{\"children\":[\"We used New York City Taxi Data for this comparison. Original source: \",[\"$\",\"a\",null,{\"target\":\"_blank\",\"rel\":\"noopener noreferrer\",\"href\":\"https://www.kaggle.com/c/nyc-taxi-trip-duration\",\"children\":\"here\"}],\". Below are the high level details about this dataset.\"]}],[\"$\",\"p\",null,{\"children\":[\"$\",\"img\",null,{\"alt\":\"\",\"src\":\"https://www.datocms-assets.com/75153/1684246816-image6.png\"}]}],[\"$\",\"h3\",null,{\"id\":\"schema\",\"children\":[[\"$\",\"a\",null,{\"href\":\"#schema\",\"aria-hidden\":\"true\",\"tabIndex\":\"-1\",\"children\":[\"$\",\"span\",null,{\"className\":\"icon icon-link\"}]}],\"Schema:\"]}],[\"$\",\"p\",null,{\"children\":\"The dataset has 8 dimension fields and 11 metric columns as listed below.\"}],[\"$\",\"p\",null,{\"children\":[\"$\",\"img\",null,{\"alt\":\"\",\"src\":\"https://www.datocms-assets.com/75153/1684246732-image2.png\"}]}],[\"$\",\"h2\",null,{\"id\":\"2-query-pattern\",\"children\":[[\"$\",\"a\",null,{\"href\":\"#2-query-pattern\",\"aria-hidden\":\"true\",\"tabIndex\":\"-1\",\"children\":[\"$\",\"span\",null,{\"className\":\"icon icon-link\"}]}],\"2. Query Pattern\"]}],[\"$\",\"p\",null,{\"children\":\"The query pattern involved slicing and dicing the data (GROUPING) BY various dimensions (Date, Month and Year), aggregating different metrics (total trips, distance and passengers count) and FILTERING BY a time range that could go as wide as 1 year.\"}],[\"$\",\"p\",null,{\"children\":[\"$\",\"img\",null,{\"alt\":\"\",\"src\":\"https://www.datocms-assets.com/75153/1684246872-image10.png\"}]}],[\"$\",\"p\",null,{\"children\":\"Note: A key thing to note is that a single star-tree index covers a wide range of OLAP queries that comprise the dimensions, metrics and aggregate functions specified in it.\"}],[\"$\",\"h3\",null,{\"id\":\"star-tree-index-config\",\"children\":[[\"$\",\"a\",null,{\"href\":\"#star-tree-index-config\",\"aria-hidden\":\"true\",\"tabIndex\":\"-1\",\"children\":[\"$\",\"span\",null,{\"className\":\"icon icon-link\"}]}],\"Star-Tree Index Config:\"]}],[\"$\",\"p\",null,{\"children\":\"To support the various query patterns specified above, we defined the following star-tree index.\"}],[\"$\",\"$L13\",null,{\"className\":\"language-json\",\"children\":[\"$\",\"code\",null,{\"className\":\"code-highlight language-json\",\"children\":[[\"$\",\"span\",null,{\"className\":\"code-line\",\"children\":[[\"$\",\"span\",null,{\"className\":\"token property\",\"children\":\"\\\"starTreeIndexConfigs\\\"\"}],[\"$\",\"span\",null,{\"className\":\"token operator\",\"children\":\":\"}],\" \",[\"$\",\"span\",null,{\"className\":\"token punctuation\",\"children\":\"[\"}],\"\\n\"]}],[\"$\",\"span\",null,{\"className\":\"code-line\",\"children\":[\" \",[\"$\",\"span\",null,{\"className\":\"token punctuation\",\"children\":\"{\"}],\"\\n\"]}],[\"$\",\"span\",null,{\"className\":\"code-line\",\"children\":[\" \",[\"$\",\"span\",null,{\"className\":\"token property\",\"children\":\"\\\"dimensionsSplitOrder\\\"\"}],[\"$\",\"span\",null,{\"className\":\"token operator\",\"children\":\":\"}],\" \",[\"$\",\"span\",null,{\"className\":\"token punctuation\",\"children\":\"[\"}],\"\\n\"]}],[\"$\",\"span\",null,{\"className\":\"code-line\",\"children\":[\" \",[\"$\",\"span\",null,{\"className\":\"token string\",\"children\":\"\\\"dropoff_date_str\\\"\"}],[\"$\",\"span\",null,{\"className\":\"token punctuation\",\"children\":\",\"}],\"\\n\"]}],[\"$\",\"span\",null,{\"className\":\"code-line\",\"children\":[\" \",[\"$\",\"span\",null,{\"className\":\"token string\",\"children\":\"\\\"dropoff_month\\\"\"}],[\"$\",\"span\",null,{\"className\":\"token punctuation\",\"children\":\",\"}],\"\\n\"]}],[\"$\",\"span\",null,{\"className\":\"code-line\",\"children\":[\" \",[\"$\",\"span\",null,{\"className\":\"token string\",\"children\":\"\\\"dropoff_year\\\"\"}],\"\\n\"]}],[\"$\",\"span\",null,{\"className\":\"code-line\",\"children\":[\" \",[\"$\",\"span\",null,{\"className\":\"token punctuation\",\"children\":\"]\"}],[\"$\",\"span\",null,{\"className\":\"token punctuation\",\"children\":\",\"}],\"\\n\"]}],[\"$\",\"span\",null,{\"className\":\"code-line\",\"children\":[\" \",[\"$\",\"span\",null,{\"className\":\"token property\",\"children\":\"\\\"skipStarNodeCreationForDimensions\\\"\"}],[\"$\",\"span\",null,{\"className\":\"token operator\",\"children\":\":\"}],\" \",[\"$\",\"span\",null,{\"className\":\"token punctuation\",\"children\":\"[\"}],[\"$\",\"span\",null,{\"className\":\"token punctuation\",\"children\":\"]\"}],[\"$\",\"span\",null,{\"className\":\"token punctuation\",\"children\":\",\"}],\"\\n\"]}],[\"$\",\"span\",null,{\"className\":\"code-line\",\"children\":[\" \",[\"$\",\"span\",null,{\"className\":\"token property\",\"children\":\"\\\"functionColumnPairs\\\"\"}],[\"$\",\"span\",null,{\"className\":\"token operator\",\"children\":\":\"}],\" \",[\"$\",\"span\",null,{\"className\":\"token punctuation\",\"children\":\"[\"}],\"\\n\"]}],[\"$\",\"span\",null,{\"className\":\"code-line\",\"children\":[\" \",[\"$\",\"span\",null,{\"className\":\"token string\",\"children\":\"\\\"COUNT__*\\\"\"}],[\"$\",\"span\",null,{\"className\":\"token punctuation\",\"children\":\",\"}],\"\\n\"]}],[\"$\",\"span\",null,{\"className\":\"code-line\",\"children\":[\" \",[\"$\",\"span\",null,{\"className\":\"token string\",\"children\":\"\\\"SUM__passenger_count\\\"\"}],[\"$\",\"span\",null,{\"className\":\"token punctuation\",\"children\":\",\"}],\"\\n\"]}],[\"$\",\"span\",null,{\"className\":\"code-line\",\"children\":[\" \",[\"$\",\"span\",null,{\"className\":\"token string\",\"children\":\"\\\"SUM__total_amount\\\"\"}],[\"$\",\"span\",null,{\"className\":\"token punctuation\",\"children\":\",\"}],\"\\n\"]}],[\"$\",\"span\",null,{\"className\":\"code-line\",\"children\":[\" \",[\"$\",\"span\",null,{\"className\":\"token string\",\"children\":\"\\\"SUM__trip_distance\\\"\"}],[\"$\",\"span\",null,{\"className\":\"token punctuation\",\"children\":\",\"}],\"\\n\"]}],[\"$\",\"span\",null,{\"className\":\"code-line\",\"children\":[\" \",[\"$\",\"span\",null,{\"className\":\"token string\",\"children\":\"\\\"AVG__passenger_count\\\"\"}],[\"$\",\"span\",null,{\"className\":\"token punctuation\",\"children\":\",\"}],\"\\n\"]}],[\"$\",\"span\",null,{\"className\":\"code-line\",\"children\":[\" \",[\"$\",\"span\",null,{\"className\":\"token string\",\"children\":\"\\\"AVG__total_amount\\\"\"}],[\"$\",\"span\",null,{\"className\":\"token punctuation\",\"children\":\",\"}],\"\\n\"]}],[\"$\",\"span\",null,{\"className\":\"code-line\",\"children\":[\" \",[\"$\",\"span\",null,{\"className\":\"token string\",\"children\":\"\\\"AVG__trip_distance\\\"\"}],[\"$\",\"span\",null,{\"className\":\"token punctuation\",\"children\":\",\"}],\"\\n\"]}],[\"$\",\"span\",null,{\"className\":\"code-line\",\"children\":[\" \",[\"$\",\"span\",null,{\"className\":\"token string\",\"children\":\"\\\"MIN__passenger_count\\\"\"}],[\"$\",\"span\",null,{\"className\":\"token punctuation\",\"children\":\",\"}],\"\\n\"]}],[\"$\",\"span\",null,{\"className\":\"code-line\",\"children\":[\" \",[\"$\",\"span\",null,{\"className\":\"token string\",\"children\":\"\\\"MIN__total_amount\\\"\"}],[\"$\",\"span\",null,{\"className\":\"token punctuation\",\"children\":\",\"}],\"\\n\"]}],[\"$\",\"span\",null,{\"className\":\"code-line\",\"children\":[\" \",[\"$\",\"span\",null,{\"className\":\"token string\",\"children\":\"\\\"MIN__trip_distance\\\"\"}],[\"$\",\"span\",null,{\"className\":\"token punctuation\",\"children\":\",\"}],\"\\n\"]}],[\"$\",\"span\",null,{\"className\":\"code-line\",\"children\":[\" \",[\"$\",\"span\",null,{\"className\":\"token string\",\"children\":\"\\\"MAX__passenger_count\\\"\"}],[\"$\",\"span\",null,{\"className\":\"token punctuation\",\"children\":\",\"}],\"\\n\"]}],[\"$\",\"span\",null,{\"className\":\"code-line\",\"children\":[\" \",[\"$\",\"span\",null,{\"className\":\"token string\",\"children\":\"\\\"MAX__total_amount\\\"\"}],[\"$\",\"span\",null,{\"className\":\"token punctuation\",\"children\":\",\"}],\"\\n\"]}],[\"$\",\"span\",null,{\"className\":\"code-line\",\"children\":[\" \",[\"$\",\"span\",null,{\"className\":\"token string\",\"children\":\"\\\"MAX__trip_distance\\\"\"}],\"\\n\"]}],[\"$\",\"span\",null,{\"className\":\"code-line\",\"children\":[\" \",[\"$\",\"span\",null,{\"className\":\"token punctuation\",\"children\":\"]\"}],[\"$\",\"span\",null,{\"className\":\"token punctuation\",\"children\":\",\"}],\"\\n\"]}],[\"$\",\"span\",null,{\"className\":\"code-line\",\"children\":[\" \",[\"$\",\"span\",null,{\"className\":\"token property\",\"children\":\"\\\"maxLeafRecords\\\"\"}],[\"$\",\"span\",null,{\"className\":\"token operator\",\"children\":\":\"}],\" \",[\"$\",\"span\",null,{\"className\":\"token number\",\"children\":\"10000\"}],\"\\n\"]}],[\"$\",\"span\",null,{\"className\":\"code-line\",\"children\":[\" \",[\"$\",\"span\",null,{\"className\":\"token punctuation\",\"children\":\"}\"}],\"\\n\"]}],[\"$\",\"span\",null,{\"className\":\"code-line\",\"children\":[\" \",[\"$\",\"span\",null,{\"className\":\"token punctuation\",\"children\":\"]\"}],\"\\n\"]}]]}]}],[\"$\",\"p\",null,{\"children\":\"This one star-tree index can get us insights to questions such as:\"}],[\"$\",\"ul\",null,{\"children\":[[\"$\",\"li\",null,{\"children\":\"How many trips were completed in a given day, month or year?\"}],[\"$\",\"li\",null,{\"children\":\"How many passengers traveled in a given day, month or year?\"}],[\"$\",\"li\",null,{\"children\":\"What is the daily / monthly / annual average trip revenue?\"}],[\"$\",\"li\",null,{\"children\":\"What is the daily / monthly / annual average trip revenue, trip duration and distance traveled?\"}],[\"$\",\"li\",null,{\"children\":\"What is the daily / monthly / annual breakdown of total number of trips, total distance traveled and total revenue generated in 2015?\"}],[\"$\",\"li\",null,{\"children\":\"And many more…\"}]]}],[\"$\",\"p\",null,{\"children\":\"We will use one such variant query for this illustration:\"}],[\"$\",\"ul\",null,{\"children\":[\"$\",\"li\",null,{\"children\":\"What is the total number of trips, total distance traveled and total revenue generated by day in 2015?\"}]}],[\"$\",\"p\",null,{\"children\":\"We used a very small infrastructure footprint for this comparison test.\"}],[\"$\",\"p\",null,{\"children\":[\"$\",\"img\",null,{\"alt\":\"\",\"src\":\"https://www.datocms-assets.com/75153/1687549350-screen-shot-2023-06-22-at-1-32-51-pm.png\"}]}],[\"$\",\"h2\",null,{\"id\":\"4-query-results-and-stats\",\"children\":[[\"$\",\"a\",null,{\"href\":\"#4-query-results-and-stats\",\"aria-hidden\":\"true\",\"tabIndex\":\"-1\",\"children\":[\"$\",\"span\",null,{\"className\":\"icon icon-link\"}]}],\"4. Query Results and Stats\"]}],[\"$\",\"h3\",null,{\"id\":\"iteration-1-wo-any-apache-pinot-optimizations\",\"children\":[[\"$\",\"a\",null,{\"href\":\"#iteration-1-wo-any-apache-pinot-optimizations\",\"aria-hidden\":\"true\",\"tabIndex\":\"-1\",\"children\":[\"$\",\"span\",null,{\"className\":\"icon icon-link\"}]}],\"Iteration #1: w/o any Apache Pinot optimizations:\"]}],[\"$\",\"p\",null,{\"children\":\"First, we ran the query without any optimizations offered in Apache Pinot.\"}],[\"$\",\"$L13\",null,{\"className\":\"language-sql\",\"children\":[\"$\",\"code\",null,{\"className\":\"language-sql code-highlight\",\"children\":[[\"$\",\"span\",null,{\"className\":\"code-line\",\"children\":[[\"$\",\"span\",null,{\"className\":\"token comment\",\"children\":\"-- Iteration #1: w/o optimizations \u003e 120s\"}],\"\\n\"]}],[\"$\",\"span\",null,{\"className\":\"code-line\",\"children\":\"\\n\"}],[\"$\",\"span\",null,{\"className\":\"code-line\",\"children\":[[\"$\",\"span\",null,{\"className\":\"token keyword\",\"children\":\"SELECT\"}],\"\\n\"]}],[\"$\",\"span\",null,{\"className\":\"code-line\",\"children\":[\" toDateTime\",[\"$\",\"span\",null,{\"className\":\"token punctuation\",\"children\":\"(\"}],\"tpep_dropoff_datetime\",[\"$\",\"span\",null,{\"className\":\"token operator\",\"children\":\"/\"}],[\"$\",\"span\",null,{\"className\":\"token number\",\"children\":\"1000\"}],[\"$\",\"span\",null,{\"className\":\"token punctuation\",\"children\":\",\"}],\" \",[\"$\",\"span\",null,{\"className\":\"token string\",\"children\":\"'yyyy-MM-dd'\"}],[\"$\",\"span\",null,{\"className\":\"token punctuation\",\"children\":\")\"}],\" \",[\"$\",\"span\",null,{\"className\":\"token string\",\"children\":\"\\\"Date\\\"\"}],[\"$\",\"span\",null,{\"className\":\"token punctuation\",\"children\":\",\"}],\"\\n\"]}],[\"$\",\"span\",null,{\"className\":\"code-line\",\"children\":[\" \",[\"$\",\"span\",null,{\"className\":\"token function\",\"children\":\"count\"}],[\"$\",\"span\",null,{\"className\":\"token punctuation\",\"children\":\"(\"}],[\"$\",\"span\",null,{\"className\":\"token operator\",\"children\":\"*\"}],[\"$\",\"span\",null,{\"className\":\"token punctuation\",\"children\":\")\"}],\" \",[\"$\",\"span\",null,{\"className\":\"token string\",\"children\":\"\\\"Total # of Trips\\\"\"}],[\"$\",\"span\",null,{\"className\":\"token punctuation\",\"children\":\",\"}],\"\\n\"]}],[\"$\",\"span\",null,{\"className\":\"code-line\",\"children\":[\" \",[\"$\",\"span\",null,{\"className\":\"token function\",\"children\":\"sum\"}],[\"$\",\"span\",null,{\"className\":\"token punctuation\",\"children\":\"(\"}],\"trip_distance\",[\"$\",\"span\",null,{\"className\":\"token punctuation\",\"children\":\")\"}],\" \",[\"$\",\"span\",null,{\"className\":\"token string\",\"children\":\"\\\"Total distance traveled\\\"\"}],[\"$\",\"span\",null,{\"className\":\"token punctuation\",\"children\":\",\"}],\"\\n\"]}],[\"$\",\"span\",null,{\"className\":\"code-line\",\"children\":[\" \",[\"$\",\"span\",null,{\"className\":\"token function\",\"children\":\"sum\"}],[\"$\",\"span\",null,{\"className\":\"token punctuation\",\"children\":\"(\"}],\"passenger_count\",[\"$\",\"span\",null,{\"className\":\"token punctuation\",\"children\":\")\"}],\" \",[\"$\",\"span\",null,{\"className\":\"token string\",\"children\":\"\\\"Total # of Passengers\\\"\"}],[\"$\",\"span\",null,{\"className\":\"token punctuation\",\"children\":\",\"}],\"\\n\"]}],[\"$\",\"span\",null,{\"className\":\"code-line\",\"children\":[\" \",[\"$\",\"span\",null,{\"className\":\"token function\",\"children\":\"sum\"}],[\"$\",\"span\",null,{\"className\":\"token punctuation\",\"children\":\"(\"}],\"total_amount\",[\"$\",\"span\",null,{\"className\":\"token punctuation\",\"children\":\")\"}],\" \",[\"$\",\"span\",null,{\"className\":\"token string\",\"children\":\"\\\"Total Revenue\\\"\"}],\"\\n\"]}],[\"$\",\"span\",null,{\"className\":\"code-line\",\"children\":[[\"$\",\"span\",null,{\"className\":\"token keyword\",\"children\":\"FROM\"}],\"\\n\"]}],[\"$\",\"span\",null,{\"className\":\"code-line\",\"children\":\" nyc_taxi_demo\\n\"}],[\"$\",\"span\",null,{\"className\":\"code-line\",\"children\":[[\"$\",\"span\",null,{\"className\":\"token keyword\",\"children\":\"WHERE\"}],\"\\n\"]}],[\"$\",\"span\",null,{\"className\":\"code-line\",\"children\":[\" \",[\"$\",\"span\",null,{\"className\":\"token string\",\"children\":\"\\\"Date\\\"\"}],\" \",[\"$\",\"span\",null,{\"className\":\"token operator\",\"children\":\"BETWEEN\"}],\" \",[\"$\",\"span\",null,{\"className\":\"token string\",\"children\":\"'2015-01-01'\"}],\" \",[\"$\",\"span\",null,{\"className\":\"token operator\",\"children\":\"AND\"}],\" \",[\"$\",\"span\",null,{\"className\":\"token string\",\"children\":\"'2015-12-31'\"}],\"\\n\"]}],[\"$\",\"span\",null,{\"className\":\"code-line\",\"children\":[[\"$\",\"span\",null,{\"className\":\"token keyword\",\"children\":\"GROUP\"}],\" \",[\"$\",\"span\",null,{\"className\":\"token keyword\",\"children\":\"BY\"}],\"\\n\"]}],[\"$\",\"span\",null,{\"className\":\"code-line\",\"children\":[\" \",[\"$\",\"span\",null,{\"className\":\"token string\",\"children\":\"\\\"Date\\\"\"}],\"\\n\"]}],[\"$\",\"span\",null,{\"className\":\"code-line\",\"children\":[[\"$\",\"span\",null,{\"className\":\"token keyword\",\"children\":\"ORDER\"}],\" \",[\"$\",\"span\",null,{\"className\":\"token keyword\",\"children\":\"BY\"}],\"\\n\"]}],[\"$\",\"span\",null,{\"className\":\"code-line\",\"children\":[\" \",[\"$\",\"span\",null,{\"className\":\"token string\",\"children\":\"\\\"Date\\\"\"}],\" \",[\"$\",\"span\",null,{\"className\":\"token keyword\",\"children\":\"ASC\"}],\"\\n\"]}],[\"$\",\"span\",null,{\"className\":\"code-line\",\"children\":[[\"$\",\"span\",null,{\"className\":\"token keyword\",\"children\":\"limit\"}],\" \",[\"$\",\"span\",null,{\"className\":\"token number\",\"children\":\"1000\"}],\"\\n\"]}]]}]}],[\"$\",\"p\",null,{\"children\":\"This was a wide time range query (365 days). It required scanning across ~146M out of ~633M documents. In addition, it involved performing an expensive ToDateTime transformation on the tpep_dropoff_datetime entry in each of those ~146M documents during query time.\"}],[\"$\",\"p\",null,{\"children\":\"Result: The query took 131,425 milliseconds (~131.4s; ~2m 11s) to complete.\"}],[\"$\",\"h3\",null,{\"id\":\"iteration-2-w-inverted-index\",\"children\":[[\"$\",\"a\",null,{\"href\":\"#iteration-2-w-inverted-index\",\"aria-hidden\":\"true\",\"tabIndex\":\"-1\",\"children\":[\"$\",\"span\",null,{\"className\":\"icon icon-link\"}]}],\"Iteration #2: w/ Inverted Index \"]}],[\"$\",\"p\",null,{\"children\":\"In this iteration, we used a derived column - dropoff_date_str - which performed the ToDateTime transformation for every record during ingestion time. Since the cardinality of this derived column was much lower (granularity was at Day level instead of milliseconds), this enabled us to use an inverted index on this column.\"}],[\"$\",\"$L13\",null,{\"className\":\"language-sql\",\"children\":[\"$\",\"code\",null,{\"className\":\"language-sql code-highlight\",\"children\":[[\"$\",\"span\",null,{\"className\":\"code-line\",\"children\":[[\"$\",\"span\",null,{\"className\":\"token comment\",\"children\":\"-- Iteration #2: w/ Ingestion Time Transformation\"}],\"\\n\"]}],[\"$\",\"span\",null,{\"className\":\"code-line\",\"children\":[[\"$\",\"span\",null,{\"className\":\"token keyword\",\"children\":\"SELECT\"}],\"\\n\"]}],[\"$\",\"span\",null,{\"className\":\"code-line\",\"children\":[\" dropoff_date_str \",[\"$\",\"span\",null,{\"className\":\"token string\",\"children\":\"\\\"Date\\\"\"}],[\"$\",\"span\",null,{\"className\":\"token punctuation\",\"children\":\",\"}],\"\\n\"]}],[\"$\",\"span\",null,{\"className\":\"code-line\",\"children\":[\" \",[\"$\",\"span\",null,{\"className\":\"token function\",\"children\":\"count\"}],[\"$\",\"span\",null,{\"className\":\"token punctuation\",\"children\":\"(\"}],[\"$\",\"span\",null,{\"className\":\"token operator\",\"children\":\"*\"}],[\"$\",\"span\",null,{\"className\":\"token punctuation\",\"children\":\")\"}],\" \",[\"$\",\"span\",null,{\"className\":\"token string\",\"children\":\"\\\"Total # of Trips\\\"\"}],[\"$\",\"span\",null,{\"className\":\"token punctuation\",\"children\":\",\"}],\"\\n\"]}],[\"$\",\"span\",null,{\"className\":\"code-line\",\"children\":[\" \",[\"$\",\"span\",null,{\"className\":\"token function\",\"children\":\"sum\"}],[\"$\",\"span\",null,{\"className\":\"token punctuation\",\"children\":\"(\"}],\"trip_distance\",[\"$\",\"span\",null,{\"className\":\"token punctuation\",\"children\":\")\"}],\" \",[\"$\",\"span\",null,{\"className\":\"token string\",\"children\":\"\\\"Total distance traveled\\\"\"}],[\"$\",\"span\",null,{\"className\":\"token punctuation\",\"children\":\",\"}],\"\\n\"]}],[\"$\",\"span\",null,{\"className\":\"code-line\",\"children\":[\" \",[\"$\",\"span\",null,{\"className\":\"token function\",\"children\":\"sum\"}],[\"$\",\"span\",null,{\"className\":\"token punctuation\",\"children\":\"(\"}],\"passenger_count\",[\"$\",\"span\",null,{\"className\":\"token punctuation\",\"children\":\")\"}],\" \",[\"$\",\"span\",null,{\"className\":\"token string\",\"children\":\"\\\"Total # of Passengers\\\"\"}],[\"$\",\"span\",null,{\"className\":\"token punctuation\",\"children\":\",\"}],\"\\n\"]}],[\"$\",\"span\",null,{\"className\":\"code-line\",\"children\":[\" \",[\"$\",\"span\",null,{\"className\":\"token function\",\"children\":\"sum\"}],[\"$\",\"span\",null,{\"className\":\"token punctuation\",\"children\":\"(\"}],\"total_amount\",[\"$\",\"span\",null,{\"className\":\"token punctuation\",\"children\":\")\"}],\" \",[\"$\",\"span\",null,{\"className\":\"token string\",\"children\":\"\\\"Total Revenue\\\"\"}],\"\\n\"]}],[\"$\",\"span\",null,{\"className\":\"code-line\",\"children\":[[\"$\",\"span\",null,{\"className\":\"token keyword\",\"children\":\"FROM\"}],\"\\n\"]}],[\"$\",\"span\",null,{\"className\":\"code-line\",\"children\":\" nyc_taxi_demo\\n\"}],[\"$\",\"span\",null,{\"className\":\"code-line\",\"children\":[[\"$\",\"span\",null,{\"className\":\"token keyword\",\"children\":\"WHERE\"}],\"\\n\"]}],[\"$\",\"span\",null,{\"className\":\"code-line\",\"children\":[\" \",[\"$\",\"span\",null,{\"className\":\"token string\",\"children\":\"\\\"Date\\\"\"}],\" \",[\"$\",\"span\",null,{\"className\":\"token operator\",\"children\":\"BETWEEN\"}],\" \",[\"$\",\"span\",null,{\"className\":\"token string\",\"children\":\"'2015-01-01'\"}],\" \",[\"$\",\"span\",null,{\"className\":\"token operator\",\"children\":\"AND\"}],\" \",[\"$\",\"span\",null,{\"className\":\"token string\",\"children\":\"'2015-12-31'\"}],\"\\n\"]}],[\"$\",\"span\",null,{\"className\":\"code-line\",\"children\":[[\"$\",\"span\",null,{\"className\":\"token keyword\",\"children\":\"GROUP\"}],\" \",[\"$\",\"span\",null,{\"className\":\"token keyword\",\"children\":\"BY\"}],\"\\n\"]}],[\"$\",\"span\",null,{\"className\":\"code-line\",\"children\":[\" \",[\"$\",\"span\",null,{\"className\":\"token string\",\"children\":\"\\\"Date\\\"\"}],\"\\n\"]}],[\"$\",\"span\",null,{\"className\":\"code-line\",\"children\":[[\"$\",\"span\",null,{\"className\":\"token keyword\",\"children\":\"ORDER\"}],\" \",[\"$\",\"span\",null,{\"className\":\"token keyword\",\"children\":\"BY\"}],\"\\n\"]}],[\"$\",\"span\",null,{\"className\":\"code-line\",\"children\":[\" \",[\"$\",\"span\",null,{\"className\":\"token string\",\"children\":\"\\\"Date\\\"\"}],\" \",[\"$\",\"span\",null,{\"className\":\"token keyword\",\"children\":\"ASC\"}],\"\\n\"]}],[\"$\",\"span\",null,{\"className\":\"code-line\",\"children\":[[\"$\",\"span\",null,{\"className\":\"token keyword\",\"children\":\"limit\"}],\" \",[\"$\",\"span\",null,{\"className\":\"token number\",\"children\":\"1000\"}],\"\\n\"]}],[\"$\",\"span\",null,{\"className\":\"code-line\",\"children\":[[\"$\",\"span\",null,{\"className\":\"token keyword\",\"children\":\"option\"}],[\"$\",\"span\",null,{\"className\":\"token punctuation\",\"children\":\"(\"}],\"useStarTree\",[\"$\",\"span\",null,{\"className\":\"token operator\",\"children\":\"=\"}],[\"$\",\"span\",null,{\"className\":\"token boolean\",\"children\":\"false\"}],[\"$\",\"span\",null,{\"className\":\"token punctuation\",\"children\":\",\"}],\" timeoutMs\",[\"$\",\"span\",null,{\"className\":\"token operator\",\"children\":\"=\"}],[\"$\",\"span\",null,{\"className\":\"token number\",\"children\":\"20000\"}],[\"$\",\"span\",null,{\"className\":\"token punctuation\",\"children\":\")\"}],\"\\n\"]}]]}]}],[\"$\",\"p\",null,{\"children\":[\"$\",\"img\",null,{\"alt\":\"\",\"src\":\"https://www.datocms-assets.com/75153/1684246716-image1.png\"}]}],[\"$\",\"p\",null,{\"children\":\"Result: The query completed in 1,513 milliseconds. (~1.5s); from ~131s to ~1.5s was a BIG improvement. However, results still took more than a second — which is a relatively long time for an OLAP database, especially if it is faced with multiple concurrent queries.\"}],[\"$\",\"h3\",null,{\"id\":\"iteration-3-w-star-tree-index\",\"children\":[[\"$\",\"a\",null,{\"href\":\"#iteration-3-w-star-tree-index\",\"aria-hidden\":\"true\",\"tabIndex\":\"-1\",\"children\":[\"$\",\"span\",null,{\"className\":\"icon icon-link\"}]}],\"Iteration #3: w/ Star-Tree Index: \"]}],[\"$\",\"p\",null,{\"children\":\"In this iteration, we ran the same query with star-tree index enabled.\"}],[\"$\",\"$L13\",null,{\"className\":\"language-sql\",\"children\":[\"$\",\"code\",null,{\"className\":\"language-sql code-highlight\",\"children\":[[\"$\",\"span\",null,{\"className\":\"code-line\",\"children\":[[\"$\",\"span\",null,{\"className\":\"token comment\",\"children\":\"-- Iteration #3: w/ Ingestion Time Transformation + StarTree Index\"}],\"\\n\"]}],[\"$\",\"span\",null,{\"className\":\"code-line\",\"children\":[[\"$\",\"span\",null,{\"className\":\"token keyword\",\"children\":\"SELECT\"}],\"\\n\"]}],[\"$\",\"span\",null,{\"className\":\"code-line\",\"children\":[\" dropoff_date_str \",[\"$\",\"span\",null,{\"className\":\"token string\",\"children\":\"\\\"Date\\\"\"}],[\"$\",\"span\",null,{\"className\":\"token punctuation\",\"children\":\",\"}],\"\\n\"]}],[\"$\",\"span\",null,{\"className\":\"code-line\",\"children\":[\" \",[\"$\",\"span\",null,{\"className\":\"token function\",\"children\":\"count\"}],[\"$\",\"span\",null,{\"className\":\"token punctuation\",\"children\":\"(\"}],[\"$\",\"span\",null,{\"className\":\"token operator\",\"children\":\"*\"}],[\"$\",\"span\",null,{\"className\":\"token punctuation\",\"children\":\")\"}],\" \",[\"$\",\"span\",null,{\"className\":\"token string\",\"children\":\"\\\"Total # of Trips\\\"\"}],[\"$\",\"span\",null,{\"className\":\"token punctuation\",\"children\":\",\"}],\"\\n\"]}],[\"$\",\"span\",null,{\"className\":\"code-line\",\"children\":[\" \",[\"$\",\"span\",null,{\"className\":\"token function\",\"children\":\"sum\"}],[\"$\",\"span\",null,{\"className\":\"token punctuation\",\"children\":\"(\"}],\"trip_distance\",[\"$\",\"span\",null,{\"className\":\"token punctuation\",\"children\":\")\"}],\" \",[\"$\",\"span\",null,{\"className\":\"token string\",\"children\":\"\\\"Total distance traveled\\\"\"}],[\"$\",\"span\",null,{\"className\":\"token punctuation\",\"children\":\",\"}],\"\\n\"]}],[\"$\",\"span\",null,{\"className\":\"code-line\",\"children\":[\" \",[\"$\",\"span\",null,{\"className\":\"token function\",\"children\":\"sum\"}],[\"$\",\"span\",null,{\"className\":\"token punctuation\",\"children\":\"(\"}],\"passenger_count\",[\"$\",\"span\",null,{\"className\":\"token punctuation\",\"children\":\")\"}],\" \",[\"$\",\"span\",null,{\"className\":\"token string\",\"children\":\"\\\"Total # of Passengers\\\"\"}],[\"$\",\"span\",null,{\"className\":\"token punctuation\",\"children\":\",\"}],\"\\n\"]}],[\"$\",\"span\",null,{\"className\":\"code-line\",\"children\":[\" \",[\"$\",\"span\",null,{\"className\":\"token function\",\"children\":\"sum\"}],[\"$\",\"span\",null,{\"className\":\"token punctuation\",\"children\":\"(\"}],\"total_amount\",[\"$\",\"span\",null,{\"className\":\"token punctuation\",\"children\":\")\"}],\" \",[\"$\",\"span\",null,{\"className\":\"token string\",\"children\":\"\\\"Total Revenue\\\"\"}],\"\\n\"]}],[\"$\",\"span\",null,{\"className\":\"code-line\",\"children\":[[\"$\",\"span\",null,{\"className\":\"token keyword\",\"children\":\"FROM\"}],\"\\n\"]}],[\"$\",\"span\",null,{\"className\":\"code-line\",\"children\":\" nyc_taxi_demo\\n\"}],[\"$\",\"span\",null,{\"className\":\"code-line\",\"children\":[[\"$\",\"span\",null,{\"className\":\"token keyword\",\"children\":\"WHERE\"}],\"\\n\"]}],[\"$\",\"span\",null,{\"className\":\"code-line\",\"children\":[\" \",[\"$\",\"span\",null,{\"className\":\"token string\",\"children\":\"\\\"Date\\\"\"}],\" \",[\"$\",\"span\",null,{\"className\":\"token operator\",\"children\":\"BETWEEN\"}],\" \",[\"$\",\"span\",null,{\"className\":\"token string\",\"children\":\"'2015-01-01'\"}],\" \",[\"$\",\"span\",null,{\"className\":\"token operator\",\"children\":\"AND\"}],\" \",[\"$\",\"span\",null,{\"className\":\"token string\",\"children\":\"'2015-12-31'\"}],\"\\n\"]}],[\"$\",\"span\",null,{\"className\":\"code-line\",\"children\":[[\"$\",\"span\",null,{\"className\":\"token keyword\",\"children\":\"GROUP\"}],\" \",[\"$\",\"span\",null,{\"className\":\"token keyword\",\"children\":\"BY\"}],\"\\n\"]}],[\"$\",\"span\",null,{\"className\":\"code-line\",\"children\":[\" \",[\"$\",\"span\",null,{\"className\":\"token string\",\"children\":\"\\\"Date\\\"\"}],\"\\n\"]}],[\"$\",\"span\",null,{\"className\":\"code-line\",\"children\":[[\"$\",\"span\",null,{\"className\":\"token keyword\",\"children\":\"ORDER\"}],\" \",[\"$\",\"span\",null,{\"className\":\"token keyword\",\"children\":\"BY\"}],\"\\n\"]}],[\"$\",\"span\",null,{\"className\":\"code-line\",\"children\":[\" \",[\"$\",\"span\",null,{\"className\":\"token string\",\"children\":\"\\\"Date\\\"\"}],\" \",[\"$\",\"span\",null,{\"className\":\"token keyword\",\"children\":\"ASC\"}],\"\\n\"]}],[\"$\",\"span\",null,{\"className\":\"code-line\",\"children\":[[\"$\",\"span\",null,{\"className\":\"token keyword\",\"children\":\"limit\"}],\" \",[\"$\",\"span\",null,{\"className\":\"token number\",\"children\":\"1000\"}],\"\\n\"]}],[\"$\",\"span\",null,{\"className\":\"code-line\",\"children\":[[\"$\",\"span\",null,{\"className\":\"token keyword\",\"children\":\"option\"}],[\"$\",\"span\",null,{\"className\":\"token punctuation\",\"children\":\"(\"}],\"useStarTree\",[\"$\",\"span\",null,{\"className\":\"token operator\",\"children\":\"=\"}],[\"$\",\"span\",null,{\"className\":\"token boolean\",\"children\":\"true\"}],[\"$\",\"span\",null,{\"className\":\"token punctuation\",\"children\":\")\"}],\"\\n\"]}]]}]}],[\"$\",\"p\",null,{\"children\":[\"$\",\"img\",null,{\"alt\":\"\",\"src\":\"https://www.datocms-assets.com/75153/1684246852-image9.png\"}]}],[\"$\",\"p\",null,{\"children\":\"Result: The query completed in 4 milliseconds! Notice in particular that the numDocsScanned came down from ~146M to 409!\"}],[\"$\",\"h3\",null,{\"id\":\"comparison\",\"children\":[[\"$\",\"a\",null,{\"href\":\"#comparison\",\"aria-hidden\":\"true\",\"tabIndex\":\"-1\",\"children\":[\"$\",\"span\",null,{\"className\":\"icon icon-link\"}]}],\"Comparison:\"]}],[\"$\",\"p\",null,{\"children\":[\"Let’s take a closer look at the \",[\"$\",\"a\",null,{\"target\":\"_blank\",\"rel\":\"noopener noreferrer\",\"href\":\"https://docs.pinot.apache.org/users/api/querying-pinot-using-standard-sql/response-format\",\"children\":\"query response stats\"}],\" across all three iterations to understand the “how” part of this magic of indexing in Apache Pinot.\"]}],[\"$\",\"p\",null,{\"children\":[\"$\",\"img\",null,{\"alt\":\"\",\"src\":\"https://www.datocms-assets.com/75153/1684246748-image3.png\"}]}],[\"$\",\"ol\",null,{\"children\":[[\"$\",\"li\",null,{\"children\":[\"$\",\"p\",null,{\"children\":\"The dataset has 633,694,594 records (documents) spread across 130 segments.\"}]}],[\"$\",\"li\",null,{\"children\":[[\"$\",\"p\",null,{\"children\":\"Query Stats:\"}],[\"$\",\"ol\",null,{\"children\":[[\"$\",\"li\",null,{\"children\":\"w/o any index optimizations (Iteration #1), the query scanned ALL 633,694,594 records (check numEntriesScannedInFilter) during processing. Also, numEntriesScannedPostFilter was 584,147,312 (numDocsScanned = ~146M). All 130 segments were processed which was very inefficient.\"}],[\"$\",\"li\",null,{\"children\":\"w/ Inverted Index (Iteration #2), numEntriesScannedInFilter was 0; numEntriesScannedPostFilter was 584,147,312 (numDocsScanned = ~146M) which meant that the query selectivity was low (the query had to scan a lot of records during post filter phase; about 92% of overall records). This is an indication of when a star-tree index could help.\"}],[\"$\",\"li\",null,{\"children\":\"w/ Star-tree Index (Iteration #3), numEntriesScannedInFilter was 0; numEntriesScannedPostFilter was only 2,045 (numDocsScanned = 409). The star-tree index helped improve query performance tremendously by providing high query selectivity.\"}]]}]]}]]}],[\"$\",\"h2\",null,{\"id\":\"5-impact-summary\",\"children\":[[\"$\",\"a\",null,{\"href\":\"#5-impact-summary\",\"aria-hidden\":\"true\",\"tabIndex\":\"-1\",\"children\":[\"$\",\"span\",null,{\"className\":\"icon icon-link\"}]}],\"5. Impact Summary:\"]}],[\"$\",\"p\",null,{\"children\":[\"$\",\"img\",null,{\"alt\":\"\",\"src\":\"https://www.datocms-assets.com/75153/1684246766-image4.png\"}]}],[\"$\",\"ol\",null,{\"children\":[[\"$\",\"li\",null,{\"children\":\"356,968x improvement (or 99.999% drop) in num docs scanned from ~146M to 409.\"}],[\"$\",\"li\",null,{\"children\":\"378.5x improvement (~99.736% drop) in query latency from 1,513 ms to 4 ms.\"}]]}],[\"$\",\"h3\",null,{\"id\":\"key-benefits-of-the-star-tree-index\",\"children\":[[\"$\",\"a\",null,{\"href\":\"#key-benefits-of-the-star-tree-index\",\"aria-hidden\":\"true\",\"tabIndex\":\"-1\",\"children\":[\"$\",\"span\",null,{\"className\":\"icon icon-link\"}]}],\"Key Benefits of the Star-Tree Index:\"]}],[\"$\",\"ul\",null,{\"children\":[[\"$\",\"li\",null,{\"children\":[\"$\",\"p\",null,{\"children\":\"User controllable: Tune space vs. time overhead\"}]}],[\"$\",\"li\",null,{\"children\":[\"$\",\"p\",null,{\"children\":\"Flexible: create any number of indexes. The right index is chosen based on the query structure.\"}]}],[\"$\",\"li\",null,{\"children\":[\"$\",\"p\",null,{\"children\":\"Transparent: Unlike traditional MVs, users don’t need to know about the existence of a star-tree index. The same query will be accelerated with a star-tree index in place.\"}]}],[\"$\",\"li\",null,{\"children\":[\"$\",\"p\",null,{\"children\":\"Dynamic: Very easy to generate a new index at any point of time.\"}]}],[\"$\",\"li\",null,{\"children\":[[\"$\",\"p\",null,{\"children\":\"Disk IO is the most expensive operation in query processing. Latency is linear to the number of disk reads a query has to perform. Star-Tree Index brings the number of disk reads down exponentially.\"}],[\"$\",\"ul\",null,{\"children\":[\"$\",\"li\",null,{\"children\":\"In this example, star-tree Index reduced the disk reads by 99.999% from ~584 Million entries (~146 Million documents or records) in case of an inverted index to 2,045 entries (409 documents or records). Query latency came down from 1,513 ms to 4 ms!\"}]}]]}]]}],[\"$\",\"p\",null,{\"children\":[[\"$\",\"a\",null,{\"target\":\"_blank\",\"rel\":\"noopener noreferrer\",\"href\":\"https://startree.ai/blog/star-tree-indexes-in-apache-pinot-part-2-understanding-the-impact-during-high-concurrency\",\"children\":\"In part 2 of this series,\"}],\" we will perform throughput tests to measure the impact of star-tree index under high load.\"]}]]}]}]}]]}],[\"$\",\"aside\",null,{\"className\":\"mt-10 hidden border-l-2 pl-5 lg:sticky lg:top-1 lg:block lg:h-full\",\"children\":[\"$\",\"section\",null,{\"className\":\"sticky top-0 mb-4 w-[15.375rem]\",\"children\":[[\"$\",\"div\",null,{\"className\":\"flex flex-col space-y-1.5 pb-3\",\"children\":[\"$\",\"h3\",null,{\"className\":\"text-sm font-semibold leading-snug text-neutral-500 dark:text-neutral-100\",\"children\":\"Table of Contents\"}]}],[\"$\",\"$L14\",null,{\"chapters\":[{\"value\":\"1. The Dataset:\",\"url\":\"#1-the-dataset\",\"depth\":2},{\"value\":\"Schema:\",\"url\":\"#schema\",\"depth\":3},{\"value\":\"2. Query Pattern\",\"url\":\"#2-query-pattern\",\"depth\":2},{\"value\":\"Star-Tree Index Config:\",\"url\":\"#star-tree-index-config\",\"depth\":3},{\"value\":\"4. Query Results and Stats\",\"url\":\"#4-query-results-and-stats\",\"depth\":2},{\"value\":\"Iteration #1: w/o any Apache Pinot optimizations:\",\"url\":\"#iteration-1-wo-any-apache-pinot-optimizations\",\"depth\":3},{\"value\":\"Iteration #2: w/ Inverted Index \",\"url\":\"#iteration-2-w-inverted-index\",\"depth\":3},{\"value\":\"Iteration #3: w/ Star-Tree Index: \",\"url\":\"#iteration-3-w-star-tree-index\",\"depth\":3},{\"value\":\"Comparison:\",\"url\":\"#comparison\",\"depth\":3},{\"value\":\"5. Impact Summary:\",\"url\":\"#5-impact-summary\",\"depth\":2},{\"value\":\"Key Benefits of the Star-Tree Index:\",\"url\":\"#key-benefits-of-the-star-tree-index\",\"depth\":3}]}]]}]}]]}]}]]}]]\n"])</script><script>self.__next_f.push([1,"6:[[\"$\",\"meta\",\"0\",{\"name\":\"viewport\",\"content\":\"width=device-width, initial-scale=1\"}],[\"$\",\"meta\",\"1\",{\"charSet\":\"utf-8\"}],[\"$\",\"title\",\"2\",{\"children\":\"StarTree Indexes in Apache Pinot Part-1 - Understanding the Impact on Query Performance | Apache Pinot™\"}],[\"$\",\"meta\",\"3\",{\"name\":\"description\",\"content\":\"The blog post explains the star-tree index in Apache Pinot and its benefits compared to traditional materialized views. By implementing a star-tree index, query performance significantly improved, reducing query latency from 1,513 ms to just 4 ms and drastically reducing disk reads by 99.999%.\"}],[\"$\",\"meta\",\"4\",{\"name\":\"robots\",\"content\":\"index, follow\"}],[\"$\",\"meta\",\"5\",{\"name\":\"googlebot\",\"content\":\"index, follow, max-video-preview:-1, max-image-preview:large, max-snippet:-1\"}],[\"$\",\"link\",\"6\",{\"rel\":\"canonical\",\"href\":\"https://pinot.apache.org/blog/2023/05/16/star-tree-indexes-in-apache-pinot-part-1-understanding-the-impact-on-query-performance\"}],[\"$\",\"link\",\"7\",{\"rel\":\"alternate\",\"type\":\"application/rss+xml\",\"href\":\"https://pinot.apache.org/feed.xml\"}],[\"$\",\"meta\",\"8\",{\"property\":\"og:title\",\"content\":\"StarTree Indexes in Apache Pinot Part-1 - Understanding the Impact on Query Performance\"}],[\"$\",\"meta\",\"9\",{\"property\":\"og:description\",\"content\":\"The blog post explains the star-tree index in Apache Pinot and its benefits compared to traditional materialized views. By implementing a star-tree index, query performance significantly improved, reducing query latency from 1,513 ms to just 4 ms and drastically reducing disk reads by 99.999%.\"}],[\"$\",\"meta\",\"10\",{\"property\":\"og:url\",\"content\":\"https://pinot.apache.org/blog/2023/05/16/star-tree-indexes-in-apache-pinot-part-1-understanding-the-impact-on-query-performance\"}],[\"$\",\"meta\",\"11\",{\"property\":\"og:site_name\",\"content\":\"Apache Pinot™\"}],[\"$\",\"meta\",\"12\",{\"property\":\"og:locale\",\"content\":\"en_US\"}],[\"$\",\"meta\",\"13\",{\"property\":\"og:image\",\"content\":\"https://pinot.apache.org/static/images/twitter-card.png\"}],[\"$\",\"meta\",\"14\",{\"property\":\"og:type\",\"content\":\"article\"}],[\"$\",\"meta\",\"15\",{\"property\":\"article:published_time\",\"content\":\"2023-05-16T00:00:00.000Z\"}],[\"$\",\"meta\",\"16\",{\"property\":\"article:modified_time\",\"content\":\"2023-05-16T00:00:00.000Z\"}],[\"$\",\"meta\",\"17\",{\"property\":\"article:author\",\"content\":\"Sandeep Dabade\"}],[\"$\",\"meta\",\"18\",{\"name\":\"twitter:card\",\"content\":\"summary_large_image\"}],[\"$\",\"meta\",\"19\",{\"name\":\"twitter:title\",\"content\":\"StarTree Indexes in Apache Pinot Part-1 - Understanding the Impact on Query Performance\"}],[\"$\",\"meta\",\"20\",{\"name\":\"twitter:description\",\"content\":\"The blog post explains the star-tree index in Apache Pinot and its benefits compared to traditional materialized views. By implementing a star-tree index, query performance significantly improved, reducing query latency from 1,513 ms to just 4 ms and drastically reducing disk reads by 99.999%.\"}],[\"$\",\"meta\",\"21\",{\"name\":\"twitter:image\",\"content\":\"https://pinot.apache.org/static/images/twitter-card.png\"}],[\"$\",\"meta\",\"22\",{\"name\":\"next-size-adjust\"}]]\n"])</script><script>self.__next_f.push([1,"f:null\n"])</script><script>self.__next_f.push([1,""])</script></body></html> |
