‫Welcome, guys.
‫Today, we're going to explain explain in Posterous, get it?
‫So there is this command in postscripts that is called Explain and it is used to retrieve information
‫about what Quarrie plans Postgres will use for a given SQL statement about jump into it.
‫I have a beautiful table here.
‫It has to be beautiful.
‫If it's not beautiful, I send it back.
‫Michael Scott, this table is called great.
‫And there is an idea and there is a great there's a name.
‫So there's a bunch of students.
‫There's a lot of Rosio, I think over 200 million.
‫And I showed many times how to create such stable and sort of this much value of rows.
‫So there's an index on the ID that is an index on the G the grade, but there's no index on the name
‫of the students.
‫So here's what I'm going to do.
‫So we're going to execute a bunch of queries and we're going to explain them and see what this will
‫do.
‫So let's start with explaining the select star the worst query you can ever do.
‫When we do that, we get this beautiful one liner.
‫And let's explain let's let's demystify what's going on there.
‫So the first part is usually the query plan.
‫And this is a sequential scan and this is equivalent to a full table scan and other databases.
‫Essentially, what what the database decided to do is say, OK, you're selecting everything and there
‫is no filter.
‫So I'm going to go directly to the table.
‫I'm going to go directly to the heap and fetch everything.
‫That's the sequential scan.
‫Sometimes Puskás do a parallel sequential scan with threading.
‫They spin up multiple threads just to do the scanning on grades.
‫And here the cost and the cost is usually has two numbers, almost has two numbers separated by two
‫dots, the first number and the second number.
‫So let's explain what this means.
‫The first number means how many millisecond it took me to fetch the first page.
‫Right.
‫And in this case, it took zero Y because Postgres immediately went to the table and fetched the first
‫row and immediately got results.
‫So it cost you almost nothing to get the first.
‫So if you're doing some sort of a limit, I select start from guys, limit one, you're going to get
‫a result very, very quick.
‫This number can increase the start up cost can increase f postgres decide the database design to do
‫some work before Fitchett, such as aggregating an order by things, has nothing to do with actual fishing
‫work that precedes the actual things that you want to do.
‫So that's you going to see this number.
‫If you see this number go up, that means you're doing a lot of stuff before fetching.
‫And this number is essentially the total amount of time that it thinks because remember, it didn't
‫really execute the query, right?
‫It just told me, hey, I think I'm going to do this right.
‫And this is the total amount of time which Envisat a millisecond.
‫So it it it estimates there's going to finish the whole thing in two hundred and eighty nine seconds
‫essentially.
‫And this is the another estimation.
‫This is very valuable by the way.
‫Right.
‫This is not an accurate number, but it gives you a quick number approximate based on its own statistics
‫that this is the number of that is going to fit.
‫That's why I always recommend when you want to do a count and you don't really care about the actual
‫number like you're doing at it or you're building Instagram and you you're counting the number of likes,
‫use this, don't you?
‫Don't do select count, select count will kill your performance is actually go and physically do the
‫count on all your roles.
‫And if you have brilliant rows, tough luck counting that stuff.
‫Right.
‫That's why a lot of people use this.
‫Explain, give me an estimate, return that and there is the width, the width of the row.
‫So this is the sum of all the bytes for all the columns.
‫And I have three bytes.
‫If you some of them, you're going to get thirty one because there is text them.
‫All right.
‫So that's the first one.
‫Let's spice things up a little bit.
‫Let's do it.
‫Explain select start from grades and we're going to do an order.
‫By this time I'm going to order by G and G is the great.
‫What do we do.
‫What do we get.
‫You see this number bumped up a little bit.
‫It's a it's a point for three millisecond.
‫So it did Postgres did some work or it is attempting to do some work before finishing the role and that
‫work is what is actually doing the short order by G.
‫And it's not so bad because it did use the index that is on G two to assorting and that's what then
‫this is already sorted.
‫So the work is very, very trivial now, but it is work nonetheless.
‫And that's the total number of thing.
‫The rows this time this is almost the same number.
‫Should be, better be.
‫And then the wealth is also the same.
‫OK, now I want to spice things a little bit and do.
‫Select start from grades, order by name, something that has no index and you can see.
‫Was a little bit more because in this case says, do you want me to sort by the name?
‫The name is a column that is not indexed, so I have to go to the heap and guess what?
‫I'll get to pull everything to in order to sort it.
‫So I started to first of all, it did a parallel sequence, can always read it from bottom going up.
‫Right.
‫This is how we do it.
‫Don't read it from up there.
‫It start from the inner thing going up, up, up, up, up.
‫OK, so the sequential scan on grades, it took nothing to do that because, hey, I'm going crazy,
‫right?
‫Cause nothing to go out there again.
‫We didn't start yet.
‫This is how much time we're going to take.
‫So it's estimating that is going to take me two hundred second two hundred eighteen seconds.
‫This is the number of rows that we're going to get back.
‫Thirty one.
‫Got it.
‫All right.
‫Now we're going to shorten the name.
‫Look at that.
‫Look how much it took just to get to the point of something the sorting took.
‫Man, that's a lot.
‫That's that's a thousand seconds.
‫Yikes.
‫Right.
‫So you can see that this number is very critical.
‫The first number the second number is not so much.
‫That's the last one.
‫Right.
‫But the first number is everything that you need.
‫And then obviously we plan to work.
‫So there is two parallel doing this thing.
‫And since we had two threads to process processes doing the work, we got to merge the result at the
‫end of the day.
‫Right.
‫And that's the total results.
‫Obviously, we get this number and that's the final result.
‫And thirty one width.
‫So you see that there is more work that needs to happen to get to get to the actual thing.
‫All right.
‫Let's play a little bit with the other stuff, guys.
‫What I'm going to do here is I'm going to do explain select ID, not just the whole thing, just the
‫ID from grades.
‫Let's just try this thing.
‫What do we get?
‫Obviously we get zero cost cost.
‫Nothing to go to the table.
‫At what cost?
‫Nothing to go to the table to fetch the first page.
‫That's almost nothing.
‫Zero.
‫Right.
‫And the rows.
‫But look at this, the width we get for y for four bytes is the result you're asking to return.
‫Only the ID and the ID by default is an integer and integers for byte by the way.
‫Careful, don't put your primary key as as just integer if you're expecting this table to grow beyond
‫two billion.
‫And that's what happened to parallel by the way.
‫They grow beyond two billion notification then blew up their table.
‫Yeah.
‫So there are four whites in the idea.
‫OK, let's do explain select name from grades.
‫You can see that the eye of the average size is nineteen because the name is text.
‫Right.
‫And depends on the name of the students can go nineteen.
‫They took the averages there.
‫We are expecting that's going to be nineteen.
‫But so be careful with this number, especially if you're returning this across the wire to your back
‫end application.
‫The largest, this number, the larger the network you're going to take the the higher the TCP packet,
‫right.
‫I mean, don't put it like a blob and do select star when you don't use this thing.
‫That's why it's insulting to do a select start to the database, only to pick one draw or one column
‫from those.
‫Right.
‫Only select what you need.
‫And you can see if I do, for example, the G it's eight.
‫That's that, that's a number of bytes in the double value.
‫All right, let's do.
‫And how was I was indexing working and the post office select staff from grades where I'd equal ten.
‫So there's an obviously there is a unique index on the ID here, so we're expecting this to be fast.
‫Obviously we scanned the index and we jumped to Fitch.
‫We jumped to the heap to fit some real value and tokens point for three seconds and the time to talk
‫to the whole thing is eight Milicic.
‫Again, this is not actual terms.
‫There is another command that we're going to explain in another lesson or video where we're going to
‫show analyze.
‫That's the actual time talk.
‫This is this is just an estimation.
‫So we did an index scan and we used the index condition, this one in this case.
‫And you can see if we played with this a little bit and say, OK, this is a silly query, but bear
‫with me here.
‫So now when I do explain select ID from grades where ID equals ten Siliguri, of course, because you
‫just said ID equal ten, but you can see that now we're getting an index only scan, which is a faster
‫one.
‫Right, that the work we didn't even need to jump to the heap to do the work.
‫Right.
‫So that's just another type of I, I did many videos talking about that as well.
‫All right, guys, that was a quick video explaining explain.
‫And it's the very, very basics of the basics of the stuff, right.
‫Me, I'm going to make a few or more lessons and more videos to explain the Analise, explain the buffer's
‫claims, the shared and.
‫Other stuff as well.
‫Let's give this video short and sweet, going to see in the next one, you guys, the ultimate goodbye.