‫What's going on, guys?
‫My name is Hussein, and in this video I want to discuss indexing, database indexing.
‫It's a very interesting topic and I think everybody can engineer specifically if you are involved with
‫databases and relational databases, any kind of databases, you need to know what indexes and how indexes
‫work and what is the benefit of indexing.
‫So on this video will describe what is indexing.
‫We're going to work with indexes, we're going to work without indexing, and we're going to see how
‫the performance differ, whether you have an index or you don't have index.
‫So first of all, what is an index?
‫An index is is a data structure that you build and you assign on top of an existing table that basically
‫looks through your table and then tries to analyze it and summarizes so that it can create kind of shortcuts.
‫They the best way I can think of an index is like, if you know these, Fairvale, if you ever seen
‫a secretary's hand bulk or phone book where you have like thick big book and it is labeled, there is
‫like a label colored A, B, C, D until Z.
‫Right.
‫So the the letter A. starts with all the companies phone books that that start with the letter A and
‫B..
‫So with all the B's.
‫Right.
‫And C's all that.
‫So if you want to look through, find a specific company name, phone number and you know like oh is
‫it, I don't know, SQL Xebra.
‫So you go immediately, you go to the Z, right.
‫And then you start searching there.
‫That's the simplest thing I can think of that and X Y and basically the index guys are are are two types
‫so far that we know of something called B three and LSM trees, and I'm not going to go through that.
‫The depth of how they are constructed.
‫This is outside of the scope of this video, but essentially it's a, it's a data structure and you
‫can look it up.
‫It's basically allowing you to search very effectively.
‫Right.
‫And you find quickly what you're looking for.
‫So guys, here I have a table called employees with around 11 million rows.
‫That's a quite large table.
‫So I have an ID field is integer.
‫It's not null and it's sequential.
‫So every time I enter a row, I don't touch this field and it will automatically insert and increment
‫the value.
‫And it's also a primary keep.
‫That means unique.
‫Right.
‫And it also has an index by default.
‫Every primary key has an index by default, and it's a B three index.
‫So that's what I have here.
‫And I also have a name field on this, which is we do have an index is just a bunch of characters,
‫nothing fancy.
‫It's not really the actual name.
‫I just spent some time generating a hundred million random strength's.
‫Right.
‫So so now if I, for example, to clear this up and do select ideas from employees where I'd equal thousand,
‫I will return the thousands.
‫I'd write that.
‫That's not fancy.
‫But if I do select start, I will get everything back.
‫I.D. and name and one is a name, so don't don't pay attention to actual name is just this drink and
‫that's the idea of the strike here.
‫So guys, what I want to do here is I want to go through a steps of different queries.
‫I'm going to show you the performance of each of these queries in order to do that in Postgres.
‫That's the database I'm using, by the way, Postgres, I'm going to do explain, analyze, which will
‫actually explain the query, then I'm going to do and I'm going to tell me how long it took.
‫Right.
‫Which is which is really neat.
‫So I'm going to do a select ID from employees where ID equal, let's say two thousand because the first
‫one is cached.
‫I don't want to mess up the statistics.
‫All right.
‫So what happened here guys?
‫Let's let's decipher what happened.
‫It says, OK, your predicate filter, you're trying to find ID two thousand.
‫That's the employee with ID two thousand and an order.
‫I find that I didn't go through the actual table, 11 million and I searched sequentially.
‫No, I actually scanned the index, which is way faster because the index is always smaller than the
‫actual table, because it tells you that OK, it is structured in a way so that if you search and I'm
‫going to put an animation here a little bit, you can see like if I went if I'd idea number two thousand,
‫start searching, OK, two thousand is between four thousand and and one thousand.
‫So it was OK, let me go to this three.
‫So it goes you go to another three and then search for from another between this number and this number.
‫So until you actually find the number.
‫Right.
‫That's how Beatriz's basically work.
‫Right.
‫So now what I did, I only scanned the index, which is very, very effective.
‫Right.
‫He pitches.
‫That's another interesting thing.
‫So says the value that I actually queried, which is the ID.
‫I did not have to go to the heap to fetch this information because the ID, since that's the only thing
‫you're selecting is in the index.
‫So I just pulled it in line.
‫That's called inline query.
‫If the information is available in the index, that is the sweetest query to our database engineer.
‫If you can put as much information as you can in the index so that as I, as I scan the index and you
‫only select the ID, that is the sweetest thing you can do.
‫So the whole thing took point six millisecond and to plan the query and to to study what we go with
‫the I the the fact of oh should we use the index or should you scan the table is a query is a decision
‫and that's called the planning time.
‫The execution is that is the actual work to go and do the work.
‫Right.
‫So let's spice things a little bit.
‫What I want to do here in this case, I want to.
‫I want to select 3000 in this case, and you can see that point one point eight, what I want to do
‫is I'm going to change the ID five thousand.
‫And instead this time I'm going to ask for the name and compare that cost.
‫Here, guys, look how long it took.
‫So selecting only the ID, where do you know the idea, which is a Cassilly query, if you ask me,
‫it's a little bit of silico because you know the idea, why are you just like me?
‫But it's just for science.
‫So we're doing this for science.
‫So it's pretty cool, right?
‫Point one milliseconds, less than a millisecond, which is quite fast.
‫However, when we actually selected the name, which is a column in the table, it is not the column
‫is not in the index.
‫It is on the table.
‫It's in the heap.
‫It's in a different data structure.
‫Right.
‫And in this case, it took us to thinking two and a half milliseconds, not not seconds, two and a
‫half millisecond, which is which is quite fast, but it's still there.
‫It may speak.
‫It's a little bit slow.
‫This is a slower query.
‫And you guys need to understand when you execute these squares what it is really mean, because I found
‫the ID in the index, but I had to jump into the table row on disk, which is a different structure.
‫By the way, the table is in in a different place than the index.
‫The index is a data structure and the table is a different data structure.
‫Right.
‫Table is actually the heavier thing.
‫We try to avoid going to the table as much as possible because there's eleven stinking million rules
‫there.
‫So we try not to touch it as much as well, but sometimes we do.
‫So what we did is we found the ID and then we actually tried to go and seek to the desk.
‫I mean I haven't this is this or that is not seek.
‫We go to the page that has this information and we retrieve it from disk.
‫So that's another read from that.
‫So it's a little bit slower, took us to milliseconds.
‫And obviously if I execute the same thing again, it's faster.
‫Why?
‫Because caching because we cache things, cache.
‫There's so many caches going on, SSD caches, the control is the caches, the database caches, everyone
‫caches.
‫So God knows what wise is faster now.
‫But that's why, because I'm executing the same query.
‫But if I change this to something else,
‫I might not get it right.
‫But you get the idea like two milliseconds again.
‫All right.
‫So now I want to spice things a little bit.
‫I'm going to do explain, analyze, select ideas from employees where name is equal zests.
‫Let's do it, you guys, are you feeling it?
‫You feeling it right?
‫That was slow.
‫That was that was deeply slow.
‫Why?
‫Because that's what the name column does not have an index.
‫And that means the only way to actually search through the name the value Z.
‫S is to actually go one by one and do a sequential scan on employees table.
‫That's the worst thing.
‫It's called the full table scan.
‫You want to avoid full table scans as much as possible over tries to be a little bit smarter by executing
‫multiple threads, worker threads and does does the sequential scan on parallel, which is still a good.
‫So the planning two point eighty three milliseconds, which is not much because.
‫Well, does this does this column, which is the name equal, this filter?
‫Does this have a workload that does this have an index?
‫No, it does not have an index move on full tables.
‫It doesn't have a choice.
‫Right.
‫So it went through and scanned.
‫11 million rows were not necessary.
‫Well, yeah, it isn't.
‫So they have to scan the whole thing.
‫Otherwise, how do you know that?
‫All of them.
‫Right.
‫You have to check every single one of them, which is the slowest thing.
‫How long it took, it took three seconds.
‫Yikes.
‫See, that is so slow.
‫Yeah.
‫So it is pretty slow, guys, to do this.
‫Another another thing that we notice all the time, as I used to do this all the time without knowing
‫this, that is a command and sequel called like like if you don't know, hey, is this before and after
‫you just add this percentage like this is the war squarish.
‫Why?
‫Because it is literally has to go through all the rows and does matching in this case.
‫Right.
‫It doesn't really matter because we don't have an index began to show you with an index.
‫How does it does it do it right.
‫So what this does is it will return all rows that have anything in the beginning, anything of the end,
‫but has to have Z capital as small in the middle, slow to go one point one one second to do all that
‫stuff.
‫But yeah.
‫So 11 million row.
‫But we had two workers to do that work for us.
‫All right, guys.
‫So what I want to do here is actually going to create an index on I'm going to name it employee's name
‫on employees and it is on the name column.
‫So this was going to take some time to create.
‫Why?
‫Because guess what, guys?
‫It is building the B three bitmap index on top of all the stuff.
‫Right.
‫So it has to fit all the 11 million rows and does all this magic to actually create the fancy
‫index.
‫So that's a way for it.
‫All right, guys, we've finished creating our index.
‫So now let's try the same query that we have done just with all the like, just directly equal.
‫So now if I do this query, look how fast it is.
‫Still a little bit slow.
‫Forty seven millisecond.
‫But damn, that was quick.
‫Why?
‫Because now we're doing a bitmap HEB scan on employees.
‫Right.
‫So we're using the actual bitmap index scan on employee's name.
‫So we're actually scanning the employee's name index that we created.
‫And this is way faster because we have fewer rows to work with and we're going to actually jump between
‫few rows until we find exactly what we are looking for.
‫Right.
‫And obviously, guys, the same thing applies where A, if you only PILT pulled the name, which is
‫already in this index, is going to be faster than pulling the idea in the name.
‫I'm not quite sure about this.
‫Maybe this actually doesn't hit the heap because the index, the primary key I know this is a MySQL.
‫I'm not sure about Bosco's, but the primary key is usually stored with every single index in in in
‫in other databases.
‫Right.
‫So the name index here, the primary will be store.
‫So pulling the primary key from the name index will be fast because it's it's right there, right with
‫it.
‫So however, if you're if you have more than other tables, then we had to go to the table structure
‫and that will be a little bit slow.
‫All right, guys, so what will happen if I add like here?
‫What do you think will happen?
‫Back to this local area, back to the exact same local area.
‫Guys, why let's call this again as the let's do something else so we don't have we had the cash like
‫this back to the same slow query.
‫Why?
‫Because we could not scan the index.
‫Why?
‫This is this is a lot of people make this mistake.
‫Yeah.
‫The name column has an index.
‫But what you did is you're not actually asking for a for literal value.
‫You're asking for an expression.
‫And there is no index that will satisfy this expression because we cannot search the index on this on
‫this expression because this is not a single value.
‫So the planner quickly detects this, says, OK, planning time, we check less than then.
‫Sorry, the filter sucks.
‫And that's why you have to do explain on your queries and see why are they actually slow because of
‫this?
‫Because look at this.
‫We did a parallel sequential scan.
‫Again, parallel.
‫Not all databases do parallel scanning, by the way, but possibly still.
‫We we had to do it.
‫We had to go to the thinking table and scan my 11 million rolls if I have a billion rows, is going
‫to be even slower than that.
‫So we are going through all that stuff.
‫And I'm going to explain all this number and then other videos, because I don't want to make this video
‫longer than that.
‫I'm going to make an advance video discussing these actual numbers.
‫What do they mean?
‫All that stuff.
‫But but yeah, we did a parallel scan on employees.
‫Guys, look at that is so slow we could undo in lining.
‫We couldn't do any optimizations.
‫This is an expression, obviously.
‫So that's we had to we had to do all that stuff.
‫So we took a hit.
‫We took a hit despite us having an index, guys, despite X having an index.
‫So, guys, that's very quickly indexing in a nutshell in a very, very quick video.
‫I wanted to explain of that.
‫Yeah, having an index does not mean that the database will always use it.
‫It's going to plan and according to the planner, will decide to use the index or not.
‫It's up to you as as the engineer who is executing queries against the database to actually give hints
‫to the database to actually use the index or not.
‫This is a bad query in general.
‫Select Star is a bad query in general.
‫Why?
‫Because if because if you think about the cost to go to the actual disk and pull all the information.
‫That is expensive, right?
‫Really, really depends on how much like, let's say you have a blob or column on the field, on the
‫table that is expensive to pull up.
‫So if you don't need it, don't ask for it.
‫Right.
‫And only ask for things that are absolutely there.
‫And if you can ask for things that are in the index.
‫Right, I'm going to talk about that in another video where is like a multi column index where you can
‫add other columns in the index that you're about to create.
‫This is called In Lining where where as I search my beautiful index, my neat, efficient index, I
‫want their name, for example.
‫Right.
‫That's bad example, but I want the name.
‫I'm searching for the ID and I don't want the name or the name is right there is just literally sitting
‫in my index.
‫So I just put and retrieve it to the user.
‫I don't have to go back on disk and find the location, seek and desk if that's a mechanical drive or
‫pull the page in case of SSD.
‫All right, guys, that's it for me today.
‫I'm going to see you in the next one.
‫You guys stay awesome.
‫Goodbye.