‫In this video, I want to go through some of the best practices that you can create indexes with so
‫that you can gain the best performance for your queries.
‫I'm going to execute four different queries on this beautiful table that I have here.
‫I have a table of three integers, A, B and C, and I'm going to play with the indexes on A and B,
‫so I'm going to create a configuration where I have an index on both A and B, I'm going to create a
‫configuration where I have a composite index on A and B, and I'm going to create a different kind of
‫configuration.
‫I want to have a composite index and a normal index on the right and I'm going to execute the following
‫queries where I'm going to query only on a and a great query only on B, and I'm going to query A and
‫B and query on A or B, because that's how most of the stuff that we do on the back end.
‫We query with these particular filters.
‫I understand we have in the statements we have other kind of aggregates, but we'll take it step by
‫step.
‫I you know, something useful with this video that said, let's just jump into this and play with this
‫thing.
‫All right, guys, so let's start with creating an index on a at another index on B, it's going to
‫create an index on this A, by the way, I think I have around 12 million rows in this table, not much,
‫but enough for us to experiment with.
‫So I'm going to create another index on B.
‫All right, all done as a test, so we have an index on an index number, so let's check our queries.
‫So the first question I want to do is select C from test where A's equals 70.
‫And I'm going to prefect's this was explain and analyze that will tell us how much of the analysis take
‫talk and the plan that has been used to do this thing about jump into it, do it fast, faster, this
‫very faster.
‫All right.
‫So what happened?
‫Obviously, the Postgres decided to use the index.
‫However, it created a bitmap in order to query this index because there are a lot of rows.
‫Look at how many rows we have.
‫Nine hundred nine thousand roll.
‫So it has to create a bitmap Y because we have to jump back to the heap to pull C.
‫C is not in the index.
‫C is not included in the index.
‫So we have to jump back to the heap to pull the value of C right after looking at.
‫So we are building a map of all the values, the role, the tuples in order to jump back to the table
‫and pull that.
‫And that explains why we have a bitmap index scan scanning the index itself that we just created on
‫a and then we jump back to the PEOP to actually pull those roles and we have to talk to one hundred
‫fifty three millisecond.
‫Not that bad.
‫All right.
‫So that's, that's why we have done and what I want to do here and show you that we can easily change
‫this kind of infer the decision of Postgres by actually limiting the number.
‫Of course.
‫So if I say OK, give me only two queries back two rows back, look what the public has decided to do
‫because we have only two rows came back.
‫Postgres doesn't need to take the overhead to build a bitmap because building A takes a little bit of
‫time.
‫You have to compile this structure.
‫You have to come up with old values, but it says, Oh, I only found two rows.
‫I don't need to build a MacNab.
‫So I decided to do an index scan.
‫A clean index against these roles are are continuous.
‫They are order, they are sorted.
‫So the values that I'm going to get, I don't need I don't need a map for this.
‫I just come go pull the those values directly from the heap.
‫All right.
‫So we use the index about to go to the other query.
‫My other query is B is equal hundred.
‫So stop going see what we're being Soquel.
‫OK, let's take a look.
‫So it took two hundred and fifty millisecond.
‫We also use the index.
‫This time we use the B index because the allocating on B natural.
‫Now I'm going to use then next one B and then I hit the table directly with that heap scan using the
‫values that I picked up from B.
‫Right.
‫This is also a bad idea.
‫Right, because you're not going to pull ten thousand rolls.
‫What are you going to do with ten thousand rolls unless you're doing some sort of aggregate.
‫That's a different story.
‫But all right, so we have the value so we're using the index.
‫All right.
‫So let's do this.
‫The query now, but we're going to query on both A and B, so I'm going to do equal, I don't know,
‫a hundred and B is equal to hundred.
‫Let's see what world will decide.
‫So now we're going to do an add between those square.
‫So both because the size is OK in parallel, I'm going to scan the index and I'm going to scan the B
‫in this because you you gave me two values.
‫Right?
‫And Skåne, those two values, I'm going to scan them and I'm going to build a bitmap.
‫Right.
‫And literally you've got a bitmap and then literally just and those bits, maps, you're going to end
‫up with a certain values, like some values will zero up some voters want.
‫And then you're going to end up with a set of tuples that you're going to hit the heap and pull the
‫table.
‫And that is extremely fast.
‫As you can see, it's just happened is like how how many values to do really return?
‫Right.
‫We return six rows.
‫That's why it's pretty fast.
‫And the ending operation is extremely fast as well.
‫So that's what postcodes do.
‫In case of an ad, the Steiner, what would an order do?
‫I'm just going to change this to an R pu with an OR it took obviously longer.
‫And that explains a lot, obviously, because or always brings more rolls than and.
‫Right.
‫And now we did exactly the same.
‫We scanned this index.
‫We scanned this index, we brought the bitmap, we ordered them got a lot more rows than we are or where
‫we wanted.
‫And then we got back.
‫That is also pay attention to what this really happened.
‫Right.
‫We're doing double the work we're doing, scanning this index scanning index and going to the table,
‫giving it just rolls and then going to the table to pull these rolls.
‫All right.
‫So now I'm going to.
‫All of these suckers and I create a composite index instead, as do that just to be all right, both
‫indexes dropped.
‫Now going to do is create index on test A B, so this is a composite index a little bit different.
‫Right.
‫So we have one index that have both values.
‫So it's going to take longer to create and it's going to be more efficient for queries that have both
‫A and B, especially the and cases, because the cases we did a lot of work and guys don't pay attention
‫to the values that the how long it took, because this is really depends on the machine, depends on
‫so many things.
‫I wanted to pay attention to how much work postscripts do, and that's what I'm explaining essentially.
‫All right.
‫Let's do the first query on this.
‫So now I'm going to only query against air and let's see what we're going to do.
‫Look at that.
‫Actually, Postgres decided to use the A B index despite me querying on air.
‫Why?
‫Because AEL is in the left hand side when I created that and that dang that matters because it's in
‫the left hand side, the values can be easily scanned.
‫That's the PostgreSQL rule.
‫You can scan the values from the left, but you cannot scan of them.
‫Right.
‫Just let the index are both left or right.
‫Right.
‫And when you build that query, the index decided to use the postgres, decided to use their index and
‫then jump back to the heap.
‫Right.
‫So it took two to fifty millisecond to find all those values.
‫And I believe if I do a limit, let's say limit ten, as you can see, it's an index scan instead of
‫a beat with the bitmap.
‫So it's like a little bit faster.
‫All right, let's do the B.
‫What do fossickers do when I do A B?
‫Oh, you felt that, huh?
‫It's a battle sequence.
‫Why Bosco's will not use the index.
‫Why?
‫Because you cannot use this composite index when you're scanning a filter that is not the left hand
‫side.
‫It's either A and B or A, you cannot use B, so be very careful.
‫So if you have a composite index on IMDB querying, OMB will not use the index.
‫So what the table does, what Postgres does is just jump directly to the table and does a full table
‫scan and using multiple walker threats to do that.
‫Right.
‫So obviously very, very slow for very careful.
‫Very careful with that.
‫All right.
‫What happens if I do A and B equals 70 and B equal to whatever values?
‫Look how vast this thing is.
‫Point five, half a millisecond.
‫This is absolutely insane.
‫That is the best case scenario.
‫If you really all the time if your queries are all the time like this, have that value add value.
‫It's a great way to create an index composite index.
‫If if if you if your application only does that and hopefully only does that and you're also you're
‫coding in a best case scenario is to create a composite index.
‫Yeah.
‫It's more costly to create a composite index compared to a single index because it stores more values
‫obviously.
‫Right.
‫But the performance is remarkable.
‫Right.
‫All right.
‫How about or how about all of this or this scar or.
‫No, or and or all bets are off.
‫You cannot use any of the indexes.
‫Right?
‫I mean, Postgres decide in this case, it's useless for me to query and try to use that because I know
‫what might be faster for me just to go and jump to the table and do a full table scan instead.
‫So that's what both scores and scores does.
‫Most databases do all these decisions on behalf of us?
‫OK, maybe it's better to do this, but there it is.
‫I mean, if you look at this, OK, you have an index, Owney, you can query on a I mean, you have
‫an index on IMDB, but you can query, you can use the index for a but decided.
‫No, you know what, it's way slower.
‫I'm going to do this.
‫I'm going to do a bit more and then go for B, I don't have an index.
‫So anyway, I have to go jump there and do the full scan anyway.
‫Right.
‫What happens if I do a limit limit one second just as can on this.
‫It's not even parallel, just sequential because we know we're going to pull only the first row.
‫That's fast and that's directly on the heap.
‫All right.
‫All right, guys.
‫The first final thing we want to do is.
‫We know what we know that, B, suffered queries on BSR when I have a composite index, so what I'm
‫going to do here is show you that I have a composite index on IMDB.
‫What I'm going to do is create an index.
‫On test beep in addition to this index and then execute the same queries again and see how things are
‫doing.
‫All right, so what will happen now if I query on and we saw this because we're going to use the composite
‫index.
‫That's fine.
‫That's fast, right?
‫That's nice.
‫Right.
‫How about if I use B, if I use B now.
‫So we're going to use your beautiful index that you just created for us.
‫So that's kind of a compromise as you have the backing engine, as you database engineer, you can know,
‫OK, I might add some queries.
‫I'm going to use the B to query this so I might need an index there.
‫So you going to create an index in this case to speed up these queries?
‫Right.
‫And then obviously we're going to head the table and and the heap in order to do that.
‫All right.
‫Let's do A and B and B.
‫Absolutely genius and fast look at this.
‫Right, we're going to use the composite index because to be right, right, how about A or B?
‫Or now that we have an index on B and an index on a B now because oh, what is that?
‫And actually we have an index and maybe I can query the index on B, do A, build A bitmap and then
‫I get a query.
‫Since we're counting on E, I can still use the composite index because it's a left hand side.
‫I can use the query index and then build my bitmap beautiful but not below single bitmap jump to the
‫heap and then pull the rolls that I want.
‫All right, guys, that was some of the best practices that you can do to work with indexes, right?
‫This is just just some of them, right?
‫Guys, sometimes indexes can shoot you in the foot.
‫Sometimes they are great, but just learned how to use them.
‫I can't possibly in ten minutes explain all possible combinations, but hopefully this helps you kind
‫of understand what the database is doing well when you are actually acquiring the index.
‫This is very, very clearly the cause of death and injury.
‫And this is one of the skill set that you have to put in your belt when you're building a database.
‫Applications as and should hope you enjoyed this video.
‫Give it a like if you like it, give it a dislike.
‫If you don't like it, I'm going to see in the next one.
‫You guys stay awesome.
‫Good bye.