1
00:00:00,120 --> 00:00:00,780
‫Hey, guys.

2
00:00:00,960 --> 00:00:08,520
‫In this lecture, I'd like to discuss the difference between key versus non key column indexes.

3
00:00:08,850 --> 00:00:16,140
‫In a nutshell, when you want to create an index in both grass or really any other database, you specify

4
00:00:16,140 --> 00:00:22,610
‫a field or more than one field to index to create that B3 on top off.

5
00:00:22,620 --> 00:00:22,920
‫Right.

6
00:00:23,500 --> 00:00:28,200
‫And that key will be used for searching purposes.

7
00:00:30,420 --> 00:00:37,740
‫And when you do a search on that index, you build the database planner or will use that index and search

8
00:00:37,740 --> 00:00:38,340
‫for that.

9
00:00:38,760 --> 00:00:45,330
‫And the entries that found in the index will contain roll pointers to point back to the table to fetch

10
00:00:45,330 --> 00:00:46,620
‫the actual rows.

11
00:00:46,650 --> 00:00:50,470
‫So what we are where we have more than one field there.

12
00:00:50,820 --> 00:00:51,150
‫All right.

13
00:00:51,870 --> 00:00:54,310
‫Nunca index is our abilities.

14
00:00:54,330 --> 00:00:57,120
‫I don't believe it exists in all databases, but it definitely exists.

15
00:00:57,120 --> 00:01:06,540
‫And Postgres, where you can effectively create an index and create none key, include some columns

16
00:01:06,870 --> 00:01:12,030
‫inside the index not to search for what to include.

17
00:01:12,390 --> 00:01:18,900
‫So in this case, if you select like you created an index on the grades field raid.

18
00:01:19,320 --> 00:01:26,070
‫And you can optionally include other fields in the index, like the ID.

19
00:01:26,760 --> 00:01:33,270
‫So now if you select and search by their grade and you want to select the I.D., then next doesn't have

20
00:01:33,270 --> 00:01:36,600
‫to go to the table because everything is literally in the index.

21
00:01:36,960 --> 00:01:40,890
‫Let's explain those and see the performance benefits that we get in all of them.

22
00:01:42,720 --> 00:01:43,410
‫Let's jump into it.

23
00:01:43,860 --> 00:01:50,730
‫So I'm going to include in this lecture sequal file that will be creating the students table here and

24
00:01:50,730 --> 00:01:51,390
‫the studier table.

25
00:01:51,420 --> 00:01:59,970
‫I deliberately added kind of a big table with a lot of fields to demonstrate the expense of doing this

26
00:01:59,970 --> 00:02:00,330
‫stuff.

27
00:02:00,450 --> 00:02:02,490
‫And we have a primary key here.

28
00:02:02,940 --> 00:02:06,210
‫And this table actually have 50 million records.

29
00:02:06,980 --> 00:02:09,780
‫Let's do a select star for all the students.

30
00:02:10,740 --> 00:02:11,160
‫Boom.

31
00:02:12,030 --> 00:02:12,280
‫Yep.

32
00:02:12,690 --> 00:02:14,040
‫Fifty million rows.

33
00:02:14,430 --> 00:02:22,680
‫Make sure if you're using Docker to create to run this container with plenty of shared memory and do

34
00:02:22,680 --> 00:02:29,400
‫a vacuum so that we update all the stuff, make sure the visibility map is up to date so we can get

35
00:02:29,400 --> 00:02:30,630
‫the optimal query.

36
00:02:30,840 --> 00:02:31,080
‫Right.

37
00:02:31,650 --> 00:02:35,820
‫Now, that being said, right, I don't have any indexes here.

38
00:02:35,820 --> 00:02:36,090
‫Right.

39
00:02:36,600 --> 00:02:45,450
‫So we're going to do is do an let's do this query, explain, analyze, explain so we can explain the

40
00:02:45,450 --> 00:02:48,660
‫query and also tell us how long it took.

41
00:02:49,140 --> 00:02:51,300
‫I'm going to do select idie and grade.

