1
00:00:00,060 --> 00:00:00,840
‫Hey, everybody.

2
00:00:00,930 --> 00:00:12,360
‫It is November 2021, and you guys have submitted a lot of good questions in the month of October.

3
00:00:12,870 --> 00:00:15,890
‫So I thought I'll make another video.

4
00:00:16,470 --> 00:00:17,400
‫Another lecture.

5
00:00:17,820 --> 00:00:26,940
‫What I answer those topic question that I pick that I felt that are interesting and then answer them

6
00:00:26,940 --> 00:00:27,810
‫in detail.

7
00:00:27,970 --> 00:00:34,620
‫And I really enjoyed making the October video and you seem you guys seem to also enjoy it.

8
00:00:35,160 --> 00:00:39,540
‫And as it gives you this, you know, it's almost like a live experience.

9
00:00:39,540 --> 00:00:40,680
‫Almost not quite.

10
00:00:41,100 --> 00:00:45,150
‫But how about we jump into your interesting questions?

11
00:00:45,150 --> 00:00:51,500
‫I'll try to answer as much as I can, but usually I always give it lengthy answers, right?

12
00:00:51,510 --> 00:00:54,480
‫Then all the questions that I'm going to go through are all the answered.

13
00:00:54,990 --> 00:01:02,180
‫Yeah, but I'll I'll provide my own in-depth answer when I when I go through them.

14
00:01:02,890 --> 00:01:11,490
‫So one answer seventy one to question 72 hours ago from Alex when the question goes as follows.

15
00:01:12,090 --> 00:01:17,920
‫Why can't I get bitmap index scan was the last equal statement.

16
00:01:17,920 --> 00:01:22,830
‫So this is this particular question is very related to one of the lectures.

17
00:01:23,190 --> 00:01:30,180
‫So they were trying to go through the lecture, which I explain bitmap index, scan, index, scan and

18
00:01:30,180 --> 00:01:30,960
‫tables cam.

19
00:01:31,290 --> 00:01:33,970
‫And they were following badly.

20
00:01:34,080 --> 00:01:40,230
‫They're following my lecture and they execute inadequate, but they were given different results and

21
00:01:40,230 --> 00:01:44,760
‫this is absolutely no more different result in terms of the plan.

22
00:01:45,000 --> 00:01:52,570
‫The database depends on the version in the patch, depends on the nature of the query print of the the

23
00:01:52,590 --> 00:01:55,200
‫statistics of the square of the table.

24
00:01:55,200 --> 00:01:57,690
‫At that point, we'll give you a different plan.

25
00:01:57,690 --> 00:01:59,690
‫What a better give you the same result.

26
00:02:00,540 --> 00:02:06,200
‫So we when I do something, when I show you something and I show you that that database at that point,

27
00:02:06,210 --> 00:02:09,990
‫you chose that plan in version.

28
00:02:09,990 --> 00:02:14,580
‫In the new version of Bosca's, it might decide to change that algorithm and take a different plan.

29
00:02:14,730 --> 00:02:20,040
‫So just keep that in mind with that out of the way, this is actually explainable.

30
00:02:20,760 --> 00:02:23,160
‫So here's here's the query they were executing.

31
00:02:23,380 --> 00:02:31,560
‫And so here they are showing that the great stable, which is around a million row here and this is

32
00:02:31,710 --> 00:02:39,580
‫where they were executing explain select name from grades where grade is greater than 95.

33
00:02:39,600 --> 00:02:41,160
‫Can we expand the expand this?

34
00:02:41,490 --> 00:02:42,150
‫I guess we can.

35
00:02:42,810 --> 00:02:43,860
‫Maybe it's better this way.

36
00:02:44,340 --> 00:02:47,530
‫Yeah, where the grade is greater than ninety five, right?

37
00:02:47,880 --> 00:02:50,220
‫And the idea is is less than 1000.

38
00:02:50,260 --> 00:02:54,420
‫So just to go through the table again, the schema itself.

39
00:02:54,430 --> 00:03:01,920
‫And thank you so much, Alex, for giving us fantastic screenshots for us so we can go through this

40
00:03:02,100 --> 00:03:03,420
‫in a nice manner.

41
00:03:03,720 --> 00:03:05,700
‫So the grade schema is like this.

42
00:03:05,970 --> 00:03:13,100
‫We have an ID field and this is the default primary key on this particular table, right?

43
00:03:13,380 --> 00:03:16,020
‫And shows here grades primary key, right?

44
00:03:16,890 --> 00:03:24,420
‫So we have a primary key that means it is unique by default on the feel and there is another index or

