Here’s<\/a> the paper I tell people to read on the subject. It’s written for the person implementing a database, but anyone who can read query plans should get the basics from the paper. It is the basis for most of the hard-core optimizations (and tricky problems) that face all query processors today. <\/p>\nThe basic idea is caused by understanding that an aggregate function can be split into multiple operations and done in parts. Some parts can be done earlier in a query, saving a lot of work. If these results can be combined later, you might be able to speed up a query by computing these partial results early and then combining them at the end of the query. The usual savings is that you don’t have to materialize the results of a join when you only care about the aggregate over some piece of it.<\/p>\n
Most of the core aggregate functions defined in SQL can be decomposed. If you have a set of rows {x} := concat({y}, {z}), then
SUM({x}) == SUM({y}) + SUM({z}).
COUNT == COUNT + COUNT
MAX == MAX (MAX(), MAX())
…<\/p>\n
Not all aggregate operations can be decomposed in this manner, but many of them can. <\/p>\n
If you take this concept and then apply it a query with some joins:<\/p>\n
select sum(col1) from a join b join c<\/p>\n
Then the idea of local-global aggregation is that you can do part of the sum before joins and pass up the SUM for each group instead of all the rows from that group. <\/p>\n
This idea becomes more powerful when you start throwing more complex operations into a query processor, such as partitioning or parallelism. Often, you want to perform partial aggregations on these groups to minimize the amount of data you have to send between threads or perhaps between nodes on a NUMA machine. All of this is the fun stuff that makes databases interesting – you can <\/p>\n
Not every aggregate can be pushed below every join – there are rules about what can and can’t be done and maintain the same results from a query. For example, you may need to consider whether the aggregate function can handle seeing additional NULL values and return the same result or not.<\/p>\n
If you go look at the SQL CLR user-defined aggregate definition, you’ll see the exposed pieces of some of this capability in the SQL Server query optimizer. I won’t spoil all of your fun, but go take a look. <\/p>\n
Happy Querying!<\/p>\n
Conor<\/p>\n","protected":false},"excerpt":{"rendered":"
I don’t know about you, but groupby is one of my favorite operators. There are a TON of interesting optimizations that a QP considers when you start adding group by into queries, especially when you have joins and then a group by. TPC-H benchmark wars among the large database vendors are won and lost on […]<\/p>\n","protected":false},"author":6,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-401","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"yoast_head":"\n
Local-Global Aggregation - Conor Cunningham<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/conor\/local-global-aggregation\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Local-Global Aggregation - Conor Cunningham\" \/>\n<meta property=\"og:description\" content=\"I don’t know about you, but groupby is one of my favorite operators. There are a TON of interesting optimizations that a QP considers when you start adding group by into queries, especially when you have joins and then a group by. TPC-H benchmark wars among the large database vendors are won and lost on […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/conor\/local-global-aggregation\/\" \/>\n<meta property=\"og:site_name\" content=\"Conor Cunningham\" \/>\n<meta property=\"article:published_time\" content=\"2008-03-26T21:01:48+00:00\" \/>\n<meta name=\"author\" content=\"Conor Cunningham\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Conor Cunningham\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/local-global-aggregation\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/local-global-aggregation\/\",\"name\":\"Local-Global Aggregation - Conor Cunningham\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/#website\"},\"datePublished\":\"2008-03-26T21:01:48+00:00\",\"dateModified\":\"2008-03-26T21:01:48+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/#\/schema\/person\/f9106e03423de6b5157295891b8c3ae3\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/local-global-aggregation\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/conor\/local-global-aggregation\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/local-global-aggregation\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Local-Global Aggregation\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/\",\"name\":\"Conor Cunningham\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/#\/schema\/person\/f9106e03423de6b5157295891b8c3ae3\",\"name\":\"Conor Cunningham\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/d9c37eff231ec89c1b244347d966860875eea8b55b366911d2694e8cd9913e57?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/d9c37eff231ec89c1b244347d966860875eea8b55b366911d2694e8cd9913e57?s=96&d=mm&r=g\",\"caption\":\"Conor Cunningham\"},\"url\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/author\/conor\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Local-Global Aggregation - Conor Cunningham","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/conor\/local-global-aggregation\/","og_locale":"en_US","og_type":"article","og_title":"Local-Global Aggregation - Conor Cunningham","og_description":"I don’t know about you, but groupby is one of my favorite operators. There are a TON of interesting optimizations that a QP considers when you start adding group by into queries, especially when you have joins and then a group by. TPC-H benchmark wars among the large database vendors are won and lost on […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/conor\/local-global-aggregation\/","og_site_name":"Conor Cunningham","article_published_time":"2008-03-26T21:01:48+00:00","author":"Conor Cunningham","twitter_misc":{"Written by":"Conor Cunningham","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/conor\/local-global-aggregation\/","url":"https:\/\/www.sqlskills.com\/blogs\/conor\/local-global-aggregation\/","name":"Local-Global Aggregation - Conor Cunningham","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/conor\/#website"},"datePublished":"2008-03-26T21:01:48+00:00","dateModified":"2008-03-26T21:01:48+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/conor\/#\/schema\/person\/f9106e03423de6b5157295891b8c3ae3"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/conor\/local-global-aggregation\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/conor\/local-global-aggregation\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/conor\/local-global-aggregation\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/conor\/"},{"@type":"ListItem","position":2,"name":"Local-Global Aggregation"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/conor\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/conor\/","name":"Conor Cunningham","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/conor\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/conor\/#\/schema\/person\/f9106e03423de6b5157295891b8c3ae3","name":"Conor Cunningham","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/conor\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/d9c37eff231ec89c1b244347d966860875eea8b55b366911d2694e8cd9913e57?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/d9c37eff231ec89c1b244347d966860875eea8b55b366911d2694e8cd9913e57?s=96&d=mm&r=g","caption":"Conor Cunningham"},"url":"https:\/\/www.sqlskills.com\/blogs\/conor\/author\/conor\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/conor\/wp-json\/wp\/v2\/posts\/401","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/conor\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/conor\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/conor\/wp-json\/wp\/v2\/users\/6"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/conor\/wp-json\/wp\/v2\/comments?post=401"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/conor\/wp-json\/wp\/v2\/posts\/401\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/conor\/wp-json\/wp\/v2\/media?parent=401"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/conor\/wp-json\/wp\/v2\/categories?post=401"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/conor\/wp-json\/wp\/v2\/tags?post=401"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}