 Carnegie Mellon University's Advanced Database Systems course is filmed in front of a live studio audience. All right, so today's class, I'm going to try to get as much as I can into today's lecture and might spill over in a little bit into next class. But this is it. We've been talking about this for a long time. We're finally going to get into discussing how we actually want to build the query optimizer, which is the arguably the most important part of a database management system. Every year I debate whether to even bother teaching 7.21 and just teach a topics course on query optimization. Admittedly, this is the part of databases that I know the least about. For people that are actually working on the query optimizer here, the new project, at this point you guys probably know more than I do. But this is the hardest piece because obviously we can build the fastest engine, but if our query plans are terrible, then it's all wasted. You okay? Okay. All right. All right, so this refresher. Last class we were talking about networking protocols and again the main takeaway from this discussion was it looks and smells a lot like when we talked about storage on disk. If we have a, if our application is running queries that really care about a small number of tuples, then a row-oriented API or network protocol that's going over a row-oriented or sort of row-oriented API or row-oriented network protocol that's being accessed through a row-oriented API, the ODBC or GDBC, that's going to be sufficient. But when we start doing queries that want to get a lot of data out of the data system or do bulk export, then we want to use something that looks, that's columnar, take advantage of all the other things we talked about before. And as I said, the Arrow Dataless Connectivity Library is one to facilitate that. I think that's the future of OLAP systems are all going to support this, if they don't already. And so as I said already, the next two weeks is really going to talk about query optimization. So today's class and next class will be talking about how actually you want to implement the optimizer itself at a high level, like how you're going to define the rules, how you then do the search to figure out what transformations you want to apply to optimize things. We'll talk a little bit about query rewriting and obviously plain integration is part of this. Cost models we'll discuss more next week. Next class will be, sorry, next week we'll then talk about, sorry, next class will be further into the dynamic programming approach that's used by Hyper and Umbra from the Germans. And then on Monday next week we'll talk about doing adaptive query optimization, like if the query runs for a bit and you make decisions on the fly, whether it changes things. And then that will feed into the cost model discussion next week. So the next two weeks is getting purely on query optimization, because again it's super important. So what do we care about in this scope? So this is obviously a refresher from the intro class, but the goal of the DB Systems query optimizer, sometimes called the query planner, sometimes called the query compiler, if you're an older system or an old person, right, because that's a remnant of the early 1970s when the rational model came along by the idea of taking a high level language like SQL and converting it into a low level, not assembly, but execution instructions to run the query, like they saw that being akin to writing a high level language like C, because C was considered super high level back in the day, and converting that into a low level assembly or machine code. So the idea of the query optimizer is that we want to generate a, for a given query, we want to generate a correct physical plan that will execute that query, ideally with the lowest cost. And I'm underlying the word correct here because obviously it doesn't matter if we have this super fast query plan, if it does not actually produce the result that we want, then it's useless. For that one, we can ignore approximate query processing stuff, right? We really want an exact match, we're given SQL query, we want to produce the exact output. And the cost is in quotes because as we'll see next week, this is going to be a relative term that's going to change depending on the system itself that's actually implementing this, right? The cost is going to be some internal metric that we can use to compare one plan to another and decide this one's better than another based on the number of tuples read or CPU instructions used or network traffic, right? And again, that's going to change from one system to the next. And typically this cost is not usually mapped to something in the real world, like runtime. Some of the enterprise systems, I know DB2 can do this, where they're actually spit out, this query is expected to run this long. But here I look like Postgres' output of explain-analyze or SQLite or other systems. It's always going to be some number that's meaningless outside of that system. So, as I said before, this is going to be the hardest part of the system. Just picking the joiner has proven to be, I think, NP-hard, but the whole problem of figuring out what's the optimal query plan is NP-complete. And so that means that despite the name Optimizer, we're almost never going to really find the true optimal plan, right? If it's something really stupid, like select one, semicolon, right? Actually, that usually doesn't even get past the Optimizer. Some systems will recognize that and immediately just send you back to the result. It doesn't even execute it, like they're a little short circuit it. But for things like looking up on a single index on one table, like, yeah, that'll be optimal because we know how to find exactly what we want. But once we start adding joins, that's when things get really tricky. And so because we can't do an exhaustive search to find optimal plan, we're going to have to use a bunch of methods to trim down the number of choices we have to consider and try to guide the Optimizer towards a good plan, right? Because we can't prove that it's going to be optimal. And then because it's too expensive for all these possible choices we could have to actually run them to see whether what their cost actually is, this is where the cost model is going to help us speed things up. But it's going to be an estimation of what we think the system is actually going to do when it encounters real data that you're targeting. So this is going to be super, super hard and I'll try to sort of break it down and walk through the different approaches that were discussed in the paper you guys were assigned. And the old joke for query optimization is like, if say you want to be a rocket scientist, sorry, other way around. If you want to be a work on query optimizers and you can't hack it and it doesn't work out, then the backup plan could be rocket science. Cuz query optimization is considered harder than rocket science. Of course, databases don't blow things up in the air, so maybe that's debatable. Okay, so the important thing to understand is the distinction between the logical plan and the physical plan and the terms are going to be sometimes conflated in the earlier optimizer implications we're going to see because they might not even have logical plans. They'll go immediately from like a sequel query into physical plans. But the Cascades approach and the stratified search from Starbucks that we'll see in a second, they'll have a clean distinction between the logical and the physical plan. And so the idea of the logical plan is the high level operators we want to execute for a query based on something that looks like relational algebra. Like I want to scan this table, I want to join these two tables. But it's not specifying what actual algorithm you want to use to execute those different operators. So the optimizer is going to take the parse tree from the sequel query that shows up, do some transformation to convert that into logical plans that represent what that sequel query was trying to do. And then now it can do further optimizations or further transformations of those logical plans into new logical plans. Or it can convert the logical plans into physical operators, a physical plan that'll define how we actually want to execute things. And this physical plan often times will depend on what the data actually looks like on disk. Do we expect things to be sorted? Is it going to be compressed a certain way? And it will specify also again what algorithm you want to use to produce that result. So we're not always going to have a one-to-one mapping from a logical operator to a physical operator. Like you could have a logical join, a logical order buy could be combined together into a physical sort merge join. But once you're in a physical form, you typically don't, like a physical operator form, you typically don't convert that back to logical. That doesn't make sense. And that sort of explodes in search space if you actually want to consider stuff like that. So it's mostly logical to logical or logical to physical. And this I've already said, but again, the cost estimation is how we're going to use internally to figure out whether one query plan is going to be better than another during our search process. Again, we'll talk about this next week. But basically, it's going to be a combination of these different metrics, like how much data I think I'm going to read from disk or read from my child operator, how much data I'm going to spit out based on the selectivity or the cardinality of any operations I'm doing on that data as it comes in. Is the data skewed? Is it compressed? Where it's actually physically located? All of these things we have to consider to have a true cost estimate. But it's never going to be exactly perfect. And the paper you guys read next week from the Germans will show you that once you do two joins, then all these estimates get way out of whack. And the query optimizer, the cost model estimate is going to be woefully underestimating the amount of tubos that are coming out. And so then again, it'll lead it to choose an incorrect plan. So today, we're going to try to get through all five different approaches you could have. We'll see how far we can get. We don't get to a randomized search as fine because nobody actually does this except for Postgres. And Postgres only does it if you have 13 tables in your query. But we'll cover that next class if you're out of time. The major ones that we're going to focus on are the middle three. So again, we're going to walk through in the order of complexity from simplest to hardest, although stratified and unified are essentially equivalent. And we're going to walk through, here's how people implement these query optimizers. What are the pros and cons of them? What can't they handle? And then how the next approach, as we go down, tries to solve the problems for the previous one. And we'll sprinkle in a discussion a little bit about how real-world systems do this. And the TLDR is going to be stratified search and unified search are going to be the most common approaches. So say you're a brand-new startup and you're branding a brand-new database system of Scratch. You're not like 14 Postgres. You're just literally starting from nothing. The heuristic-based optimizer is probably most people almost everyone builds first, because it's super simple. It's a bunch of if-and-else clauses that look for patterns in the SQL query and then apply some transformation to convert them into a better form. And the reason why this works is that it's based on domain knowledge about what we know as humans about query optimization and queries in general. And we're basically codifying that in our code to always apply those changes without worrying about whether they're the right thing to do or not. Yes? As far as I have to double-check, I don't think they have a call-space optimizer. The way MongoDB works is that they generate all the query plans, run them all, when everyone comes back first, that's what they pick, because that's what it was a year or two ago. So you don't run like, you generate all the query plans. You pick one, you send that out first, see how long it takes. Next time the query shows up, pick the next one and then you just pick which one. You laugh, but it's pretty simple. It works. And then after like 20 iterations, they'll try again. Yes? It's not going to be similar. The statement is the MongoDB approach works if you assume the query is going to be very similar to each other, maybe just different input parameters. Yes, you could do that. And in the OLTP world or operational workloads that Mongo initially targeted, this works. Because again, it's like, go look up Andy's record. Go look up Kyle's record. It's the same query, just different input parameters. I'm going to get too bogged down on Mongo. And again, I'm not knocking them. I'm just saying it's cleverly simplistic. There's other things to bang on them about like M-Map. For this one, when I was like, oh, yeah. I can see why you would do this. Because again, what did I just say at the beginning? This is the hardest part of database systems. You're trying to get a system up and running right away. They're getting huge growth. They got that far without a query optimizer. So again, it's going to be if and else statements. That like look for patterns in the query plan and then applies some transformation to put it into a different form. So the most obvious thing you could do is always do the most restrictive selection first. You're just trying to filter things out as soon as possible. Because we know as humans, if you know why spend time copying data from one upper to the next, if I know I'm not going to need it. So let me go ahead and try to throw things out as soon as possible, right? So that's predicate pushdown, limits, projections, and so forth. For join ordering, if you're lucky, they'll have rules that'll look at actually cardinality estimates and maybe say, oh, this one's bigger than the other one, swap them. We'll see in the case of the oracles, their first implementation didn't do that. So the two out of the three first relational database systems built in the 19, the major relational database systems built in the United States in the 1970s all did this. So Ingress did this up until the mid-80s. Oracle did this up until the mid-90s. But in the case of the other major one, System R out of IBM Research, like they're actually going to do a call space search, which we'll see next. Again, most new database systems, unless you can reuse something like CalSight or Orca or an existing query optimizer like in Postgres, this is pretty much everyone does. And long-term it's not sustainable, all right? The reason I qualify is the two out of three first relational databases in the U.S. Because there was another one in Sweden called Meimer SQL and there was a German one called AdaBoss, but I don't know what those guys actually did. So there are other relational databases at the time, yes. Sternberger built Ingress, yes. Why is it called Postgres? Because it's Post-Ingress, yeah. All right, he built Ingress, it stands for something interactive, graphical, it was something, they got a grant to build it for a GIS application, and then they started building it out to be more general purpose, right? Yes. So about the last one. You see, but on cardinality estimates considered part of the cost model. But the difference is gonna be like, there isn't a search process where I'm enumerating over different plans to see is this one better than this. It's literally like, if I have a join and the cardinality estimate is this one is greater than this one, slip them as literally all it is. All right, again, back in the day, data is sort of small. It was, you know, you didn't have CTEs, you didn't have window functions, all these other stuff we've been talking about. So this would get you pretty far. And for OTP, again, this will get you pretty far. So let's look at some basic optimizations that you can do with this. So these, again, and this is a refresher for the intro class, but I'm gonna show you these because this will be what the, some of the heuristic rules will look like when we look at the stratified search and the other stuff, because again, this is, you can do this without a cost model and for the, like a heuristic, pure heuristic, heuristic based approach, that's to generate a plan you can then run, but the idea when we do have a cost based search, we wanna use these transformations to guide us and push us towards what will at least be a starting point in our search for a reasonable plan. So with that, we're not like blindly starting from the most stupidest query plan. And then spending all our search time, just getting us into a basic form that we've could have gotten instantaneously using one of these rules. All right, so say we have a query like this, it's a three way join on, on appears, artist appears an album, and we're gonna look up all the people, all the artists that are on my, on my mixtape, right? So the first thing you do is just split, split conjunctive predicates. So splitting any, any filter on the AND clauses to break them up into separate filter operators, right? So you, again, you identify that I have an, I have a filter operator within that, if the filter operator's expression, I have an ANDs, I spin on the ANDs and I create additional filter operators for that, right? Again, I don't need to cost money to do that, I know I always want to do that. So now, if I have these different filter operators, I can easily do now predicate push down to push the filter D below any join operator. Because again, why do a join when on table, on data, I know I'm gonna throw, throw away up above, right? So I just push all them down, actually to be right above the join, in this case here. But I guess they're below this join up here. So these are the filters that are combining two, two tables, after I do the Cartesian product, then I do the join. Or sorry, then I apply the filter. Then the next step, obviously, we want to get rid of the Cartesian products. So we just convert the, we recognize that we have an equality predicate right above us. So we can convert any Cartesian product into an inner join, or an equa join. That's always gonna be faster. And then depending on the system, I may also want to do projection push down. Can, whether or not I have a cost model that just decides whether the size of the table that I'm, or the data that I'm pushing up between one upper to the next is really big or not. But I guess to recognize that rather than copying all the data that I know I'm not gonna need, I'll just push down the projections to be below an any join operator. So I'm only passing along the minimum amount of data from one upper to the next. So all of these were these four or five steps I showed right here. Again, these are logical transformations that I can do without having to have a cost model. And I didn't specify what join algorithm I'm using or how I'm accessing these tables. I can just operate directly on the logical plan to do this. And again, we know this is always going to be faster than what I started out with, which is like, in the canonical form of converting SQL into relational algebra. Yes. I think there are actually some edge cases. As David is, there are some cases where if the predicate, the evaluation of the predicate is very expensive from a computational standpoint, then pushing down the predicate is a bad idea. Yes. But you're not there yet. You don't know that at this point here. Right, because how do you weigh the number of tuples going in to the filter? All right, that's a projection. Like, how do I know the number of tuples coming in to the join is gonna be, say it's this one here. Say this comparison is super expensive. How do I know that the number of tuples going in would outweigh the cost of applying this for everyone versus above after the join? You don't know. You need a cost model. If you need a cost model, you need statistics. In the very beginning, they didn't have any of that. All right, so let me show what Ingress did back in the day. And again, this is like kind of a MongoDB one where I was saying like, it's delightfully stupid meaning like, you would never want to do this today, but given the constraints that they had at the time and the hardware they were working with, without having a cost model and a career optimizer, is actually kind of clever. So the dirty secret about Ingress, it's the first version of it is, it actually couldn't do joins. Yes, first version of Ingress in like 1974-ish, three-ish, could, five. Couldn't do joins. They couldn't even do that. Okay, well, yeah, all the TV stuff back in the day. Let's see how they, like, they want to support the query like this. The example they always have in the old papers is like employer employee salary, like, or department. Like the- Yeah, they're all pulling it up. Let's see how they do this. All right, so say we had the same query before, so three-way join artist appears at Alam, but now I want to throw in this order by clause for the artist ID. So the first thing they're going to do is they're going to rewrite the SQL query into single value queries, meaning the same case of the first one here, I take the first query, I extract out or move out the lookup on an artist and appears, and I have a singular query looking up on the album based on the album name. But then I'm going to materialize the output into temp one, some table here. Now I can further decompose query two into two new queries, where the first one does a lookup on peers and in a join with temp one, and the second one's going to do a lookup on artists and a join with temp two. And again, this one is materializing the output into temp two. So now what I'm going to do is I'm going to run the queries one by one, starting from the top to the bottom, and whatever the result is, I then inject that into the next query I'm going to execute. So if I run this first query query one, lookup the album based on the name, it's going to produce album ID 999. So then I take that, map that into the rewritten query on the appears table, and substitute what would have been the join clause on the artist table or the album table, and now inject 9999. And then this thing produces a result, two results here, and then I'm just going to do a forlip on that and run each of those queries one by one. Yes. Is there a reason for query two? Going back here. Query two got rewritten into, so query two had a three-way joiner to have the two artists and peers, and that gets rewritten into three and four. Another question, yes. What's the definition of a single? What is the definition of a single value query? Two, like one tuple for one table. Oh. Yeah. Right, so like, well, in this case here, you would run it. Well, it could be for one table. So you would get, this query would run once, you get two results. They could support multiple tuples in the output. And then now you take these two values, then just expand that out into different instances of the same query. What do you mean, what's the attitude? Yeah, I mean, one table queries, single table queries. So I'm showing this at the SQL level, but they did this all at the logical level, just logical query plans. And then they would just sort of run this. And so why I say this is like stupidly clever is, you're actually running the query optimizer every single time you generate one of these queries. So for whatever reason, if like for this artist ID equals one, two, three, if there was a better way to execute that than doing look button four, five, six, you could actually support that because you would take this query plan, run it through the planner and might choose a different extrusion path than this other one here. And because it's choosing on a per single value within the lookup, what's the best thing to do? So it's sort of an early example of adaptive query optimization, which we'll see next week, but like, because you're running the optimizer on a per query basis, but obviously, this would be super slow. You wouldn't actually want to do this, right? So again, it's a nice, to me, it's a nice historical curiosity. So there is a big optimization. It's the easiest implement and debug because again, it's just a bunch of if and else's. This is pretty much everyone does. And for symbol queries, you're not going to get any faster than this because you don't mean any tainting state. You're not doing any lookup in the queries against the cost model, do estimations. It's like boom, boom, boom. And then here's the query and run it. Obviously the downside is going to be this is going to be a nightmare to expand when you want to start doing more complicated things. It's going to lie in magic constants to say like, how much better really is something than another? If you start weighing in like the number of tools this operator is going to spit out if you're flipping one versus another. And then any time you have like a nested query, anything that becomes a train wreck, right? And most obviously, actually not in just nest queries. And then you have joins to figure out what's the right join ordering would be a total nightmare. Like going back to this one here, the reason why we started with query one on the outmid, because that's the only input we have to the query. So we know what the starting point is. But if it was just a join across the tables without any input, then you have to pick what is the starting point, which is the first table you want to put in part of the join and then it all falls apart. So as I said, this is roughly what Ingress and Oracle did back in the day. System R we'll see in the next two slides. They're going to have a call space search for at least for the joins. If you read the unofficial biography of Larry Ellison, there's this nice little paragraph here somewhere where Sternberger talks about the Oracle's query optimizer. Again, this is about the mid-19, well, this book came out in the 1990s, but he's talking about the sort of the race between Oracle and Ingress in the 1980s. And he talks about how Ingress actually built a query optimizer that's going to look like IBM's, but Oracle was kept going on how that was actually the wrong way to do it and the right way to do is what Oracle did, right? And that instead of calling it like a heuristic-based optimizer, they called it a semantic optimizer because they couldn't do call space search so to figure out the join order, so the join order was whatever the order that the tables appeared in the actual SQL string. And Larry Ellison called that a semantic optimizer. So good marketing. And then like I said, Oracle's going to be right there in the 1990s and make it more state of the art. All right, so at the same time, the Ingress and Oracle guys were building their heuristic-based optimizers. IBM Researcher, IBM was building System R and they ended up building the first cost-based query optimizer. And the idea is that it's going to use a heuristic stage just like we saw before where you can do the logical-logical optimizations, but then they're going to do a plan enumeration and transform physical operators. Actually, that's backwards. It should be logical and a physical and not physical logical. They would transfer logical to physical operators and try to find one with the lowest cost, right? And obviously to do this, you need a cost model, you need estimates, but each operator is going to spit out. But you can't guarantee that you're going to find the optimal plan, but there's things they do would try to at least get to a good plan, like only choosing left deep trees instead of bushy trees. There's another cut down the search base. So this is what System R did in the very beginning. This is what IBM DB2, at least in the early 1980s, because that was the first commercialization of a relational database at IBM. They did something based on this. And then most of the open source database systems are out today. Postgres, MySQL, SQLite, they're going to be doing something that looks and not smells like this. So for this one, actually just kidding, but basically converting the query plans, logical operators, physical operators, like to do this correctly, we need to understand like what the table is going to look like, what the inputs are going to be to this given operator, where the data is going to be located. All this is related to the cost model stuff that we talked about before. And we'll talk about more next week. So to enumerate the plans, there's basically there's two categories. And this is going to be similar to the debate between do I want to partition my hash join or not, like the people go back and forth on this. But for query optimization, the question is going to be, do I want to enumerate plans and build things up the physical plan from the bottom of the query plan to the top or from the top to the bottom? Or another way to think about this is am I going to use a generative approach or a transformative approach? So a generative approach would be, I start with nothing. Like I have no physical operators in my query plan. And then I'm going to iteratively assemble or inject new physical operators to work myself up to the final output, the root of the query plan that's going to produce the final result. And I can do cost-based selection as I go from one level to the next. So this is what system R is going to do. This is what Starburst, which is the query optimizer that IBM built later on in the 1980s, early 1990s. This is what they're going to do as well, right? The alternative approach is to do the top-down and transformation where I start with the goal that I want, which is the root of the query plan. Like I want my final output of my query plan to look like this, right? I'm joining these tables, it's sort of this way or whatever it is that that's what I want. And then I'm going to permute it, the query plan going down and adding new operators that will then feed into my root to then produce the final result that I want. And it sort of seems like I'm just making hand gestures and going up and down like that. It doesn't make any sense. But it does have implications for the scope in which you can examine and operate on the query plan when you apply different time to transformations and how you're actually going to cost them. In the end, they're both kind of doing something that is, they're doing a dynamic programming approach. It's just the way and they're doing the costing and pruning things will differ. All right, so let's look at the system R optimizer. Let's see an early version of a bottom-up approach. And then we'll see how that gets expanded into a little bit at Starburst. And as I said, next class we'll go to more details of the state-of-the-art approach of bottom-up plan enumeration that the Germans do in Hyper. And I think DuckDB does the same thing as well. So the way system R is going to work is that queries are going to show up and they're going to break it up, the query plan into blocks that have logical operators for each block. Sort of thinking of like a block could be like a pipeline breaker or it could be a nested subquery. It's going to be some subcomponent or subplan of the overall query plan. And then for the logical operator within a subplan or block, they're going to generate the set of physical operators that could possibly implement it. And they're primarily going to be focusing on joint access paths. So figuring out how can I scan this table, your index or sequential scan, and then what sort of, sorry, what join algorithm I'm going to want to use. And again, to reduce the complexity of the search base, they're only going to look at left deep trees. So they're not going to set a right deep or bushy trees. Again, this is a relic of the 1970s and a limited computing harbor, but a lot of systems still today make this big assumption, right? As far as you know, left deep and right deep are always going to be equivalent, but sometimes you do actually want a bushy plan and they're not going to be able to find those. And so they're going to construct this left deep join tree and they want to choose the one that's going to have the minimal cost at the end. Again, based on some cost model estimate. So we go back to our query we may have before. So in the first step, we're going to choose the best access paths for all the tables that we're going to, we're going to, we're touching in the query. So independently, we're going to decide, oh, we want to do a sequential scan on artists and peers, but then we identify that the best lookup for the album table will be on the index we have on name. So all of those are occurring independently of how we're actually going to do the join. Then we're going to enumerate all possible join orderings for the tables. And this literally is just a Cartesian product with all kinds of combinations, or combination of all the possible ways we could do a join. Different join algorithms, different join orders, whether or not we even do a Cartesian product. The simple thing System R is going to do is sort of what I showed in the beginning is recognize that I've known I was never going to do a Cartesian product. So I can immediately throw all those away. Then now I'm going to do this bottom-up based search for all these different combinations that I have to figure out what the join order is that I want to be. So again, in this diagram here, think of the top as the final output. All right, and the bottom here is my starting point. So my final output as well, I want to have joined artists, appears an album. But at the bottom here, I haven't joined anything. I assume also too, I've selected, for each of these individual tables, as I showed in the previous slide, I've already selected what access method I'm going to use. So sequential scans for artist and appears, and then the index look up on one album. So again, if we're going to do a bottom approach, so starting here at the bottom, they're going to say, here's all the possible join combinations I could have for these three tables. And because it's PowerPoint and for simplicity, assume it goes all the way on the other side with all possible combinations. I'm truncating here in the sake of time, right? And then now these physical operators then produce an output that's going to either have, that's going to choose one of the two tables that will be joined together, and then the third table is just waiting to be joined after this, right? So I can do hash joins, merge joins, and so forth. And then now, for all these possible paths up to the next level, I'm going to choose for each of these next nodes, you know, at the next level above, what's the path that has the lowest cost? All right, so for each of these, I'm going to choose one of them as the best one based on my cost model estimates. And then now, again, proceed at the next level to do the exact same thing. For each of these possible choices here, choose the different physical join operators that then get me to my final result that I want. And then once I've done that, do the same thing, choose along which one of these has the shortest, the least cost. And then now, since I've reached the top of my query plan, I know this is the final result that I want, now I just request back and figure out which of these is the cheapest path. And that's what I'm choosing as the optimal query plan for us. Yes? Just to clear it up, this example is going to just join order. So if this bottom-up construct is like the grade, is this the only work that happened for join ordering, or is it going to do all of this for like a different, well, I don't know if there's many choices for like a built-in order, but are they going to try like built for here, built for here? His question is like, when do other optimizations can apply like predicate push down here? For this approach in system R, they're only going to pick this in join ordering. In the case of, in hyper-roscene next week, they're going to do DP just for join ordering. The way you would handle the additional things that you're talking about is that you would define those additional transformation rules in like a stratified search and you would apply them potentially with the cost model as well, and then you do this DP search. Cast case will integrate everything all at once. So this approach where you, am I correct in saying this is only practical, and that if you weren't doing something like this push down from other more advanced optimizations you would have been in a different way. His question is, would you only want to do this bottom approach? Could you, would you only do this for join ordering? Yes. Because again, like you're trying to like march through if I have to do, recognize what I want to do predicate push down, you have to go back, insert it in somewhere and then go back up. Be kind of funky. Question? Is there special handling? Special handling for aggregations. You'd have to, you would treat the aggregation as a block. And you could subdivide that further. Think of this as a query block. And so, yeah, in this case the aggregation would be the, if it's known as a query, the aggregation is the final output. So it would be a gray block above this. Yes. Yes. And then the same thing you could choose what aggregation algorithm I want to use, which most of us, it's going to be hash join or the hash aggregate. Yes. You said you first select how you access the table, where it is possible that for a different join ordering, different access methods would be better? The state of it is, I said in the beginning, in this approach here, you first pick the access method you're going to use, then you pick join ordering. Is it possible that there would be a different access method would be better for a different join order? Yes. Like if I'm doing, if I recognize that I have an index and I should be doing nested loop join instead of a hash join, right? But if I'm picking to always do an index join before I check my join order, yes, I would have a disconnect between the access method and the join order. For this approach, yes, that's a problem. The stratified search and unified search will fix that because we'll get everything all at once. So what's one problem with this query here? It's actually not correct, right? Because my original query said I wanted to join these three tables, but I also wanted to do an order by on the final output, right? And so in the original implementation of system R, this dynamic programming search piece had no notion of physical properties of data. So I'm choosing to do a hash join, if I go back here, one of my choices could have been a certain merge join. So maybe in the case where I would have been better off doing the sort merge join because then my data would have already been sorted. So the way IBM handles this or system R handled this is that they would keep track of the best plan they've seen with and without a physical property like the sort order. And then, so they would have that, they would have two plans at the end and then they would say, okay, well, my data needs to be sorted. So if I have an estimate what the sort cost is gonna be, if I add that on to my unsorted data query plan, is that gonna be less than the cost then of doing the sorting directly within the sort merge join? And if still less, then you pick that one. So it's sort of like an afterthought, they had to add this additional step to deal with physical properties of the data because they had no way to handle that natively in the search. Yes? So what alternatives are, you do the sort merge as part of the join, I'm sorry, you do the sorting as part of the join. Yes. Or you add an order by like a sort node above. Yes, correct, yes. So statement is, what I'm talking about in this case here, because the query wants the data to be sorted by artist ID, if I did a sort merge join on artist ID here, then this, the output of this would have been sort of in the way that the query wants. So you keep track of this query plan and then, because it says it's the best one of all the ones that are sorted, then you keep track of the one that you pick that is not sorted and then at the end you do this final step to say, okay, if I add an order sort operator on the unsorted hash join, is that gonna be less than changing the path of merge join? If yes, then I choose it. If no, then I revert back to my merge join. Yes. So simple downfall over here is that it's not looking at the entire query plan just again. Is it, statement is the down, it said downfall? I mean the reason it's bad. It's not, again, it's not bad. The reason why this is maybe insufficient for what we need is that they can't holistically look at all the possible choices you could have for their actual query, right? In addition to the heuristic step that they have in the beginning, is it actually gonna look a lot like the thing we saw before with Ingress and others? I mean, not how Ingress did joins, but like those logical transformations are gonna be written as if and else close. Was it like while you were able to start or sort it? Did you have like, were they able to like pop it in the opposite of the box and like have it sort later? His question is, is it possible that the transformation rules to understand what the data's properties look like so that an account for that in its decisions, system R does not, the later ones will, right? Yes? So, if you wanted to do heuristic rules, you would probably do it after building up the tree regularly. Your statement is, if you wanted to do heuristic rules, you'd do this after this search thing? Yeah. No, you'd do this at the end. You do this at the end. Yes. You can go back and touch it up, which Poroskos does, right? After you do the search, then they go back and do some other additional optimizations potentially. So you sort of force it, if you want to pay. Yes? So you want to do transformations on logical things that you would do too? Logical transformations need to happen for this and then once you have a physical plan, you can do additional physical transformations on it. Postgres does it, you really look at the Postgres code, they do the join sort, they pick the join, they do a bunch of transformations at the beginning, then they do the, there's cost-based join o-ring, then they go back and touch it up and do additional physical optimizations. So the touch-ups would be physical and physical transformation? Correct, the touch-ups would be physical and physical, yes. Okay. So, as I said before, this is an NP-complete problem. We can run this forever and may not ever actually find the true physical plan, so we need a way to know when we should stop. So we need a notion of search termination and this will both arise in, we need this for life-studio and so forth in system R, but we'll need this in the stratified search and the unified search of the cascades. So the simplest thing to do is to do wall clock time. This is basically what Postgres and other systems do, you can set basically a timeout of how long you want the query optimizer to actually run based on a physical wall clock time. You can set a cost threshold where you recognize that if I generate a, the first plan that I see has some kind of cost estimate and then if I run for a certain amount of time and I produce a plan that's maybe 10% better or some number like that, then I just say, all right, that's probably good enough and you stop. Of course now, you still need the wall clock time because this thing could be unbounded because maybe you never find actually anything better. So you still need to account for that and maybe cut things off, right? You can try to be dynamic on this, right? If you can estimate the complexity of the query and say, okay, well, it's a 20 table join. So let me go ahead and give it 10 seconds versus like a one, two table join, maybe give it half a second or something like that. But that's a really hard problem because you're essentially trying to predict how long a query is gonna run before you actually can know what the query plan is gonna be. It is possible to just recognize that there's no more things for me to examine. Let me go ahead and stop. So you can do this on the sub-plan or within a group we'll see in Cascades. Like if I know that there's nothing ever, there's no other permutations I can even consider for some sub-plan, let me just go ahead and stop and don't keep spinning wheels trying to additional things. The last one actually comes from Microsoft which I think is actually really clever and it seems obviously after they sort of say it. Where they found that instead of specifying any of these other metrics like the wall clock time, what you really care about is the number of transformations that you've actually considered. And that's what they use to just determine whether to stop or not. Because the idea is that some transformations may be cheap to apply, some may be expensive. So I wanna know, I get a rough estimate of how long you think a transformation is gonna take on average and the number of transformations I need to apply and then you sort of calibrate that to see, okay, this is when I see most of the benefit for the queries that I'm showing, throwing into it. And then now that's independent of actually the hardware. So no matter whether someone's running on a cell phone or a really expensive high-end server to get the right query to play in, it's the number of transformations you apply rather than the wall clock time. Yes? Can you see the number of transformations you just said that somewhere easier is 100 to apply, but they like weighted so that like it's like not as though just the hard number. Weighted in terms of like the, oh, for like the count? Actually I don't know, who's they? In their talk, they don't mention it. All right, so the pros and cons of this, this actually works pretty well in practice. As I said, most of the persistence are going to use something that looks a lot like this. And you can do additional rules, again, to filter out things like only looking at left deep joins to prune the search base to limit the scope and how long things are actually going to take. Downside is going to be like, if you start throwing away things like bushy joins, then you may be just completely missing what the actual two optimal plans going to be, right? Because you're making these decisions to prune things out without considering anything but cost. And as we said, in the case of our case, you got to do extra steps to deal with the physical property. So the two approaches that I've shown so far, these are typically written in embedded inside the database system, as I was saying, as more or less if than else clauses. If my query plan looks like this, then do this transformation, all right? And if you ever looked at the Postgres code, at least for the query optimizer and the sub-planer function, it is like I do these, check these things, then I check these things, then I check these things. And when you go look at them, there are more or less if than else causes that are looking for queries of a certain pattern. But the challenge of that is that, of these approaches, it's really hard to write a query optimizer in this style coding because you're writing it in procedural code and you're gonna make mistakes or you have to deal with a lot of duplicate logic to identify patterns and so forth and then apply certain rules and then check to see whether the rule you just applied breaks some other assumption you have about the query plan, right? So a better approach to do this and what people figured out in late 80s and what is used in state of the system today is that instead of writing the code for here's actually the check I wanna apply and then the change I wanna make, you write the pattern in a high level DSL that's looking for queries of a certain type with nodes operatives of a certain type and then the transformation rule ideally in a DSL but not always the case because you can't do that. And then you then have a optimizer or you have your system generate the code that does those pattern checks and transformations for you. It's very similar to the JIT or CodeGen stuff we talked about for queries. So in the late 80s, early 90s there was this big movement on what are creating what are called optimizer generators where again the idea is that you declare in a high level language, here's the patterns I wanna check for and then here's transformations I wanna apply when those patterns match. And now I can build these, I can then convert these into a, in a CodeGen into the actual code that I put inside my database system and I only do this when I'm actually compiling the thing and not like on a per query basis. And then now I can then build the search strategy or the search mechanism to then look for those patterns and apply them independently of the rules themselves. So one team can go ahead and build the search engine, another team can go ahead and define the rules. And now even in this single location I have all my rules defined and I can easily extend them and expand them over time. So this is what we're gonna see in all the newer approaches either doing stratified search or unified search these are all be based on optimizer generators. And the two big projects at the time that sort of led this idea was IBM Starburst which is still used in DB2 today and Casc or the first version was Exodus which is a precursor to the volcano which is a precursor to Cascades. You end up building, the guy built three query optimizers. Cascades is the last one he ended up on. Right? So again the idea is again you're co-gening the query optimizer's patterns based on some higher level language. So the two ways approaches are gonna be a stratified search and a unified search. And I've already started talking about this already but the stratified search is that I'm gonna do a bunch of transformations in the beginning based on heuristics things where I know I always wanna apply certain rules and then I go ahead and do my cost-based search. The unified search is trying to do this all at once. And for, again, another way I think it's not exactly always gonna be top-down versus bottom-up, right? But you can think Cascades is unified search and that's a top-down approach because everything's all in the mix and you're just trying to figure things out. And then Starburst and others are gonna be stratified search but they're primarily gonna be using the bottom-up. But you could do this in a sort of a stratified search. You could do a top-down search in stratified search. Like basically that's what Microsoft does. They have a bunch of rules that you know you always wanna apply like predicate pushdown. And even though they're defined in the same DSL that you would use for the cost-based search, they sort of fire those at the very beginning and only at the end later on then they do the unified search to do to clean things up further. Yes? CalCite, I think they claim they're based on a volcano. But they claim that but I think when we look at it, the lines get blurry. They claim the volcano but it looks like Cascades when I look at it and as far as I can tell I think they're doing stratified because they're doing a bunch of rules you always wanna apply and then they do a cost-based search. But I may be wrong. I haven't looked at the code in a long time. Yes? Yes, I'm saying the lines get blurred. So Cascades is a unified approach. The way Microsoft implements Cascades is a stratified plus you know is the heuristics then the search. Yes? So what's the DSL with DSL like reputation before you compile it? This question is would you convert the, sorry, with the DSL would you convert the logical plan into no the DSL would say like I wanna find, if I see these some high-level construct or definition say if I see these three operator nodes next to each other or inline then apply this rule. So you're not like you're defining what you wanna see in the data structure of the query plan. And then the transformation rule ideally if you can write that into the DSL but nobody actually does. That's usually like gonna be C plus loss or something that's the same language that the system's written in. So the transformation you actually apply. Doesn't like the original definition of all these optimizer generators did not define it like that in practice everyone does. I think even in cockroach DB I think they have it for the rules themselves they have it in their DSL but you can then escape that and fall back down to go. Yes? The difference between these two that in unified search they're all written in the same DSL and stratified search they're defined separately. No, so the question is is the key difference that the unified search and stratified search is that these are all written in the same DSL and these are all written separately? No. It's like are you gonna do, are you gonna apply a bunch of transformation rules without a cost model? Or do you do everything all at once with a cost model? But SQL server doesn't apply without the cost model. They're using Cascades. Cascades that's what I'm saying the lines are blurry, let's not. I'm more concerned about top down versus bottom up. How about that? Okay, so again stratified search again everything we've already talked about you first do all the transformation rules on the logical plans and you don't consider cost and you basically as the program or the system you define here's the rules I always wanna consider. Now you could search engine could be clever and figure out like okay well I know the properties of the query plan and I would have enforcer rules in my for enforcer rules that make sure the properties are being or maintain when go from one plan to the next you can have all that in the mix but the idea again we're doing these transformations without any kind of cost model. So it's not an exhaustive search and then I do the cost based search to figure out the logical plan convert the logical plan to a physical plan. So Starburst was the first one that did something like this and again you see the basic two stage is the rewrite stage where I'm just like before in system R I'm breaking down the Chinese SQL query into blocks. In this case here they're actually converting the query plan into a relational calculus not relational algebra. Take that little line. It's like existential qualifiers and things like that. It's less, it doesn't map easily to executable code. It's more mathematical. I don't teach relational calculus anymore because unless you go work on query optimizers which she's going to do, sorry. Like most people don't need this. We used to teach it, we don't teach normal forms there's a bunch of stuff like in the text where we just don't teach. Basically again it's more higher level expressiveness for relational algebra and relational calculus. I don't have caught them in it. It's from the 70s though. All right, so they've converted this higher level form, do a bunch of these rewrites, then they convert that back to a logical plan or the query graph model they call it and then you do a system R style bottoms up dynamic programming phase to figure out the join or additional optimizations. So this is what DB2 still even uses today. And as far as I know, again the lines get blurry is calcite technically doing a stratified search. Yes, but I don't think anybody converts it to relational calculus other than IBM. And so for this exact computation of like relational calculus into then logical plans and then logical and physical and the step-by-step here as far as I know, only DB2 does it. But the tricky thing is DB2 doesn't do this for all, sorry, IBM doesn't do this for all versions of DB2. So this, I mean I know this, there's actually four separate code bases of DB2, right? There's DB2 for ZOS, DB2 for the A900 thing from the 70s and then there's one for Linux, Unix and Windows and there's a fourth one I'm forgetting, right? But they're all completely separate code bases and the one that they built for Linux and Unix is actually derived from an earlier project called OS2 database manager. Who here has ever heard of OS2? One, it's the operating system that IBM built in the late 80s, early 90s to overtake Windows before Windows became huge, right? Because IBM made the first personal PCs but they made it over commodity hardware. Everybody started cloning them. So if you look at old magazines they talk about like PCs being IBM PC compatible or clone compatible because they're just redoing similar things that IBM did. IBM wasn't making any money off of that so they got back into the operating system business for personal computers, made OS2, Windows killed them, right? But anyway, so they had this database management system that they built for OS2 that they then wanted to port renamed to DB2 and port this but in this great blog article from James Hamilton and James Hamilton basically helped set up all AWS's like infrastructure and cloud computing stuff. Like he's a big deal there. But he has this great blog article because he used to work on IBM and actually SQL server as well but talks about how in the early 1990s they had this crappy implementation of a database system in OS2 but then they went to IBM research and got the Starburst query optimizer and put that in, right? So the links on the slides, it's a really good blog article. Yes? Are there any general advantages? His question is what are the advantages to operating on relational calculus rather than logical operators? I don't know offhand, like I'm sure there are. Yeah, it might be just a higher form that you then can apply additional optimizations on. I don't know. Again, outside of Starburst, I don't, you don't cross anything that's outside of theory in database systems that operate on relational calculus. All right, in the second time, we'll just skip this, but like the Starburst Optimized Works Ground Practice but in the, I don't think the paper you guys read talked about this, but in a lot of the follow-up papers from IBM they talk about there are struggles of writing the, the engineer struggle of writing the transformation rules in this DSL that IBM had. Again, I think part of it is because you're operating on relational calculus, which is unnatural. Again, we can cover that further if you want offline. All right, so the last 20 minutes are gonna try to cover cascades. Let's see how far we can get with this, okay? So again, Unified Search is that logical, logical and logical physical are all within one giant stage. Of course, the challenge is gonna be is that there can be a lot of transformations that we're gonna generate as we do this and so we're gonna try to, we're gonna use memoization as a way to keep track of what we've done in an efficient manner and try to reduce the amount of redundant work or redundant computation that we're doing. So we show what Volcano does and then we'll see the efficiency of that and then we'll jump into cascades. So again, the few things, there's a Volcano approach. The dude's legendary, so there's the Volcano project of the system defines the iterator model that we all know about, defines the exchange operator to do parallel computation but then he's also about our state of error optimizer generator that could be used for these things as well. As far as I know, nobody does this, although Calcite claims they do this, there's based on this, but this is one of the first approaches that doing a top-down on search to generate query plans. So again, in a top-down approach, you start with the top, you start with like, this is the output I want, then you're gonna work down from the bottom and assemble the pieces you need to get back to that top. So you're basically gonna evoke all these transformation rules that you have to generate new physical nodes and logical nodes based on where you're at in the query plan, right? So here's all the things, combinations I could have just like before leading down to the individual scans on each of the tables. So in the first step here, I could apply transformation rules to convert the physical join or sort of the logical join artist appears an album into a merge join on two of the tables and then have a third table just being fed into it, right? So then I traverse down here and then I say, okay, well, for this physical operator, these are the logical operators that fed into me to get me to this result. So then I apply transformation rules to then generate the physical operators then produce the result that I was fed into there. And as I'm going down, I'm estimating the cost which I'm not showing here of each of these physical operators summing up the total back up to the root and that tells me the cost to, you know, where I'm at at this branch in the search stream. And then I get down to here and say, well, what fed into this? And then I could, I'm not showing here, but you could pick then the access method for each individual tables, go back up here, traverse down the other side, here's the merge join for the other two tables, produce this, same thing I'm costing as I'm going along, right? You just keep doing this and over and over until you produce the final result. And again, because we care about what the sort order is for the table and the output, I have these additional fortune rules that I'm defining to make sure that any data that's being fed into me from operators down below is putting the data in the physical property that I need or expect. So in this case here, because I care about the data being sorted by artist ID, if I then apply a transformation rule that generates a hash join, in this case here, that hash join cannot guarantee the data is sorted, so I can go ahead and cut this off. And I don't need to do any further traversal down into that branch. Likewise, if I say, okay, I have a quicksort operator, well that'll get my data sorted the way I wanted, but then if I come down here, expand it out now to say, well, what was the physical operator's feeding into me? If I then say, oh, I could do a hash join below, and now the cumulative cost of the quicksort plus the hash join is greater than maybe the lowest path I've seen down to the bottom, then I know I don't need to expand this any further and I can cut off this branch right there. This is classic branch of bound search, nothing fancy here. All right, so let's jump to the Cascades. So the reason why I don't have you read the original Cascades paper is that it's actually not that great. Have you guys read it? It's not like you keep banging on how great his stuff is object oriented, because that was the hot thing in the 90s and volcano exodus were not, right? But you can't, you can't actually take this paper and actually implement it, right? The best you could do is this thing from, it's a master's thesis from 1998 at a Portland State where the first 30 pages tells you actually what Cascades is actually doing, way better than the original paper is. So if you want to know what Cascades is, you could read pages one to 31, and I'll tell you how to do it in there. But this will again be a quick crash course on it. So again, just like in volcano, we're gonna do a top-down approach, backward chaining, but the key thing is that now we're gonna explore rewriting through these direct mapping functions that can iteratively generate the transformations, apply them to fan out the search tree rather than doing them all at once. So I didn't show in the case of a volcano because it's PowerPoint, but every time I went down to another node, I immediately applied the transformation rule to generate all the possible combinations below me. And then I would iteratively look at them one by one. Of course now that would be super expensive to do if a really complex query plan, because now the search base is gonna balloon and you're gonna run out and run out of memory. So the key idea they're gonna do is that they're gonna introduce placeholders to say, here's what the data should look like below me at this part of the tree, but I don't actually know what the right way to execute it just yet is. So I'm just gonna have a placeholder for now. And then only when, when I care about going that further, then I can expand it out. But now I can expand it incrementally based on a priority that I can define to say, here's the things actually I should be looking at first. And you define that all within the constructs of the DSL that you're defining these rules. So the four key ideas. One is that all the opportunity tasks are gonna be self-contained data structures. So no different analysis thing of these like structs or objects that say, here's the pattern I wanna match in my query plan and if you match apply this rule. And the additional metadata to specify like what are the properties I need to guarantee or that I'll generate from this transformation rule for this operator and what priority do I wanna give it. And this priority stuff can become interesting is now as I'm traversing down, I can dynamically change, although Microsoft doesn't do this but in the original paper they talk about or at least the master thesis. You can, as you look, go down and you recognize my query plan is looking a certain way, I can say, well I wanna apply these transformation rules because I think that's gonna help me out better than just picking one at random, right? And then the other key thing is that although Microsoft doesn't do this, but CockroachGB does, is that when you think about doing optimization on the where clause, the expressions, the predicates inside the query plan? Well, that's just another tree. And so within the same search engine or in the rules engine, you can do optimizations for the where clause expressions in the same way you can do optimizations for the operator tree, operator tree, right? So simple things like I can identify where one equals two, you could have a rule that then gets fired to say convert one equals two into false instead of having to do these optimizations separately. All right, so, yes, question, go to the second term. So basically you define in the implementation of the optimization tasks like a pattern plus a transformation rule, you define in that within directly, here's the properties that I need you to enforce. And then the surgeon can recognize, okay, suddenly feeding into me is gonna violate that so therefore I can't choose anything below that, right? And the second one basically says like, if I recognize as I'm going along, I'm trying to think of having an example here. Yeah, I don't have an immediate example. Like if I'm traversing, I may want to consider some transformations more sooner than others. Like for example, if I know my operator above me, say is an index and as a loop join, when I go down below, rather than looking at all possible transformations from logical to physical, like choosing a sequential scan and then adding a sort, I may want to just choose the index probe first because then that gives me the data in the right order that I need going up. So does that also define? Yes, yes, this is what the priority, it's generated on the fly. Well, so the case of Microsoft, I think they just like hard-coded it. So yeah, it's part of the task itself. In the original paper, which caucuses you be, I think they said they actually do, I actually, I don't know whether anybody does this, but in theory, you could dynamically change this as you go along. So even though you may revisit the same, you may come back to a same group later on because you didn't evaluate everything, you could change the order you evaluate stuff. So this part is going to be confusing because I use expressions that typically mean like the predicates and the where cause, but in cascades, expression is going to be some operation within the query plan that's going to do something, right? Or do some amount of computation in the query plan. So logical expression could be three-way join on A, B, and C where I join A and B and first followed by joining C, but then the physical expression could then be that I do a hash on it on a sequential scan for this, sequential scan for that, then I do an SLIP join on index probe on this. So it's defining like some set of, I'm going to say operators, but tasks within our query plan that we want to then transform into physical operators, right? And so the key thing that we're going to exploit is obviously equivalency rules in relational algebra because we would know that, you know, we could switch the order of A join B to B join A and that'll still produce this, you know, the same correct result that we would want. And so we use that to define when we do our transformations if we're permuting things a certain way that we're not violating any of those, these commutativity properties or other properties that we care about, relational algebra. So then now we have the definition of a group and that'll be for a given expression, it'll be all the logical and physical expressions that are equivalent to some output that I'd expect. So in this case here, I want to produce the result of joining A, B, and C together and my logical expressions within that group will be all the different permutations of doing those joins and the physical expressions will be all the actual implementations of that. So again, it's all the logical forms and then all the physical forms that can be derived that from these logical forms. So the entire collection of these things is the group in addition with the properties that we would need to be enforced going into this and then we have all again all the equivalent expressions for the logical and physical. And then now we're gonna find a multi-expression which is sort of confusing but that's basically meant to be as a placeholder to say here's some expression that I have but I don't know exactly the details of what's going on inside of it and it's a placeholder to say there's something below B in the search tree. So for example, I want to join A, B, and C so I could have, oops, sorry, I could have a multi-expression to say, okay, there's a join and A and B together, I don't know what order and which way logically and then I join with C or I could join B and C and I don't define again what way I'm doing that but it's just a placeholder to say something below me in the tree is gonna be tell me how to do this. The idea is that we're using these multi-expressions as a way to reduce the number of unique operators that we have to look at in this giant search tree. So the idea again, because we're top down we can make decisions by looking at this placeholder at this point at some level in the tree without having to go all the way to the bottom. In the case of a bottom-ups optimizer you're enumerating all the multi-expressions one at time and putting them together going up to the top. So in that case, they have not been materialized yet because you're starting from the bottom going to the top. In the case of the top down one, you assume sort of roughly that you have the path going down the bottom, even though you don't actually at this point. So this we've already talked about before but they're basically transformation rules, logical, logical, logical physical. In the Cascades parlance, they'll say transformation rule is logical, logical and implementation rule is be logical or physical and then they'll have this pattern defines the structure of the logical expression that you wanna look for and then you have a substitution to define the rule of the new structure you expect to see after you do this. Now, in some cases that you don't actually wanna maintain the previous history or the previous plan you've permitted or transformed the plan into because you know it's something you always wanna do. So for example, like converting one equals two into false, I don't wanna maintain that history of like, oh yeah, I did this transformation to convert one equals two to false because you never wanna go back. And so there are ways to just always apply the change and then not keep a history because you don't wanna balloon up your history space. All right, so here's looking at a simple example of a rule. So my pattern is that I wanna have two equijoints with a right deep join tree. So each of these nodes here are corresponding to groups because I'm not defining at least at the rule level what actually is going on inside this group. This could be an index scan, it could be another join, it could be whatever, it doesn't matter, it's just some group, it's a placeholder. We have to have a distinct equijoint, at least a logical operator in this case here, that I'm joining two tables together. So say this is my plan that matches this. So again, you see that I have my multi-expression at the top join A and B, but I'm not defining how I actually wanna do this. But then when you traverse down and below, it says I'm joining A and B and below that we have some get operators corresponding to the access method, the way you're actually retrieving this data. So this plan here, a logical plan would match to this rule and I could have two types of transformations. I got two types of permutations. I don't need transformation because that's the type of rule. But I could do a transformation rule that girts me from a left deep join to a right deep join just rotating the tree so that the joints are coming down the right side. But again, this is converting a logical operator to another set of logical operators. Or I could have an implementation rule that converts all the aqua joins into then to be all sort merge joins or hash joins or whatever I want, right? So again, this is what the patterns are gonna look like and then we would match on this and then we can do these additional, we can do these changes based on that. In this case here, you can see how like the, well in this case we've joined with A and B on the sort merge join, but again in this one here, we have to sort out the placeholder for the multi-expression because that's feeding up into it. What's one obvious problem with this, say this top one here, because I'm going logical to logical, say I have another plan that does right to left. What could I end up with? Same plan, infinite loop. You just keep flipping it back and forth. So this is where the memo table is gonna help us out because we can use that to keep track of, oh, I've already have applied this transformation because I know the cost of the query plan at this point to avoid getting stuck in these infinite loops. So the memo table, at least in the original implementation or definition of the Columbia paper, talks about being a separate data structure, but you could actually embed this information into the groups themselves. Yeah, in the sake of time, this is too heavy to go into for like the last three minutes, but let me show one example now and then we'll come back to this. We'll start next class with this all over again. With all this build, I'll at least wanna show you what it looks like. So say this is our query plan we wanna look like. Again, the memo table just keeping track of for any multi-expression. Here's the best physical expression we've seen with the given cost. So in the very beginning, again, we're at sort of the top. We wanna have a join between A, B, and C. And we have no physical properties because we're not doing the order by it for simplicity. So I could do a logical transformation to convert the transformation rule, I'll apply it to my output to generate a multi-expression on A and B joined by C. So now I wanna start costing this and decide whether, you know, is it worth pursuing further inspection of this multi-expression by traversing down to the query plan, say now my output would be join A and B. Well, I could join A, the one way to do this is join A followed by join B. And the reason my A and B are multi-expression is because I'm not defining how I'm actually getting this data. I'm not defining what the access method is. So to get further information about what this possible sub-plan could look like, I have to go down down to the tree now. And now in this case here, the only way to access A is to do a logical operator called get A. That's not interesting. I can't cost that because it doesn't tell me how I'm gonna do it. But I can then do implementation rules to transform this into either a sequential scan on A and then, or an index scan on A. But then let's say for whatever reason, for this data, whatever this query plan is, the sequential scan is faster. So now in my memo table, for the multi-expression on looking up A, the sequential scan is the best physical operator I have for this, and then the cost is 10. I do the same thing. I bounce up back up here. Do you look on the other side of the join? Now I do a look at the B multi-expression, same thing. I have a logical operator B and I can transform that into a sequential scan on B and next scan on B. For whatever reason, the sequential scan is so faster. Then I update my memo table here. So now I bounce back up here and now I just have a transformation rule to go from swapping the order for B and A and that's commutative, that's a lot to do. But now when I would do the same thing, I say I have this multi-expression on B and a multi-expression on A, I gotta go down to the groups below me and figure out what the cost of those things are. Well, I've already done that, right? Because I can go look up on my memo table, recognize that I know the best operator for B and the best operator for A based on what's up in here. So I actually don't even need to do that traversal because I have this information already. I just have to do look up on the memo table, right? So now, since I've exhausted all my logical expressions or transformations that I could do, then I start generating all the physical expressions. But again, I would do this incrementally, I'm showing this four, but again you can think of like all possible combinations of join ordering and all possible combinations of different join hour that I wanna use. So now, say for whatever reason, when I cost this, the hash join between A and B turns out to be the best, the fastest. So then the cost of this now multi-expression is just the cost of accessing A, the cost of accessing B, plus the cost of doing the hash join. Again, these are all just made up numbers. So it's the summation of all of them and now the cost is 80. So for the multi-expression AB, the best physical plan is the hash join on A and B and the cost is 80 and now I bounce up the top and do the same thing down on the other side and so forth. This is what basically Cascades is doing. Again, the devil's in the details about how you actually apply the transformation rules. I'm not sure anything about priorities, I'm not sure anything about properties, but this is the high level, this is what the search looks like. And then I produce my final cost here. And then now if I do any other traversals for maybe other physical operators or other joins, I stop the search once I see a cost that's greater than 80 or so greater than 125. All right, so let's stop here, any quick questions and we'll pick up this already left off. What do you mean I predict wrong? The cost of getting B, actually in this scenario, is it gonna rebuild the entire realm of people? So the question is, and we'll talk about it next week at Activity Stuff, like what if my estimates are wrong, what happens? Most systems, nothing, you just keep going. We'll see ways to put hooks in the query plan to say, okay, if I'm getting wrong, go back and re-plan things. We'll see cases like in IBM and other systems where you get feedback from like, hey, you told me it was this, but it's really this when I ran it again. So the next query comes along, you can get updated on this. But in general, all this memo state, it's thrown away when the query's done, which is why I don't wanna do this in our optimizer. But then this thing is huge, how long should that be maintained for? Also when I'm not showing in these multi-expressions, like I'm not showing what the original query is, like the sequential can costs of 10 on A, but what's the predicate? What's the where clause? That could be different from one query than another. You'd have to account for that in your memo table as well. So that you potentially could reuse it across different queries. Okay, I'm always over ambitious of what I can try to cover in the last class or in this one lecture. So let me just say we'll pick up next class on, we'll go through Cascades again, because we sort of rushed that. Then we'll talk about randomized search for like in Postgres and others. I debate whether I actually teach that because like I said, nobody actually really uses it. And the Postgres one is broken. Last year I put together this playlist on YouTube. These are all the talks from the various companies over the years, I've come to talk to the aircraft optimizers. The one I cannot recommend enough is the one from Microsoft on SQL server. Because again, in my opinion, that's the other than the way hyper is gonna do join ordering. This is the best one and this is a really great talk. The next best one is probably from Becca from CockroachDB to talk about how she built their query optimizer, which is based on Cascades. And they do some of the things that Microsoft doesn't do. So, all right, next class will be finish up with Cascades, finish up with random algorithms. We'll see how Germans do unnesting subqueries and how Germans do dynamic programming through hypergraphs for picking join ordering. Okay? All right guys, see ya. Take off, you know, great hiding. Got a bounce to get the 40 ounce bottle. Get a grip, take a sip, and you'll be picking up models. Ain't it no puzzle, I got some, because I'm more a man. I'm down in the 40, and my shorty's got sore cans on the table. And I'm able to see no shorts with the cloths. You know I got them. I take off the cap, and first I'll tap on the bottom. Throw my three in the freezer so I can chill it. Careful with the bottom, baby.