45
00:03:24,840 --> 00:03:35,160
‫on the grades for these students, and it effectively has a just a normal index, and they are including

46
00:03:35,160 --> 00:03:37,860
‫the ID as part of their index here.

47
00:03:38,100 --> 00:03:41,040
‫So this is a non key column and this is a that key column.

48
00:03:41,820 --> 00:03:46,680
‫So the query that query goes as follows.

49
00:03:46,950 --> 00:03:51,320
‫This is a where G is greater than 95.

50
00:03:51,870 --> 00:03:59,880
‫Give me all the students that have their ideal is the thousand and only of those thousand.

51
00:04:00,930 --> 00:04:04,650
‫Give me those who scored more than 95.

52
00:04:05,340 --> 00:04:05,570
‫All right.

53
00:04:05,910 --> 00:04:08,910
‫The query doesn't make doesn't need to make sense, right?

54
00:04:08,940 --> 00:04:11,040
‫But just understand what is it doing?

55
00:04:11,700 --> 00:04:17,970
‫And every time they execute this, they are getting an index scan.

56
00:04:18,330 --> 00:04:18,660
‫Right?

57
00:04:19,320 --> 00:04:24,600
‫So the question is why they're getting an index scan where they're selecting the name of those tools.

58
00:04:24,600 --> 00:04:25,500
‫That's or they're not.

59
00:04:26,160 --> 00:04:28,050
‫So the question is why right?

60
00:04:28,470 --> 00:04:30,690
‫As opposed to a bitmap index scan?

61
00:04:31,770 --> 00:04:34,260
‫So what is the best method next year?

62
00:04:34,860 --> 00:04:38,130
‫I would explain it, but it doesn't hurt, right to explain it again.

63
00:04:38,670 --> 00:04:42,270
‫A bit of index scan is, is is a technique.

64
00:04:42,930 --> 00:04:50,700
‫I think it's especially in Postgres, but I think other databases implement them where if there are

65
00:04:50,700 --> 00:05:04,200
‫multiple index indexes, the database will scan the first index, but not to to get the actual rose

66
00:05:04,200 --> 00:05:07,470
‫from the index that satisfies a particular query.

67
00:05:07,890 --> 00:05:15,490
‫No, it finds that pages that might have those results right?

68
00:05:15,810 --> 00:05:21,930
‫So if because one page goes, you gotta watch the video?

69
00:05:22,470 --> 00:05:24,120
‫I'll just add it in October.

70
00:05:24,510 --> 00:05:30,480
‫This is a very critical video where I explain what it is a rabbit or a page, a table owl row or IDs.

71
00:05:30,480 --> 00:05:31,140
‫All these things.

72
00:05:31,140 --> 00:05:34,590
‫These are fundamental building blocks that you need to understand.

73
00:05:34,600 --> 00:05:36,750
‫So when I mention them, they make sense.

74
00:05:38,130 --> 00:05:41,100
‫So what page have many rows, right?

75
00:05:41,340 --> 00:05:48,150
‫So the index will actually include two pieces of information when you find a particular value if it

76
00:05:48,180 --> 00:05:53,820
‫points to the I.D. and opposed to which page this row lives in, right?

77
00:05:54,880 --> 00:06:01,990
‫A bit of index can only takes note of the pages, so it will scan the index, find all the candidate

78
00:06:01,990 --> 00:06:05,410
‫rows, but discard the rows and find the pages instead.

79
00:06:05,770 --> 00:06:06,130
‫Right.

80
00:06:06,520 --> 00:06:13,090
‫So we're going to come to why the pages will definitely have more rows than the actual values that we

81
00:06:13,270 --> 00:06:14,020
‫pulled, right?

82
00:06:14,340 --> 00:06:16,870
‫They were going to have more stuff and more stuff that we don't need.

83
00:06:16,870 --> 00:06:17,470
‫Definitely.

84
00:06:18,070 --> 00:06:18,820
‫But here's the thing.

85
00:06:19,100 --> 00:06:26,230
‫It builds a bitmap kind of index right of of of a like a bit gate, which says, OK.

86
00:06:26,440 --> 00:06:28,630
‫Oh, and this gets us all zeroed out.

87
00:06:28,930 --> 00:06:35,680
‫And if it detects like that as a result of this scan, I found Page Zero, I need to pull Page Zero.

88
00:06:35,680 --> 00:06:36,340
‫Page one.

89
00:06:36,490 --> 00:06:38,260
‫Page 17, page 19.

90
00:06:38,590 --> 00:06:39,310
‫That's what it does.

