शैडोइंग अभ्यास: SELECT COUNT (*) can impact your Backend Application Performance, here is why - वीडियो के साथ अंग्रेजी बोलना सीखें

aggregating large entries in the database to perform a count for

example is a lot of work the database has to sort

through large number of records whether it's in an index

or in the raw heap table itself doing this too often can impact the performance of both your database

and your application let us discuss why count can be slow and an alternative if you really don't want the actual count, but yes, you want an estimate.

Let's just jump into it.

All right, guys.

So there are many ways you can execute a count on your data.

So I have here a grades table, our famous student grades table.

So there is a G, which the grade itself.

That's the ID of the students, right?

And I think it has like around 60 million rows that I created.

So what I'm going to do here is do a select count G from grades,

where id between 1000 and 4000 give me the 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 caching going on i executed this query many times

but i want to pay attention to the number that come back

2900 and the reason this number is a little bit low from comparing 2000

and 4000 which around 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 g

that means we had to go to the table to check those uh the value of g whether it's null

or not So let's do an explain.

Let's just add an explain analyze before these puppies.

Let's see what happened here.

Why do that?

Let's pay attention to what happened here.

We're doing an index scan on Postgres.

We have returned 3001 rows because guess what?

We're in the index.

The actual index entries, I don't have any deleted rows by the way here.

So 3001 is about right, but the final count has been reduced

because we went back to the table to get the value G because that's what we asked it.

We ask, hey, count the value G.

And when you do count G here or count any field, the database will filter through the fields and only count the not null values.

And I have few nulls there.

Okay.

So now it uses an index scan.

And that tells me that, hey, I scan the index

but i had to go to the table it's not an index only scan right let's 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 a misconception

that count star actually goes to the table and fetch 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 values if you're scanning the index give me the values right

and you can see that we got a higher number

so let's take a look at the plan that did

that postgres used to do that stuff what did you use postgres tell me

if you will look at the plan now look at this it's 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 want you to, the most important thing is to understand the 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 memory, so the database will start caching these pages if I execute them over and over again.

But just understand that plan is the most important thing.

And as a result, the larger the number of rows come back, the more work the database is doing.

So 118 milliseconds.

Index only scan.

All right, Jose, 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.

Set g equal 20, where id is between 1,000 and 4,000.

So those rows between 1,000 and 4,000, I'm going to slam all of them and update them.

Change this.

Let's see how the Postgres will freak out now.

What will happen?

I'm going to execute this count star, 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 rows the more actual real data you have this will go back

but but look at that it still says index only scan

but look at this heap features i want you to pay

attention to this the moment you start seeing heap features that means index only scan yeah we did only scan the index,

but we had to go back to the table 6,002 times, right?

For this amount of values, right?

I'm not sure these are the blocks or the actual rows.

I have to go back and check.

But we had to go back this amount of time, which is expensive, right?

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 rows 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 row exists to check if the row is actually deleted.

Because when you delete something in Postgres, the index is not immediately marked as deleted.

It just adds a new record and keeps the old records for MVCC reasons,

so multiple concurrency control, so other transactions can't see those old tuples.

All right.

So how do we fall?

How do you solve this problem?

100

Very simple.

101

You just vacuum the table.

102

Vacuuming the table will update the visibility map saying that, by the way, those old rows that you just updated, nobody's reading them.

103

Nope.

104

There are no running transactions that are reading them.

105

In a production system, there might be, but not now.

106

So if I do now the same query again, so if we do it again, you can see that we got heap features zero.

107

All right.

108

And guys, every time I increase that number 3004, you can see that this is going to get slower and slower and slower.

109

Just to show you that, for example, now it took one and a half second to execute.

110

And then if I increase that number a little bit, you can see, oh, you can feel it.

111

So count is not cheap, right?

112

It was cheap for the 3,000 rows that I'm going to return.

113

But every time you do that, the plan now changes.

114

It says, okay, we're still going to do an index, but I'm going to use threading.

115

The database decided to do multiple threads, multiple workers, not necessarily threads, multiple workers to scan that index.

116

So I can give you the results, right?

117

Still, we're good we didn't do any heap features

118

but it took six seconds to return this many rows right so every time you increase that number goes larger

119

the operation is going to go slower.

120

It's just proportional.

121

It's just so proportional.

122

So all right.

123

So what if I, I'm saying I don't care about the actual row count, right?

124

I don't really care about giving the actual exact value, right?

125

But here's what I want to do.

