‫So one of you guys asks a very interesting question, and I thought, I'll make this lecture just to
‫address that particular question in details.
‫Let's jump into it.
‫This comes from Adam Salama.
‫Hopefully that's how you pronounce there's an H in your name.
‫So I suppose it's Adam.
‫So question is an explain analyze shows, bitmap scan instead of an index only scan.
‫Why is that?
‫And thank you, by the way, Adam, for actually including the script, because, you know, I built
‫this this lecture months ago, even maybe years.
‫Right.
‫So I have no idea if I read a question, I don't remember anything.
‫So by you actually providing exactly what you did, I just copy it in my Postgres instance and I am
‫with you exactly the same thing.
‫And instead of asking me, Oh, in this minute you did this, I have to open the lecture.
‫Go to the minute.
‫It takes time.
‫Right this way.
‫I just look at what you have.
‫I Exactly.
‫I'm in the same boat as you.
‫So thank you for doing this.
‫Oh, this is awesome.
‫If you do this right.
‫So what what Adam is experiencing is he created the.
‫The grade table.
‫He created the partitions.
‫He just slammed a bunch of rows, a thousand rows, and then immediately did a query on the on the,
‫on the main partition, you know, doing explain, analyze, select count star from grades port where
‫G is equal one.
‫So he wants the first grade that we know that since G is equal to one, we're going to hit the first
‫partition right between 0 and 35 because that's that's the range.
‫Right?
‫So that's how how it works.
‫So we're going to hit that.
‫And we have an index there because we create an index on the main.
‫An index will be created on each partition because a partition is really just a table, right?
‫Each one is just an individual table.
‫And this partition, this main part is really just the logical thing, a nice view for you, you know,
‫So what what Adam is, is a, I suppose a question is like they got a bit bitmap index scan right on
‫the G zero 35 G index, which is perfect.
‫That's the index of that table.
‫Right.
‫And the cost here is 0 to 4.
‫And again, I made a mistake in one of my lectures that said, oh, this is millisecond.
‫That's that's not correct.
‫It's just a unit that Postgres uses.
‫And this unit means it's a cost, right?
‫The more the value, the higher the cost.
‫You can think of it as I o you can think of it as milliseconds.
‫Not really, but it could be anything.
‫And, and that's just the value that the higher the cost, the, the, the worse the performance.
‫Right.
‫So we estimated nine rows.
‫We got nine rows with this.
‫So it's just an estimate.
‫And then we the actual gave us one.
‫But the question is like why is it not using the index on his.
‫It's a count.
‫Right.
‫So I'm just counting the rows.
‫Like why does it have to go to the heap and the cost of going to the heap is high, right?
‫Because now going to the heap, you need to fetch the pages and all the columns for that table.
‫Right?
‫So if you have like a 100 columns going to the heap will by default fetch the page and fetch the table
‫the tuple.
‫Right.
‫And then the table will by default give you all the columns.
‫Right.
‫And.
‫And I know that some of you asked the question was like, oh, in this case, like, why is why do we
‫do select column?
‫Just just always do select star.
‫No, not not quite.
‫While true, when you do a tuple query, you get all the column.
‫But the but the parser still have to do work to actually crack that index and actually find the attributes
‫that you're looking for by parsing the binary page in Postgres.
‫So there is work there.
‫It's not like free.
‫So that's the first word.
‫Then you have to serialize it to the data structure in Postgres, then you have to transfer it to the
‫client.
‫So select Star is bad regardless, even if it's a raw store.
‫Right?
‫There is so much work, right?
‫Especially like let's say you have Blob Fields, you're doing Select Star, you're pulling it from the
‫host table and then pulling it back to the network to the client.
‫NA All right, so back to the original question.
‫Why did it do a heap scan, an index heap scan?
‫And we talked about that, a bitmap index scan.
‫What it does is that it scan the index and it doesn't really care about the columns per se.
‫Sorry, the tuples.
‫It just make marks of the pages.
‫Oh, we found page zero and page 17 and page 30.
‫Right.
‫These are the pages that contains the rows we want.
‫That's all what it does, right?
‫And then it takes that and fetches page zero with all its tuples.
‫Fetches page.
‫What did we say?
‫Seven.
‫And then fetches page 31 with all the rows and then it does an additional filter on the heap to filter
‫out what it doesn't want.
‫In this case, we're looking for grade equal one, right?
‫This is in this case the bitmap this way, because if it has to, it had to go to the heap for some
‫reason.
‫We don't know why in here.
‫I know.
‫I'm going to tell you why.
‫Right.
‫But if it has to go to the heap, it doesn't use an index only scan because it's not an index only scan.
‫We have to go to the heap to fetch an additional column.
‫But what is the column that it needed?
‫We didn't need anything.
‫The question the answer is there are two hidden system columns that Postgres uses to maintain the Mvcc
‫the Multiversion Concurrency control.
‫And those are the X-Men and then X-Men and some other columns.
‫This lives as just another column in the table in the heap.
‫Okay.
‫So in the pages.
‫And there's just two additional attributes, including the table ID as well.
‫Right.
‫So now, if you think about it, these two columns will tell you whether the row is visible or not,
‫whether the tuple is visible or not.
‫What does that mean?
‫So if I am transaction right.
‫If I'm transaction one and I start it, I'm doing some work here.
‫I started.
‫And meanwhile, after a little bit, while I didn't commit transaction one started running and then
‫another transaction continues to run right as it started another transaction and inserted a bunch of
‫tuples.
‫Right.
‫And didn't commit.
‫Does this transaction.
‫Transaction one should should it see these rows?
‫No, it should not.
‫Right.
‫Because it started before that transaction.
‫And even if it did, if transaction two did commit those rows, if I'm in transaction one and I started
‫a query, I started running a long query.
‫Let's say I'm selecting everything.
‫And just right after I started executing the query, then it's running the other, uh, the other transaction
‫committed the inserted rows.
‫This transaction should not see those inserted rows because it started right before the commit because
‫I have already read committed works.
‫Right.
‫Read committed works the moment at the start of the query.
‫Right.
‫So yeah, once we finish that query, the next select will see those inserts.
‫So that's how our isolation works.
‫We talked about it in details in this course, right?
‫The problem here is Postgres does not trust the index because the index has what it has.
‫The key, which is the grades.
‫In this case, that's where we're building the b-tree on.
‫And the value that is corresponding to the key is the tuple ID, right.
‫Which is which is basically the page index, which is zero one, which page and which index in that
‫page belongs to which tuple.
‫Right?
‫So that's how the tuple ID works in Postgres.
‫So that's the only information we have.
‫So we have no idea that someone inserted something.
‫When you insert something, you update the index as well, right?
‫So when you read it, that transaction should check.
‫Am I supposed to see that row?
‫Is this row deleted and I'm still needed?
‫Is this row inserted and I'm not really need to see it.
‫So this visibility information lives in the heap in the table as just another column which is x men
‫and x max x men tells you that this is the transaction that started the this is the transaction that
‫started the.
‫That created those tuples X max tells you this is the transaction that killed the tuple or deleted it
‫or even updated it.
‫Right?
‫Because Postgres creates a new tuple for every update.
‫So now, based on this information, Postgres said, All right, uh, for Adam's case, a select count.
‫I know I got this, but guess what?
‫These are new stuff.
‫He just inserted them, Remember?
‫They just inserted this row.
‫And then immediately after that, he did a query.
‫So based on Postgres will say, Well, I can't really trust those rows because they are.
‫They might not be visible for me.
‫So I need to go to the heap to read the X men.
‫And when I read the X men say, Oh, this is a transaction that started before me and committed.
‫I am supposed to see those, those rows.
‫I am actually supposed to see them.
‫So they will pull it back.
‫Right.
‫But.
‫You might say, ask the question Now, does that mean that every query now has to go to the heap to
‫check if it's visible or not?
‫Not really.
‫Postgres has this optimization called the Visibility Page.
‫Each page there's like a data section in memory that Postgres maintains that says, okay, page zero,
‫don't bother going to the heap.
‫All the rows in this page zero are visible to every single transaction.
‫There is no transaction that is running for a long time that requires the the old version or requires
‫not not to see these rows for some reason, for isolation purposes, all of them are required.
‫How do we do that?
‫We do a vacuum on the table.
‫If you do a vacuum on the table, Postgres will go through all of them and do this expensive check,
‫which is like, Is there anybody really running right now needing.
‫Is there anybody running?
‫Is there any transaction that is running that?
‫Needs not to see this row.
‫Right?
‫Is this one of these rows are invisible to any of them?
‫The question is no, because nobody is running.
‫So by running vacuum.
‫Right.
‫I actually tested it.
‫If you run vacuum right after this and then you run experiment, you get an index only scan.
‫So that explains why do we get an index and apply this to everything, Right?
‫Everything.
‫You get this heap, I think.
‫Yeah.
‫The heap plan is like the heap features you're going to see.
‫The heap fetches somewhere here.
‫Sometimes I see it.
‫I can't see it right now, but you'll see the number of pages that we had to go to the, to the, to
‫the heap for this.
‫So that's basically one reason why the the in this case, the the plan completely is different.
‫They said, hey, you know what, this is a brand new thing.
‫I really need to go to the heap anyway to do this.
‫Well, that's one reason sometimes the index only scan.
‫In this case, we'll always do that.
‫But.
‫Again, it's always not guaranteed, right?
‫Because especially in the count case, like you inserted a row, something is not visible.
‫Everything just just falls apart.
‫So it's really hard for the planner to pick up the right plan based on this information.
‫Right?
‫So so, yeah, that's all I wanted to talk about here.
‫It's really interesting to understand that.
‫And based on that, you can explain some of the things that happened to you.
‫I can't think of every possible situation, but always happen that if a planner does something, it
‫does it for a reason.
‫It might be a bad plan, right?
‫Listen to me.
‫It might be a bad plan because just the statistics are not up to date or the statistics are not really
‫smart enough to for a specific use case.
‫And that's basically another another another talk altogether.
‫This is advanced statistics.
‫Right.
‫And database.
‫All right, guys.
‫That's that's it for me today.
‫Hope you enjoyed this lecture.