All right, everybody, we're going to get started.
Please be quiet if you want to talk.
Do it outside.
Be respectful of the speakers, please.
Hello, everybody, please be quiet because Charlie's going to give us a run-down
of what happens under the hood when you actually execute an SQL select statement.
Hey, the way.
Thank you.
Hey, everybody.
It's just one better or the other one better.
The other one?
Okay.
Okay, it's similar to that.
I'm possible, right?
So they think that they's going to gamble with your wish.
The planer is going to do some bats.
It's going to get some informations.
It's going to get some coins and going to bat with your wish.
And then the executor at the end is going to pay for the bill.
Will it pay a smaller or a larger bill?
How does that work?
This is what we're going to see today.
So this is our example bat.
We're going to run this select.
If you see we have four tables, three joints, two filters, and one soft limit.
It's not the very simplistic select, but it's not the very complex select either.
So it's something that you can use to see how that's going to play together, right?
So when you send this to the database, this was supposed to be an animation.
See?
Okay, let's run again.
So when you send this to the database, it goes to the pass, then to the analyzer,
re-writer, planer, executor, and then the result.
So help each because you're going to see each of them going to do here.
So everything starts from the pass.
So the pass is the guy that's going to get your query and see if everything is in order.
It doesn't care if the table exists now.
It doesn't care if the columns exist or not.
But it cares if it has an order, just like when you speak an English.
When you say, I would like to, you say, I would like to.
You don't say, like would I do to do something, right?
So it has order.
It's need to say if you write front, not for, or M, F, or R.
So that's what I'm going to do.
And then it's going to create a tree.
Just like the one that we see here, this is the pass in three.
So this pass in three, going to follow our curve along the way, because it's going to be re-use it layer.
This is the starting of the internal language of the database.
We're going to use force with an example here, but that happens in almost all the database.
At least the ones that I know.
So after that, we need to do a semantics analysis.
Okay, now we really care if they will exist or not.
So we need to check if those tables exist.
We need to check if the comparison that we're doing there,
we're using the right operator for the correct times.
Sometimes you want to do a select where something was equals something else.
But we're comparing different types.
We're comparing an integer with a date or something else.
That depending on your database and the syntax is it's not allowed.
Then you need to do either change the types or you need to do an in place change for them.
So this is what this guy is going to do.
So and then it create an audit tree.
If we use the tree that we sent before, a pass tree, it's going to create a query tree.
So these are those where you could rewrite some inserts inside of the bills.
So these are all of those things that are going to be to change it here.
All right, now we have most of the things that we need to execute.
We have a plan now.
So but before we execute, we need to decide how we're going to execute.
We know which are the tables we want to select.
We know which are the roles we want to select, the kind and no because we have the referrals.
And you know the max number of roles we want to get.
Remember we have a limit 100 here.
So we want to cap our result when it gets to 100 roles.
We don't want more.
We may get less, but we don't want more of those ones.
So this is the information that the database has.
But now it needs to make a few of decisions.
So the first decision that it needs to do.
Here is how to scan.
Then how to join.
And the order and if you do some part of the list.
And to do those things to make those decisions.
This is where come the odds for the our batch.
The decisions that are made by based on course.
Remember we want to pay the lesser bill.
The lesser value.
The lesser money to run our select.
But how do we calculate that course?
How do they calculate that course?
So we have some unities that the database is going to use.
And some of them, how do we get the data?
Do we get the data sequentially?
Or do we get the data in a random way inside of the disk?
Why does it matter?
So in all days when a user to use is speeding this.
Get in the data sequentially is a lot faster than randomly.
Because the speed in the disk.
The head of the disk for one place or another took a lot of time.
And if you install your course risk well right now.
And you go for the configuration.
You see this parameter here.
Random page cost equals four.
It's telling the database that if you need to take a random page.
It's going to cost four times a stake in one sequential page.
Or give it, why does it matter?
Well, it matters because your index is basically random access on the database.
And your table, full table scan is sequential access on the database.
So if you have a table and in that table have an index.
And if the number of roles compared that's read from the index on the table is similar
or four times lesser the database is not going to use your index.
It's going to do a full table scan.
And that's why sometimes you have this really nice index here in my table.
And if it is a lack, it's not using my index.
Because it's expensive to the database.
That's one of the reasons, but they only one.
So we have all the parameters as well.
So we have the CPU couple costs.
That is how much going to cost to the database to process each role that you're going to select.
So when you have these outdated statistics, those numbers might be wrong.
And then you might be having full table scans instead of index scans.
Not because it's cheaper, but because the polar things are believes it's cheaper.
Based on the statistics, it gets.
Right?
We need to be careful on that one.
So this is having an example here of a planning.
This is what you get when you do an x-playing on post-grace quo.
It's going to try to see, based on what you have, all the cost.
What are the cost for my sequential scan?
They index this scan, and so on and so forth.
So we have the information that we put here and how it used on the explain.
Right?
And an example.
And if you are familiar with post-grace quo,
we have all the database they do the same as well.
We have a few different types of explain.
We have the explain and explain analyze.
The explain is what the planer believes it's going to do.
And the analyze it actually runs and shows what exactly it does.
And this is the build that you're going to pay.
Not the build that we believe we're going to pay.
So on this way, we have to look at different things.
Okay, sequential scan.
Why or how we usually run sequential scans?
Well, there's a matter.
If you have a table that is too small or if you're going to get a lot more data from the table,
compared to the total amount of the table, it's going to do a full table scan.
Because it's cheaper.
Remember, when we do a index scan, it needs to go to the index which is a random search.
Again, the information then is still need to get the data from the table.
Unless we do what we call an index only scan.
If all the information you want is on the index, that's fine.
There's a need to go to the table.
For example, if you want, if you have an index that has the names.
And they won't only to get the name, so all that information.