91
00:06:39,310 --> 00:06:41,230
‫It does nothing else.

92
00:06:41,760 --> 00:06:42,040
‫All right.

93
00:06:42,460 --> 00:06:43,030
‫And then.

94
00:06:43,960 --> 00:06:48,910
‫It scans the other index, says, OK, I'm going to now scans grade 95, right?

95
00:06:48,940 --> 00:06:52,820
‫Whatever, that's a given index, it does exactly the same thing, says it.

96
00:06:53,260 --> 00:06:55,750
‫Take note of what pages it needs to pull.

97
00:06:56,140 --> 00:06:56,950
‫So it's going to appeal.

98
00:06:57,050 --> 00:07:00,820
‫So we said zero, one, two and 17 for the first index.

99
00:07:01,030 --> 00:07:08,380
‫The second one is, I don't know, three, four, five and 17, let's say, for example.

100
00:07:09,250 --> 00:07:14,200
‫So the second index gave results three, four or five pages and Page 17.

101
00:07:14,500 --> 00:07:16,510
‫And the first index gave one two three.

102
00:07:17,050 --> 00:07:18,250
‫And Page 17.

103
00:07:18,940 --> 00:07:23,020
‫Now that the Polska says, OK, well, we need to end.

104
00:07:23,410 --> 00:07:29,620
‫And is that the best thing you can do in a query and eliminates a lot of garbage?

105
00:07:29,890 --> 00:07:38,620
‫And in this particular thing, we end those results when you end means the rule must exist in both index

106
00:07:39,520 --> 00:07:40,630
‫search space, right?

107
00:07:41,050 --> 00:07:49,930
‫So if you have a road that exists and you found it as a result of Page three, but not in the first

108
00:07:49,930 --> 00:07:50,260
‫index.

109
00:07:50,560 --> 00:07:51,730
‫Then you just eliminated.

110
00:07:52,060 --> 00:07:56,170
‫As a result, the only page that shared between those two indexes is Page 17.

111
00:07:56,500 --> 00:08:02,800
‫So you, the pollsters, will discover Page zero one two and will discard page three, four or five.

112
00:08:03,190 --> 00:08:08,950
‫Discard means eliminated from the result and just literally takes these stupid moves and does an actual

113
00:08:08,950 --> 00:08:09,910
‫and between them.

114
00:08:10,240 --> 00:08:16,240
‫So one one one, it's going to be ended with zero zero zero and you're going to end up with a with the

115
00:08:16,240 --> 00:08:21,880
‫new bitmap with only bit 17, which to respond to the page 17 to be pulled.

116
00:08:22,030 --> 00:08:24,940
‫So you go and fetch just that page.

117
00:08:25,630 --> 00:08:26,230
‫Beautiful.

118
00:08:26,380 --> 00:08:29,400
‫So now when you go back, this is quite a bit Mebane next.

119
00:08:29,400 --> 00:08:30,490
‫Again, this is not enough.

120
00:08:31,060 --> 00:08:33,640
‫You go to the heap now you have to go to the heap.

121
00:08:33,970 --> 00:08:36,310
‫And I talked a about what a heap is.

122
00:08:36,670 --> 00:08:37,630
‫Go watch that video.

123
00:08:38,140 --> 00:08:48,430
‫And now when I jump into that heap and pull page 17, I have to reapply those filters.

124
00:08:48,430 --> 00:08:52,170
‫And this is called usually when you see here, it's called filtering, right?

125
00:08:52,180 --> 00:08:54,340
‫You apply a filter on the heap itself.

126
00:08:54,580 --> 00:09:00,820
‫So you pick up page four and 15 and then now you pull all the rows and page 17 and then manually goes

127
00:09:00,820 --> 00:09:09,400
‫through these rows and then filter out, OK, I need I'd thousand and whatever I need, I'd be less

128
00:09:09,400 --> 00:09:11,820
‫than a thousand and great more than 95.

129
00:09:11,830 --> 00:09:16,720
‫And since you have the G and the ID in the heap, you have everything you can.

130
00:09:16,750 --> 00:09:18,430
‫You can definitely do this filter.

131
00:09:18,490 --> 00:09:20,590
‫That's what's that's what a bitmap index can.

132
00:09:20,860 --> 00:09:27,700
‫So sometimes best was, guess will decide to do that right to to optimize certain situation.

133
00:09:27,730 --> 00:09:31,740
‫This seems like a way, a case where it should, right?

134
00:09:31,780 --> 00:09:32,560
‫There is an end.

135
00:09:32,770 --> 00:09:34,480
‫There are two indexes to scam.

