‫Hey, everybody.
‫It is November 2021, and you guys have submitted a lot of good questions in the month of October.
‫So I thought I'll make another video.
‫Another lecture.
‫What I answer those topic question that I pick that I felt that are interesting and then answer them
‫in detail.
‫And I really enjoyed making the October video and you seem you guys seem to also enjoy it.
‫And as it gives you this, you know, it's almost like a live experience.
‫Almost not quite.
‫But how about we jump into your interesting questions?
‫I'll try to answer as much as I can, but usually I always give it lengthy answers, right?
‫Then all the questions that I'm going to go through are all the answered.
‫Yeah, but I'll I'll provide my own in-depth answer when I when I go through them.
‫So one answer seventy one to question 72 hours ago from Alex when the question goes as follows.
‫Why can't I get bitmap index scan was the last equal statement.
‫So this is this particular question is very related to one of the lectures.
‫So they were trying to go through the lecture, which I explain bitmap index, scan, index, scan and
‫tables cam.
‫And they were following badly.
‫They're following my lecture and they execute inadequate, but they were given different results and
‫this is absolutely no more different result in terms of the plan.
‫The database depends on the version in the patch, depends on the nature of the query print of the the
‫statistics of the square of the table.
‫At that point, we'll give you a different plan.
‫What a better give you the same result.
‫So we when I do something, when I show you something and I show you that that database at that point,
‫you chose that plan in version.
‫In the new version of Bosca's, it might decide to change that algorithm and take a different plan.
‫So just keep that in mind with that out of the way, this is actually explainable.
‫So here's here's the query they were executing.
‫And so here they are showing that the great stable, which is around a million row here and this is
‫where they were executing explain select name from grades where grade is greater than 95.
‫Can we expand the expand this?
‫I guess we can.
‫Maybe it's better this way.
‫Yeah, where the grade is greater than ninety five, right?
‫And the idea is is less than 1000.
‫So just to go through the table again, the schema itself.
‫And thank you so much, Alex, for giving us fantastic screenshots for us so we can go through this
‫in a nice manner.
‫So the grade schema is like this.
‫We have an ID field and this is the default primary key on this particular table, right?
‫And shows here grades primary key, right?
‫So we have a primary key that means it is unique by default on the feel and there is another index or
‫on the grades for these students, and it effectively has a just a normal index, and they are including
‫the ID as part of their index here.
‫So this is a non key column and this is a that key column.
‫So the query that query goes as follows.
‫This is a where G is greater than 95.
‫Give me all the students that have their ideal is the thousand and only of those thousand.
‫Give me those who scored more than 95.
‫All right.
‫The query doesn't make doesn't need to make sense, right?
‫But just understand what is it doing?
‫And every time they execute this, they are getting an index scan.
‫Right?
‫So the question is why they're getting an index scan where they're selecting the name of those tools.
‫That's or they're not.
‫So the question is why right?
‫As opposed to a bitmap index scan?
‫So what is the best method next year?
‫I would explain it, but it doesn't hurt, right to explain it again.
‫A bit of index scan is, is is a technique.
‫I think it's especially in Postgres, but I think other databases implement them where if there are
‫multiple index indexes, the database will scan the first index, but not to to get the actual rose
‫from the index that satisfies a particular query.
‫No, it finds that pages that might have those results right?
‫So if because one page goes, you gotta watch the video?
‫I'll just add it in October.
‫This is a very critical video where I explain what it is a rabbit or a page, a table owl row or IDs.
‫All these things.
‫These are fundamental building blocks that you need to understand.
‫So when I mention them, they make sense.
‫So what page have many rows, right?
‫So the index will actually include two pieces of information when you find a particular value if it
‫points to the I.D. and opposed to which page this row lives in, right?
‫A bit of index can only takes note of the pages, so it will scan the index, find all the candidate
‫rows, but discard the rows and find the pages instead.
‫Right.
‫So we're going to come to why the pages will definitely have more rows than the actual values that we
‫pulled, right?
‫They were going to have more stuff and more stuff that we don't need.
‫Definitely.
‫But here's the thing.
‫It builds a bitmap kind of index right of of of a like a bit gate, which says, OK.
‫Oh, and this gets us all zeroed out.
‫And if it detects like that as a result of this scan, I found Page Zero, I need to pull Page Zero.
‫Page one.
‫Page 17, page 19.
‫That's what it does.
‫It does nothing else.
‫All right.
‫And then.
‫It scans the other index, says, OK, I'm going to now scans grade 95, right?
‫Whatever, that's a given index, it does exactly the same thing, says it.
‫Take note of what pages it needs to pull.
‫So it's going to appeal.
‫So we said zero, one, two and 17 for the first index.
‫The second one is, I don't know, three, four, five and 17, let's say, for example.
‫So the second index gave results three, four or five pages and Page 17.
‫And the first index gave one two three.
‫And Page 17.
‫Now that the Polska says, OK, well, we need to end.
‫And is that the best thing you can do in a query and eliminates a lot of garbage?
‫And in this particular thing, we end those results when you end means the rule must exist in both index
‫search space, right?
‫So if you have a road that exists and you found it as a result of Page three, but not in the first
‫index.
‫Then you just eliminated.
‫As a result, the only page that shared between those two indexes is Page 17.
‫So you, the pollsters, will discover Page zero one two and will discard page three, four or five.
‫Discard means eliminated from the result and just literally takes these stupid moves and does an actual
‫and between them.
‫So one one one, it's going to be ended with zero zero zero and you're going to end up with a with the
‫new bitmap with only bit 17, which to respond to the page 17 to be pulled.
‫So you go and fetch just that page.
‫Beautiful.
‫So now when you go back, this is quite a bit Mebane next.
‫Again, this is not enough.
‫You go to the heap now you have to go to the heap.
‫And I talked a about what a heap is.
‫Go watch that video.
‫And now when I jump into that heap and pull page 17, I have to reapply those filters.
‫And this is called usually when you see here, it's called filtering, right?
‫You apply a filter on the heap itself.
‫So you pick up page four and 15 and then now you pull all the rows and page 17 and then manually goes
‫through these rows and then filter out, OK, I need I'd thousand and whatever I need, I'd be less
‫than a thousand and great more than 95.
‫And since you have the G and the ID in the heap, you have everything you can.
‫You can definitely do this filter.
‫That's what's that's what a bitmap index can.
‫So sometimes best was, guess will decide to do that right to to optimize certain situation.
‫This seems like a way, a case where it should, right?
‫There is an end.
‫There are two indexes to scam.
‫Why did Postgres didn't use the best method to scan?
‫So what?
‫What Puskas thought here is, you know what?
‫We could be smarter than that because bitmap index scan can be expensive because you're scanning to
‫indexes, right?
‫Look what Bosca's did here, bosses look at this and says, Oh, wait a second.
‫You're querying you want ideas less than a thousand and Postgres actually have statistics.
‫What is it?
‫Ideas is a primary key when you say.
‫It is a primary key that definitely means and will have some statistics.
‫OK.
‫Is this a positive integer?
‫A. Well, that means the values must be that we're going to look at between zero and nine nine nine
‫nine.
‫We will only need 999 rows at maximum.
‫So the moment pollsters realize that is going to only work with nine nine nine rows it automatically
‫flips is what is that?
‫I don't need to do a bitmap index cup.
‫Let me go.
‫I don't even need to scan.
‫I use the other index.
‫I don't care.
‫Let's scan the I.D. The primary key.
‫Pull all those pull over.
‫Less than a thousand.
‫So now are you going to have a lot of rows, right rows from zero to 99?
‫But not all of them is the actual results that you want.
‫Some of them will have grades greater than 95.
‫Some of them will have less.
‫Right.
‫So you need to be careful there.
‫So what process does?
‫OK, now I'm going to pull all the pages.
‫All right.
‫What pages do you need to fetch, right?
‫Because I need to go to the heap anyway, right?
‫To to do this results, right?
‫Because they need the name.
‫The name is not in the primary key idea of index.
‫So now it takes a mark of what pages need to pull and goes to the heap and pull those pages.
‫And it should be a page or two, or maybe less.
‫It depends on on on the size of the rows and all that stuff and then pull these pages.
‫Now what it does.
‫Just go through them and then literally just applies the square.
‫OK, since we infect the heap.
‫We have that the great fields.
‫Let's just filter the manual.
‫And this was way faster Bosca's deciders than actually using bitmap index.
‫Because because the result is so small, if Alex increase this to more than a thousand, I don't know
‫what the number is, to be honest, because it's an implementation detail.
‫Who knows?
‫It's heuristics at the end of the day if if it means less than 10000, 20000, 30000, then pauses or
‫thousands, a lot of rows.
‫I'm not going to scan an index and then go and fetch all the rows.
‫Maybe I can eliminate some some pages by doing a bitmap index.
‫All right.
‫So that's one question that I found interesting.
‫Apologies for that change of scenery and the audio quality.
‫I'm currently troubling and my video was cut due to something.
‫I had to take care of something, but I had to finish the lecture for you guys.
‫The Q&A for November 20, 2021 and we have a very interesting question here from Rakeysh, and it's
‫a three part question.
‫So we're going to and try to answer this question and then just call it a day.
‫How about that?
‫Because it's just there is no time to go through more questions.
‫And guys, let me know, how do you enjoy these kind of content?
‫I think it's beneficial.
‫So the question goes as follows how to choose the order of columns to create a composite index.
‫So I say in my question has three parts.
‫If I have a query that does a filter on, say, 10 columns joined by an another is advisable to have
‫a composite index on all 10 columns.
‫Let's assume for simplicity that this is the only query for the stable.
‫So think about this right now.
‫I already answered this an hour ago, but it's always good to kind of add more details or not explain
‫things like that.
‫So when you create an index, especially a composite index, a composite index is just an index of multiple
‫columns.
‫And the way it is designed, it is, as will be these columns, all the values of these columns will
‫be added together as a tuple in the index and index accordingly.
‫So means it really depends on the data type of what are you indexing and the number of columns you are
‫indexing as well?
‫And this increases the index size, because if you're indexing a lot of columns, that means all these
‫entries, if this is a string as an integer float all adds up really to the size of the index of my
‫tails.
‫And what is a matter, the index.
‫So I have all the space I need, right?
‫I have one petabytes of disk drive and I don't care.
‫Well, we're not talking about really storage.
‫Storage is not an issue anymore.
‫The issue is the O right because.
‫You write all this law, whether a sentence is the boy as is dear, it's much better than hard drive.
‫What you want to minimize is this does this precious are yours because of an index is large and when
‫I'm large, I mean really large.
‫If you have 300 million rows and you are indexing 10 columns, then the index will be proportionately
‫large to the size, not going to be as large as the actual heap table, but it's going to be a large,
‫large index means that that that that structure won't be able to fit in memory.
‫Now, large indexes won't fit in memory, and if they don't fit in memory, they have to be put in desk.
‫And when they are put in desk to read and scan the index, you need to go to describe and you need to
‫read page by page or whatever outnumber the file system allows you to, and that gives you a finite
‫amount number of index entries to read.
‫So the larger the index columns, the more pages you need to read, and that slows that.
‫So that's the first thing is just the additional ISOs you want to do because of the increased site.
‫Another side effect of having many columns.
‫Again, I'm just saying, what is the side effect of what you're proposing wreckage, right?
‫Having an index on many columns and side effect is the right amplification that happens as it as you
‫update your table when you update your table.
‫Right?
‫You add in something you entry.
‫This index is touching a lot of column.
‫So any update to an existing row that touches this column, we'll have to update them.
‫Any new role you insert will have to have digital.
‫Well, it depends of these columns.
‫Are nulls or not.
‫The index might not need to be updated or if you have like, a partial index where you're only indexing
‫certain water clauses that also might not need to update index, but regardless, you need to update
‫the index in these particular situations.
‫So that also adds not necessarily to a performance point of view when you're updating the index.
‫So no to updated index, right?
‫It's just an extra i o to the right that is almost negligible and adds up a little bit, but in a batch
‫mode that slows things down.
‫But it's not negligible when it comes to one off.
‫That's right.
‫The side effect really becomes when you when you when you try to bulk update this right, you're touching
‫the index.
‫And that results in a lot of rights.
‫And that writes, especially in our replication
‫environment.
‫If you're trying to push all this right, then the bandwidth increases, bandwidth increases means and
‫more network latency.
‫And that when you think about that, that's what all were complained about with Postgres compared to
‫Michael Bosca's had way more write amplification compared to my single, for example.
‫So they went one route to minimise the write amplification caused network is precious and goes, I just
‫what I want you to think about.
‫Anything you do has an effect and you don't need anybody to tell you any of this.
‫If you understand the fundamental thing, when you actually get this course, you understand the fundamentals.
‫You don't need me.
‫There's just because, like, say, I can answer any question because I understand the basics and we
‫understand the bare bare metal things, you can just answer any questions just like, Oh, because of
‫this, this will result.
‫This will be the result is just it's just cause and effect, cause and effect.
‫So the second question is, is the query planner smart enough to arrange the filter in order that will
‫be subset of the composite index yet?
‫So for example, if I have a composite index on ABC, I always talk about that letter if I have a BCD.
‫The order does matter.
‫I've talked about that in the video.
‫The composite index has to be from left to right, and they will be built from left to right.
‫So if you're searching on, so he is asking like, what if I my work was started with C Equal 10 and
‫equal 20 and be equal 30.
‫It is a map and is interchangeable.
‫So have Planner better be smart enough to understand that c equal 10 and 20 is the same as equal to
‫any and equal to will that one that includes AWS and ends in that, obviously based on this great mathematical
‫formula that changes, right?
‫Right.
‫But but just discrete algebra stuff.
‫But when it's L and it doesn't matter, you can interchangeable.
‫So just don't worry about it as long as they actually search for a if your stuff is only C and B right
‫and is not included, this won't hit index right.
‫And you probably know this is their best practice for the order in which the column or therefore composite
‫index be chosen.
‫This or indeed, there is no best practice when it comes to these kind of things, like really, if
‫you're if you're querying from the left most columns, if you're counting a certain field and and that
‫field is the only field as being queried, I said just putting that to the left, because just on the
‫logic of the end of the data, what is the minimum subset of filters that you're quoting against?
‫Put that at the left side.
‫Call right you want you.
‫So that means you need to understand the nature of your query by the end of the day, the nature for
‫your queries of very, very critical that cut out.
‫That's why you back in and just have to work with the frontend engineers to know what kind of, I guess,
‫their front drink don't normally issue back squat queries, but just understand how that how that filters,
‫how the workers are being executed.
‫That's what I really don't like to use alarms because they exclude just abstractly leaking the back
‫end and execute codes that we have no idea.
‫It has a mind of its own right.
‫It hides the complexity and as a result adds more and more performance penalty that we engineer Mark
‫to see it right.
‫I just need to see everything.
‫I mean, you probably know this from my YouTube channel I really don't use or MS.
‫I don't like to advise anyone, I guess anything.
‫But if you are using em, right, use it.
‫But understand how it works.
‫Completely understand how it works.
‫You have to understand completely anything component that you use.
‫You really understand what is it doing if you are using it blindly just to save you a couple of lines
‫of code?
‫That's a, in my opinion, that's a bad practice.
‫Never use a code to like a library, just so you don't want to write the next couple of lines of code.
‫If you understand that this actually saves you this and you know how it works without it, and you can
‫build it without it.
‫Yes, absolutely.
‫I'm fine with using it, Armel.
‫Any framework for that map is just my problem becomes when you don't understand what what this thing
‫is doing.
‫This hides a lot of complexity.
‫But as a result, things like this complexity leaks to the front end all the time.
‫Like, You have suction Google that you'll find it.
‫You'll find my video on that topic.
‫Yes, so is it a good idea to create a single column with that 10 column index?
‫It really depends, right, how big it comes, really how how complex are the data types?
‫But you can try it and see if it's effective.
‫It affects you, but normally comes with a lot, right?
‫So if you really understand the queries, my guess is you want need 10 columns in.
‫Just because something is in the way close doesn't mean it has to be indexed.
‫Because when you do an end, especially the database.
‫Think about this.
‫Like, let's say, if you have if you're doing sequel 10, just six equals 10, you just eliminated
‫anything that is not C content, right?
‫But no, because you using an and.
‫This also eliminates a lot of other roles.
‫So now you're working with a subset of that of that table kind of right.
‫So now if you add to that another filter equal 20, then you just eliminated maybe another bunch of
‫rows, right?
‫Think about this how much rows are being eliminated, if if by here you eliminated?
‫I don't know by here, if you are left with.
‫10000 rolls.
‫I would say, yes, maybe just add another index.
‫Right, because now this will eliminate eliminated to three rolls.
‫But if you add these to the index and you are left with, I don't know, two rows.
‫This is absolutely useless because what two rows is going to be eliminated in one row?
‫Yeah, I'd rather just jump to the heap and fetch the page and then filter the extra one, even if it's
‫a thousand either.
‫A little bit.
‫That's OK.
‫So understand the statistics of that table, and I think I'll leave it at that, guys.
‫Thank you so much for watching.
‫Enjoy the course.