‫Hey, guys.
‫In this lecture, I'd like to discuss the difference between key versus non key column indexes.
‫In a nutshell, when you want to create an index in both grass or really any other database, you specify
‫a field or more than one field to index to create that B3 on top off.
‫Right.
‫And that key will be used for searching purposes.
‫And when you do a search on that index, you build the database planner or will use that index and search
‫for that.
‫And the entries that found in the index will contain roll pointers to point back to the table to fetch
‫the actual rows.
‫So what we are where we have more than one field there.
‫All right.
‫Nunca index is our abilities.
‫I don't believe it exists in all databases, but it definitely exists.
‫And Postgres, where you can effectively create an index and create none key, include some columns
‫inside the index not to search for what to include.
‫So in this case, if you select like you created an index on the grades field raid.
‫And you can optionally include other fields in the index, like the ID.
‫So now if you select and search by their grade and you want to select the I.D., then next doesn't have
‫to go to the table because everything is literally in the index.
‫Let's explain those and see the performance benefits that we get in all of them.
‫Let's jump into it.
‫So I'm going to include in this lecture sequal file that will be creating the students table here and
‫the studier table.
‫I deliberately added kind of a big table with a lot of fields to demonstrate the expense of doing this
‫stuff.
‫And we have a primary key here.
‫And this table actually have 50 million records.
‫Let's do a select star for all the students.
‫Boom.
‫Yep.
‫Fifty million rows.
‫Make sure if you're using Docker to create to run this container with plenty of shared memory and do
‫a vacuum so that we update all the stuff, make sure the visibility map is up to date so we can get
‫the optimal query.
‫Right.
‫Now, that being said, right, I don't have any indexes here.
‫Right.
‫So we're going to do is do an let's do this query, explain, analyze, explain so we can explain the
‫query and also tell us how long it took.
‫I'm going to do select idie and grade.
‫I'm going to I want all the students that God results between 1995.
‫Right.
‫So from students where grade is greater than, let's say, 80.
‫And less than.
‫And G is less done.
‫Ninety five.
‫So you can effectively select these range of grades of these students.
‫And I'm going to order by G in a descending man or so that the largest grade goes first and all the
‫way down.
‫Obviously, this is a little bit of an expensive query because you're going to select a lot of rows.
‫Usually this is not recommended, but we're showing the capabilities here.
‫So right of that is going to take a long time.
‫And he's going to tell us how long.
‫All right.
‫So the query is finished.
‫And it took a C.
‫It took 21 seconds, 22 seconds, actually, to finish and look at it, what does it have to do?
‫A sort?
‫Why?
‫Because we're doing order by grade, right?
‫Because I want to sort these grades that I results.
‫Right.
‫It didn't have any index on the field that I'm searching because I don't have any index there.
‫So it had to do the parallel sequential scan.
‫Sure.
‫Spun up multiple processes to do that.
‫Hit the desk directly fitting on all this beautiful stuff that emerged from desk is so slow.
‫We're going directly to the data file on the table and pulling pages, all the junk.
‫Right.
‫There is so much junk there.
‫There is grades that are 10, 12, 13, and we have to filter this out or remove and filter this out.
‫Look at the look of this.
‫This is what baneful rows removed by filter.
‫Right.
‫So we pulled pages and we had to discard, what, 14 million is.
‫That is our 40 million rows are useless.
‫So we pulled pages with rows, but they are just basically filled with stuff that we don't want.
‫And that's what the index hoped for effectively.
‫So and you can you can you can do something like more elastic.
‫You know, it's like, oh, no, I just want the first thousand students.
‫And this should run faster, but not always in a sequential scan.
‫This is not nearly necessary to is going to take faster because it doesn't know.
‫It needs to search search until it gets the results.
‫Right.
‫So, yeah, it took 70 seconds or three seconds faster to pull for only a thousand students.
‫All right.
‫So that's that's obviously slow.
‫So the first thing we need to do is actually create an index.
‫How do we do that?
‫Create index index on students G.
‫So now this is the fields.
‫I want to create a an index on G so that we can order by the G and filter on the G.
‫Because that's what we're doing.
‫We better be a little bit effective when it comes to that stuff.
‫And that's because the indexes are by default ordered.
‫And we love in databases.
‫We love things that are already ordered for us because it's predictable.
‫And instead of randomness, randomness, there's chaos.
‫That was deep.
‫All right, so we have an index.
‫Let's execute my beautiful query again.
‫Let's assume the original one.
‫So what we'll do now we're querying Biji greater than 80, less than 95 order biji that we'll use the
‫index.
‫We can't guarantee it because the planner has a mind of its own.
‫But let's say they're going to decide to use the index to search for things.
‫But look at what we asked for.
‫We asked for the ID.
‫Where is the I.D.?
‫Well, the I.D. is actually in two places.
‫The I.D. is on another index because the primary key and it is also in the heap as another field.
‫So this has to go to the table again to pull the IDs.
‫So it will use the index.
‫It should use the index to pull whatever it needs to pull.
‫Right.
‫Find the candidate rows and then jump to the table to pull the ideas of the students.
‫Let's see how fast it is.
‫So it didn't help much.
‫We have an index and we still took 16 glorious seconds to fetch this.
‫All of the information, while still better than 21.
‫Right.
‫But still slow.
‫Why?
‫And main reason is really we did an index scan backward.
‫You might say I sent Y backward because we said or there by descending.
‫So which is at the end.
‫So we we did a search for 80.
‫We did this condition on the index.
‫And we found one entry.
‫And since this is a P plus three, we're going to release it's all nicely tucked in together all the
‫values.
‫That's the beauty of the B plus three.
‫Right.
‫So all the values are next to each other.
‫We found all the candidate rows is actually this this many arrows that we found.
‫It took us 42 millisecond.
‫This is not really an millisecond, but this that cost 42 cost, whatever this unit is, to find the
‫first one.
‫And fifteen point eight to find the last one.
‫Right.
‫So that's like equivalent to a millisecond by some.
‫Some people say that's not it's not ready millisecond.
‫And so.
‫Yeah.
‫And is slow still slow?
‫Uh, Hussein, that's a ridiculous query because nobody's going to fix all these roads, right?
‫I just want a thousand students.
‫Well, it is much, much faster this time.
‫And that makes sense because it took a point five milliseconds.
‫So bear in mind that we won't be always this lucky to to take point four or five.
‫Right.
‫Because usually this this queried are hitting the cash, the operating system cash, because we did
‫execute a lot of quarters before.
‫So if I do like something like analyze and then I did Buffer's.
‫Right.
‫You can notice that look at this shared hit 20.
‫That means all the things that we queried are cached.
‫That is why is so fast.
‫That's why a lot of you asked me a good question, Hosain.
‫You said you did this query and so, so slow and you did it again so fast.
‫Why?
‫Because the operating system does all the magic of caching all the time.
‫So we need to track the operating system a little bit.
‫So let's just little bit change the equation.
‫Change.
‫Let's just do this.
‫Give me all the students from, I don't know, 10 to 20.
‫So this should look at that.
‫We're doing a little bit of reading now.
‫We did hit the end and at the end we had the audio.
‫We had the disk is not we no longer do a shared hit.
‫Still, we did a few head pages head, but it's better.
‫So that's why we got up to six milliseconds.
‫Right.
‫But still, there's a lot of pages that are cached.
‫And so that's why it's as absolutely blazing fast.
‫Right.
‫We need to find a way.
‫So if I if I change this to 2000, a doc, 25 minutes, I can I bet that if I remove this limit, it's
‫going to be faster now this time.
‫So a little bit better.
‫Right.
‫Look at this.
‫We got 90000 I to desk.
‫We went to this 90000 times effectively and we took 11 seconds to do that.
‫Yikes.
‫All right.
‫So we know why.
‫We understand how the system works, because it's just an index of scan.
‫And the reason is because you're selecting the idea which we have to go to the table for.
‫And that's most of the cost.
‫The the eyehole cost to go to the table while we already living in the index.
‫So the idea is, how can we do a pure index only scan?
‫Let's drop this index that we just created.
‫And we're going to create a beautiful new index that is on the G.
‫But we're going to include a non key index here.
‫Nunca columns or shiitakes on students G.
‫But we're going to include do.
‫Why?
‫Because that's a very common execution path that we're doing.
‫That's where you as a back engineer, you really need to think like what columns are you selecting?
‫And based on those columns, you create your indexes effectively.
‫That's great that.
‫So it finished a little bit longer.
‫And that is makes sense because.
‫Well, the index is larger now because we're including more stuff in it.
‫The ID field, which is in a 32 bit, I believe.
‫Right.
‫And has to pull this values and store it.
‫So obviously it's going to take more time.
‫But what happens if I get my beautiful query now?
‫Let's do just the normal one.
‫Look at that.
‫It took only four seconds and we had to go to the desk myself.
‫I was in.
‫You just said this is an index only scan.
‫Right.
‫Built in an index only scan, which is nicely done here.
‫Right.
‫It did said index only skin.
‫So in this index only skin, we didn't have to go to the heap at all.
‫He pitches equal zero.
‫So this rule, you see this line, if only you wanted to see index only scan.
‫If you see he pitches more than zero.
‫That means we had to go to the heap for any reason.
‫Right.
‫One reason is that you didn't run vacuum.
‫That invisibility map is out of sync, right?
‫You did.
‫You didn't.
‫You made some changes.
‫You updated the rows.
‫So you have to do a vacuum in order to update all this stuff.
‫So we did a read for some reason.
‫We had to dirty three pages.
‫I'm not sure why.
‫Right.
‫But again, even selects in most cases, sometimes does write all the time.
‫Right.
‫To update these visibility things, the hint bits, as they call them.
‫So it took four seconds.
‫And and I bet if you execute it again and again, guys, this is the entire thing, huh?
‫I'm not doing any limits here.
‫I pulled that everything that's between 10 and 20.
‫Right.
‫And it took one second this time, because that's what we cached more.
‫We had more pages.
‫Right.
‫And we only had to go to go to the iOS.
‫Six hundred and two times.
‫And you might say, why?
‫Why are we going to do this?
‫I thought the original reading, the end guys, the index is still on disk.
‫Right.
‫It's a large is a value or very large index.
‫And we have to hit the audio because it lives on the disk.
‫It will be very beautiful if we can fit the index entirely in memory.
‫That's the ideal situation.
‫But unfortunately, that's that's not always the case.
‫I have a beer.
‫I have an old MacBook with 16 gig GB ram.
‫It's a docker and container that is not really running with beer.
‫Bad default.
‫So you want really.
‫And I think I have only one GB shared memory shared with this guy with Postgres.
‫But it's always important to run, by the way, always run by by a few that if you run back, you verbose.
‫Right.
‫And on the students table, that's just run it.
‫This will tell us, like, if there is anything wrong, if you got any errors here and you have to do
‫verbose, it will tell you that, oh, by the way, there is some bad things.
‫I have to remove all this dead tables, Bedros.
‫So you guys have if you want to replicate this lecture, you have to run vacuum verbose students, right.
‫Or even just from vacuum verbose, full or altogether.
‫Right.
‫This will help you update all the pages, the visibility map, anything.
‫It will basically treat Posterous as as as if it's a production ready to be read.
‫Right.
‫And the moment you start updating fields and adding values and inserting rows, then you have to do
‫a vacuum again.
‫I mean, vacuum is or run by default, but it's always good to run this.
‫So you can see that we got much better performance.
‫Let's do a limit now.
‫And this is even a point five, right, is really fast.
‫So, guys, at the end of the day, you seeing the performance is not really huge.
‫If you think about it, it really depends on your use case.
‫The larger the table, the better non key indexes will be.
‫Right.
‫But.
‫Keep in mind that the index grow larger, if you include stuff in it.
‫And if you include stuff in it, it can't fit in memory.
‫And if it can fit the memory there, it will be put on desk.
‫And if I put an index, that's a read that we have to read the index from the desk to do the queries.
‫So learn about this.
‫You don't have to implement that.
‫Just know that it exist.
‫Put it in your back pocket as a tool.
‫I'm going to see in the next one.
‫Hope you enjoyed this.
‫All the code, the sequel stuff is going to be included in the lecture resources as he.
‫And that's one.
‫Goodbye.