136
00:09:34,840 --> 00:09:40,930
‫Why did Postgres didn't use the best method to scan?

137
00:09:41,050 --> 00:09:41,560
‫So what?

138
00:09:42,010 --> 00:09:44,230
‫What Puskas thought here is, you know what?

139
00:09:44,260 --> 00:09:49,510
‫We could be smarter than that because bitmap index scan can be expensive because you're scanning to

140
00:09:49,510 --> 00:09:50,500
‫indexes, right?

141
00:09:51,720 --> 00:09:56,280
‫Look what Bosca's did here, bosses look at this and says, Oh, wait a second.

142
00:09:57,790 --> 00:10:03,910
‫You're querying you want ideas less than a thousand and Postgres actually have statistics.

143
00:10:04,030 --> 00:10:04,690
‫What is it?

144
00:10:04,960 --> 00:10:07,720
‫Ideas is a primary key when you say.

145
00:10:08,730 --> 00:10:12,690
‫It is a primary key that definitely means and will have some statistics.

146
00:10:12,810 --> 00:10:13,110
‫OK.

147
00:10:13,320 --> 00:10:14,970
‫Is this a positive integer?

148
00:10:15,190 --> 00:10:22,950
‫A. Well, that means the values must be that we're going to look at between zero and nine nine nine

149
00:10:22,950 --> 00:10:23,250
‫nine.

150
00:10:24,000 --> 00:10:31,560
‫We will only need 999 rows at maximum.

151
00:10:32,040 --> 00:10:38,700
‫So the moment pollsters realize that is going to only work with nine nine nine rows it automatically

152
00:10:38,700 --> 00:10:39,780
‫flips is what is that?

153
00:10:40,920 --> 00:10:42,660
‫I don't need to do a bitmap index cup.

154
00:10:43,110 --> 00:10:43,950
‫Let me go.

155
00:10:44,250 --> 00:10:45,510
‫I don't even need to scan.

156
00:10:45,810 --> 00:10:46,980
‫I use the other index.

157
00:10:47,220 --> 00:10:47,790
‫I don't care.

158
00:10:48,360 --> 00:10:51,690
‫Let's scan the I.D. The primary key.

159
00:10:52,140 --> 00:10:54,430
‫Pull all those pull over.

160
00:10:55,450 --> 00:10:56,420
‫Less than a thousand.

161
00:10:56,760 --> 00:11:02,910
‫So now are you going to have a lot of rows, right rows from zero to 99?

162
00:11:02,910 --> 00:11:05,850
‫But not all of them is the actual results that you want.

163
00:11:06,090 --> 00:11:09,070
‫Some of them will have grades greater than 95.

164
00:11:09,090 --> 00:11:10,200
‫Some of them will have less.

165
00:11:10,230 --> 00:11:10,530
‫Right.

166
00:11:10,740 --> 00:11:11,970
‫So you need to be careful there.

167
00:11:12,180 --> 00:11:13,140
‫So what process does?

168
00:11:13,230 --> 00:11:16,260
‫OK, now I'm going to pull all the pages.

169
00:11:16,470 --> 00:11:16,700
‫All right.

170
00:11:16,710 --> 00:11:18,540
‫What pages do you need to fetch, right?

171
00:11:18,540 --> 00:11:21,000
‫Because I need to go to the heap anyway, right?

172
00:11:21,240 --> 00:11:22,910
‫To to do this results, right?

173
00:11:23,730 --> 00:11:24,780
‫Because they need the name.

174
00:11:24,780 --> 00:11:28,710
‫The name is not in the primary key idea of index.

175
00:11:29,010 --> 00:11:36,480
‫So now it takes a mark of what pages need to pull and goes to the heap and pull those pages.

176
00:11:36,870 --> 00:11:40,920
‫And it should be a page or two, or maybe less.

177
00:11:40,920 --> 00:11:45,720
‫It depends on on on the size of the rows and all that stuff and then pull these pages.

178
00:11:45,750 --> 00:11:46,770
‫Now what it does.

179
00:11:47,490 --> 00:11:50,980
‫Just go through them and then literally just applies the square.

180
00:11:51,000 --> 00:11:52,530
‫OK, since we infect the heap.

181
00:11:52,800 --> 00:11:54,540
‫We have that the great fields.

182
00:11:54,990 --> 00:11:57,260
‫Let's just filter the manual.

183
00:11:57,270 --> 00:12:03,300
‫And this was way faster Bosca's deciders than actually using bitmap index.

184
00:12:03,540 --> 00:12:10,650
‫Because because the result is so small, if Alex increase this to more than a thousand, I don't know