126

I want you just to give me an estimate.

127

If you do just an explain, right?

128

And let's just format this so it's JSON-y, right?

129

If you do that, without an analyze again, without an analyze, analyze actually execute the query.

130

Explain will not execute the query, but it will estimate it.

131

It will estimate that, hey, I'm going to use the index.

132

I am planning that I might get this much rows back, 2,868, right?

133

So compared to the select count, so it's not an accurate number.

134

It's 200 values up.

135

And a lot of people use this.

136

And I actually got this trick from a blog that I'm going to reference below.

137

Let's remember that the table itself has some statistics to update itself.

138

So Postgres, without actually looking at the table, it knows roughly how many rows are on the table.

139

roughly how much rows will come back are this are these actual correct numbers absolutely not

140

but if you're building instagram or you're building something

141

that like count the likes or something like

142

that this is way better right so i can quickly estimate this stuff

143

and as you update your table obviously a good idea to do an analyze on your grades table

144

or your table that will update the statistics to the correct numbers

145

and obviously this operation is going to take a long time all right guys that's it for me today

146

that was count and how count is essentially a lot of work for the database

147

and if you really don't need an actual count correct number especially

148

if it's in the millions why would you show the the user 60 million

149

and 320 exact number right almost no one does that right

150

and let's see yeah so it's on estimate always matter

151

if you're working with a fewer rows you can do an actual account but

152

if you expect the table to grow avoid you using an

153

excellent account use do an estimate with a planner like that

154

if that works for you hope

155

that that's way more than enough all right again guys i'm

156

gonna see you in the next one you guys stay awesome goodbye

📺 उसी चैनल से

Proxy vs Reverse Proxy Server Explained

Business EnglishUniversity StudentsWorking Adults

I ask this question to every Backend Engineer I interview

Business EnglishInterview EnglishUniversity Students

✨ अनुशंसित वीडियो

53 वाक्य

How to Create Gold, Silver, and Bronze Tables in Postgres for a Medallion Data Architecture!

Business EnglishUniversity StudentsWorking Adults

27 वाक्य

You are wasting memory

Business EnglishUniversity StudentsWorking Adults

158 वाक्य

OWASP Top 10 Explained

Business EnglishUniversity StudentsWorking Adults

29 वाक्य

Amazon Athena explained in 120 seconds💡

Business EnglishUniversity StudentsWorking Adults

111 वाक्य

Financial Analysis Fundamentals: How to Analyze Financial Statements Like a Pro Part 7 | CFI Course

Business EnglishUniversity StudentsWorking Adults

243 वाक्य

Proximity Search & Geospatial Indexes Explained

Business EnglishUniversity StudentsWorking Adults

190 वाक्य

MySQL took 21 years to fix this bug

Business EnglishUniversity StudentsWorking Adults

218 वाक्य

Next.js 16.3 got good (if you care)

Business EnglishUniversity StudentsWorking Adults

Vocabulary and speaking notes for this lesson

This C1 speaking lesson is built on the video “SELECT COUNT (*) can impact your Backend Application Performance, here is why”. The speaker keeps coming back to these words: index, count, row, table, scan. This video has 156 sentences and 1723 words to shadow. The speech runs for 10:37. The speaker talks at a natural 162 words per minute, close to everyday conversation. 86% of the words are among the 3,000 most common in English; the rest is worth studying before you start.

Key vocabulary in this video

The 15 most advanced words in the video, with pronunciation and meaning:

Word	Pronunciation	Meaning
scan verb	/skæn/	To examine sequentially, carefully, or critically; to scrutinize; to behold closely.
execute verb	/ˈɛksɪˌkjuːt/	To kill, especially as punishment for a capital crime.
null noun	/nʌl/	A non-existent or empty value or set of values.
analyze verb	/ˈæn.əˌlaɪz/	To subject to analysis.
query noun	/ˈkwɪɹ.i/	A question, an inquiry (US), an enquiry (UK).
cache noun	/kæʃ/	Such a store of physical supplies, placed by humans or other animals for practical reasons.
visibility noun	/ˌvɪz.əˈbɪl.ɪ.ti/	The condition of being visible.
proportional adjective		At a constant ratio (to). Two magnitudes (numbers) are said to be proportional if the second varies in a direct relation arithmetically to the first. Symbol: ∝.
vacuum noun	/ˈvæ.kjuːm/	A region of space that contains no matter.
concurrency noun	/kəŋˈkʌɹənsi/	The property or an instance of being concurrent; something that happens at the same time as something else.
millisecond noun	/ˈmɪlɪˌsɛkənd/	An SI unit of time equal to 10⁻³ seconds. Symbol: ms
misconception noun	/ˌmɪs.kənˈsɛp.ʃən/	A mistaken belief, a wrong idea.
planner noun	/ˈplænɚ/	One who plans.
scanner noun	/ˈskænə/	One who scans.
fetch verb	/fɛt͡ʃ/	To retrieve; to bear towards; to go and get.