42
00:02:51,450 --> 00:02:57,360
‫I'm going to I want all the students that God results between 1995.

43
00:02:57,600 --> 00:02:57,930
‫Right.

44
00:02:58,470 --> 00:03:06,370
‫So from students where grade is greater than, let's say, 80.

45
00:03:06,450 --> 00:03:07,860
‫And less than.

46
00:03:08,070 --> 00:03:10,110
‫And G is less done.

47
00:03:10,680 --> 00:03:11,370
‫Ninety five.

48
00:03:12,630 --> 00:03:20,400
‫So you can effectively select these range of grades of these students.

49
00:03:20,790 --> 00:03:28,710
‫And I'm going to order by G in a descending man or so that the largest grade goes first and all the

50
00:03:28,710 --> 00:03:29,460
‫way down.

51
00:03:30,030 --> 00:03:34,320
‫Obviously, this is a little bit of an expensive query because you're going to select a lot of rows.

52
00:03:35,160 --> 00:03:39,300
‫Usually this is not recommended, but we're showing the capabilities here.

53
00:03:39,930 --> 00:03:41,670
‫So right of that is going to take a long time.

54
00:03:42,390 --> 00:03:43,470
‫And he's going to tell us how long.

55
00:03:43,950 --> 00:03:44,280
‫All right.

56
00:03:44,280 --> 00:03:45,870
‫So the query is finished.

57
00:03:46,710 --> 00:03:49,440
‫And it took a C.

58
00:03:52,540 --> 00:03:58,800
‫It took 21 seconds, 22 seconds, actually, to finish and look at it, what does it have to do?

59
00:03:58,800 --> 00:03:59,280
‫A sort?

60
00:03:59,430 --> 00:03:59,940
‫Why?

61
00:04:00,540 --> 00:04:03,690
‫Because we're doing order by grade, right?

62
00:04:03,690 --> 00:04:06,630
‫Because I want to sort these grades that I results.

63
00:04:06,640 --> 00:04:06,930
‫Right.

64
00:04:07,920 --> 00:04:12,600
‫It didn't have any index on the field that I'm searching because I don't have any index there.

65
00:04:12,600 --> 00:04:16,210
‫So it had to do the parallel sequential scan.

66
00:04:16,230 --> 00:04:16,710
‫Sure.

67
00:04:17,040 --> 00:04:19,410
‫Spun up multiple processes to do that.

68
00:04:19,710 --> 00:04:26,880
‫Hit the desk directly fitting on all this beautiful stuff that emerged from desk is so slow.

69
00:04:27,300 --> 00:04:33,750
‫We're going directly to the data file on the table and pulling pages, all the junk.

70
00:04:33,930 --> 00:04:34,320
‫Right.

71
00:04:34,350 --> 00:04:36,030
‫There is so much junk there.

72
00:04:36,450 --> 00:04:44,250
‫There is grades that are 10, 12, 13, and we have to filter this out or remove and filter this out.

73
00:04:44,790 --> 00:04:46,110
‫Look at the look of this.

74
00:04:46,200 --> 00:04:49,500
‫This is what baneful rows removed by filter.

75
00:04:49,860 --> 00:04:50,160
‫Right.

76
00:04:50,170 --> 00:04:55,440
‫So we pulled pages and we had to discard, what, 14 million is.

77
00:04:55,440 --> 00:04:58,170
‫That is our 40 million rows are useless.

78
00:04:58,500 --> 00:05:04,680
‫So we pulled pages with rows, but they are just basically filled with stuff that we don't want.

79
00:05:04,920 --> 00:05:07,170
‫And that's what the index hoped for effectively.

80
00:05:07,380 --> 00:05:10,810
‫So and you can you can you can do something like more elastic.

81
00:05:10,830 --> 00:05:14,460
‫You know, it's like, oh, no, I just want the first thousand students.

82
00:05:14,460 --> 00:05:21,990
‫And this should run faster, but not always in a sequential scan.

83
00:05:22,230 --> 00:05:27,480
‫This is not nearly necessary to is going to take faster because it doesn't know.

84
00:05:27,840 --> 00:05:31,800
‫It needs to search search until it gets the results.

