‫So now, if I do explain and allies select Count Starr from grades, parts where G equal 30.
‫So now which table is going to where are we going to head?
‫Guys, we should head the first partition.
‫Right.
‫Let's check this out.
‫So we says, hey, index can only on what's for you G 003 G index on this partition.
‫So only had one partition.
‫Right.
‫And Tokers one second takeaway still.
‫Yeah.
‫This is not much compared to what.
‫To the original table.
‫Right.
‫The original table.
‫You're going to see guys.
‫It's the same thing.
‫Hosain, you say that partition are great and now we're going to be faster.
‫Not really.
‫If you think about it, my EMAC here is that sixteen ram, sixteen of Ram and the docker container doesn't
‫have a limit.
‫I believe I didn't have a limit.
‫So it's going to use all the memory it needs and that is the same identical query.
‫There is no bounding limit, there's no memory, but there is no Eobard.
‫If I limited the container to like a five hundred megs.
‫Right.
‫And the index was so large such that it doesn't fit a memory, then you can start seeing the difference
‫hitting that huge index versus hitting the smaller index.
‫But now you don't see it, guys.
‫So how about we show you the the the sizes of these indexes now compared to what we have here?
‫So overall, I remember the query select relational.
‫All right.
‫So the function called PJI relation size, that's a function that gives you the size of the relation
‫and that's an index on table or anything that's going to or idee.
‫That's Object ID.
‫And then I'm going to also select the relation name.
‫And from PJI classes, the table order by exactly the same thing, relation, size or ID.
‫Descending because I want the biggest ones first.
‫All right, so you can see this, that our original table, Gray's original and it's around what?
‫That's around that's bitts, right, so you divide it by ten thousand one hundred thousand, so you
‫get this one megabyte, so on three hundred sixty two megabyte, which is not big.
‫Right.
‫But multiply that four instead of 10 million.
‫Make it 100 million.
‫Twenty five hundred million.
‫Then you can still see this increase.
‫And these are the other partition.
‫That's the the the.
‫Is the rest of my partitions, partition, one partition, do all the partition, and these are the
‫sizes, though not something that large, this is also one 26 megabyte.
‫Apparently, this is zero to thirty five is a very popular grade.
‫That's bad.
‫Everybody is failing.
‫So the original index is around, what, sixty nine megabyte.
‫That's a big large and existing nine megabytes six now.
‫It was nothing for my for my Mac.
‫That's why we didn't see a difference.
‫But increase the number, the size of the rows will increase the number of the index and that will slow
‫things down even more so.
‫But look at this guy, the individual indexes are way smaller than the large indexes or so the individual
‫index, which is the zero thirty five.
‫Right.
‫That's the first politician is on only twenty four megabytes.
‫So sounds like, what, three times smaller.
‫And obviously queering this index is way faster than queering this index.
‫Right.
‫The size and the index matter.
‫Where is the last and the scatter indexes and all that stuff guys.
‫So a really really really matter.
‫Guys this, this a little bit information.
‫And the final thing I'm going to show here is there's a feature in Postgres, I want you to make sure
‫that it's on and it's called Let Me Get It Right Here.
‫It's called enable partition pruning.
‫Right.
‫Let's start.
‫Let's see.
‫What's the value of that?
‫Enable partition pruning the value of whenever partition and pruning is on.
‫And you want this on guys by default if it's off.
‫Well, how about I show you what happened if this is off.
‫Right.
‫So let's sit let's sit this two off and this is off.
‫And I do explain analyze what I really need to analyze.
‫Just explain, select, count.
‫Start from grades, pass the partition table where G equal 30, so which tradition this is supposed
‫to partition one but nope, look at this stuff.
‫It had partition one and partition two, it had partition three and had partition four, and I have
‫no idea why do we have this option to begin with?
‫Maybe it's a safeguard somewhere and prosperous, but this is dangerous.
‫So if this is off, you just waste it.
‫This is the whole partition is useless because you had you had all the four indexes, all the four partitions.
‫Compare this to say it to on actually execute the same query again.
‫And look at this.
‫We only had one partition.
‫So make sure that this this thing, that partition and pruning is always enabled.
‫Otherwise you will not take advantage of this thing, guys.