Shadowing Practice: SELECT COUNT (*) can impact your Backend Application Performance, here is why - Learn English Speaking with Video
בחר מצב תרגול
כל מצב פותח חדר תרגול במסך מלא.
156 משפטים
אם המשפטים קצרים או ארוכים מדי, לחץ על ערוך כדי להתאים אותם.
1
aggregating large entries in the database to perform a count for
2
example is a lot of work the database has to sort
3
through large number of records whether it's in an index
4
or in the raw heap table itself doing this too often can impact the performance of both your database
5
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.
6
Let's just jump into it.
7
All right, guys.
8
So there are many ways you can execute a count on your data.
9
So I have here a grades table, our famous student grades table.
10
So there is a G, which the grade itself.
11
That's the ID of the students, right?
12
And I think it has like around 60 million rows that I created.
13
So what I'm going to do here is do a select count G from grades,
14
where id between 1000 and 4000 give me the grades of those people
15
but i just want to count if you execute that it's
16
so fast right now don't pay attention to the speed
17
because i have caching going on i executed this query many times
18
but i want to pay attention to the number that come back
19
2900 and the reason this number is a little bit low from comparing 2000
20
and 4000 which around should be around 3000
21
because count g will return entries that are not null
22
and just just think about it i have an index on the id field
23
that means the database have used
24
that index to pull the rows in order to count them
25
so we're on the index but we asked the database to do a count on g
26
that means we had to go to the table to check those uh the value of g whether it's null
27
or not So let's do an explain.
28
Let's just add an explain analyze before these puppies.
29
Let's see what happened here.
30
Why do that?
31
Let's pay attention to what happened here.
32
We're doing an index scan on Postgres.
33
We have returned 3001 rows because guess what?
34
We're in the index.
35
The actual index entries, I don't have any deleted rows by the way here.
36
So 3001 is about right, but the final count has been reduced
37
because we went back to the table to get the value G because that's what we asked it.
38
We ask, hey, count the value G.
39
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.
40
And I have few nulls there.
41
Okay.
42
So now it uses an index scan.
43
And that tells me that, hey, I scan the index
44
but i had to go to the table it's not an index only scan right let's spice things up
45
and see do the same thing here
46
but i'm going to do a select star this time
47
and a lot of people have a misconception
48
that count star actually goes to the table and fetch all the fields
49
and count the fields no almost no database do this anymore
50
right count star essentially means just count whatever entries you have right
51
this will include null values if you're scanning the index give me the values right
52
and you can see that we got a higher number
53
so let's take a look at the plan that did
54
that postgres used to do that stuff what did you use postgres tell me
55
if you will look at the plan now look at this it's It's an index only scan
56
and always index only scan always trumps
57
and always it's better than the actual index scan because I don't really need to go back to the table.
58
Again, don't pay attention to these numbers because I have caches all over the place.
59
I just want you to, the most important thing is to understand the plan.
60
Running these numbers don't mean anything right now because first of all, I'm in a container.
61
I have a large amount of memory, so the database will start caching these pages if I execute them over and over again.
62
But just understand that plan is the most important thing.
63
And as a result, the larger the number of rows come back, the more work the database is doing.
64
So 118 milliseconds.
65
Index only scan.
66
All right, Jose, what are you trying to do here?
67
Here's what I'm going to do.
68
I'm going to do an update now.
69
I'm going to do an update grades.
70
Set g equal 20, where id is between 1,000 and 4,000.
71
So those rows between 1,000 and 4,000, I'm going to slam all of them and update them.
72
Change this.
73
Let's see how the Postgres will freak out now.
74
What will happen?
75
I'm going to execute this count star, and then let's see what will happen.
76
all of a sudden guys look this number jumped again not by much
77
but it is significant the more rows the more actual real data you have this will go back
78
but but look at that it still says index only scan
79
but look at this heap features i want you to pay
80
attention to this the moment you start seeing heap features that means index only scan yeah we did only scan the index,
81
but we had to go back to the table 6,002 times, right?
82
For this amount of values, right?
83
I'm not sure these are the blocks or the actual rows.
84
I have to go back and check.
85
But we had to go back this amount of time, which is expensive, right?
86
Why?
87
because we have updated the values the visibility map told the index scanner
88
that hey by the way yeah you're scanning the index
89
and i have i'm going to give you only values in the index which is
90
usually fast again if you're not scanning the whole index
91
but these rows that i'm scanning in the index might have been updated might have been deleted
92
so i have to go back to the heap
93
where the actual visibility of the row exists to check if the row is actually deleted.
94
Because when you delete something in Postgres, the index is not immediately marked as deleted.
95
It just adds a new record and keeps the old records for MVCC reasons,
96
so multiple concurrency control, so other transactions can't see those old tuples.
97
All right.
98
So how do we fall?
99
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
הורד אפליקציה
כל מה שאתה צריך כדי לדבר בשטף
הגיית AIציון לכל משפט
תרגול IPAשלוט בכל צליל
אוצר מיליםבנה את מאגר המילים שלך
משחק אוצר מיליםלמד תוך כדי משחק
אותו ערוץ
FOR YOU
המלץ על סרטון
C11
27 משפטים
You are wasting memory
Daily ConversationUniversity StudentsWorking Adults
C11
28 משפטים
PostgreSQL in 100 Seconds
Daily ConversationWorking Adults
C11
218 משפטים
Next.js 16.3 got good (if you care)
Daily ConversationWorking Adults
C11
54 משפטים
Traceroute: You're Probably Reading the Results Wrong
Daily ConversationUniversity StudentsWorking Adults
C11
158 משפטים
OWASP Top 10 Explained
Daily ConversationUniversity StudentsWorking Adults
C11
689 משפטים
Database Sharding! Designing Data-Intensive Applications chapter 6
Daily ConversationUniversity StudentsWorking Adults
C13
444 משפטים
Here's what's REALLY Happening...
Daily ConversationWorking Adults
C12
20 משפטים
SAP Concur migrates over 85,000 SQL Server databases to Amazon Web Services | Amazon Web Services
Daily ConversationWorking Adults
C11
114 משפטים
Design a File Upload Service Like a Senior Engineer
Daily ConversationUniversity StudentsWorking Adults
C1109 משפטים
Explain IEnumerable in C# ( C# Interview Questions)
Daily ConversationWorking Adults
C11
77 משפטים
The Secret Sauce Behind NoSQL: LSM Tree
Daily ConversationUniversity StudentsWorking Adults
אוצר מילים והערות הגייה לשיעור הזה
שיעור הדיבור הזה ברמה C1 מבוסס על הסרטון “SELECT COUNT (*) can impact your Backend Application Performance, here is why”. המילים שחוזרות הכי הרבה: index, count, row, table, scan. בסרטון הזה יש 156 משפטים ו-1723 מילים לתרגול Shadowing. הדיבור נמשך 10:37. הדובר מדבר בקצב טבעי של כ-162 מילים בדקה, קרוב לשיחה יומיומית. 86% מהמילים נמצאות בין 3,000 המילים הנפוצות ביותר באנגלית; כדאי ללמוד את השאר לפני שמתחילים.
אוצר מילים מרכזי בסרטון הזה
8 המילים המתקדמות ביותר בסרטון, עם ההגייה והמשמעות שלהן:
| מילה | הגייה | משמעות |
|---|---|---|
| scan פועל | /skæn/ | סָרַק |
| execute פועל | /ˈɛksɪˌkjuːt/ | להוציא להרוג |
| cache שם עצם | /kæʃ/ | מִצְבּוֹר, מַחְבּוֹא |
| vacuum שם עצם | /ˈvæ.kjuːm/ | ואקום, ריק |
| fetch פועל | /fɛt͡ʃ/ | הביא, השיג |
| spouse שם עצם | /spaʊ̯s/ | בֶּן זוּג, בַּת זוּג |
| puppy שם עצם | /ˈpʌpi/ | כלבלב, גּוּר |
| container שם עצם | /kənˈteɪ.nəɹ/ | מיכל |
Phrasal verbs שתשמעו
| מילה | משמעות |
|---|---|
| come back פועל | שָׁב |
משפטים שכדאי לחזור עליהם
משפטים קצרים ושלמים מהסרטון שאפשר להשתמש בהם בשיחה יומיומית:
- 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?
דקדוק בסרטון הזה
המבנים שהדובר משתמש בהם הכי הרבה, עם המילים המדויקות מהסרטון:
| מבנה | בסרטון |
|---|---|
| סביל (Passive voice) be + past participle — הדגש הוא על מה שקורה, לא על מי שעושה את הפעולה | has been reduced · have been updated · is actually deleted |
| הווה מושלם (Present perfect) have/has + past participle — פעולה שקרתה בעבר ועדיין יש לה חשיבות או השפעה עכשיו | have returned · has been reduced · have updated |
| משפטי זיקה (Relative clauses) who / which + clause — מידע נוסף על אדם או דבר | time, which is · index which is |
הגייה ששווה לשים לב אליה
הדובר משתמש ב-40 קיצורים וצורות מקוצרות, כמו I'm, don't, you're. אמרו אותם בצורה הקצרה, כפי שאתם שומעים אותם.
- הצלילים של “sh” ו-“zh”: cache /kæʃ/, misconception /ˌmɪs.kənˈsɛp.ʃən/
- מילים ארוכות — שימו לב להטעמה: visibility /ˌvɪz.əˈbɪl.ɪ.ti/, concurrency /kəŋˈkʌɹənsi/, millisecond /ˈmɪlɪˌsɛkənd/, misconception /ˌmɪs.kənˈsɛp.ʃən/
צלילים שדוברי עברית מתקשים בהם:
- /æ/ — פתוח יותר מה-“אֶ” העברית, לא כמו “a” ב-“cup”: scan /skæn/, analyze /ˈæn.əˌlaɪz/, cache /kæʃ/, vacuum /ˈvæ.kjuːm/, planner /ˈplænɚ/
- /r/ — הלשון לא נוגעת בשום מקום, לא ה-“ר” הגרונית: query /ˈkwɪɹ.i/, concurrency /kəŋˈkʌɹənsi/, aggregate /ˈæɡ.ɹɪ.ɡət/, container /kənˈteɪ.nəɹ/
- עיצור קולי בסוף מילה — /b/, /d/, /g/, /z/, /v/ בלי להפוך אותם לאטומים: analyze /ˈæn.əˌlaɪz/, millisecond /ˈmɪlɪˌsɛkənd/
איך לתרגל עם הסרטון הזה
- הקשיבו לכל הסרטון פעם אחת בלי לדבר, ורשמו את המילים שאתם לא מכירים.
- התחילו במהירות 0.75×, תרגלו Shadowing משפט אחר משפט, וחזרו למהירות רגילה כשזה מרגיש קל.
- הקליטו את עצמכם והשוו למקור, תוך תשומת לב למילים כמו scan, execute, cache.
מהי טכניקת השאדואינג?
שאדואינג היא טכניקת למידת שפה מבוססת מדע שפותחה במקור להכשרת מתורגמנים מקצועיים ופופולרית על ידי הפוליגלוט ד"ר אלכסנדר ארגואלס. השיטה פשוטה אך עוצמתית: אתה מקשיב לאודיו באנגלית ילידית וחוזר עליו בקול רם מיד — כמו צל העוקב אחרי הדובר עם עיכוב של 1–2 שניות בלבד. בניגוד להאזנה פסיבית או תרגילי דקדוק, שאדואינג מכריח את המוח והשרירים בפה לעבד ולשחזר דפוסי דיבור אמיתיים בו זמנית. מחקרים מראים שזה משפר משמעותית את דיוק ההגייה, האינטונציה, הקצב, הדיבור המחובר, הבנת השמיעה ושטף הדיבור — מה שהופך אותו לאחת השיטות היעילות ביותר להכנה לIELTS Speaking ולתקשורת באנגלית בעולם האמיתי.