185
00:12:10,650 --> 00:12:14,370
‫what the number is, to be honest, because it's an implementation detail.

186
00:12:14,370 --> 00:12:14,820
‫Who knows?

187
00:12:14,820 --> 00:12:22,740
‫It's heuristics at the end of the day if if it means less than 10000, 20000, 30000, then pauses or

188
00:12:23,400 --> 00:12:24,540
‫thousands, a lot of rows.

189
00:12:24,540 --> 00:12:27,960
‫I'm not going to scan an index and then go and fetch all the rows.

190
00:12:28,110 --> 00:12:31,770
‫Maybe I can eliminate some some pages by doing a bitmap index.

191
00:12:32,220 --> 00:12:32,460
‫All right.

192
00:12:32,760 --> 00:12:36,120
‫So that's one question that I found interesting.

193
00:12:36,420 --> 00:12:41,310
‫Apologies for that change of scenery and the audio quality.

194
00:12:41,320 --> 00:12:48,630
‫I'm currently troubling and my video was cut due to something.

195
00:12:48,630 --> 00:12:53,130
‫I had to take care of something, but I had to finish the lecture for you guys.

196
00:12:54,000 --> 00:13:05,250
‫The Q&A for November 20, 2021 and we have a very interesting question here from Rakeysh, and it's

197
00:13:05,250 --> 00:13:06,120
‫a three part question.

198
00:13:06,120 --> 00:13:14,400
‫So we're going to and try to answer this question and then just call it a day.

199
00:13:14,430 --> 00:13:15,300
‫How about that?

200
00:13:15,300 --> 00:13:20,580
‫Because it's just there is no time to go through more questions.

201
00:13:21,600 --> 00:13:24,690
‫And guys, let me know, how do you enjoy these kind of content?

202
00:13:25,500 --> 00:13:26,700
‫I think it's beneficial.

203
00:13:27,450 --> 00:13:35,460
‫So the question goes as follows how to choose the order of columns to create a composite index.

204
00:13:35,940 --> 00:13:37,800
‫So I say in my question has three parts.

205
00:13:38,220 --> 00:13:45,540
‫If I have a query that does a filter on, say, 10 columns joined by an another is advisable to have

206
00:13:45,540 --> 00:13:47,730
‫a composite index on all 10 columns.

207
00:13:48,270 --> 00:13:52,290
‫Let's assume for simplicity that this is the only query for the stable.

208
00:13:55,620 --> 00:13:58,910
‫So think about this right now.

209
00:13:58,950 --> 00:14:06,060
‫I already answered this an hour ago, but it's always good to kind of add more details or not explain

210
00:14:06,060 --> 00:14:06,750
‫things like that.

211
00:14:08,140 --> 00:14:13,660
‫So when you create an index, especially a composite index, a composite index is just an index of multiple

212
00:14:13,660 --> 00:14:14,080
‫columns.

213
00:14:14,440 --> 00:14:19,600
‫And the way it is designed, it is, as will be these columns, all the values of these columns will

214
00:14:19,600 --> 00:14:26,440
‫be added together as a tuple in the index and index accordingly.

215
00:14:27,010 --> 00:14:35,950
‫So means it really depends on the data type of what are you indexing and the number of columns you are

216
00:14:35,950 --> 00:14:37,270
‫indexing as well?

217
00:14:38,610 --> 00:14:48,960
‫And this increases the index size, because if you're indexing a lot of columns, that means all these

218
00:14:49,110 --> 00:14:58,200
‫entries, if this is a string as an integer float all adds up really to the size of the index of my

219
00:14:58,200 --> 00:14:58,550
‫tails.

220
00:14:58,560 --> 00:15:00,150
‫And what is a matter, the index.

221
00:15:00,150 --> 00:15:03,780
‫So I have all the space I need, right?

222
00:15:03,780 --> 00:15:09,750
‫I have one petabytes of disk drive and I don't care.

223
00:15:10,290 --> 00:15:12,630
‫Well, we're not talking about really storage.

224
00:15:12,630 --> 00:15:14,160
‫Storage is not an issue anymore.

225
00:15:14,730 --> 00:15:19,020
‫The issue is the O right because.

226
00:15:20,010 --> 00:15:25,410
‫You write all this law, whether a sentence is the boy as is dear, it's much better than hard drive.

227
00:15:25,770 --> 00:15:33,360
‫What you want to minimize is this does this precious are yours because of an index is large and when

228
00:15:33,360 --> 00:15:36,000
‫I'm large, I mean really large.