Phrasal verbs you will hear

Word	Meaning
go back to verb	To stem from; to originate in.
come back verb	To return to a place.
go back verb	To return to a place or state after having been there at a previous time.

Sentences worth repeating

Short, complete lines from the video that you can reuse in everyday conversation:

Let's just jump into it.
Let's just add an explain analyze before these puppies.
Let's see what happened here.
Let's pay attention to what happened here.
How do you solve this problem?

इस वीडियो का व्याकरण

वक्ता जिन संरचनाओं का सबसे अधिक प्रयोग करता है, वीडियो के असली शब्दों के साथ:

संरचना	वीडियो में
Present perfect have/has + past participle — बीता हुआ काम जिसका असर अभी भी है	have returned · has been reduced · have updated
Passive voice be + past participle — ज़ोर इस पर कि क्या होता है, न कि कौन करता है	has been reduced · have been updated · is actually deleted
Relative clauses who / which + उपवाक्य — व्यक्ति या वस्तु के बारे में अतिरिक्त जानकारी	time, which is · index which is

Pronunciation to watch

The speaker uses 40 contractions and reduced forms, such as I'm, don't, you're. Say them the short way, as you hear them.

The “sh” and “zh” sounds: cache /kæʃ/, misconception /ˌmɪs.kənˈsɛp.ʃən/
Long words — get the stress right: visibility /ˌvɪz.əˈbɪl.ɪ.ti/, concurrency /kəŋˈkʌɹənsi/, millisecond /ˈmɪlɪˌsɛkənd/, misconception /ˌmɪs.kənˈsɛp.ʃən/

How to practise with this video

Listen to the whole video once without speaking and note the words you do not know.
Start at 0.75× speed, shadow it sentence by sentence, then go back to normal speed once it feels easy.
Record yourself and compare with the original, paying attention to words like scan, execute, null.

शैडोइंग तकनीक क्या है?

शैडोइंग (Shadowing) एक विज्ञान-समर्थित भाषा सीखने की तकनीक है जो मूल रूप से पेशेवर दुभाषिया प्रशिक्षण के लिए विकसित की गई थी। विधि सरल लेकिन शक्तिशाली है: आप मूल अंग्रेज़ी ऑडियो सुनते हैं और तुरंत इसे ज़ोर से दोहराते हैं — जैसे वक्ता की छाया 1-2 सेकंड की देरी से। शोध से पता चलता है कि यह उच्चारण सटीकता, स्वर, लय, जुड़ी हुई ध्वनियाँ, सुनने की समझ और बोलने की प्रवाहशीलता में काफ़ी सुधार करता है।

शैडोइंग तकनीक: पूरी चरण-दर-चरण गाइड पढ़ें →

शैडोइंग अभ्यास: SELECT COUNT (*) can impact your Backend Application Performance, here is why - वीडियो के साथ अंग्रेजी बोलना सीखें

what is a database index?

Proxy vs Reverse Proxy Server Explained

I ask this question to every Backend Engineer I interview

System Design Was HARD - Until You Knew the Trade-Offs

Gaussian Naive Bayes, Clearly Explained!!!

Explain IEnumerable in C# ( C# Interview Questions)

Traceroute: You're Probably Reading the Results Wrong

How to Create Gold, Silver, and Bronze Tables in Postgres for a Medallion Data Architecture!

You are wasting memory

OWASP Top 10 Explained

Amazon Athena explained in 120 seconds💡

Financial Analysis Fundamentals: How to Analyze Financial Statements Like a Pro Part 7 | CFI Course

Proximity Search & Geospatial Indexes Explained

MySQL took 21 years to fix this bug

Next.js 16.3 got good (if you care)

Vocabulary and speaking notes for this lesson

Key vocabulary in this video

Phrasal verbs you will hear

Sentences worth repeating

इस वीडियो का व्याकरण

Pronunciation to watch

How to practise with this video

शैडोइंग तकनीक क्या है?