‫Welcome, everybody.
‫This is the Q&A for April 2022 for the the ABC engineering course.
‫We've been doing this for a few months now and it's been very successful and very useful for people.
‫And I believe it's always good to be like Samir Sumaidaie, interactive like this.
‫So we receive a good amount of questions on this course because it became very popular and it's only
‫fair to pick one or two questions every month to answer them in depth.
‫So I answer almost every question.
‫I to my to my extent and to my time allocated, you know, but some some answers you can just write
‫just he needs time to think about it and write about and some some of them, like, deserve, you know,
‫time like this and to to explain it better.
‫And this is one of them.
‫So this comes from Dominique.
‫And Dominique is asking about the implications of none key column indexes in real world database systems.
‫Would it be best practice to create and drop new indexes with non key columns for short term purposes?
‫Or would this be a decision I make once the setting up the table when I launch a new application utilizing
‫the table?
‫And the second question is, is it really advisable to drop indexes of tables which might have various
‫different use cases to different users?
‫So here's, here's what I want to say.
‫The the question, while we'll kind of relate to non key indexes, it's kind of it's a it covers all
‫type of indexes.
‫Is it do you need some time to create indexes just for specific applications?
‫Sure, you can you can create an index if you seen an app follows a path, a query path that is not
‫covered by an index.
‫You might want to create an index to do that.
‫The question is how often this AB is executed because the moment you get an index, you've got to go
‫with the maintenance of the index while the maintenance is not really that bad.
‫If that, if you have like a few million rows.
‫But if you go to two multimillion rows and more than them, the index maintenance becomes really a problematic.
‫What I want to mention here is the real world implications of non key indexes.
‫So nike.com indexes are usually created when you.
‫You have an index that is used in equity path and that query path.
‫Will efficiently use the index to get to collection of results.
‫No.
‫But then the query asked for.
‫Certain columns that are obviously not indexed most of the time.
‫And.
‫If this collection that is returned is significantly large.
‫Then with the database has to do is take the squeeze that have been resulted because we found that the
‫results are from the index.
‫Now we have to go to the heap or to the clustered table and query to pull the full pages.
‫No.
‫And in a heap based.
‫Databases such as Postgres, where by default there is no clustering.
‫You know what the index will do?
‫It will say, okay, here are the pages that I need, because it knows that this this index, this topologies
‫belong to this page.
‫So it needs to fetch all the pages in the heap and then pull these pages and then.
‫From these pages.
‫It will take the columns it needs effectively write.
‫In addition to the things that is selected and that is slow, right?
‫Because because you didn't you just use the index as a path to get to the rules.
‫And if these things are random and believe me, they will, because what would you find is a bunch of
‫tuples.
‫And these table are all over the place.
‫They're not as nicely ordered as in the index.
‫They'll be all over the place.
‫So you'll be calling all over the place.
‫You'll be having a lot of random reads to the heap.
‫And that is very expensive, and especially expensive in a clustered table where you're going to do
‫ab3 scans right to two because the on the primary key in order to fetch these pages or the results.
‫So it is it is quite expensive to do that and especially if it's one or two results, not big deal.
‫But if you're furnishing hundreds or thousands of results, then you are doing a see this random reads
‫on the table which starts as the random IO, which obviously hits the disk and slows things down.
‫You probably get a miss the cache or escalation other caches in the process.
‫So what do you do is take the columns that you would have selected and recreate the index with those
‫columns as non key.
‫This way when you do your nice scan of the index right and you found the candidate rows, you don't
‫you just didn't just find the candidate rows.
‫You found all the columns you need in line in the index itself, which is very, very powerful.
‫Now you're code is done.
‫It's an index only scan.
‫You don't have to go back to the heap to do do those random zeros.
‫Right.
‫Or the primary key clustered index to do these random files and your performance will be tripled if
‫not even more high, depending on the size of the results they're using.
‫And then you might say, I don't really return 10,000 results.
‫You don't?
‫But don't you have some queries that has select in Select and the inner selector returns a lot of rows.
‫But the other select just picks few one of them.
‫Right and then returns those some of these inner select queries can with a lot of rows.
‫So a few if you can do this efficiently, you will have minimize the is and so you things that would
‫have exacerbate the problem will be much much isolated and minimized.
‫So yeah and and key is definitely find it to close this and key indexes.
‫Said he was in production all the time that we have been.
‫I've been in this for 17 years and I always see none.
‫Key indexes in production systems.
‫Yeah.
‫It just.
‫It just makes sense.
‫Yeah.
‫What's the cost?
‫That is all the cost of everything.
‫The cost is space, and the cost is I.
‫Oh, because now the token will be plus threes.
‫And if you're listening to this, you probably watch the whole course, which is good.
‫So B plus three, everything in the leaf.
‫So I ose.
‫Now you're going to need more.
‫I always.
‫Two Fitch.
‫The B three lineage.
‫The leaf trees.
‫Is it?
‫A bit.
‫Is that.
‫Is it a big deal?
‫Not necessarily.
‫Yeah, it is.
‫How bad it is?
‫I wouldn't tell.
‫I can't really tell you how bad it is.
‫Month.
‫It's probably insignificant, but it is always a cost.
‫Right.
‫And then to the need to try and see was the cost of everything happened there.
‫I got a very quick video today for the April 20, 22 cook you're going to see in the next one.