85
00:05:31,800 --> 00:05:32,130
‫Right.

86
00:05:32,760 --> 00:05:37,860
‫So, yeah, it took 70 seconds or three seconds faster to pull for only a thousand students.

87
00:05:38,340 --> 00:05:38,640
‫All right.

88
00:05:38,850 --> 00:05:40,590
‫So that's that's obviously slow.

89
00:05:41,010 --> 00:05:43,290
‫So the first thing we need to do is actually create an index.

90
00:05:43,290 --> 00:05:43,980
‫How do we do that?

91
00:05:44,130 --> 00:05:47,670
‫Create index index on students G.

92
00:05:48,600 --> 00:05:49,890
‫So now this is the fields.

93
00:05:50,130 --> 00:05:56,340
‫I want to create a an index on G so that we can order by the G and filter on the G.

94
00:05:57,000 --> 00:05:58,020
‫Because that's what we're doing.

95
00:05:58,590 --> 00:06:03,720
‫We better be a little bit effective when it comes to that stuff.

96
00:06:03,960 --> 00:06:07,920
‫And that's because the indexes are by default ordered.

97
00:06:08,430 --> 00:06:10,080
‫And we love in databases.

98
00:06:10,080 --> 00:06:14,970
‫We love things that are already ordered for us because it's predictable.

99
00:06:15,970 --> 00:06:20,320
‫And instead of randomness, randomness, there's chaos.

100
00:06:22,690 --> 00:06:23,350
‫That was deep.

101
00:06:24,700 --> 00:06:26,290
‫All right, so we have an index.

102
00:06:26,800 --> 00:06:28,660
‫Let's execute my beautiful query again.

103
00:06:28,810 --> 00:06:30,130
‫Let's assume the original one.

104
00:06:30,910 --> 00:06:40,930
‫So what we'll do now we're querying Biji greater than 80, less than 95 order biji that we'll use the

105
00:06:40,930 --> 00:06:41,590
‫index.

106
00:06:42,220 --> 00:06:47,080
‫We can't guarantee it because the planner has a mind of its own.

107
00:06:47,260 --> 00:06:51,190
‫But let's say they're going to decide to use the index to search for things.

108
00:06:51,190 --> 00:06:52,350
‫But look at what we asked for.

109
00:06:52,360 --> 00:06:53,200
‫We asked for the ID.

110
00:06:53,830 --> 00:06:54,700
‫Where is the I.D.?

111
00:06:54,730 --> 00:06:57,100
‫Well, the I.D. is actually in two places.

112
00:06:57,430 --> 00:07:04,960
‫The I.D. is on another index because the primary key and it is also in the heap as another field.

113
00:07:05,680 --> 00:07:11,380
‫So this has to go to the table again to pull the IDs.

114
00:07:11,830 --> 00:07:13,690
‫So it will use the index.

115
00:07:13,690 --> 00:07:17,530
‫It should use the index to pull whatever it needs to pull.

116
00:07:17,710 --> 00:07:18,000
‫Right.

117
00:07:18,010 --> 00:07:24,820
‫Find the candidate rows and then jump to the table to pull the ideas of the students.

118
00:07:26,020 --> 00:07:27,790
‫Let's see how fast it is.

119
00:07:27,910 --> 00:07:30,820
‫So it didn't help much.

120
00:07:31,240 --> 00:07:36,250
‫We have an index and we still took 16 glorious seconds to fetch this.

121
00:07:36,520 --> 00:07:39,880
‫All of the information, while still better than 21.

122
00:07:40,000 --> 00:07:40,420
‫Right.

123
00:07:40,430 --> 00:07:41,560
‫But still slow.

124
00:07:41,810 --> 00:07:42,280
‫Why?

125
00:07:42,850 --> 00:07:47,440
‫And main reason is really we did an index scan backward.

126
00:07:47,470 --> 00:07:51,730
‫You might say I sent Y backward because we said or there by descending.

127
00:07:51,740 --> 00:07:52,900
‫So which is at the end.

