‫Welcome to another lecture in this Q&A section of the database Engineering Fundamentals.
‫And today we have a question from karate.
‫Sorry if I didn't answer your name correctly.
‫It's a very good question, actually.
‫It says, Hey, I have a pulse, Chris, Using sequential scan rather than index only scan.
‫Why is that?
‫And that's a very good question.
‫Let's take a look at what was what was a curator's question here.
‫I'm using PostgreSQL.
‫I have created a table with four columns ID, first name, last name, date of birth, and I created
‫an index.
‫On the ID field, which is a primary key as well.
‫When I do a select query like this, which is a select ID where from, obviously he forgot to add the
‫table here, it's like from blah blah, the table where id equal to he's getting it is using a sequential
‫scan rather than an index only scan.
‫What's the issue here?
‫The table has only seven rows.
‫Even if he didn't say that, I would have guessed that his table is small.
‫All right.
‫So my guess is here.
‫So what is happening here?
‫All right, so I already answered here, but I'll give you I'll give you a hint of what's happening
‫here.
‫The.
‫You see, Postgres or any database planner will get to understand how things work, right?
‫We have the data of the table in a file and we created also an index, which is another file, and the
‫index has the structure of we talked about the B3 structure for an entire section.
‫It's a complex structure with so many intensities, right?
‫Where we have a root, we have intermediate nodes, we have deltas there, we have leaf nodes, we have
‫complex structure here and we have the data file, which is just a dump of everything that is in the
‫table.
‫So when we load the database and we start querying the table, what will the database will look is like,
‫okay.
‫We have we have the data file and it's a single page.
‫Why is it a single page?
‫Because it has only seven rows and the seven rows happened to fit neatly inside that page.
‫A page is around, what, eight K and postcodes?
‫I think.
‫So.
‫This is way too small of a table.
‫Right?
‫So it's a single page.
‫And here's what the database says.
‫All right.
‫What I'm going to do is actually, yes, you start using the ID field, but guess what?
‫It's not even worth it to for me to go to the index.
‫Because to go to the index, I have to do two things.
‫I have to go to the index, I have to fetch the index.
‫I have to build the B three index in my memory and then I have to traverse it just to find everything
‫that has ID equal to versus.
‫I know the whole table is one page and that will cost me one.
‫So here it is.
‫I'm going to do an IEO and I'm going to pull it in memory.
‫And that goes to the heap as we talked about it, the postgres heap, and then that page in the heap
‫was, etc. Here is our heritage user because we just read all the things that are ID equal to and it's
‫a single read, it's a sequential scan in this case.
‫That's why tables.
‫In this particular case, there are too tiny.
‫The database will just do a sequential scan on the heap, right.
‫It will just fetch one page.
‫And that's zero cost almost to false.
‫Correct.
‫Now, here is where it gets interesting.
‫And I got bitten many times that I have count with this, actually.
‫And so I have a tiny table and I start querying this like like what karate did here, right?
‫I was querying it and the plan started getting cached.
‫This is it.
‫Oh, this table is too tiny, so sequential scan is the best.
‫I don't want to go to the complex fee tree and scan many indexes to get my results.
‫I was going to do a sequential scan because it's just it's a simple table.
‫It's a small table.
‫And what I do is immediately after doing all the queries, I would insert my data.
‫And when I do that right.
‫What will happen is I started like inserting 3 million rows, right?
‫And immediately after 3 million, I didn't give the database times to breathe.
‫I would start querying for a few minutes and my queries are so slow.
‫Why is that right?
‫Why are more queries slow?
‫Because the database thinks the table is small.
‫So if I have a query, the statistics and we talked about statistics in the course.
‫That the statistics, which tells the table how many rows are in there, are not up to date with the
‫3 million rolls that are inserted.
‫So the planner thinks, Oh, it's a tables that is small.
‫Let me go and query it.
‫And when it does that, it says, Oh, it starts doing a sequential scan of an actual instead of an
‫actual performance.
‫B three scan.
‫Because remember B, three scans or an index scans are very amazing when you have large amounts of data
‫because you can traverse a few blocks.
‫But then you're going to find exactly what you want in the B three immediately go to that.
‫Right.
‫That's the problem here.
‫The problem is the planner is out of date.
‫Right.
‫The cache, the stats that already.
‫So the planner thinks, let me do a sequential scan.
‫So start scanning then toyour heap fetching millions of pages.
‫I'm exaggerating thousands of pages to find what I'm looking for.
‫Why?
‫Because it was out of date.
‫Obviously, this is a very common problem when you just insert the data and immediately you query it,
‫That's that's something you have to watch out for.
‫That's why after inserting a lot of data, you have to do a vacuum in case of Bosphorus.
‫And in the case of Oracle, you have to do a.
‫Vacuum analyser, I suppose, will will do computing the stats again, it will just do a count against
‫its OC.
‫This is how many active rows are there?
‫I'm going to update my stats so the planner will make smarter decisions.
‫And in Oracle I think it's called gather schema stats and in SQL Server it's called something else and
‫that's updating the schema stats are very, very critical in this particular case.
‫So I thought a very good question.
‫I thought I'll talk about it here because it's it's really something you have to watch out for.
‫And very good observation correctly.
‫I like that you guys are actually looking at the plans.
‫It says, wait a minute, I have I'm using an index.
‫Right.
‫Why is it not using an index?
‫It's a very smart thing.
‫Even with an index only scan is not fast enough.
‫Right.
‫To to to fetch the index compared to just having the one page.
‫Right.
‫If you add a few rows to the table, more zeros at 10,000 rolls, you'll immediately feel.
‫I hope you guys enjoyed this Q&A lecture.
‫See in the next one.
‫Goodbye.