229
00:15:36,240 --> 00:15:45,600
‫If you have 300 million rows and you are indexing 10 columns, then the index will be proportionately

230
00:15:45,600 --> 00:15:51,540
‫large to the size, not going to be as large as the actual heap table, but it's going to be a large,

231
00:15:52,020 --> 00:15:59,400
‫large index means that that that that structure won't be able to fit in memory.

232
00:15:59,520 --> 00:16:05,670
‫Now, large indexes won't fit in memory, and if they don't fit in memory, they have to be put in desk.

233
00:16:06,360 --> 00:16:17,010
‫And when they are put in desk to read and scan the index, you need to go to describe and you need to

234
00:16:17,220 --> 00:16:24,000
‫read page by page or whatever outnumber the file system allows you to, and that gives you a finite

235
00:16:24,000 --> 00:16:27,540
‫amount number of index entries to read.

236
00:16:28,740 --> 00:16:36,300
‫So the larger the index columns, the more pages you need to read, and that slows that.

237
00:16:36,310 --> 00:16:44,810
‫So that's the first thing is just the additional ISOs you want to do because of the increased site.

238
00:16:45,660 --> 00:16:49,050
‫Another side effect of having many columns.

239
00:16:49,200 --> 00:16:53,450
‫Again, I'm just saying, what is the side effect of what you're proposing wreckage, right?

240
00:16:53,790 --> 00:17:02,040
‫Having an index on many columns and side effect is the right amplification that happens as it as you

241
00:17:02,040 --> 00:17:05,670
‫update your table when you update your table.

242
00:17:06,000 --> 00:17:06,390
‫Right?

243
00:17:07,380 --> 00:17:08,640
‫You add in something you entry.

244
00:17:08,820 --> 00:17:11,490
‫This index is touching a lot of column.

245
00:17:11,500 --> 00:17:17,610
‫So any update to an existing row that touches this column, we'll have to update them.

246
00:17:17,910 --> 00:17:20,870
‫Any new role you insert will have to have digital.

247
00:17:21,600 --> 00:17:22,920
‫Well, it depends of these columns.

248
00:17:22,920 --> 00:17:24,120
‫Are nulls or not.

249
00:17:24,810 --> 00:17:33,450
‫The index might not need to be updated or if you have like, a partial index where you're only indexing

250
00:17:33,450 --> 00:17:39,280
‫certain water clauses that also might not need to update index, but regardless, you need to update

251
00:17:39,280 --> 00:17:41,100
‫the index in these particular situations.

252
00:17:42,120 --> 00:17:50,100
‫So that also adds not necessarily to a performance point of view when you're updating the index.

253
00:17:51,210 --> 00:17:53,850
‫So no to updated index, right?

254
00:17:54,120 --> 00:18:01,020
‫It's just an extra i o to the right that is almost negligible and adds up a little bit, but in a batch

255
00:18:01,020 --> 00:18:03,450
‫mode that slows things down.

256
00:18:04,020 --> 00:18:07,680
‫But it's not negligible when it comes to one off.

257
00:18:07,800 --> 00:18:08,370
‫That's right.

258
00:18:09,660 --> 00:18:23,340
‫The side effect really becomes when you when you when you try to bulk update this right, you're touching

259
00:18:23,340 --> 00:18:23,970
‫the index.

260
00:18:23,970 --> 00:18:26,160
‫And that results in a lot of rights.

261
00:18:26,520 --> 00:18:29,040
‫And that writes, especially in our replication

262
00:18:31,200 --> 00:18:32,120
‫environment.

263
00:18:32,120 --> 00:18:38,960
‫If you're trying to push all this right, then the bandwidth increases, bandwidth increases means and

264
00:18:39,000 --> 00:18:40,360
‫more network latency.

265
00:18:40,560 --> 00:18:46,050
‫And that when you think about that, that's what all were complained about with Postgres compared to

266
00:18:46,050 --> 00:18:52,140
‫Michael Bosca's had way more write amplification compared to my single, for example.

267
00:18:52,140 --> 00:18:59,040
‫So they went one route to minimise the write amplification caused network is precious and goes, I just

268
00:18:59,070 --> 00:19:00,180
‫what I want you to think about.

269
00:19:00,540 --> 00:19:07,050
‫Anything you do has an effect and you don't need anybody to tell you any of this.

270
00:19:07,620 --> 00:19:13,050
‫If you understand the fundamental thing, when you actually get this course, you understand the fundamentals.

271
00:19:13,440 --> 00:19:14,160
‫You don't need me.

272
00:19:15,720 --> 00:19:20,300
‫There's just because, like, say, I can answer any question because I understand the basics and we