128
00:07:53,170 --> 00:07:55,600
‫So we we did a search for 80.

129
00:07:57,190 --> 00:07:59,110
‫We did this condition on the index.

130
00:07:59,380 --> 00:08:01,000
‫And we found one entry.

131
00:08:01,000 --> 00:08:07,180
‫And since this is a P plus three, we're going to release it's all nicely tucked in together all the

132
00:08:07,180 --> 00:08:07,720
‫values.

133
00:08:08,020 --> 00:08:09,590
‫That's the beauty of the B plus three.

134
00:08:09,620 --> 00:08:09,850
‫Right.

135
00:08:10,360 --> 00:08:12,220
‫So all the values are next to each other.

136
00:08:12,370 --> 00:08:18,160
‫We found all the candidate rows is actually this this many arrows that we found.

137
00:08:20,410 --> 00:08:22,420
‫It took us 42 millisecond.

138
00:08:22,780 --> 00:08:28,960
‫This is not really an millisecond, but this that cost 42 cost, whatever this unit is, to find the

139
00:08:28,960 --> 00:08:29,710
‫first one.

140
00:08:29,920 --> 00:08:33,770
‫And fifteen point eight to find the last one.

141
00:08:33,820 --> 00:08:34,120
‫Right.

142
00:08:34,600 --> 00:08:37,290
‫So that's like equivalent to a millisecond by some.

143
00:08:37,300 --> 00:08:39,940
‫Some people say that's not it's not ready millisecond.

144
00:08:41,070 --> 00:08:42,460
‫And so.

145
00:08:42,460 --> 00:08:42,640
‫Yeah.

146
00:08:43,730 --> 00:08:45,020
‫And is slow still slow?

147
00:08:45,790 --> 00:08:51,950
‫Uh, Hussein, that's a ridiculous query because nobody's going to fix all these roads, right?

148
00:08:52,130 --> 00:08:53,810
‫I just want a thousand students.

149
00:08:54,500 --> 00:08:57,080
‫Well, it is much, much faster this time.

150
00:08:57,290 --> 00:09:02,060
‫And that makes sense because it took a point five milliseconds.

151
00:09:02,450 --> 00:09:08,020
‫So bear in mind that we won't be always this lucky to to take point four or five.

152
00:09:08,030 --> 00:09:08,300
‫Right.

153
00:09:08,300 --> 00:09:16,580
‫Because usually this this queried are hitting the cash, the operating system cash, because we did

154
00:09:16,580 --> 00:09:18,280
‫execute a lot of quarters before.

155
00:09:18,950 --> 00:09:23,640
‫So if I do like something like analyze and then I did Buffer's.

156
00:09:24,500 --> 00:09:24,920
‫Right.

157
00:09:25,910 --> 00:09:29,250
‫You can notice that look at this shared hit 20.

158
00:09:29,360 --> 00:09:34,460
‫That means all the things that we queried are cached.

159
00:09:34,760 --> 00:09:36,230
‫That is why is so fast.

160
00:09:37,820 --> 00:09:40,550
‫That's why a lot of you asked me a good question, Hosain.

161
00:09:40,550 --> 00:09:45,330
‫You said you did this query and so, so slow and you did it again so fast.

162
00:09:45,350 --> 00:09:45,630
‫Why?

163
00:09:45,650 --> 00:09:49,650
‫Because the operating system does all the magic of caching all the time.

164
00:09:49,670 --> 00:09:51,680
‫So we need to track the operating system a little bit.

165
00:09:51,690 --> 00:09:54,440
‫So let's just little bit change the equation.

166
00:09:54,440 --> 00:09:54,890
‫Change.

167
00:09:55,160 --> 00:09:56,450
‫Let's just do this.

168
00:09:57,320 --> 00:10:00,170
‫Give me all the students from, I don't know, 10 to 20.

169
00:10:00,590 --> 00:10:02,810
‫So this should look at that.

170
00:10:02,810 --> 00:10:04,490
‫We're doing a little bit of reading now.

171
00:10:04,580 --> 00:10:08,200
‫We did hit the end and at the end we had the audio.

