1:HL["/_next/static/media/6905431624c34d00-s.p.woff2","font",{"crossOrigin":"","type":"font/woff2"}]
2:HL["/_next/static/css/9e925a33b1acdac1.css","style",{"crossOrigin":""}]
0:["rmcKjFZ3e9kKdH1iJwCIQ",[[["",{"children":["blog",{"children":[["slug","2022/11/22/Apache-Pinot-Timestamp-Indexes","c"],{"children":["__PAGE__?{\"slug\":[\"2022\",\"11\",\"22\",\"Apache-Pinot-Timestamp-Indexes\"]}",{}]}]}]},"$undefined","$undefined",true],"$L3",[[["$","link","0",{"rel":"stylesheet","href":"/_next/static/css/9e925a33b1acdac1.css","precedence":"next","crossOrigin":""}]],"$L4"]]]]
5:HL["/_next/static/css/c130d1629644f070.css","style",{"crossOrigin":""}]
6: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"]
7: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"],""]
8:I[9640,["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"],"AlgoliaSearchProvider"]
9: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"],""]
a:I[6954,[],""]
b:I[7264,[],""]
c:I[8326,["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"],""]
f:T9fe,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.0023:[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":["$","$L6",null,{"children":[["$undefined","$undefined","$undefined","$undefined",[["$","$L7",null,{"strategy":"afterInteractive","src":"https://www.googletagmanager.com/gtag/js?id=G-ZXG79NJEBY"}],["$","$L7",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":["$","$L8",null,{"algoliaConfig":{"appId":"CKRA00L2X9","apiKey":"6531f8f7783a88d76629190843f1801e","indexName":"prod_apache_pinot_docs"},"children":[["$","$L9",null,{}],["$","main",null,{"children":["$","$La",null,{"parallelRouterKey":"children","segmentPath":["children"],"loading":"$undefined","loadingStyles":"$undefined","loadingScripts":"$undefined","hasLoading":false,"error":"$undefined","errorStyles":"$undefined","errorScripts":"$undefined","template":["$","$Lb",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."}],["$","$Lc",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":["$","$La",null,{"parallelRouterKey":"children","segmentPath":["children","blog","children"],"loading":"$undefined","loadingStyles":"$undefined","loadingScripts":"$undefined","hasLoading":false,"error":"$undefined","errorStyles":"$undefined","errorScripts":"$undefined","template":["$","$Lb",null,{}],"templateStyles":"$undefined","templateScripts":"$undefined","notFound":"$undefined","notFoundStyles":"$undefined","initialChildNode":["$","$La",null,{"parallelRouterKey":"children","segmentPath":["children","blog","children",["slug","2022/11/22/Apache-Pinot-Timestamp-Indexes","c"],"children"],"loading":"$undefined","loadingStyles":"$undefined","loadingScripts":"$undefined","hasLoading":false,"error":"$undefined","errorStyles":"$undefined","errorScripts":"$undefined","template":["$","$Lb",null,{}],"templateStyles":"$undefined","templateScripts":"$undefined","notFound":"$undefined","notFoundStyles":"$undefined","initialChildNode":["$Ld","$Le",null],"childPropSegment":"__PAGE__?{\"slug\":[\"2022\",\"11\",\"22\",\"Apache-Pinot-Timestamp-Indexes\"]}","styles":[["$","link","0",{"rel":"stylesheet","href":"/_next/static/css/c130d1629644f070.css","precedence":"next","crossOrigin":""}]]}],"childPropSegment":["slug","2022/11/22/Apache-Pinot-Timestamp-Indexes","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":"$f"}],["$","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":[["$","$Lc",null,{"href":"/powered-by","className":"block py-1 text-gray-600 hover:text-gray-900","children":"Company Stories"}],["$","$Lc",null,{"href":"/download","className":"block py-1 text-gray-600 hover:text-gray-900","children":"Download"}],["$","$Lc",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]
4:[["$","meta","0",{"name":"viewport","content":"width=device-width, initial-scale=1"}],["$","meta","1",{"charSet":"utf-8"}],["$","title","2",{"children":"Apache Pinot™ 0.11 - Timestamp Indexes | Apache Pinot™"}],["$","meta","3",{"name":"description","content":"Users write queries that use the datetrunc function to filter at a coarser grain of functionality. Unfortunately, this approach results in scanning data and time value conversion work that takes a long time at large data volumes. The timestamp index solves that problem!"}],["$","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/2022/11/22/Apache-Pinot-Timestamp-Indexes"}],["$","link","7",{"rel":"alternate","type":"application/rss+xml","href":"https://pinot.apache.org/feed.xml"}],["$","meta","8",{"property":"og:title","content":"Apache Pinot™ 0.11 - Timestamp Indexes"}],["$","meta","9",{"property":"og:description","content":"Users write queries that use the datetrunc function to filter at a coarser grain of functionality. Unfortunately, this approach results in scanning data and time value conversion work that takes a long time at large data volumes. The timestamp index solves that problem!"}],["$","meta","10",{"property":"og:url","content":"https://pinot.apache.org/blog/2022/11/22/Apache-Pinot-Timestamp-Indexes"}],["$","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":"2022-11-22T00:00:00.000Z"}],["$","meta","16",{"property":"article:modified_time","content":"2022-11-22T00:00:00.000Z"}],["$","meta","17",{"property":"article:author","content":"Mark Needham"}],["$","meta","18",{"name":"twitter:card","content":"summary_large_image"}],["$","meta","19",{"name":"twitter:title","content":"Apache Pinot™ 0.11 - Timestamp Indexes"}],["$","meta","20",{"name":"twitter:description","content":"Users write queries that use the datetrunc function to filter at a coarser grain of functionality. Unfortunately, this approach results in scanning data and time value conversion work that takes a long time at large data volumes. The timestamp index solves that problem!"}],["$","meta","21",{"name":"twitter:image","content":"https://pinot.apache.org/static/images/twitter-card.png"}],["$","meta","22",{"name":"next-size-adjust"}]]
10: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"],""]
11:I[7246,["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"],""]
12: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"],""]
13:I[5185,["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"],""]
e:[["$","script",null,{"type":"application/ld+json","dangerouslySetInnerHTML":{"__html":"{\"@context\":\"https://schema.org\",\"@type\":\"BlogPosting\",\"headline\":\"Apache Pinot™ 0.11 - Timestamp Indexes\",\"datePublished\":\"2022-11-22T00:00:00.000Z\",\"dateModified\":\"2022-11-22T00:00:00.000Z\",\"description\":\"Users write queries that use the datetrunc function to filter at a coarser grain of functionality. Unfortunately, this approach results in scanning data and time value conversion work that takes a long time at large data volumes. The timestamp index solves that problem!\",\"image\":\"/static/images/twitter-card.png\",\"url\":\"https://pinot.apache.org/blog/2022-11-22-Apache-Pinot-Timestamp-Indexes\",\"author\":[{\"@type\":\"Person\",\"name\":\"Mark Needham\"}]}"}}],["$","section",null,{"className":" px-5 pt-10 md:px-[13.313rem] md:py-16","children":[["$","$L10",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":"Apache Pinot™ 0.11 - Timestamp Indexes"}],["$","p",null,{"className":"pt-2 text-lg","children":["By: ","Mark Needham"]}],["$","p",null,{"className":"py-2 text-sm","children":["November 22nd, 2022"," • ","8 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":[" ",["$","$L11",null,{"src":"https://www.youtube.com/embed/DetGpHZuzDU","title":"YouTube video player"}],["$","p",null,{"children":["The recent Apache ",["$","a",null,{"target":"_blank","rel":"noopener noreferrer","href":"https://medium.com/apache-pinot-developer-blog/apache-pinot-0-11-released-d564684df5d4","children":"Pinot™ 0.11.0"}]," release has lots of goodies for you to play with. This is the third in a series of blog posts showing off some of the new features in this release."]}],["$","p",null,{"children":"Pinot introduced the TIMESTAMP data type in the 0.8 release, which stores the time in millisecond epoch long format internally. The community feedback has been that the queries they’re running against timestamp columns don’t need this low-level granularity."}],["$","p",null,{"children":"Instead, users write queries that use the datetrunc function to filter at a coarser grain of functionality. Unfortunately, this approach results in scanning data and time value conversion work that takes a long time at large data volumes."}],["$","p",null,{"children":["The ",["$","a",null,{"target":"_blank","rel":"noopener noreferrer","href":"https://docs.pinot.apache.org/basics/indexing/timestamp-index","children":"timestamp index"}]," solves that problem! In this blog post, we’ll use it to get an almost 5x query speed improvement on a relatively small dataset of only 7m rows."]}],["$","p",null,{"children":["$","img",null,{"alt":"Time in milliseconds with and without timestamp indexes bar chart","src":"https://www.datocms-assets.com/75153/1669133004-image1.png","title":"Time in milliseconds with and without timestamp indexes bar chart"}]}],["$","h2",null,{"id":"spinning-up-pinot","children":[["$","a",null,{"href":"#spinning-up-pinot","aria-hidden":"true","tabIndex":"-1","children":["$","span",null,{"className":"icon icon-link"}]}],"Spinning up Pinot"]}],["$","p",null,{"children":"We’re going to be using the Pinot Docker container, but first, we’re going to create a network, as we’ll need that later on:"}],["$","p",null,{"children":"docker network create timestamp_blog"}],["$","p",null,{"children":["We’re going to spin up the empty ",["$","a",null,{"target":"_blank","rel":"noopener noreferrer","href":"https://docs.pinot.apache.org/basics/getting-started/quick-start","children":"QuickStart"}]," in a container named pinot-timestamp-blog:"]}],["$","$L12",null,{"className":"language-bash","children":["$","code",null,{"className":"language-bash code-highlight","children":[["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token function","children":"docker"}]," run ",["$","span",null,{"className":"token punctuation","children":"\\"}],"\n"]}],["$","span",null,{"className":"code-line","children":["  ",["$","span",null,{"className":"token variable parameter","children":"-p"}]," ",["$","span",null,{"className":"token number","children":"8000"}],":8000 ",["$","span",null,{"className":"token punctuation","children":"\\"}],"\n"]}],["$","span",null,{"className":"code-line","children":["  ",["$","span",null,{"className":"token variable parameter","children":"-p"}]," ",["$","span",null,{"className":"token number","children":"9000"}],":9000 ",["$","span",null,{"className":"token punctuation","children":"\\"}],"\n"]}],["$","span",null,{"className":"code-line","children":[" ",["$","span",null,{"className":"token variable parameter","children":"--name"}]," pinot-timestamp-blog ",["$","span",null,{"className":"token punctuation","children":"\\"}],"\n"]}],["$","span",null,{"className":"code-line","children":["  ",["$","span",null,{"className":"token variable parameter","children":"--network"}]," timestamp_blog ",["$","span",null,{"className":"token punctuation","children":"\\"}],"\n"]}],["$","span",null,{"className":"code-line","children":["  apachepinot/pinot:0.11.0 ",["$","span",null,{"className":"token punctuation","children":"\\"}],"\n"]}],["$","span",null,{"className":"code-line","children":["  QuickStart ",["$","span",null,{"className":"token variable parameter","children":"-type"}]," EMPTY\n"]}]]}]}],["$","p",null,{"children":"Or if you’re on a Mac M1, change the name of the image to have the arm-64 suffix, like this:"}],["$","$L12",null,{"className":"language-bash","children":["$","code",null,{"className":"language-bash code-highlight","children":[["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token function","children":"docker"}]," run ",["$","span",null,{"className":"token punctuation","children":"\\"}],"\n"]}],["$","span",null,{"className":"code-line","children":["  ",["$","span",null,{"className":"token variable parameter","children":"-p"}]," ",["$","span",null,{"className":"token number","children":"8000"}],":8000 ",["$","span",null,{"className":"token punctuation","children":"\\"}],"\n"]}],["$","span",null,{"className":"code-line","children":["  ",["$","span",null,{"className":"token variable parameter","children":"-p"}]," ",["$","span",null,{"className":"token number","children":"9000"}],":9000 ",["$","span",null,{"className":"token punctuation","children":"\\"}],"\n"]}],["$","span",null,{"className":"code-line","children":["    ",["$","span",null,{"className":"token variable parameter","children":"--network"}]," timestamp_blog ",["$","span",null,{"className":"token punctuation","children":"\\"}],"\n"]}],["$","span",null,{"className":"code-line","children":[" ",["$","span",null,{"className":"token variable parameter","children":"--name"}]," pinot-timestamp-blog ",["$","span",null,{"className":"token punctuation","children":"\\"}],"\n"]}],["$","span",null,{"className":"code-line","children":["  apachepinot/pinot:0.11.0-arm64 ",["$","span",null,{"className":"token punctuation","children":"\\"}],"\n"]}],["$","span",null,{"className":"code-line","children":["  QuickStart ",["$","span",null,{"className":"token variable parameter","children":"-type"}]," EMPTY\n"]}]]}]}],["$","p",null,{"children":["Once that’s up and running, we’ll be able to access the Pinot Data Explorer at ",["$","a",null,{"target":"_blank","rel":"noopener noreferrer","href":"http://localhost:9000/","children":"http://localhost:9000"}],", but at the moment, we don’t have any data to play with."]}],["$","h2",null,{"id":"importing-chicago-crime-dataset","children":[["$","a",null,{"href":"#importing-chicago-crime-dataset","aria-hidden":"true","tabIndex":"-1","children":["$","span",null,{"className":"icon icon-link"}]}],"Importing Chicago Crime Dataset"]}],["$","p",null,{"children":["The ",["$","a",null,{"target":"_blank","rel":"noopener noreferrer","href":"https://startree.ai/blog/analyzing-chicago-crimes-with-apache-pinot-and-streamlit","children":"Chicago Crime dataset"}]," is a small to medium-sized dataset with 7 million records representing reported crimes in the City of Chicago from 2001 until today."]}],["$","p",null,{"children":"It contains details of the type of crime, where it was committed, whether an arrest was recorded, which beat it occurred on, and more."}],["$","p",null,{"children":"Each of the crimes has an associated timestamp, which makes it a good dataset to demonstrate timestamp indexes."}],["$","p",null,{"children":["You can find the code used in this blog post in the ",["$","a",null,{"target":"_blank","rel":"noopener noreferrer","href":"https://github.com/startreedata/pinot-recipes/tree/main/recipes/analyzing-chicago-crimes","children":"Analyzing Chicago Crimes"}]," recipe section of ",["$","a",null,{"target":"_blank","rel":"noopener noreferrer","href":"https://github.com/startreedata/pinot-recipes","children":"Pinot Recipes GitHub repository"}],". From here on, I’m assuming that you’ve downloaded this repository and are in the recipes/analyzing-chicago-crimes directory."]}],["$","p",null,{"children":"We’re going to create a schema and table named crimes by running the following command:"}],["$","$L12",null,{"className":"language-bash","children":["$","code",null,{"className":"language-bash code-highlight","children":[["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token function","children":"docker"}]," run ",["$","span",null,{"className":"token punctuation","children":"\\"}],"\n"]}],["$","span",null,{"className":"code-line","children":["   ",["$","span",null,{"className":"token variable parameter","children":"--network"}]," timestamp_blog ",["$","span",null,{"className":"token punctuation","children":"\\"}],"\n"]}],["$","span",null,{"className":"code-line","children":["   ",["$","span",null,{"className":"token variable parameter","children":"-v"}]," ",["$","span",null,{"className":"token constant environment","children":"$$PWD"}],"/config:/config ",["$","span",null,{"className":"token punctuation","children":"\\"}],"\n"]}],["$","span",null,{"className":"code-line","children":["   apachepinot/pinot:0.11.0-arm64 AddTable ",["$","span",null,{"className":"token punctuation","children":"\\"}],"\n"]}],["$","span",null,{"className":"code-line","children":["     ",["$","span",null,{"className":"token variable parameter","children":"-schemaFile"}]," /config/schema.json ",["$","span",null,{"className":"token punctuation","children":"\\"}],"\n"]}],["$","span",null,{"className":"code-line","children":["     ",["$","span",null,{"className":"token variable parameter","children":"-tableConfigFile"}]," /config/table.json ",["$","span",null,{"className":"token punctuation","children":"\\"}],"\n"]}],["$","span",null,{"className":"code-line","children":["     ",["$","span",null,{"className":"token variable parameter","children":"-controllerHost"}]," pinot-timestamp-blog ",["$","span",null,{"className":"token punctuation","children":"\\"}],"\n"]}],["$","span",null,{"className":"code-line","children":["    ",["$","span",null,{"className":"token variable parameter","children":"-exec"}],"\n"]}],["$","span",null,{"className":"code-line","children":"\n"}]]}]}],["$","p",null,{"children":"We should see the following output:"}],["$","$L12",null,{"className":"language-bash","children":["$","code",null,{"className":"language-bash code-highlight","children":["$","span",null,{"className":"code-line","children":["  ",["$","span",null,{"className":"token number","children":"2022"}],"/11/03 ",["$","span",null,{"className":"token number","children":"13"}],":07:57.169 INFO ",["$","span",null,{"className":"token punctuation","children":"\\"}],["$","span",null,{"className":"token punctuation","children":"["}],"AddTableCommand",["$","span",null,{"className":"token punctuation","children":"\\"}],["$","span",null,{"className":"token punctuation","children":"]"}]," ",["$","span",null,{"className":"token punctuation","children":"\\"}],["$","span",null,{"className":"token punctuation","children":"["}],"main",["$","span",null,{"className":"token punctuation","children":"\\"}],["$","span",null,{"className":"token punctuation","children":"]"}]," ",["$","span",null,{"className":"token punctuation","children":"{"}],["$","span",null,{"className":"token string","children":"\"unrecognizedProperties\""}],":",["$","span",null,{"className":"token punctuation","children":"{"}],["$","span",null,{"className":"token punctuation","children":"}"}],",",["$","span",null,{"className":"token string","children":"\"status\""}],["$","span",null,{"className":"token builtin class-name","children":":"}],["$","span",null,{"className":"token string","children":"\"TableConfigs crimes successfully added\""}],["$","span",null,{"className":"token punctuation","children":"}"}],"\n"]}]}]}],["$","p",null,{"children":"A screenshot of the schema is shown below:"}],["$","p",null,{"children":["$","img",null,{"alt":"Chicago crime dataset table schema","src":"https://www.datocms-assets.com/75153/1669132979-image3.png","title":"Chicago crime dataset table schema"}]}],["$","p",null,{"children":["We won’t go through the table config and schema files in this blog post because we did that in the last post, but you can find them in the ",["$","a",null,{"target":"_blank","rel":"noopener noreferrer","href":"https://github.com/startreedata/pinot-recipes/tree/main/recipes/analyzing-chicago-crimes/config","children":"config"}]," directory on GitHub."]}],["$","p",null,{"children":"Now, let’s import the dataset."}],["$","$L12",null,{"className":"language-bash","children":["$","code",null,{"className":"language-bash code-highlight","children":[["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token function","children":"docker"}]," run ",["$","span",null,{"className":"token punctuation","children":"\\"}],"\n"]}],["$","span",null,{"className":"code-line","children":["   ",["$","span",null,{"className":"token variable parameter","children":"--network"}]," timestamp_blog ",["$","span",null,{"className":"token punctuation","children":"\\"}],"\n"]}],["$","span",null,{"className":"code-line","children":["   ",["$","span",null,{"className":"token variable parameter","children":"-v"}]," ",["$","span",null,{"className":"token constant environment","children":"$$PWD"}],"/config:/config ",["$","span",null,{"className":"token punctuation","children":"\\"}],"\n"]}],["$","span",null,{"className":"code-line","children":["   ",["$","span",null,{"className":"token variable parameter","children":"-v"}]," ",["$","span",null,{"className":"token constant environment","children":"$$PWD"}],"/data:/data ",["$","span",null,{"className":"token punctuation","children":"\\"}],"\n"]}],["$","span",null,{"className":"code-line","children":["   apachepinot/pinot:0.11.0-arm64 LaunchDataIngestionJob ",["$","span",null,{"className":"token punctuation","children":"\\"}],"\n"]}],["$","span",null,{"className":"code-line","children":["    ",["$","span",null,{"className":"token variable parameter","children":"-jobSpecFile"}]," /config/job-spec.yml ",["$","span",null,{"className":"token punctuation","children":"\\"}],"\n"]}],["$","span",null,{"className":"code-line","children":["     ",["$","span",null,{"className":"token variable parameter","children":"-values"}]," ",["$","span",null,{"className":"token variable assign-left","children":"controllerHost"}],["$","span",null,{"className":"token operator","children":"="}],"pinot-timestamp-blog\n"]}]]}]}],["$","p",null,{"children":"It will take a few minutes to load, but once that command has finished, we’re ready to query the crimes table."}],["$","h2",null,{"id":"querying-crimes-by-date","children":[["$","a",null,{"href":"#querying-crimes-by-date","aria-hidden":"true","tabIndex":"-1","children":["$","span",null,{"className":"icon icon-link"}]}],"Querying crimes by date"]}],["$","p",null,{"children":"The following query finds the number of crimes that happened after 16th January 2017, grouped by week of the year, with the most crime-filled weeks shown first:"}],["$","$L12",null,{"className":"language-sql","children":["$","code",null,{"className":"code-highlight language-sql","children":[["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token keyword","children":"select"}]," datetrunc",["$","span",null,{"className":"token punctuation","children":"("}],["$","span",null,{"className":"token string","children":"'WEEK'"}],["$","span",null,{"className":"token punctuation","children":","}]," DateEpoch",["$","span",null,{"className":"token punctuation","children":")"}]," ",["$","span",null,{"className":"token keyword","children":"as"}]," tsWeek",["$","span",null,{"className":"token punctuation","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":")"}],"\n"]}],["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token keyword","children":"from"}]," crimes\n"]}],["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token keyword","children":"WHERE"}]," tsWeek ",["$","span",null,{"className":"token operator","children":">"}]," fromDateTime",["$","span",null,{"className":"token punctuation","children":"("}],["$","span",null,{"className":"token string","children":"'2017-01-16'"}],["$","span",null,{"className":"token punctuation","children":","}]," ",["$","span",null,{"className":"token string","children":"'yyyy-MM-dd'"}],["$","span",null,{"className":"token punctuation","children":")"}],"\n"]}],["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token keyword","children":"group"}]," ",["$","span",null,{"className":"token keyword","children":"by"}]," tsWeek\n"]}],["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token keyword","children":"order"}]," ",["$","span",null,{"className":"token keyword","children":"by"}]," ",["$","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 keyword","children":"DESC"}],"\n"]}],["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token keyword","children":"limit"}]," ",["$","span",null,{"className":"token number","children":"10"}],"\n"]}]]}]}],["$","p",null,{"children":"If we run that query, we’ll see the following results:"}],["$","p",null,{"children":["$","img",null,{"alt":"Chicago crime dataset query result","src":"https://www.datocms-assets.com/75153/1669133027-image6.png","title":"Chicago crime dataset query result"}]}],["$","p",null,{"children":"And, if we look above the query result, there’s metadata about the query, including the time that it took to run."}],["$","p",null,{"children":["$","img",null,{"alt":"Chicago crime dataset metadata about the query, including the time that it took to run","src":"https://www.datocms-assets.com/75153/1669133059-image5.png","title":"Chicago crime dataset metadata about the query, including the time that it took to run"}]}],["$","p",null,{"children":"The query took 141 ms to execute, so that’s our baseline."}],["$","h2",null,{"id":"adding-the-timestamp-index","children":[["$","a",null,{"href":"#adding-the-timestamp-index","aria-hidden":"true","tabIndex":"-1","children":["$","span",null,{"className":"icon icon-link"}]}],"Adding the timestamp index"]}],["$","p",null,{"children":"We could add a timestamp index directly to this table and then compare query performance, but to make it easier to do comparisons, we’re going to create an identical table with the timestamp index applied."}],["$","p",null,{"children":["The full table config is available in the ",["$","a",null,{"target":"_blank","rel":"noopener noreferrer","href":"https://github.com/startreedata/pinot-recipes/blob/main/recipes/analyzing-chicago-crimes/config/table-index.json","children":"config/table-index.json"}]," file, and the main change is that we’ve added the following section to add a timestamp index on the DateEpoch column:"]}],["$","$L12",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":"\"fieldConfigList\""}],["$","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":"\"name\""}],["$","span",null,{"className":"token operator","children":":"}]," ",["$","span",null,{"className":"token string","children":"\"DateEpoch\""}],["$","span",null,{"className":"token punctuation","children":","}],"\n"]}],["$","span",null,{"className":"code-line","children":["    ",["$","span",null,{"className":"token property","children":"\"encodingType\""}],["$","span",null,{"className":"token operator","children":":"}]," ",["$","span",null,{"className":"token string","children":"\"DICTIONARY\""}],["$","span",null,{"className":"token punctuation","children":","}],"\n"]}],["$","span",null,{"className":"code-line","children":["    ",["$","span",null,{"className":"token property","children":"\"indexTypes\""}],["$","span",null,{"className":"token operator","children":":"}]," ",["$","span",null,{"className":"token punctuation","children":"["}],["$","span",null,{"className":"token string","children":"\"TIMESTAMP\""}],["$","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":"\"timestampConfig\""}],["$","span",null,{"className":"token operator","children":":"}]," ",["$","span",null,{"className":"token punctuation","children":"{"}],"\n"]}],["$","span",null,{"className":"code-line","children":["      ",["$","span",null,{"className":"token property","children":"\"granularities\""}],["$","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":"\"DAY\""}],["$","span",null,{"className":"token punctuation","children":","}],"\n"]}],["$","span",null,{"className":"code-line","children":["        ",["$","span",null,{"className":"token string","children":"\"WEEK\""}],["$","span",null,{"className":"token punctuation","children":","}],"\n"]}],["$","span",null,{"className":"code-line","children":["        ",["$","span",null,{"className":"token string","children":"\"MONTH\""}],"\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"]}],["$","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":"]"}],["$","span",null,{"className":"token punctuation","children":","}],"\n"]}]]}]}],["$","p",null,{"children":[["$","em",null,{"children":"encodingType"}]," will always be ‘DICTIONARY’ and ",["$","em",null,{"children":"indexTypes"}]," must contain ‘TIMESTAMP’. We should specify granularities based on our query patterns."]}],["$","p",null,{"children":["As a rule of thumb, work out which values you most commonly pass as the first argument to the ",["$","a",null,{"target":"_blank","rel":"noopener noreferrer","href":"https://docs.pinot.apache.org/configuration-reference/functions/datetrunc","children":"datetrunc function"}]," in your queries and include those values."]}],["$","p",null,{"children":["The full list of valid granularities is: ",["$","em",null,{"children":"millisecond"}],", ",["$","em",null,{"children":"second"}],", ",["$","em",null,{"children":"minute"}],", ",["$","em",null,{"children":"hour"}],", ",["$","em",null,{"children":"day"}],", ",["$","em",null,{"children":"week"}],", ",["$","em",null,{"children":"month"}],", ",["$","em",null,{"children":"quarter"}],", and ",["$","em",null,{"children":"year"}],"."]}],["$","p",null,{"children":"Our new table is called crimes_indexed, and we’re also going to create a new schema with all the same columns called crimes_indexed, as Pinot requires the table and schema names to match."}],["$","p",null,{"children":"We can create the schema and table by running the following command:"}],["$","$L12",null,{"className":"language-bash","children":["$","code",null,{"className":"language-bash code-highlight","children":[["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token function","children":"docker"}]," run ",["$","span",null,{"className":"token punctuation","children":"\\"}],"\n"]}],["$","span",null,{"className":"code-line","children":["   ",["$","span",null,{"className":"token variable parameter","children":"--network"}]," timestamp_blog ",["$","span",null,{"className":"token punctuation","children":"\\"}],"\n"]}],["$","span",null,{"className":"code-line","children":["   ",["$","span",null,{"className":"token variable parameter","children":"-v"}]," ",["$","span",null,{"className":"token constant environment","children":"$$PWD"}],"/config:/config ",["$","span",null,{"className":"token punctuation","children":"\\"}],"\n"]}],["$","span",null,{"className":"code-line","children":["   apachepinot/pinot:0.11.0-arm64 AddTable ",["$","span",null,{"className":"token punctuation","children":"\\"}],"\n"]}],["$","span",null,{"className":"code-line","children":["     ",["$","span",null,{"className":"token variable parameter","children":"-schemaFile"}]," /config/schema-index.json ",["$","span",null,{"className":"token punctuation","children":"\\"}],"\n"]}],["$","span",null,{"className":"code-line","children":["     ",["$","span",null,{"className":"token variable parameter","children":"-tableConfigFile"}]," /config/table-index.json ",["$","span",null,{"className":"token punctuation","children":"\\"}],"\n"]}],["$","span",null,{"className":"code-line","children":["     ",["$","span",null,{"className":"token variable parameter","children":"-controllerHost"}]," pinot-timestamp-blog ",["$","span",null,{"className":"token punctuation","children":"\\"}],"\n"]}],["$","span",null,{"className":"code-line","children":["    ",["$","span",null,{"className":"token variable parameter","children":"-exec"}],"\n"]}]]}]}],["$","p",null,{"children":"We’ll populate that table by copying the segment that we created earlier for the crimes table."}],["$","$L12",null,{"className":"language-bash","children":["$","code",null,{"className":"language-bash code-highlight","children":[["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token function","children":"docker"}]," run ",["$","span",null,{"className":"token punctuation","children":"\\"}],"\n"]}],["$","span",null,{"className":"code-line","children":["   ",["$","span",null,{"className":"token variable parameter","children":"--network"}]," timestamp_blog ",["$","span",null,{"className":"token punctuation","children":"\\"}],"\n"]}],["$","span",null,{"className":"code-line","children":["   ",["$","span",null,{"className":"token variable parameter","children":"-v"}]," ",["$","span",null,{"className":"token constant environment","children":"$$PWD"}],"/config:/config ",["$","span",null,{"className":"token punctuation","children":"\\"}],"\n"]}],["$","span",null,{"className":"code-line","children":["   ",["$","span",null,{"className":"token variable parameter","children":"-v"}]," ",["$","span",null,{"className":"token constant environment","children":"$$PWD"}],"/data:/data ",["$","span",null,{"className":"token punctuation","children":"\\"}],"\n"]}],["$","span",null,{"className":"code-line","children":["   apachepinot/pinot:0.11.0-arm64 LaunchDataIngestionJob ",["$","span",null,{"className":"token punctuation","children":"\\"}],"\n"]}],["$","span",null,{"className":"code-line","children":["    ",["$","span",null,{"className":"token variable parameter","children":"-jobSpecFile"}]," /config/job-spec-download.yml ",["$","span",null,{"className":"token punctuation","children":"\\"}],"\n"]}],["$","span",null,{"className":"code-line","children":["     ",["$","span",null,{"className":"token variable parameter","children":"-values"}]," ",["$","span",null,{"className":"token variable assign-left","children":"controllerHost"}],["$","span",null,{"className":"token operator","children":"="}],"pinot-timestamp-blog\n"]}]]}]}],["$","p",null,{"children":["If you’re curious how that job spec works, I ",["$","a",null,{"target":"_blank","rel":"noopener noreferrer","href":"https://www.markhneedham.com/blog/2021/12/06/apache-pinot-copy-segment-new-table/","children":"wrote a blog post explaining it in a bit more detail"}],"."]}],["$","p",null,{"children":"Once the Pinot Server has downloaded this segment, it will apply the timestamp index to the DateEpoch column."}],["$","p",null,{"children":"For the curious, we can see this happening in the log files by connecting to the Pinot container and running the following grep command:"}],["$","$L12",null,{"className":"language-bash","children":["$","code",null,{"className":"language-bash code-highlight","children":[["$","span",null,{"className":"code-line","children":["​​docker ",["$","span",null,{"className":"token builtin class-name","children":"exec"}]," ",["$","span",null,{"className":"token variable parameter","children":"-iti"}]," pinot-timestamp-blog ",["$","span",null,{"className":"token punctuation","children":"\\"}],"\n"]}],["$","span",null,{"className":"code-line","children":["  ",["$","span",null,{"className":"token function","children":"grep"}]," ",["$","span",null,{"className":"token variable parameter","children":"-rni"}]," ",["$","span",null,{"className":"token variable parameter","children":"-A10"}]," ",["$","span",null,{"className":"token string","children":"\"Successfully downloaded segment:.*crimes_indexed_OFFLINE.*\""}]," ",["$","span",null,{"className":"token punctuation","children":"\\"}],"\n"]}],["$","span",null,{"className":"code-line","children":"  logs/pinot-all.log\n"}]]}]}],["$","p",null,{"children":"We’ll see something like the following (tidied up for brevity):"}],["$","$L12",null,{"className":"language-log","children":["$","code",null,{"className":"code-highlight language-log","children":[["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token punctuation","children":"["}],"BaseTableDataManager",["$","span",null,{"className":"token punctuation","children":"]"}],"  ",["$","span",null,{"className":"token property","children":"Successfully downloaded segment:"}]," ",["$","span",null,{"className":"token property","children":"crimes_OFFLINE_0 of table:"}]," ",["$","span",null,{"className":"token property","children":"crimes_indexed_OFFLINE to index dir:"}]," ",["$","span",null,{"className":"token string file-path","children":"/tmp/1667490598253/quickstart/PinotServerDataDir0/crimes_indexed_OFFLINE/crimes_OFFLINE_0"}],"\n"]}],["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token punctuation","children":"["}],"V3DefaultColumnHandler",["$","span",null,{"className":"token punctuation","children":"]"}],"  ",["$","span",null,{"className":"token property","children":"Starting default column action:"}]," ",["$","span",null,{"className":"token property","children":"ADD_DATE_TIME on column:"}]," ",["$","span",null,{"className":"token operator","children":"$$"}],"DateEpoch",["$","span",null,{"className":"token operator","children":"$$"}],"DAY\n"]}],["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token punctuation","children":"["}],"SegmentDictionaryCreator",["$","span",null,{"className":"token punctuation","children":"]"}],"  ",["$","span",null,{"className":"token property","children":"Created dictionary for LONG column:"}]," ",["$","span",null,{"className":"token operator","children":"$$"}],"DateEpoch",["$","span",null,{"className":"token operator","children":"$$"}],"DAY with cardinality",["$","span",null,{"className":"token operator","children":":"}]," ",["$","span",null,{"className":"token number","children":"7969"}],["$","span",null,{"className":"token punctuation","children":","}]," range",["$","span",null,{"className":"token operator","children":":"}]," ",["$","span",null,{"className":"token number","children":"978307200000"}]," to ",["$","span",null,{"className":"token number","children":"1666742400000"}],"\n"]}],["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token punctuation","children":"["}],"V3DefaultColumnHandler",["$","span",null,{"className":"token punctuation","children":"]"}],"  ",["$","span",null,{"className":"token property","children":"Starting default column action:"}]," ",["$","span",null,{"className":"token property","children":"ADD_DATE_TIME on column:"}]," ",["$","span",null,{"className":"token operator","children":"$$"}],"DateEpoch",["$","span",null,{"className":"token operator","children":"$$"}],"WEEK\n"]}],["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token punctuation","children":"["}],"SegmentDictionaryCreator",["$","span",null,{"className":"token punctuation","children":"]"}],"  ",["$","span",null,{"className":"token property","children":"Created dictionary for LONG column:"}]," ",["$","span",null,{"className":"token operator","children":"$$"}],"DateEpoch",["$","span",null,{"className":"token operator","children":"$$"}],"WEEK with cardinality",["$","span",null,{"className":"token operator","children":":"}]," ",["$","span",null,{"className":"token number","children":"1139"}],["$","span",null,{"className":"token punctuation","children":","}]," range",["$","span",null,{"className":"token operator","children":":"}]," ",["$","span",null,{"className":"token number","children":"978307200000"}]," to ",["$","span",null,{"className":"token number","children":"1666569600000"}],"\n"]}],["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token punctuation","children":"["}],"V3DefaultColumnHandler",["$","span",null,{"className":"token punctuation","children":"]"}],"  ",["$","span",null,{"className":"token property","children":"Starting default column action:"}]," ",["$","span",null,{"className":"token property","children":"ADD_DATE_TIME on column:"}]," ",["$","span",null,{"className":"token operator","children":"$$"}],"DateEpoch",["$","span",null,{"className":"token operator","children":"$$"}],"MONTH\n"]}],["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token punctuation","children":"["}],"SegmentDictionaryCreator",["$","span",null,{"className":"token punctuation","children":"]"}],"  ",["$","span",null,{"className":"token property","children":"Created dictionary for LONG column:"}]," ",["$","span",null,{"className":"token operator","children":"$$"}],"DateEpoch",["$","span",null,{"className":"token operator","children":"$$"}],"MONTH with cardinality",["$","span",null,{"className":"token operator","children":":"}]," ",["$","span",null,{"className":"token number","children":"262"}],["$","span",null,{"className":"token punctuation","children":","}]," range",["$","span",null,{"className":"token operator","children":":"}]," ",["$","span",null,{"className":"token number","children":"978307200000"}]," to ",["$","span",null,{"className":"token number","children":"1664582400000"}],"\n"]}],["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token punctuation","children":"["}],"RangeIndexHandler",["$","span",null,{"className":"token punctuation","children":"]"}],"  ",["$","span",null,{"className":"token property","children":"Creating new range index for segment:"}]," crimes_OFFLINE_0",["$","span",null,{"className":"token punctuation","children":","}]," column",["$","span",null,{"className":"token operator","children":":"}]," ",["$","span",null,{"className":"token operator","children":"$$"}],"DateEpoch",["$","span",null,{"className":"token operator","children":"$$"}],"DAY\n"]}],["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token punctuation","children":"["}],"RangeIndexHandler",["$","span",null,{"className":"token punctuation","children":"]"}],"  ",["$","span",null,{"className":"token property","children":"Created range index for segment:"}]," crimes_OFFLINE_0",["$","span",null,{"className":"token punctuation","children":","}]," column",["$","span",null,{"className":"token operator","children":":"}]," ",["$","span",null,{"className":"token operator","children":"$$"}],"DateEpoch",["$","span",null,{"className":"token operator","children":"$$"}],"DAY\n"]}],["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token punctuation","children":"["}],"RangeIndexHandler",["$","span",null,{"className":"token punctuation","children":"]"}],"  ",["$","span",null,{"className":"token property","children":"Creating new range index for segment:"}]," crimes_OFFLINE_0",["$","span",null,{"className":"token punctuation","children":","}]," column",["$","span",null,{"className":"token operator","children":":"}]," ",["$","span",null,{"className":"token operator","children":"$$"}],"DateEpoch",["$","span",null,{"className":"token operator","children":"$$"}],"WEEK\n"]}],["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token punctuation","children":"["}],"RangeIndexHandler",["$","span",null,{"className":"token punctuation","children":"]"}],"  ",["$","span",null,{"className":"token property","children":"Created range index for segment:"}]," crimes_OFFLINE_0",["$","span",null,{"className":"token punctuation","children":","}]," column",["$","span",null,{"className":"token operator","children":":"}]," ",["$","span",null,{"className":"token operator","children":"$$"}],"DateEpoch",["$","span",null,{"className":"token operator","children":"$$"}],"WEEK\n"]}],["$","span",null,{"className":"code-line","children":"\n"}]]}]}],["$","h2",null,{"id":"what-does-a-timestamp-index-do","children":[["$","a",null,{"href":"#what-does-a-timestamp-index-do","aria-hidden":"true","tabIndex":"-1","children":["$","span",null,{"className":"icon icon-link"}]}],"What does a timestamp index do?"]}],["$","p",null,{"children":"So, the timestamp index has now been created, but what does it actually do?"}],["$","p",null,{"children":"When we add a timestamp index on a column, Pinot creates a derived column for each granularity and adds a range index for each new column."}],["$","p",null,{"children":["In our case, that means we’ll have these extra columns: ",["$","span",null,{"className":"math math-inline","children":["$","span",null,{"className":"katex","children":[["$","span",null,{"className":"katex-mathml","children":["$","math",null,{"xmlns":"http://www.w3.org/1998/Math/MathML","children":["$","semantics",null,{"children":[["$","mrow",null,{"children":[["$","mi",null,{"children":"D"}],["$","mi",null,{"children":"a"}],["$","mi",null,{"children":"t"}],["$","mi",null,{"children":"e"}],["$","mi",null,{"children":"E"}],["$","mi",null,{"children":"p"}],["$","mi",null,{"children":"o"}],["$","mi",null,{"children":"c"}],["$","mi",null,{"children":"h"}]]}],["$","annotation",null,{"encoding":"application/x-tex","children":"DateEpoch"}]]}]}]}],["$","span",null,{"className":"katex-html","aria-hidden":"true","children":["$","span",null,{"className":"base","children":[["$","span",null,{"className":"strut","style":{"height":".8889em","verticalAlign":"-.1944em"}}],["$","span",null,{"className":"mathnormal mord","style":{"marginRight":".02778em"},"children":"D"}],["$","span",null,{"className":"mathnormal mord","children":"a"}],["$","span",null,{"className":"mathnormal mord","children":"t"}],["$","span",null,{"className":"mathnormal mord","children":"e"}],["$","span",null,{"className":"mathnormal mord","children":"Ep"}],["$","span",null,{"className":"mathnormal mord","children":"oc"}],["$","span",null,{"className":"mathnormal mord","children":"h"}]]}]}]]}]}],"DAY, ",["$","span",null,{"className":"math math-inline","children":["$","span",null,{"className":"katex","children":[["$","span",null,{"className":"katex-mathml","children":["$","math",null,{"xmlns":"http://www.w3.org/1998/Math/MathML","children":["$","semantics",null,{"children":[["$","mrow",null,{"children":[["$","mi",null,{"children":"D"}],["$","mi",null,{"children":"a"}],["$","mi",null,{"children":"t"}],["$","mi",null,{"children":"e"}],["$","mi",null,{"children":"E"}],["$","mi",null,{"children":"p"}],["$","mi",null,{"children":"o"}],["$","mi",null,{"children":"c"}],["$","mi",null,{"children":"h"}]]}],["$","annotation",null,{"encoding":"application/x-tex","children":"DateEpoch"}]]}]}]}],["$","span",null,{"className":"katex-html","aria-hidden":"true","children":["$","span",null,{"className":"base","children":[["$","span",null,{"className":"strut","style":{"height":".8889em","verticalAlign":"-.1944em"}}],["$","span",null,{"className":"mathnormal mord","style":{"marginRight":".02778em"},"children":"D"}],["$","span",null,{"className":"mathnormal mord","children":"a"}],["$","span",null,{"className":"mathnormal mord","children":"t"}],["$","span",null,{"className":"mathnormal mord","children":"e"}],["$","span",null,{"className":"mathnormal mord","children":"Ep"}],["$","span",null,{"className":"mathnormal mord","children":"oc"}],["$","span",null,{"className":"mathnormal mord","children":"h"}]]}]}]]}]}],"WEEK, and ",["$","span",null,{"className":"math math-inline","children":["$","span",null,{"className":"katex","children":[["$","span",null,{"className":"katex-mathml","children":["$","math",null,{"xmlns":"http://www.w3.org/1998/Math/MathML","children":["$","semantics",null,{"children":[["$","mrow",null,{"children":[["$","mi",null,{"children":"D"}],["$","mi",null,{"children":"a"}],["$","mi",null,{"children":"t"}],["$","mi",null,{"children":"e"}],["$","mi",null,{"children":"E"}],["$","mi",null,{"children":"p"}],["$","mi",null,{"children":"o"}],["$","mi",null,{"children":"c"}],["$","mi",null,{"children":"h"}]]}],["$","annotation",null,{"encoding":"application/x-tex","children":"DateEpoch"}]]}]}]}],["$","span",null,{"className":"katex-html","aria-hidden":"true","children":["$","span",null,{"className":"base","children":[["$","span",null,{"className":"strut","style":{"height":".8889em","verticalAlign":"-.1944em"}}],["$","span",null,{"className":"mathnormal mord","style":{"marginRight":".02778em"},"children":"D"}],["$","span",null,{"className":"mathnormal mord","children":"a"}],["$","span",null,{"className":"mathnormal mord","children":"t"}],["$","span",null,{"className":"mathnormal mord","children":"e"}],["$","span",null,{"className":"mathnormal mord","children":"Ep"}],["$","span",null,{"className":"mathnormal mord","children":"oc"}],["$","span",null,{"className":"mathnormal mord","children":"h"}]]}]}]]}]}],"MONTH."]}],["$","p",null,{"children":["We can check if the extra columns and indexes have been added by navigating to the ",["$","a",null,{"target":"_blank","rel":"noopener noreferrer","href":"http://localhost:9000/#/tenants/table/crimes_indexed_OFFLINE/crimes_OFFLINE_0","children":"segment_page"}]," and typing ",["$","span",null,{"className":"math math-inline","children":["$","span",null,{"className":"katex","children":[["$","span",null,{"className":"katex-mathml","children":["$","math",null,{"xmlns":"http://www.w3.org/1998/Math/MathML","children":["$","semantics",null,{"children":[["$","mrow",null,{"children":[["$","mi",null,{"children":"D"}],["$","mi",null,{"children":"a"}],["$","mi",null,{"children":"t"}],["$","mi",null,{"children":"e"}]]}],["$","annotation",null,{"encoding":"application/x-tex","children":"Date"}]]}]}]}],["$","span",null,{"className":"katex-html","aria-hidden":"true","children":["$","span",null,{"className":"base","children":[["$","span",null,{"className":"strut","style":{"height":".6833em"}}],["$","span",null,{"className":"mathnormal mord","style":{"marginRight":".02778em"},"children":"D"}],["$","span",null,{"className":"mathnormal mord","children":"a"}],["$","span",null,{"className":"mathnormal mord","children":"t"}],["$","span",null,{"className":"mathnormal mord","children":"e"}]]}]}]]}]}],"Epoch in the search box.  You should see the following:"]}],["$","p",null,{"children":["$","img",null,{"alt":"Apache Pinot timestamp index on a column","src":"https://www.datocms-assets.com/75153/1669133112-image2.png","title":"Apache Pinot timestamp index on a column"}]}],["$","p",null,{"children":"These columns will be assigned the following values:"}],["$","ul",null,{"children":[["$","li",null,{"children":[["$","span",null,{"className":"math math-inline","children":["$","span",null,{"className":"katex","children":[["$","span",null,{"className":"katex-mathml","children":["$","math",null,{"xmlns":"http://www.w3.org/1998/Math/MathML","children":["$","semantics",null,{"children":[["$","mrow",null,{"children":[["$","mi",null,{"children":"D"}],["$","mi",null,{"children":"a"}],["$","mi",null,{"children":"t"}],["$","mi",null,{"children":"e"}],["$","mi",null,{"children":"E"}],["$","mi",null,{"children":"p"}],["$","mi",null,{"children":"o"}],["$","mi",null,{"children":"c"}],["$","mi",null,{"children":"h"}]]}],["$","annotation",null,{"encoding":"application/x-tex","children":"DateEpoch"}]]}]}]}],["$","span",null,{"className":"katex-html","aria-hidden":"true","children":["$","span",null,{"className":"base","children":[["$","span",null,{"className":"strut","style":{"height":".8889em","verticalAlign":"-.1944em"}}],["$","span",null,{"className":"mathnormal mord","style":{"marginRight":".02778em"},"children":"D"}],["$","span",null,{"className":"mathnormal mord","children":"a"}],["$","span",null,{"className":"mathnormal mord","children":"t"}],["$","span",null,{"className":"mathnormal mord","children":"e"}],["$","span",null,{"className":"mathnormal mord","children":"Ep"}],["$","span",null,{"className":"mathnormal mord","children":"oc"}],["$","span",null,{"className":"mathnormal mord","children":"h"}]]}]}]]}]}],"DAY = dateTrunc(‘DAY’, DateEpoch)"]}],["$","li",null,{"children":[["$","span",null,{"className":"math math-inline","children":["$","span",null,{"className":"katex","children":[["$","span",null,{"className":"katex-mathml","children":["$","math",null,{"xmlns":"http://www.w3.org/1998/Math/MathML","children":["$","semantics",null,{"children":[["$","mrow",null,{"children":[["$","mi",null,{"children":"D"}],["$","mi",null,{"children":"a"}],["$","mi",null,{"children":"t"}],["$","mi",null,{"children":"e"}],["$","mi",null,{"children":"E"}],["$","mi",null,{"children":"p"}],["$","mi",null,{"children":"o"}],["$","mi",null,{"children":"c"}],["$","mi",null,{"children":"h"}]]}],["$","annotation",null,{"encoding":"application/x-tex","children":"DateEpoch"}]]}]}]}],["$","span",null,{"className":"katex-html","aria-hidden":"true","children":["$","span",null,{"className":"base","children":[["$","span",null,{"className":"strut","style":{"height":".8889em","verticalAlign":"-.1944em"}}],["$","span",null,{"className":"mathnormal mord","style":{"marginRight":".02778em"},"children":"D"}],["$","span",null,{"className":"mathnormal mord","children":"a"}],["$","span",null,{"className":"mathnormal mord","children":"t"}],["$","span",null,{"className":"mathnormal mord","children":"e"}],["$","span",null,{"className":"mathnormal mord","children":"Ep"}],["$","span",null,{"className":"mathnormal mord","children":"oc"}],["$","span",null,{"className":"mathnormal mord","children":"h"}]]}]}]]}]}],"WEEK = dateTrunc(‘WEEK’, DateEpoch)"]}],["$","li",null,{"children":[["$","span",null,{"className":"math math-inline","children":["$","span",null,{"className":"katex","children":[["$","span",null,{"className":"katex-mathml","children":["$","math",null,{"xmlns":"http://www.w3.org/1998/Math/MathML","children":["$","semantics",null,{"children":[["$","mrow",null,{"children":[["$","mi",null,{"children":"D"}],["$","mi",null,{"children":"a"}],["$","mi",null,{"children":"t"}],["$","mi",null,{"children":"e"}],["$","mi",null,{"children":"E"}],["$","mi",null,{"children":"p"}],["$","mi",null,{"children":"o"}],["$","mi",null,{"children":"c"}],["$","mi",null,{"children":"h"}]]}],["$","annotation",null,{"encoding":"application/x-tex","children":"DateEpoch"}]]}]}]}],["$","span",null,{"className":"katex-html","aria-hidden":"true","children":["$","span",null,{"className":"base","children":[["$","span",null,{"className":"strut","style":{"height":".8889em","verticalAlign":"-.1944em"}}],["$","span",null,{"className":"mathnormal mord","style":{"marginRight":".02778em"},"children":"D"}],["$","span",null,{"className":"mathnormal mord","children":"a"}],["$","span",null,{"className":"mathnormal mord","children":"t"}],["$","span",null,{"className":"mathnormal mord","children":"e"}],["$","span",null,{"className":"mathnormal mord","children":"Ep"}],["$","span",null,{"className":"mathnormal mord","children":"oc"}],["$","span",null,{"className":"mathnormal mord","children":"h"}]]}]}]]}]}],"MONTH = dateTrunc(‘MONTH’, DateEpoch)"]}]]}],["$","p",null,{"children":"Pinot will also rewrite any queries that use the dateTrunc function with DAY, WEEK, or MONTH and the DateEpoch field to use those new columns."}],["$","p",null,{"children":"This means that this query:"}],["$","$L12",null,{"className":"language-sql","children":["$","code",null,{"className":"code-highlight language-sql","children":[["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token keyword","children":"select"}]," datetrunc",["$","span",null,{"className":"token punctuation","children":"("}],["$","span",null,{"className":"token string","children":"'WEEK'"}],["$","span",null,{"className":"token punctuation","children":","}]," DateEpoch",["$","span",null,{"className":"token punctuation","children":")"}]," ",["$","span",null,{"className":"token keyword","children":"as"}]," tsWeek",["$","span",null,{"className":"token punctuation","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":")"}],"\n"]}],["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token keyword","children":"from"}]," crimes_indexed\n"]}],["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token keyword","children":"GROUP"}]," ",["$","span",null,{"className":"token keyword","children":"BY"}]," tsWeek\n"]}],["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token keyword","children":"limit"}]," ",["$","span",null,{"className":"token number","children":"10"}],"\n"]}]]}]}],["$","p",null,{"children":"Would be rewritten as:"}],["$","$L12",null,{"className":"language-sql","children":["$","code",null,{"className":"code-highlight language-sql","children":[["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token keyword","children":"select"}],"  $DateEpoch$WEEK ",["$","span",null,{"className":"token keyword","children":"as"}]," tsWeek",["$","span",null,{"className":"token punctuation","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":")"}],"\n"]}],["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token keyword","children":"from"}]," crimes_indexed\n"]}],["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token keyword","children":"GROUP"}]," ",["$","span",null,{"className":"token keyword","children":"BY"}]," tsWeek\n"]}],["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token keyword","children":"limit"}]," ",["$","span",null,{"className":"token number","children":"10"}],"\n"]}]]}]}],["$","p",null,{"children":"And our query:"}],["$","$L12",null,{"className":"language-sql","children":["$","code",null,{"className":"code-highlight language-sql","children":[["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token keyword","children":"select"}]," datetrunc",["$","span",null,{"className":"token punctuation","children":"("}],["$","span",null,{"className":"token string","children":"'WEEK'"}],["$","span",null,{"className":"token punctuation","children":","}]," DateEpoch",["$","span",null,{"className":"token punctuation","children":")"}]," ",["$","span",null,{"className":"token keyword","children":"as"}]," tsWeek",["$","span",null,{"className":"token punctuation","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":")"}],"\n"]}],["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token keyword","children":"from"}]," crimes\n"]}],["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token keyword","children":"WHERE"}]," tsWeek ",["$","span",null,{"className":"token operator","children":">"}]," fromDateTime",["$","span",null,{"className":"token punctuation","children":"("}],["$","span",null,{"className":"token string","children":"'2017-01-16'"}],["$","span",null,{"className":"token punctuation","children":","}]," ",["$","span",null,{"className":"token string","children":"'yyyy-MM-dd'"}],["$","span",null,{"className":"token punctuation","children":")"}],"\n"]}],["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token keyword","children":"group"}]," ",["$","span",null,{"className":"token keyword","children":"by"}]," tsWeek\n"]}],["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token keyword","children":"order"}]," ",["$","span",null,{"className":"token keyword","children":"by"}]," ",["$","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 keyword","children":"DESC"}],"\n"]}],["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token keyword","children":"limit"}]," ",["$","span",null,{"className":"token number","children":"10"}],"\n"]}]]}]}],["$","p",null,{"children":"Would be rewritten as:"}],["$","$L12",null,{"className":"language-sql","children":["$","code",null,{"className":"code-highlight language-sql","children":[["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token keyword","children":"select"}]," $DateEpoch$WEEK ",["$","span",null,{"className":"token keyword","children":"as"}]," tsWeek",["$","span",null,{"className":"token punctuation","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":")"}],"\n"]}],["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token keyword","children":"from"}]," crimes\n"]}],["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token keyword","children":"WHERE"}]," tsWeek ",["$","span",null,{"className":"token operator","children":">"}]," fromDateTime",["$","span",null,{"className":"token punctuation","children":"("}],["$","span",null,{"className":"token string","children":"'2017-01-16'"}],["$","span",null,{"className":"token punctuation","children":","}]," ",["$","span",null,{"className":"token string","children":"'yyyy-MM-dd'"}],["$","span",null,{"className":"token punctuation","children":")"}],"\n"]}],["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token keyword","children":"group"}]," ",["$","span",null,{"className":"token keyword","children":"by"}]," tsWeek\n"]}],["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token keyword","children":"order"}]," ",["$","span",null,{"className":"token keyword","children":"by"}]," ",["$","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 keyword","children":"DESC"}],"\n"]}],["$","span",null,{"className":"code-line","children":[["$","span",null,{"className":"token keyword","children":"limit"}]," ",["$","span",null,{"className":"token number","children":"10"}],"\n"]}]]}]}],["$","h2",null,{"id":"re-running-the-query","children":[["$","a",null,{"href":"#re-running-the-query","aria-hidden":"true","tabIndex":"-1","children":["$","span",null,{"className":"icon icon-link"}]}],"Re-running the query"]}],["$","p",null,{"children":["Let’s now run our initial query against the ",["$","em",null,{"children":"crimes_indexed"}]," table. We’ll get exactly the same results as before, but let’s take a look at the query stats:"]}],["$","p",null,{"children":["$","img",null,{"alt":"Chicago crime dataset updated query stats","src":"https://www.datocms-assets.com/75153/1669133083-image4.png","title":"Chicago crime dataset updated query stats"}]}],["$","p",null,{"children":"This time the query takes 36 milliseconds rather than 140 milliseconds. That’s an almost 5x improvement, thanks to the timestamp index."}],["$","h2",null,{"id":"summary","children":[["$","a",null,{"href":"#summary","aria-hidden":"true","tabIndex":"-1","children":["$","span",null,{"className":"icon icon-link"}]}],"Summary"]}],["$","p",null,{"children":"Hopefully, you’ll agree that timestamp indexes are pretty cool, and achieving a 5x query improvement without much work is always welcome!"}],["$","p",null,{"children":["If you’re using timestamps in your Pinot tables, be sure to try out this index and let us know how it goes on the ",["$","a",null,{"target":"_blank","rel":"noopener noreferrer","href":"https://stree.ai/slack","children":"StarTree Community Slack"}]," . We’re always happy to help out with any questions or problems you encounter."]}]]}]}]}]]}],["$","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"}]}],["$","$L13",null,{"chapters":[{"value":"Spinning up Pinot","url":"#spinning-up-pinot","depth":2},{"value":"Importing Chicago Crime Dataset","url":"#importing-chicago-crime-dataset","depth":2},{"value":"Querying crimes by date","url":"#querying-crimes-by-date","depth":2},{"value":"Adding the timestamp index","url":"#adding-the-timestamp-index","depth":2},{"value":"What does a timestamp index do?","url":"#what-does-a-timestamp-index-do","depth":2},{"value":"Re-running the query","url":"#re-running-the-query","depth":2},{"value":"Summary","url":"#summary","depth":2}]}]]}]}]]}]}]]}]]
d:null