273
00:19:20,990 --> 00:19:28,700
‫understand the bare bare metal things, you can just answer any questions just like, Oh, because of

274
00:19:28,700 --> 00:19:29,750
‫this, this will result.

275
00:19:29,750 --> 00:19:33,860
‫This will be the result is just it's just cause and effect, cause and effect.

276
00:19:35,190 --> 00:19:41,720
‫So the second question is, is the query planner smart enough to arrange the filter in order that will

277
00:19:41,720 --> 00:19:43,730
‫be subset of the composite index yet?

278
00:19:44,090 --> 00:19:49,570
‫So for example, if I have a composite index on ABC, I always talk about that letter if I have a BCD.

279
00:19:50,270 --> 00:19:51,590
‫The order does matter.

280
00:19:51,590 --> 00:19:53,060
‫I've talked about that in the video.

281
00:19:53,180 --> 00:19:58,010
‫The composite index has to be from left to right, and they will be built from left to right.

282
00:19:58,340 --> 00:20:06,350
‫So if you're searching on, so he is asking like, what if I my work was started with C Equal 10 and

283
00:20:06,530 --> 00:20:08,270
‫equal 20 and be equal 30.

284
00:20:08,540 --> 00:20:11,270
‫It is a map and is interchangeable.

285
00:20:11,280 --> 00:20:19,880
‫So have Planner better be smart enough to understand that c equal 10 and 20 is the same as equal to

286
00:20:19,880 --> 00:20:28,480
‫any and equal to will that one that includes AWS and ends in that, obviously based on this great mathematical

287
00:20:28,730 --> 00:20:31,010
‫formula that changes, right?

288
00:20:31,460 --> 00:20:31,750
‫Right.

289
00:20:31,970 --> 00:20:34,910
‫But but just discrete algebra stuff.

290
00:20:35,270 --> 00:20:38,330
‫But when it's L and it doesn't matter, you can interchangeable.

291
00:20:38,960 --> 00:20:46,370
‫So just don't worry about it as long as they actually search for a if your stuff is only C and B right

292
00:20:46,700 --> 00:20:49,790
‫and is not included, this won't hit index right.

293
00:20:49,880 --> 00:20:57,650
‫And you probably know this is their best practice for the order in which the column or therefore composite

294
00:20:57,980 --> 00:20:58,820
‫index be chosen.

295
00:21:00,260 --> 00:21:08,480
‫This or indeed, there is no best practice when it comes to these kind of things, like really, if

296
00:21:08,480 --> 00:21:16,790
‫you're if you're querying from the left most columns, if you're counting a certain field and and that

297
00:21:16,790 --> 00:21:24,380
‫field is the only field as being queried, I said just putting that to the left, because just on the

298
00:21:24,380 --> 00:21:31,940
‫logic of the end of the data, what is the minimum subset of filters that you're quoting against?

299
00:21:32,420 --> 00:21:34,910
‫Put that at the left side.

300
00:21:35,000 --> 00:21:36,920
‫Call right you want you.

301
00:21:37,070 --> 00:21:43,220
‫So that means you need to understand the nature of your query by the end of the day, the nature for

302
00:21:43,220 --> 00:21:46,640
‫your queries of very, very critical that cut out.

303
00:21:47,480 --> 00:21:53,300
‫That's why you back in and just have to work with the frontend engineers to know what kind of, I guess,

304
00:21:53,300 --> 00:22:00,920
‫their front drink don't normally issue back squat queries, but just understand how that how that filters,

305
00:22:01,220 --> 00:22:02,780
‫how the workers are being executed.

306
00:22:03,470 --> 00:22:12,590
‫That's what I really don't like to use alarms because they exclude just abstractly leaking the back

307
00:22:12,590 --> 00:22:15,430
‫end and execute codes that we have no idea.

308
00:22:15,530 --> 00:22:17,090
‫It has a mind of its own right.

309
00:22:17,300 --> 00:22:25,060
‫It hides the complexity and as a result adds more and more performance penalty that we engineer Mark

310
00:22:25,070 --> 00:22:25,910
‫to see it right.

311
00:22:25,910 --> 00:22:27,920
‫I just need to see everything.

312
00:22:28,460 --> 00:22:32,900
‫I mean, you probably know this from my YouTube channel I really don't use or MS.

313
00:22:33,380 --> 00:22:36,620
‫I don't like to advise anyone, I guess anything.

314
00:22:36,620 --> 00:22:40,940
‫But if you are using em, right, use it.

315
00:22:41,150 --> 00:22:42,320
‫But understand how it works.