172
00:10:08,210 --> 00:10:11,900
‫We had the disk is not we no longer do a shared hit.

173
00:10:13,700 --> 00:10:17,290
‫Still, we did a few head pages head, but it's better.

174
00:10:17,300 --> 00:10:20,320
‫So that's why we got up to six milliseconds.

175
00:10:20,330 --> 00:10:20,600
‫Right.

176
00:10:21,800 --> 00:10:24,770
‫But still, there's a lot of pages that are cached.

177
00:10:24,970 --> 00:10:29,140
‫And so that's why it's as absolutely blazing fast.

178
00:10:29,150 --> 00:10:29,390
‫Right.

179
00:10:29,420 --> 00:10:30,490
‫We need to find a way.

180
00:10:30,500 --> 00:10:36,530
‫So if I if I change this to 2000, a doc, 25 minutes, I can I bet that if I remove this limit, it's

181
00:10:36,530 --> 00:10:38,000
‫going to be faster now this time.

182
00:10:38,390 --> 00:10:40,040
‫So a little bit better.

183
00:10:41,660 --> 00:10:42,030
‫Right.

184
00:10:42,050 --> 00:10:42,530
‫Look at this.

185
00:10:42,680 --> 00:10:45,980
‫We got 90000 I to desk.

186
00:10:45,980 --> 00:10:51,200
‫We went to this 90000 times effectively and we took 11 seconds to do that.

187
00:10:52,040 --> 00:10:52,580
‫Yikes.

188
00:10:52,940 --> 00:10:53,300
‫All right.

189
00:10:53,630 --> 00:10:55,490
‫So we know why.

190
00:10:55,580 --> 00:10:59,450
‫We understand how the system works, because it's just an index of scan.

191
00:11:00,170 --> 00:11:05,060
‫And the reason is because you're selecting the idea which we have to go to the table for.

192
00:11:05,060 --> 00:11:06,590
‫And that's most of the cost.

193
00:11:06,890 --> 00:11:12,650
‫The the eyehole cost to go to the table while we already living in the index.

194
00:11:12,920 --> 00:11:19,310
‫So the idea is, how can we do a pure index only scan?

195
00:11:20,660 --> 00:11:22,640
‫Let's drop this index that we just created.

196
00:11:22,880 --> 00:11:26,930
‫And we're going to create a beautiful new index that is on the G.

197
00:11:27,230 --> 00:11:31,480
‫But we're going to include a non key index here.

198
00:11:31,800 --> 00:11:36,530
‫Nunca columns or shiitakes on students G.

199
00:11:36,680 --> 00:11:38,270
‫But we're going to include do.

200
00:11:39,390 --> 00:11:39,640
‫Why?

201
00:11:39,830 --> 00:11:44,480
‫Because that's a very common execution path that we're doing.

202
00:11:44,960 --> 00:11:51,060
‫That's where you as a back engineer, you really need to think like what columns are you selecting?

203
00:11:51,560 --> 00:11:57,220
‫And based on those columns, you create your indexes effectively.

204
00:11:59,450 --> 00:12:00,130
‫That's great that.

205
00:12:01,250 --> 00:12:03,830
‫So it finished a little bit longer.

206
00:12:04,220 --> 00:12:07,100
‫And that is makes sense because.

207
00:12:07,100 --> 00:12:11,330
‫Well, the index is larger now because we're including more stuff in it.

208
00:12:11,570 --> 00:12:14,810
‫The ID field, which is in a 32 bit, I believe.

209
00:12:15,800 --> 00:12:16,220
‫Right.

210
00:12:17,090 --> 00:12:20,180
‫And has to pull this values and store it.

211
00:12:20,180 --> 00:12:22,070
‫So obviously it's going to take more time.

212
00:12:22,460 --> 00:12:26,750
‫But what happens if I get my beautiful query now?

213
00:12:28,960 --> 00:12:30,670
‫Let's do just the normal one.

214
00:12:32,230 --> 00:12:32,860
‫Look at that.

215
00:12:34,030 --> 00:12:40,360
‫It took only four seconds and we had to go to the desk myself.

