What's all this fuss about Common Table Expressions (CTE's) anyway?

but let's start um my name is Joakim rank as you can see they and you're in the talk about what's all this fuss about common table expressions anyway if you not here for that yeah you can ballroom yeah I work for want solutions can't lucky see there and that is me that's my face and my email that's the one I don't read my working mouth you can send any requests there sir Kobus is not there right yeah okay so you I'm gonna talk about common table expressions or as it's known within the Postgres Docs with queries where common table expressions is actually within the parentheses clause I didn't know that so it's actually with queries and secondly common table expressions and um this talk will be mostly beginner related to just explain like what is it at all and I think a lot of us have felt that way when you start when I started at the company and I saw the word worth in a query I didn't know what I was going on I just left it alone and read the lay the rest and if you didn't work I didn't know what that what was happening and this is to explain what what does that do and why you should use it um and it's a great tool to to do a lot of things and um yeah we'll go through that step by step and also you're just it's not only for beginners will at the end of this will go get into some advanced stuff that you can do with with queries with common table expressions this the common table expressions has been implemented since post grades eight point four hasn't changed much since then except now in post gives twelve and we'll get there at the end they've made some slight optimizations which is actually good actually a great optimization this is what I'll cover syntax some basic syntax just to show you yeah what is water sports goods what you wanted to write we're basic use then we'll cover some recursion see these so that's where some of the cool white one stuff comes in when or why not to use CPS in some cases and then some of that's not relevant anymore because of Postgres twelve but I know a lot of people then go to twelve very soon if they're like us and then we'll do that yeah the future which is now that's Chris twelve so okay before I show that queer in a you've already seen it I know this happens a lot in a lot of companies way you know you don't want to but sometimes you write you write that functions or queries way you just end up using three or four left joins on sub queries which has more live joints on it and you don't have to have a lot before it gets out of and you only need like two or three then it gets then it gets terrible and you can't read that that stuff I don't know if you agree with me or not but you can't read what's going on your eyes are all over all the words you have no control you don't know what's joint with what actually because you have to read the end of the sub-query to find out how yet yes the next things starting so I mean that stuff is terrible and your ever very small example this came from our database but I had to remove so much code to fit it on this slide like I it's not even off of it but we have this one thing that I just used as an example I obviously renamed everything and I saw this Jason both object I do a Kido Kido I keep it to nine 100 there's not really 100 fields in there but I mean you catch the drift and it's not easy to read right you you don't know what's going on there's so many words you I don't know what to do and we're gonna try and fix that there I have a little picture from as you've watched office space and I mean that's what happens without you even planning it you just write all the queries at the same time so it's terrible and we're gonna fix that and it made me think of a little quote I don't know if you can can tell me what the end of that quote is punch in the face Klaus is relational databases and saying goodbye to your pretty queries so it's I mean it's bad but we're gonna solve that today yeah no it's great okay so let's start with syntax like how we're gonna solve that that thing and I mean this is basic syntax and the buzz Chris talks actually they've been updated a few times through the versions you can click most on the versions at the top and you can see how they after they are they've updated it and it's pretty great a lot of syntax comes from there I've just you know used some of my own words to help the story along and this is the basic thing you will start it with the worth word and you'll give this sub query I'm gonna just use South Korean City interchangeably because that's at the end of the day what you what you're gonna do you're gonna take a sub query put it in the CTE so that it's not they on the in line with the parent query and that you can read it better and that's not the only use but it will cover the rest and you'll give it a name like something that you want to retrieve and it will be a lot of data and you'll give it a name like um all the users and their girlfriends and their dogs names you will name the query that and that's what you'll fetch in here so it you don't have to fetch it later in the left join if you understand what I'm saying then you can give give column names comma separated column name name list if you want to that's optional and we'll use it in the examples and you'll say as and then you'll have a query and that query then can be almost anything and we'll get to that and then you have the Select or insert or update or delete query after that you'll that statement will make use of that CTE so let's start with the most basic one with source as select one and that sub query will return one and you can use that sub queries result as a table so select all from source we'll return as you've guessed one and then I'm just showing you that that example is exactly the same as this example so you want what you want somewhere right this into that for kicks there's no benefit to that but just to show you that's what the width that's what the city is do so you could have sub queried the Select one but normally it's not just the select one as we know lots of stuff gets added a because they want more and more data the views aren't they so you just pulled it in line with the common table expressions you can rename it the the columns within the sub query to anything you want and that will return than that because let me just go back you see it's written the anonymous column doesn't have any name you can rename it as column one it will return that you can also as we saw in the basic syntax screen in the beginning a slide on the beginning you can give it a name day you don't have to do there like a and then also column one then you can do this you can rename it rename it again and rename it again there you have all the options the power is in your hands with with queries and that will return call 3 so you started out as nothing then call one and you bamboozle it all the way to call 3 you can also return multiple columns obviously because otherwise this would not have been useful so they select one don't get stuck on I only use select one you can return anything today multiple columns anonymous again my friend now before we go on if you don't understand what that what I just happened there at all you have to ask me so that I can explain something because now it's gonna it's gonna get it's gonna get nasty so if anyone has any questions explain some syntax or something want me to go back to the slide great then side and one yeah okay so yeah it is so it's still not pretty right because remember this is not the plan you don't want to write ugly cut but when you have to do that do it this way rather than the sub-query way in this way you can immediately focus your eyes down here and that's a pretty query and you can immediately see what's being joined to what with what conditions immediately and then you can when you want to know something take your eyes above and figure out where did that thing come from like I left you on a name table but you can keep on reading and then later go to what is name table actually what's what's it being written in that table so within that city what what is what is going back and then I mean this just reads easier so that's like the very first use for a city on in the docks and on almost on every tutorials pages readability of your views of your functions so yeah and this just displays that but yeah you still try not to do that but you know you can also so you don't only have to select within a city you can do some insert and use the returning the returning clause and you know that will return something so within the source table what would have been returned is all the values that being inserted in this thing and you can select from that source just know that this is happening it's not you're not selecting from it and nothing's happening that inserts or happening and you will get the result sometimes we do this way you want to update something where a lot of conditions and then we've when you're finished you want to update something else but only when it's relevant to the things you just update it and this can accomplish that so that's that's nice you have that that you you write the query there and you write all your where conditions lots of things and you return the IDS of that and then you update any other table that's relevant to that rows and you can only update that and this does that in one query and it's not more readable than otherwise and stuff do you want to take a picture what do it because you must write the things like that okay a little example yet I made a little table not everything is there then don't get angry this is sort of what the computer consists of almost everything and I eat in one table but it's easy it's a part and there's a support and how many of that things would be in that thing okay do you understand the table so in a case there's three fans in a case this one motherboard and then it there at the bottom you'll see like in a motherboard this one GPU stuff like that and what I want to achieve is something like this like I want to ask what all the parts that makes up the GPU so what's all the parts the GPU consists of and what the answer I got day is like the blade memory and fan so you understand the GPU as memory it has a fan but the fan has blade so the GPU has six blades and one fan and that's the honest that's answer I want to get and how would one get that answer from this table I'm gonna tell you don't stress same for the motherboard and then you'll get that answer you guys understand the problem will go to the solution recursive cities I know here's some recursion means we have to go through them we don't have to vote now but Malcolm needs feedback on every speaker and you can use this this one this one is my favorite just say you know if you haven't read it now we can spend some time here my talk it's not that long okay let's go okay so I showed you the basics and tax in the beginning of the of the screen but I lied so that's what I showed you but in in fact there's a beautiful little thing there that you can be right there with recursive and all the same things and with that you can do some pretty cool things let's go with that yeah yeah okay basic basic syntax you the same as before so with recursive ct-9 I right there still sauce and then you'll have your sub query and your sub query basically has to have the same form every time you will have a seed value so select something that's the start your Union or Union all that with a recursive statement and the recursive statements and that way you can get all kinds of tricks you can write crazy stuff in the recursive I haven't but you can in that statement to build up this thing and I will get into how it works now and your recursive statement is the one that should be referencing the CTE again and after that you'll say okay give me everything and yeah so okay let's let me show you a quick example here I I said select one as as in and that's my seat value so I'm starting with the one I'm saying Union that with in plus one from source and the PostScript stops has a nice little paragraph explaining very nicely of exactly happening in every step and what a recursive city will do and when it will decide it's finished and stuff like that and you need to understand that because this recursive city will go into an infinite loop because I've given it now where condition or a limit to say when it should stop n plus 1 in for me it will just n plus 1 forever um ok let me just quickly explain how it works that one will be taken and it will be come the next value for this recursive source so because I say a forum source so the one will be the first one for that so it will say n plus 1 from source and that end that it gets from source is 1 and then next time it gets the source is 2 because when the recursive function sorry when it when it finishes it takes the result and put put it back into source so that the next time you ask for it it's 10 so it will be 1 then 2 then 3 and and this will happen forever because I haven't stopped it so unless unless you have like a a statement timeout set and I did that just to show you that's the error message I got I set the timeout of something I can't remember I just ran it and then it it killed itself because it will run forever now I'll put away in there to tell this recursive see T when to stop and give me the results and I say they were in smaller than 10 and then I run this I get this and it stopped the moment that n became thin so you won't see the value 11 there so n plus 1 would have be 10 plus 1 would have been 11 and it stopped right before that wait ok does anyone have a question quickly about any syntax or whatever what do you understand quite quite easy so far okay we're back to my computer part and this is how we're going to get the answer are you ready me too okay this example is almost exactly I think like that on the docks but I went and made the table for that example to check if it really works I don't trust I don't trust them and it does work so it's amazing so this will literally say select support port in quantity every column from pot where pot equals something in a bit and see where I will give it like okay motherboard Oh GPU give me give me all the parts of GPU or malleable Union all that with select ok so yes P for pot P support P part until quantity every column from included pots you will see included pot is the city name so that's the back reference you have to have that name in your recursive side of the statement below the union seed value recursive Ali from included parts comma part where P dot part is pure dot support and that's how it gets every get every value until it's finished I don't think I've mentioned this this will that's why the we're stopped the recursive function the recursive city will stop when it had when it has no value to return so that's the only condition it's it's checking that's why the way of stopped it so the condition is I have no values to return after I finish with my with the this recursive statement and then I'll stop so the way in the previous one stopped it because it didn't return a value at that time so that's why you get one until ten then the where is like I'm not returning this one and then the city is like oh it's not retaining this time I'm also then finished so that's why so the moment there's no more supports for a part the recursive this recursive statement won't return anything and that's when it will know it's finished that it reached basically the bottom of the pot and that's how you get this this answer if you put GPU in there the if you run this query on its own it will only give you the two supports that's linked to GPU and then it will Union all that with this recursive statement and you can just see that then that will written the extra blade row for fan because that's the only one that matches and the next time it gets there nothing will match this the recursive function finishes and you're out mother both will go one or more loops further until that finishes there's no more supports for that part and it will return any questions I think that's my next slide yes go No Oh No you're a I understand why is it my okay that's okay I've a disclaimer I don't know why the green lines are there before the words that's very weird but doesn't really matter I think I told them to come off - yeah well we'll talk about it afterwards me and the lines so some stuff to note you can't just always take any any query or view you see cpe everything and think you're gonna fix it although it's gonna look prettier because there are some caveats first quiz 12 solved some of them but we're going to talk about it now and when you do this I made a typo I think with billion rows now i'm million rows between 2 million and 3 million that's what I did it was funny I already made the other two million something broke then I made melon to end something back I did that turn those questions then I said select all from big devil where idea is just one of them and it took that amount of time then I was like so you won't do this but just to show you something I made a CT with the answer and the sub-query was select everything from BigTable and then I was like at the bottom select everything from now on so we're ID equals that and like it will give you exactly the same result but it will take almost 4 times longer in this case if there were more columns or more data or something it would stuff would happen if this would break because what's happening here is that CTE is getting all the data it doesn't care it doesn't read this sentence and then now you want that it gets all the data and then you're like okay now give me 250 so that that that is terrible that CTE is basically being materialized getting all the values and both Chris also has no optimization on that so it it doesn't know that you're gonna wear it now and then after that is like where that and then it's instant but you already wasted a lot of time so you won't do this but just remember that when you have something to do and there's a where clause move the where clause to within the CTE and that will result in the same time because we've done it a few times we put the we're after the CTE and you don't know why it's low now you know so put the conditions that has to do with that direct table big table within the city and everything is fine now we're in the future that's what I just said okay post grace below twelve cities were not optimized like at all there was now going behind it or above it couldn't turn it on or off it was just not optimized if you had a city and you use that in a left join that sub-query would not have made part of the parents query optimization plan it would run on its own and the results will be will be used in that left join or join or whatever but now it has changed from prosky is 12 so that's been launched like a five days ago or something so we'll start using this maybe 2025 from both little cities will be inlined so by inlining I mean you write the CTE but first grace is like I know what you mean and it takes it back to the unreadable it takes it back there but I mean at least you can read it we don't care what the planner does but it takes it back there in lines it and then it can use that together with the parent query and other joints to think about what you really want to do and optimize dial out of it if almost missed it and it's orange I made a tunnel purpose but it will do it by default if the worth query is non recursive so the recursive ones don't don't try that and side-effect free meaning I think I put it here yes so yeah it must be sorry freak free this means now insert updates deletes or volatile functions called by a select so basically it has to be a city with selects and no volatile functions also the parent query must yeah must reference the city just wants for this to happen by default you can override the default by area and here we are by specifying that and that's in 12 now by specifying materialized after the CTE name or you can force the CTE do not be rolled off to be rolled into parent query by specifying not materials so you can now go both ways if you want the query optimizer to materialize it you can specify it if you don't want it to do it confer specified but the defaults you have it will default depending on those conditions and you'll just write it in there like you see that so the normal one will look like that you can now tell this this query this wouldn't make sense why we do that I see now because the way closed under but you can tell that city to material are is no matter what like it shouldn't try and optimize it in twelve now it should just materialize it and there's some some reasons for this is if you have a city that that you're going to use twice but then by default it won't do it anyway so it's fine now because then you wanted to materialize once do the things and you use it twice otherwise it will if it's sub query that will happen twice the the expensive select or something but you're you can you can say materialized and non materialized in day and I'm finished should I go back to the beginning do we want to see the memes again okay but um yeah okay so they are there was a quick talk but them your eyeball condone a lot of questions or does everyone just understand these things I thank you just quick but yeah okay so yeah anyone any questions that I can answer for you or maybe come back to you maybe I knew it was yeah I didn't Jordan okay its back on okay so yeah any other questions from anyone yes so if I read something lower with now this is a another table this is one of those guys and then I think what Percy schools lateral joins I name them with double underscore okay so when I look at the column name I can see okay this comes to the table this comes from a common table expression this comes from a cross join yeah you are the conventions within a development in at least is important no matter what you choose but this yeah choose a convention and then stay with that sorry the other developers can read what you're doing but we have queries like the one you see there at first name so that you can put a comment select this if you have to debug something you started to talk oh yeah if you have multiple cities I also do that yeah seeking the sports all right and then you comment it again you jump to the next one and you uncomment and check the spot yeah if you start having a big query with like four or five CT es that becomes important because the last one breaks and you don't know where the data went wrong it happens a lot so that did I have any slide with multiple with closes at all what who even am I so attack English please so let's just take like any worth here I don't know I don't think I add but you can comma after the the second parentheses and make another CTE so with lots of data coming back into a name comma with lots of other data coming back in time and later selecting from both of them so please yes yeah you can definitely within the second one use the first one definitely okay no more questions may we have one what you must wait for the loud maker but it's still not possible to nest them inside each other you can nest so I can let let's say this select all from big devil was not a simple query like that and it was on on each turn again a difficult thing you can with there again and select worth it yeah it can do that so so let's okay wait wait we have to do it where is that thing we have okay we have a thing here but okay it not gonna happen like yes I have an example of to with you see okay there's table and you use them both I can if this was more of a mess I can go in here and have a small little CTE there again so yeah as much as you want it's Christmas okay everyone good [Applause]

Channel: PostgresConf South Africa

Views: 955

Rating: 5 out of 5

Keywords:

Id: GmRbEFhnPh0

Length: 30min 49sec (1849 seconds)

Published: Thu Nov 21 2019