Hi guys, welcome back to the channel. Hope y'all are doing well. Today I'll show you how you can optimize your SQL queries in my SQL or Postgres. We have two tables for you here. One is the book table, another is the volume table. The book table has two columns, ID and volume ID. And the volume table has three columns, ID, volume ID and year. Okay. So if we do, if we execute a basic query, so we're just going to do a select star from book, limit 10. You can see we get 10 rows from the book table, right? Now if you want to tell your database to show you the actual execution plan or the query plan, what you need to do is just pre-pend it with or prefix it with explain. Now you can see your query was not executed, but the database is showing you the plan it has to actually execute the query. So you can see it is going to do a sequential scan on your book, right? On your book table. That's the plan. Now, as I told you, when you prefix your query with explain, the actual query is not executing. It's only showing you a plan that the engine plans to take. If you want to see the plan that the query that the engine is using after, it actually executes the query. Instead of just adding explain, you want to add explain analyze. Now if I execute it, you can see we get a much more detailed query plan, and you get two very key information. One is called the planning time. This is the amount of time the engine spent on planning the query, and then you have the execution time, which is the time the engine took to actually execute the query. In this case, the planning time was 0.096 milliseconds, and execution time was 0.027. This is a very simple query. We're just doing a select star, so you don't get a ton of information, but as we are going to see in this video, depending on your query, you can get a lot of information about the query plan, which is going to help you optimize the query even more. Let's start to add some complexity to our queries. The first thing we're going to do is show you the difference between a small result and a large result. What does that mean? Let's check this out. Remember in this query, we had select star from book limit 10, so we're telling the engine to give us only 10 rows of data. We're going to run it again, and the time is not going to be the exact same. but it should be about like, there about. So for this query, we're getting only 10 rows. The execution time is 0.038 milliseconds. Now, if we instead just get rid of the limit, this table right now has 1000 rows. So with this query, the database needs to fetch all 1000 rows from the database, or from the disk. So we would expect the execution time to go up. So right now we're at 0.038 milliseconds. If I run it now, it's 0.145 milliseconds. If we add the limit back in, you'll see it go down 0.145 milliseconds, and then you have 0.032 milliseconds. So you can see in the second query, or where we don't have a limit, the database needs to fetch more rows from the disk. So the execution time increases. Okay. That's the very key information that you can see just by prefixing your query with explain and analyze. Okay. All right. So the next thing we're going to take a look at is how indexes can play into the query plan, and how you can get very good amount of information about your index, especially whether the index is being used or not in your query. Okay. So we're going to add a new index, and then I'll show you how I'll show you a query plan with the index, and one without the index. Okay. So if we go back for a second, I'm going to zoom in and zoom out. Okay. So if you look at our book table, you see we have indexes over here. And the only index right now we have in this table is on the ID column. So this ID, which is the primary key of the table, by default postgres creates the, creates this index for me. Okay. For volume ID, one thing to note is we do not have an index on volume ID. This is going to come in, come in play when we, when I show you the query. Okay. So if we do select star from book, where ID equals 20. Okay. And then let's prefix with explain analyze and execute it. Now you can see that we have once again the planning time and the execution time, but we also have something called index condition. And then it says index scan using book underscore p key on book. Okay. So I'm going to copy this query for now and focus on this over here. It says book underscore p key or primary key. Now if we go to our book table and we go to indexes, you can see that our one index in the table on the ID column, it's called book underscore primary key. Okay. I'm going to run the query again. So what this, what essentially this query plan tells you is to execute the query, the engine used the index that you had, which is the book underscore primary key. And index condition is on this index, the engine consulted with the index and it figured out where in disk the row with ID 20 is living. And then it goes and fetches that row. So you can see the execution time is 0.0 to 8 milliseconds. Now if we instead do, if we instead do, let's say volume ID, it's only to see what's the appropriate volume ID. So let me do this. Let me just copy this. And then we're just going to look at some sample data. So let's say we want, so remember we have the idea and the volume ID. Say if we do ID equals eight, we're essentially getting this row. Okay. If we're doing ID equals 25, we're getting this row. So what I'm going to do is I'm going to end the word clause. At first, we're going to do ID equals 25. This should give us the row using that index. And then we're going to do volume ID equals 200 auto generated. And then you'll see that the query is not using the index. Okay. So let me go back. So yeah, where ID equals 20, we execute it. It's taking 0.025 seconds. Now if we do volume ID equals, I forget, was it 200 auto generated? Well, try it out. So this is taking 0.025 milliseconds. And then if we execute this, you can see, I'll just copy based it here. Yeah. So you can see when we are querying by volume ID, instead of ID, it's doing a sequential scan on your table. It is not using the ID index because it cannot use the ID index. Because we are telling the database what the volume ID is. Not not what the ID is. And because of that, you can also see the execution time for the same row. It was 0.025 milliseconds before, but now it has grown up to 0.128 milliseconds. Of course, right now, the difference might not be too much because we have a very small dataset, but you can imagine in a production database where you have millions and millions of rows, the difference can be quite significant. Okay. So this is a prime example of a query depending on your use case where you want to use a index or you did not. And you have to design your table accordingly because the difference can be very huge. Okay. Reading into the query plan a little bit more. You can see that the engine had to actually do a sequential scan. So that means it had to go through all the rows from the first one until it found the row with volume ID 200. Okay. And then you see some more details. Things like it had to do a filter operation to actually do that conditional check. And then rows removed by filter 815. So you can see it brought everything into memory. It actually scanned through, removed the 815 rows that it did not need to show you and then showed you the row that you actually saw. So yeah, you can see that if you go back to querying by ID, remember we do have, we do, we do have an index on the ID column. If we do that, and then I'm going to get rid of this. Yeah. You can see that it changes from a sequential scan to an index scan. Okay. And the time it takes is significantly less. So that's how using the query plan, you can see if your query is using an index or not. And depending on that, you can either add an index or remove index that is not being used. Now we're going to look at an aggregate function, which should be interesting. So let me go back to the book table and make sure that the only index we have is the primary ID index. Okay. Now what I'm going to do is do a select. Let's do max volume ID from book. Okay. And then we're going to do explain analytes on this if I can type. Okay. Okay. So this is an aggregate function, max some average all these are aggregate functions. What this is doing is a sequential scan on book to find the row with the maximum volume ID. That's exactly what it's doing. You can see it's taking point 202 milliseconds. Now it cannot use any index on volume ID because we don't have any. Right. So I'm going to copy the query and let's go ahead and add an index. So we're going to go to alter index. We're going to add an index on the volume ID column. And then you'll save. Now we have two index one on ID one on volume ID. If I go back and execute the query, you'll see it's different. Firstly, it's taking less time. And in the plan, you can see it has something called index only scan backward using book volume ID on book. So it's using this index now to get you the row with the maximum volume ID. And it's taking less time because we actually have, we actually have an index now. So I'm going to copy this this time and then put it, I don't know. Let's say over here for reference. And then let me go back to our table and then remove the index that we created. Okay. And now back here, we're going to do the same thing, explain, analyze, select max volume ID from book. Okay. And that's execute. And it's taking this much longer. Okay. So you can see this is width index. And this is without index. All right. Yeah. So you can see that with index, it's taking significantly less time because it does not need to do a sequence shell scan. But without an index, like in this case, it actually has to do a sequence shell scan. So it takes longer. Okay. This is another example where given your query, you can see if the correct index has been used or not. All right. So we had that and we can look at a join to that might be something cool. Where is it? Or do we see order? Let's look at join and then we can come back to the order by. So, okay. Let's go back to book in our book table. We just have the primary key on ID. Right. So our sorry, we have the index on the ID field. And then we have the volume table if you remember, which also has an index on the ID field only. Okay. So now up to the foreign key here is the volume ID. Okay. So let's do another command. We're going to do a select star from book join volume on book dot volume ID equals volume dot ID. I forget. Wait. Let's try this out. I should cost you a select star from book join. Okay. That's a bit of a volume table. So it does have a volume ID. Okay. So what is this? No, upper amount is a given amount of your index with a tabcast ID equals book. Right. Great. Great. So this is going to be volume dot volume ID. There we go. So, yeah, we're just doing the join. There is only one row in the volume table. So you only see one row in the results. But now if we do the explain analyze, you can see a bunch of information about how the join was actually performed. One key thing here is you can see that there was a sequential scan on the book table. And then there was a sequential scan on the volume table. And then what else? The join type of join was a hash join. There are multiple ways you can join two tables. But the one used here was the hash join. And it took 0.37 seven milliseconds. So I'm going to note that down. And this is without any index. Just out of curiosity, what I'm going to do is add an index to see if it improves their performance. So if you take a look at the query, we are doing the join on the volume ID field. And right now, both of the scan is being done sequentially. We're not using any index. So let's try adding two indexes. One on the book table for the volume ID field. And one on the volume table for the volume ID field again. So we're going to go to the book and I'm going to copy this. We're going to go to the book table. And then we're going to add an index on the volume ID table. And now we're going to go to the volume table and then add an index on the volume ID field again. Save it. And then we're going to run the command again. Okay. So now you can see we have slightly different results. I am going to copy this. I don't know if it changed or not. Okay. So with index, it's taking 0.05 milliseconds. And without index, it was taking 0.37 milliseconds. So you can see there is a clear difference in performance when it comes to the volume ID when it comes to doing the same query with the index and without the index. Okay. And you can see if you take a look here. We are doing a index scan on the book table and then a sequential scan on the volume table. Now your question might be even though we have an index on the volume table. Why is that not being used? The main reason is the volume table has only one row. Where is the book table is larger? I think it has like 1000 rows. So the engine is smart enough to know that there is no point in using the index for the volume table because there is only one row. So it just skips the index and does a sequential scan. Whereas on the book table, it knows that there are 1000 rows. So it goes ahead and actually uses the index to find the row that it actually needs. Okay. And then you have the index condition here. You have the execution time and the planning time. Okay. So yeah, join with it. And then last thing is order by, I'm not sure if that's going to be super useful, but we can, we can try it out. So if we do a select star from book and then let's say order by volume ID descending. Okay. If we execute it, you can see it's taking 0.530 and it's doing a index scan backward. Because we want them in descending order and our index is in ascending order. But the key thing to note here is the 0.53ZL millisecond. So we are going to write it down. Okay. Sorry. This is with index I believe. Now if we go back to our table and then remove the volume ID index. index. And then, I'll have to come back again. Explain and analyze the left star from book order by volume ID. Now without an index. And it's taking 1.276 milliseconds without an index, without an index. So you can see that it's taking like half the time with an index or double the time without an index. One subtle difference might be we can try it out. So let's go and add the volume ID index again. Okay. And then we are going to run the command and we're doing order by volume ID and let's do ascending. Ascending is taking 0.260 milliseconds and what about descending? 0.005. 0.005. Yeah. So it's very, very subtle the difference between ascending or descending. Because either way your index is being used, either it's being used backward or forward. Right. So. Yeah. You can see that when we're doing ascending, it's doing an index can using the index on book. And if we do descending, it's doing a backward because it needs to the index is formed and then in an ascending order. But so when you do an order by descending, it actually has to read it in reverse. But your engine is smart enough to do that. Yeah. So I think I covered everything that I wanted to hopefully this gives you a good idea about how you can use the explain analyze command to get so much more information about the query than you typically get when just executing it directly. When it comes to reading the, reading the query plan, it's usually you read it bottom up or in reverse. So. Ah, what's one of the more complicated queries. Let's do. Let's do from book. Join volume on book dot volume ID equals volume dot volume ID. Yeah. So you can see that you read it like inside out. So the first thing this did was do the index scan using the using the book volume ID on the book table and using this condition. And then you did a sequential scan on the volume table and then you did the joining. Right. So I'll link you to a very cool article that I read, which was this one from top bud dot com. I'm going to link this article in the description below. The, the person who wrote the article goes into much more details about how you can read a query plan. And I think it like they have some very good examples of more complex queries. But hopefully the video gives you the basics of how to use explain analyze and you can start to add this into your workflow whenever you have a query that's not performing as well as your cottage. Okay. With that being said, really appreciate you watching the whole video. If you have any questions, please leave them in the comments below and I'm going to try to get back to you as soon as I can. And yeah, I have a few other videos coming up later this week or next week. So yeah, stay tuned and I'm sure those are going to be helpful too. Yeah, with that being said, I am going to catch you guys in the next one. Bye bye.