216
00:12:40,360 --> 00:12:40,750
‫I was in.

217
00:12:41,050 --> 00:12:44,050
‫You just said this is an index only scan.

218
00:12:44,710 --> 00:12:45,040
‫Right.

219
00:12:46,490 --> 00:12:51,120
‫Built in an index only scan, which is nicely done here.

220
00:12:51,120 --> 00:12:51,520
‫Right.

221
00:12:52,640 --> 00:12:55,730
‫It did said index only skin.

222
00:12:56,000 --> 00:13:01,730
‫So in this index only skin, we didn't have to go to the heap at all.

223
00:13:02,120 --> 00:13:03,350
‫He pitches equal zero.

224
00:13:03,350 --> 00:13:08,390
‫So this rule, you see this line, if only you wanted to see index only scan.

225
00:13:08,810 --> 00:13:10,700
‫If you see he pitches more than zero.

226
00:13:10,940 --> 00:13:13,730
‫That means we had to go to the heap for any reason.

227
00:13:13,950 --> 00:13:14,230
‫Right.

228
00:13:14,540 --> 00:13:16,460
‫One reason is that you didn't run vacuum.

229
00:13:16,460 --> 00:13:19,820
‫That invisibility map is out of sync, right?

230
00:13:19,830 --> 00:13:20,180
‫You did.

231
00:13:20,210 --> 00:13:20,590
‫You didn't.

232
00:13:20,600 --> 00:13:21,830
‫You made some changes.

233
00:13:21,830 --> 00:13:22,880
‫You updated the rows.

234
00:13:23,180 --> 00:13:26,360
‫So you have to do a vacuum in order to update all this stuff.

235
00:13:26,840 --> 00:13:29,060
‫So we did a read for some reason.

236
00:13:29,060 --> 00:13:30,610
‫We had to dirty three pages.

237
00:13:30,620 --> 00:13:31,940
‫I'm not sure why.

238
00:13:32,100 --> 00:13:32,480
‫Right.

239
00:13:32,780 --> 00:13:38,480
‫But again, even selects in most cases, sometimes does write all the time.

240
00:13:38,690 --> 00:13:38,990
‫Right.

241
00:13:38,990 --> 00:13:43,400
‫To update these visibility things, the hint bits, as they call them.

242
00:13:43,730 --> 00:13:44,900
‫So it took four seconds.

243
00:13:44,900 --> 00:13:49,940
‫And and I bet if you execute it again and again, guys, this is the entire thing, huh?

244
00:13:50,900 --> 00:13:52,220
‫I'm not doing any limits here.

245
00:13:52,340 --> 00:13:55,580
‫I pulled that everything that's between 10 and 20.

246
00:13:55,820 --> 00:13:56,120
‫Right.

247
00:13:56,300 --> 00:14:00,030
‫And it took one second this time, because that's what we cached more.

248
00:14:00,050 --> 00:14:01,970
‫We had more pages.

249
00:14:02,150 --> 00:14:02,480
‫Right.

250
00:14:02,780 --> 00:14:05,360
‫And we only had to go to go to the iOS.

251
00:14:05,360 --> 00:14:06,590
‫Six hundred and two times.

252
00:14:06,590 --> 00:14:07,560
‫And you might say, why?

253
00:14:07,580 --> 00:14:09,890
‫Why are we going to do this?

254
00:14:09,980 --> 00:14:14,270
‫I thought the original reading, the end guys, the index is still on disk.

255
00:14:14,990 --> 00:14:15,470
‫Right.

256
00:14:15,740 --> 00:14:18,830
‫It's a large is a value or very large index.

257
00:14:18,830 --> 00:14:21,680
‫And we have to hit the audio because it lives on the disk.

258
00:14:22,270 --> 00:14:27,170
‫It will be very beautiful if we can fit the index entirely in memory.

259
00:14:27,650 --> 00:14:29,150
‫That's the ideal situation.

260
00:14:29,750 --> 00:14:34,350
‫But unfortunately, that's that's not always the case.

261
00:14:34,370 --> 00:14:35,120
‫I have a beer.