316
00:22:43,160 --> 00:22:45,230
‫Completely understand how it works.

317
00:22:45,410 --> 00:22:48,020
‫You have to understand completely anything component that you use.

318
00:22:48,260 --> 00:22:55,610
‫You really understand what is it doing if you are using it blindly just to save you a couple of lines

319
00:22:55,610 --> 00:22:56,030
‫of code?

320
00:22:56,300 --> 00:22:58,880
‫That's a, in my opinion, that's a bad practice.

321
00:22:59,390 --> 00:23:06,740
‫Never use a code to like a library, just so you don't want to write the next couple of lines of code.

322
00:23:08,060 --> 00:23:15,470
‫If you understand that this actually saves you this and you know how it works without it, and you can

323
00:23:15,470 --> 00:23:16,520
‫build it without it.

324
00:23:17,240 --> 00:23:17,930
‫Yes, absolutely.

325
00:23:17,930 --> 00:23:19,290
‫I'm fine with using it, Armel.

326
00:23:19,340 --> 00:23:25,850
‫Any framework for that map is just my problem becomes when you don't understand what what this thing

327
00:23:25,850 --> 00:23:26,290
‫is doing.

328
00:23:26,300 --> 00:23:28,640
‫This hides a lot of complexity.

329
00:23:28,640 --> 00:23:34,040
‫But as a result, things like this complexity leaks to the front end all the time.

330
00:23:34,760 --> 00:23:36,740
‫Like, You have suction Google that you'll find it.

331
00:23:37,250 --> 00:23:39,020
‫You'll find my video on that topic.

332
00:23:41,350 --> 00:23:46,210
‫Yes, so is it a good idea to create a single column with that 10 column index?

333
00:23:47,850 --> 00:23:53,820
‫It really depends, right, how big it comes, really how how complex are the data types?

334
00:23:54,550 --> 00:23:59,200
‫But you can try it and see if it's effective.

335
00:23:59,200 --> 00:24:03,270
‫It affects you, but normally comes with a lot, right?

336
00:24:03,720 --> 00:24:10,540
‫So if you really understand the queries, my guess is you want need 10 columns in.

337
00:24:11,400 --> 00:24:14,550
‫Just because something is in the way close doesn't mean it has to be indexed.

338
00:24:16,170 --> 00:24:20,670
‫Because when you do an end, especially the database.

339
00:24:21,210 --> 00:24:21,750
‫Think about this.

340
00:24:21,750 --> 00:24:27,630
‫Like, let's say, if you have if you're doing sequel 10, just six equals 10, you just eliminated

341
00:24:27,630 --> 00:24:30,930
‫anything that is not C content, right?

342
00:24:30,960 --> 00:24:33,100
‫But no, because you using an and.

343
00:24:33,600 --> 00:24:37,230
‫This also eliminates a lot of other roles.

344
00:24:37,890 --> 00:24:42,960
‫So now you're working with a subset of that of that table kind of right.

345
00:24:43,530 --> 00:24:51,750
‫So now if you add to that another filter equal 20, then you just eliminated maybe another bunch of

346
00:24:51,750 --> 00:24:52,320
‫rows, right?

347
00:24:53,130 --> 00:25:00,090
‫Think about this how much rows are being eliminated, if if by here you eliminated?

348
00:25:00,090 --> 00:25:03,690
‫I don't know by here, if you are left with.

349
00:25:05,430 --> 00:25:06,560
‫10000 rolls.

350
00:25:07,830 --> 00:25:10,350
‫I would say, yes, maybe just add another index.

351
00:25:10,890 --> 00:25:13,740
‫Right, because now this will eliminate eliminated to three rolls.

352
00:25:14,370 --> 00:25:21,360
‫But if you add these to the index and you are left with, I don't know, two rows.

353
00:25:21,600 --> 00:25:26,010
‫This is absolutely useless because what two rows is going to be eliminated in one row?

354
00:25:26,730 --> 00:25:32,070
‫Yeah, I'd rather just jump to the heap and fetch the page and then filter the extra one, even if it's

355
00:25:32,070 --> 00:25:32,830
‫a thousand either.

356
00:25:32,980 --> 00:25:34,290
‫A little bit.

357
00:25:35,970 --> 00:25:36,780
‫That's OK.

358
00:25:37,500 --> 00:25:42,330
‫So understand the statistics of that table, and I think I'll leave it at that, guys.

359
00:25:42,340 --> 00:25:43,500
‫Thank you so much for watching.

360
00:25:43,800 --> 00:25:44,430
‫Enjoy the course.