‫Aggregating large entries in the database to perform a count, for example, is a lot of work, the
‫database has to sort through a large number of records, whether it's in an index or on their raw heap
‫table itself.
‫Doing this too often can impact the performance of both your database and your application that is discussed.
‫Why count can be slow and an alternative if you really don't want the actual company.
‫Yes, you want an estimate that's just jump into it.
‫Origo.
‫So there are many ways you can execute a count on your database or avea a great stable or famous student.
‫Great sabl.
‫So there is a G which the grade itself does the idea of the students and I think it has like around
‫60 million rows are created.
‫So what I'm going to do here is do a select count G.
‫From grades where I'd say between 3000 and 4000 grades of those people, but I just want to count if
‫you execute that it's so fast right now, don't pay attention to the speed because I have Cash-in going
‫on.
‫I ask you this question many times, but I want to pay attention to that number that come back 1900.
‫And the reason this number is little bit low from comparing 2000 and 4000 should be around 3000, because
‫Count G will return entries that are not null.
‫And just just think about it.
‫I have an index on the ID field that means the database have used that index to pull the rows in order
‫to count them.
‫So we're on the index, but we asked the database to do a count on, gee, that means we'd have to go
‫to the table to check those.
‫The value of gee, whether it's null or not, does do and explain.
‫It was just add and explain and analyze before these puppies.
‫Let's see what happened here.
‫I do that.
‫Let's pay attention to what happened here.
‫We're doing an index, a scan of this, and we have a return of three thousand and one rose because
‫guess what?
‫We're in the index and the actual index entries.
‫I don't have any deleted rolls, by the way, or so.
‫Three thousand and one is about.
‫What that that final count has been reduced because we went back to the table to get the value, because
‫that's where we ask that.
‫We ask the value.
‫And when you when you do count here in any fields, the database will filter through the fields that
‫only count the not null values.
‫And I have few nulls there.
‫OK, so now it uses an index to scan and that tells me that a isco index.
‫But I have to go to the table.
‫It's not an index only scan the spice things up and see.
‫Do the same thing here, but I'm going to do a select star this time, and a lot of people have the
‫misconception that Count Star actually goes to the table and Fitch all the fields and count the fields.
‫No, almost no database do this anymore.
‫Right.
‫Count star essentially means just count whatever entries you have.
‫Right.
‫This will include null.
‫That is if you're scouting an index, given the values.
‫Right.
‫And you can see that we got a higher number.
‫Let's take a look at that plan that did that that Posterous used to do that stuff.
‫What did you use?
‫Postgres.
‫Alimi.
‫A few we look at the plan now, look at this, it's an index only scan and always index only scan always
‫trumps and always it's better than the actual index scan because I don't really need to go back to the
‫table again.
‫Don't pay attention to these numbers because I have caches all over the place.
‫I just wanted to the most important thing is to understand that plan.
‫Running these numbers don't mean anything right now because first of all, I'm in a container.
‫I have a large amount of money.
‫So the database will start cashing these pages if I execute them over and over again.
‫But just understand that plan is the most important thing.
‫And you, as a as a result, the larger the number of rows come back, the more work the database is
‫doing.
‫So 118 index won't be scanned.
‫All right.
‫I say, what are you trying to do here?
‫Here's what I'm going to do.
‫I'm going to do an update now, I'm going to do an update grades, ciggy equal.
‫20, where I.D. as between 2000 and 4000, so those roads between 3000 and 4000, I'm going to slam
‫all of them and update them.
‫And change this, let's see how the baskets are all freaked out now, what will happen?
‫I'm going to ask this can't start and then let's see what will happen all of a sudden.
‫Guys, look, this number jumped again, not by much, but it is significant.
‫The more rose, the more actual real data.
‫You have this.
‫Got that.
‫But but look at that.
‫It still says index only scan, but look at this fitchett.
‫I always to pay attention to this.
‫The moment you start seeing he pushes that mean index game.
‫Yeah, we only scan the index, but we had to go back to the table six thousand and two times.
‫Right.
‫Or these amount of values.
‫I'm not sure this these are the blocks of actual roads I have to have to go back and check, but we
‫have to go back this amount of time, which is expensive.
‫Why?
‫Because we have updated the values.
‫The visibility map told the index scanner that, hey, by the way.
‫Yeah, you're scanning the index.
‫And I have I'm going to give you only values in the index, which is usually fast, again, if you're
‫not scanning the whole index.
‫But these rules that I'm scanning in the index might have been updated, might have been deleted.
‫So I have to go back to the heap where the actual visibility of the rule exists to check if the rule
‫is actually deleted.
‫Because when you delete something in both cases, the index is not immediately marked as deleted.
‫It just adds that adds a new record and just.
‫And keeps the old records for four NBCC reasons, so multiple currency controls so other transactions
‫can see those old temples.
‫All right, so how do we feel?
‫How do you solve this problem?
‫Very simple.
‫You just vacuum the table.
‫Vacuuming the table will update the visibility map, saying that, by the way, those Staib, those
‫old rules that you just updated, nobody's reading them.
‫No, there are no running transactions that we're reading them in our production system.
‫There might be, but no now.
‫So if I do now the same Equidae again.
‫So if we do it again, you can see that we got he features zero.
‫All right.
‫And guys, every time I increase that number three thousand four, you can see that this is going to
‫get slower and slower and slower just to just to show you that, for example, now it have one and a
‫half second to execute.
‫And then if I put that number a little bit because they oh, you can feel it.
‫So count is not cheap, right?
‫It was cheap for the three rolls that I'm going to present.
‫But every time you do that, the plan now change says, OK, we're still going to do an index, but
‫I'm going to use threading the data decided to do multiple threads, multiple workers, administrative
‫to multiple workers to scan that index so I can give you the results.
‫Right.
‫So we're good.
‫We didn't do any searches, but it took six seconds to return this many rows.
‫Right.
‫So every time you increase that number goes larger, the operation is going to go slower.
‫It's just it's a proportionate was just so.
‫So.
‫All right.
‫So what if I say I don't care about the actual roll count.
‫Right.
‫And really care about giving the actual exact value.
‫Right.
‫But here's what I want to do.
‫I want you just to give me an estimate, if you do just then explain.
‫Right.
‫And let's just format this.
‫So it's Jasni, right?
‫You do that.
‫That without it and analyze again, without and analyze, analyze, actually execute the query, explain
‫will not execute that way, but it will estimated it will estimate that, hey, I'm going to use the
‫index.
‫I am planning that I might get this much road back two thousand eight hundred sixty eight.
‫Right.
‫So compared to.
‫The select count, so it's not an accurate number.
‫It's it's 200 values up and a lot of people use this, and I actually got this trick from a blog that
‫I'm going to reference below.
‫Let's remember that the table itself has some statistics to to update itself.
‫Right.
‫So that is actually without actually looking at the table, it knows roughly how many rows or in table,
‫roughly how much raw data will come back of this or these actual correct numbers.
‫Absolutely not.
‫But if you're building Instagram or building something that law account accountant likes or something
‫like that, this is way better.
‫Right.
‫So I can quickly estimate this stuff.
‫And as you update your table, obviously a good idea to do analyze on your grades, Abel, or your table
‫that will update the statistics to the correct numbers.
‫And obviously, this operation is going to take a long time.
‫All right, guys, that's it for me today.
‫That was count and how count is as exponentially a lot of work for the database.
‫And if you really don't need an actual count, correct number, especially if it's in the millions,
‫why would you show the user 60 million and 320 exact number?
‫Right.
‫Almost no one does that, Alysse.
‫Yeah.
‫So it's an estimate.
‫Always matter.
‫If you're working with a few arrows, you can do an actual count.
‫But if you expect the table to grow avoided using an Excel account, does that do an estimate with that
‫planner like that if that works for you?
‫Hope that that's way more than enough.
‫Right.
‫Again, guys, I'm going to see you on the next one.
‫You guys say awesome.
‫Goodbye.