262
00:14:35,630 --> 00:14:39,590
‫I have an old MacBook with 16 gig GB ram.

263
00:14:40,070 --> 00:14:44,210
‫It's a docker and container that is not really running with beer.

264
00:14:44,330 --> 00:14:45,380
‫Bad default.

265
00:14:45,390 --> 00:14:47,150
‫So you want really.

266
00:14:48,560 --> 00:14:53,840
‫And I think I have only one GB shared memory shared with this guy with Postgres.

267
00:14:53,840 --> 00:15:03,370
‫But it's always important to run, by the way, always run by by a few that if you run back, you verbose.

268
00:15:04,490 --> 00:15:04,820
‫Right.

269
00:15:05,180 --> 00:15:07,970
‫And on the students table, that's just run it.

270
00:15:08,510 --> 00:15:13,060
‫This will tell us, like, if there is anything wrong, if you got any errors here and you have to do

271
00:15:13,080 --> 00:15:17,390
‫verbose, it will tell you that, oh, by the way, there is some bad things.

272
00:15:17,390 --> 00:15:20,600
‫I have to remove all this dead tables, Bedros.

273
00:15:20,960 --> 00:15:28,760
‫So you guys have if you want to replicate this lecture, you have to run vacuum verbose students, right.

274
00:15:29,090 --> 00:15:32,300
‫Or even just from vacuum verbose, full or altogether.

275
00:15:32,800 --> 00:15:32,990
‫Right.

276
00:15:33,000 --> 00:15:38,300
‫This will help you update all the pages, the visibility map, anything.

277
00:15:38,480 --> 00:15:45,950
‫It will basically treat Posterous as as as if it's a production ready to be read.

278
00:15:46,160 --> 00:15:46,510
‫Right.

279
00:15:47,210 --> 00:15:52,950
‫And the moment you start updating fields and adding values and inserting rows, then you have to do

280
00:15:52,950 --> 00:15:53,630
‫a vacuum again.

281
00:15:53,630 --> 00:15:57,800
‫I mean, vacuum is or run by default, but it's always good to run this.

282
00:15:58,250 --> 00:16:01,870
‫So you can see that we got much better performance.

283
00:16:01,880 --> 00:16:02,840
‫Let's do a limit now.

284
00:16:04,100 --> 00:16:07,010
‫And this is even a point five, right, is really fast.

285
00:16:07,160 --> 00:16:13,850
‫So, guys, at the end of the day, you seeing the performance is not really huge.

286
00:16:14,180 --> 00:16:17,210
‫If you think about it, it really depends on your use case.

287
00:16:17,600 --> 00:16:22,970
‫The larger the table, the better non key indexes will be.

288
00:16:23,120 --> 00:16:23,450
‫Right.

289
00:16:23,990 --> 00:16:24,410
‫But.

290
00:16:25,610 --> 00:16:30,020
‫Keep in mind that the index grow larger, if you include stuff in it.

291
00:16:30,410 --> 00:16:33,830
‫And if you include stuff in it, it can't fit in memory.

292
00:16:33,950 --> 00:16:36,910
‫And if it can fit the memory there, it will be put on desk.

293
00:16:36,920 --> 00:16:42,410
‫And if I put an index, that's a read that we have to read the index from the desk to do the queries.

294
00:16:43,400 --> 00:16:46,190
‫So learn about this.

295
00:16:46,550 --> 00:16:47,600
‫You don't have to implement that.

296
00:16:47,610 --> 00:16:49,070
‫Just know that it exist.

297
00:16:49,880 --> 00:16:51,650
‫Put it in your back pocket as a tool.

298
00:16:52,160 --> 00:16:53,360
‫I'm going to see in the next one.

299
00:16:53,480 --> 00:16:54,350
‫Hope you enjoyed this.

300
00:16:54,980 --> 00:17:01,130
‫All the code, the sequel stuff is going to be included in the lecture resources as he.

301
00:17:01,130 --> 00:17:01,640
‫And that's one.

302
00:17:01,640 --> 00:17:02,000
‫Goodbye.