1
00:00:00,170 --> 00:00:00,860
‫Hey, guys.

2
00:00:01,340 --> 00:00:05,990
‫One of you asked me a question about the cost unit in the Postgres plan.

3
00:00:06,020 --> 00:00:13,640
‫Here, I made a mistake in the lecture, and I incorrectly refer to it as a millisecond, which is incorrect.

4
00:00:13,670 --> 00:00:14,000
‫Right.

5
00:00:14,000 --> 00:00:15,620
‫And I want to correct that.

6
00:00:15,620 --> 00:00:17,240
‫So let's discuss that a little bit.

7
00:00:20,140 --> 00:00:20,590
‫All right.

8
00:00:20,590 --> 00:00:23,880
‫So this comes from a jasick.

9
00:00:23,890 --> 00:00:28,870
‫I'm sorry if I mispronounce your name, but the question is, like in the video, you said that the

10
00:00:28,870 --> 00:00:31,960
‫cost unit is millisecond, but that's not true.

11
00:00:32,170 --> 00:00:32,980
‫He's right.

12
00:00:33,010 --> 00:00:35,740
‫According to Postgres, the unit is is not in millisecond.

13
00:00:35,740 --> 00:00:38,350
‫It's just a unit defined by the database.

14
00:00:38,350 --> 00:00:38,630
‫Right.

15
00:00:38,650 --> 00:00:40,210
‫And that's that's very critical.

16
00:00:40,210 --> 00:00:42,820
‫And I can agree with him because that I just made a mistake.

17
00:00:42,820 --> 00:00:47,020
‫I just I think I was thinking that it's a millisecond has nothing to do with the millisecond.

18
00:00:47,050 --> 00:00:48,340
‫It's just a cost.

19
00:00:48,340 --> 00:00:49,390
‫It's just a number.

20
00:00:49,900 --> 00:00:49,990
‫Right.

21
00:00:50,050 --> 00:00:57,010
‫So when we look at a plan and it's a good example to go through another question here that Adam actually

22
00:00:57,010 --> 00:01:02,770
‫posted where these costs are actually just values, right?

23
00:01:02,890 --> 00:01:06,700
‫And you might say if it's not millisecond, then what it is, does it really matter?

24
00:01:06,880 --> 00:01:10,430
‫You can think of it as IO, like the cost of IO, right?

25
00:01:10,450 --> 00:01:12,130
‫But it's a unit, right?

26
00:01:12,250 --> 00:01:17,650
‫The what matters is the higher the value, the higher the cost of the query.

27
00:01:17,650 --> 00:01:19,240
‫That's all what matters, right?

28
00:01:19,240 --> 00:01:22,610
‫But it doesn't really equal to the number of milliseconds, right?

29
00:01:22,610 --> 00:01:28,220
‫So if I if I see a value like 4.2 here doesn't mean that it's four is going to take four minutes.

30
00:01:28,220 --> 00:01:28,460
‫Okay?

31
00:01:28,460 --> 00:01:29,060
‫No, no, no, no, no.

32
00:01:29,250 --> 00:01:30,020
‫That's.

33
00:01:30,110 --> 00:01:30,920
‫Yeah, that's all.

34
00:01:30,920 --> 00:01:33,140
‫And I corrected the lecture for that part.

35
00:01:33,140 --> 00:01:38,180
‫Yeah, it just says it's going to cost me four units of whatever this cost.

36
00:01:38,240 --> 00:01:47,270
‫Maybe it's the how many logical Io's I'm going to do, how many CPU cycles is going to take to process

37
00:01:47,270 --> 00:01:47,660
‫these things.

38
00:01:47,660 --> 00:01:51,740
‫So it's like a combination of things and that that is morphed into this unit.

39
00:01:51,950 --> 00:01:58,550
‫What we need to do is this is the first, as I said, like this is the the cost to fetch the first row.

40
00:01:58,580 --> 00:02:00,410
‫This is the cost to fetch the last one.

41
00:02:00,410 --> 00:02:00,620
‫Right.

42
00:02:00,620 --> 00:02:01,640
‫And that's what matters.

43
00:02:01,640 --> 00:02:07,220
‫But and the the second part is like what it actually took me.

44
00:02:07,220 --> 00:02:08,930
‫So it took me way less.

45
00:02:08,930 --> 00:02:09,350
‫Right.

46
00:02:09,440 --> 00:02:15,620
‫So that's part of the estimation versus the actual execution, right?

47
00:02:15,620 --> 00:02:21,860
‫If you see the estimation being way different than this, then we start looking at the statistics.

48
00:02:22,100 --> 00:02:26,780
‫Did we run vacuum or not or did we like, are we up to date?

49
00:02:26,780 --> 00:02:30,700
‫Is the statistics or did we run, analyze, is everything is up to date or not?

50
00:02:30,710 --> 00:02:32,420
‫So this is actually a very critical.

51
00:02:32,420 --> 00:02:38,570
‫And he actually posted a a link here to an article explaining this a little bit more.

52
00:02:38,570 --> 00:02:42,050
‫So apologize for the mistake if that confused you.

53
00:02:42,050 --> 00:02:49,160
‫But yeah, again, as I said, the cost here is unit doesn't matter what it is, the higher value,

54
00:02:49,160 --> 00:02:52,070
‫the higher the cost, the lower the lower the cost.

55
00:02:52,100 --> 00:02:58,190
‫The first entry is the first the cost to get to the first row, you will see that if you do an order

56
00:02:58,190 --> 00:03:05,420
‫by, that, initial value is going to be high because just before the postgres gives you an answer,

57
00:03:05,420 --> 00:03:07,030
‫it has to sort right?

58
00:03:07,040 --> 00:03:11,390
‫And the cost to sort is added on top of everything, right?

59
00:03:11,390 --> 00:03:18,200
‫The cost of the IO not only IO, the cost of the IO to fetch the page, the cost of the CPU like to

60
00:03:18,230 --> 00:03:24,020
‫calculate all this thing and then it's added to the minimum value to get you that right.

61
00:03:24,020 --> 00:03:24,230
‫All right.

62
00:03:24,230 --> 00:03:31,070
‫So to explain this, I, I wrote up two queries here, one with order by and one without order by.

63
00:03:31,070 --> 00:03:32,180
‫I want you to turn on.

64
00:03:32,180 --> 00:03:36,830
‫Is this the one without the order by look at the cost, it's zero.

65
00:03:36,830 --> 00:03:41,990
‫Because for each table, because I have partitions on this table, I'm doing select star from grades.

66
00:03:41,990 --> 00:03:42,470
‫Right?

67
00:03:42,470 --> 00:03:45,620
‫So the cost to fetch the first row is zero.

68
00:03:45,650 --> 00:03:50,690
‫There is absolutely no cost because it's literally the first thing I'm going to do, right?

69
00:03:50,690 --> 00:03:56,840
‫So what's happening here is you can see the cost is zero here because we're reading all the partitions

70
00:03:56,840 --> 00:04:02,480
‫and then finally we're going to do an append, right, and return the first row.

71
00:04:02,510 --> 00:04:07,070
‫The cost to return the first row after reading all the partitions, probably not a good idea to do the

72
00:04:07,070 --> 00:04:10,310
‫partition because it's a little bit complex, but you guys get it.

73
00:04:10,310 --> 00:04:11,600
‫You guys get it right.

74
00:04:11,630 --> 00:04:18,260
‫So the the cost to get the first row off of all these partitions after we read all of them is zero.

75
00:04:18,290 --> 00:04:19,160
‫There's nothing.

76
00:04:19,160 --> 00:04:19,850
‫There's no cost.

77
00:04:19,850 --> 00:04:25,490
‫Because literally whatever the order was that we got, just pick the first one, which we have like

78
00:04:25,490 --> 00:04:26,570
‫a pointer to.

79
00:04:26,600 --> 00:04:28,790
‫So there is no cost to fetch the first one.

80
00:04:28,790 --> 00:04:29,150
‫Right.

81
00:04:29,180 --> 00:04:31,730
‫The cost to get the last one is 21.

82
00:04:32,150 --> 00:04:36,410
‫But take this with an order by on G.

83
00:04:36,410 --> 00:04:38,860
‫I'm just going to I said order by the grade.

84
00:04:38,870 --> 00:04:43,580
‫So here again if you notice that the cost to fetch the first rows here are zero.

85
00:04:43,610 --> 00:04:43,880
‫Why?

86
00:04:43,910 --> 00:04:48,410
‫Because we're not really technically ordering for each query on the partition.

87
00:04:48,410 --> 00:04:50,900
‫We're just reading it raw, right?

88
00:04:50,900 --> 00:04:55,790
‫But then after we appending all rows, the cost to append is nothing.

89
00:04:55,790 --> 00:04:57,260
‫Right to first fetch one.

90
00:04:57,260 --> 00:05:01,580
‫But the cost, the last append, the last one is that's the most cost.

91
00:05:01,610 --> 00:05:04,550
‫Then we apply the sort and look at that.

92
00:05:04,550 --> 00:05:10,580
‫The cost to give you the first row after the sort is 70.

93
00:05:10,760 --> 00:05:12,040
‫It's not 70 millisecond.

94
00:05:12,050 --> 00:05:15,580
‫Again, it's just 70 as a value here, right?

95
00:05:15,590 --> 00:05:17,090
‫So that's a higher value.

96
00:05:17,090 --> 00:05:19,310
‫That means it's of course, 70 is greater than zero.

97
00:05:19,450 --> 00:05:21,700
‫As a result that it's a lot.

98
00:05:21,730 --> 00:05:22,150
‫Right.

99
00:05:22,150 --> 00:05:26,110
‫And the cost to give you the last after that, just three costs after that.

100
00:05:26,110 --> 00:05:27,100
‫So 73.

101
00:05:27,130 --> 00:05:27,360
‫Right.

102
00:05:30,170 --> 00:05:34,160
‫So that's because we don't have much rows, but the cost to give you that.

103
00:05:34,160 --> 00:05:39,290
‫So let's be very careful we're having order by because that.

104
00:05:40,110 --> 00:05:40,560
‫Right.

105
00:05:40,560 --> 00:05:47,880
‫That is the cost to give you the first row, especially if you have like a if you give a query, the

106
00:05:47,880 --> 00:05:49,860
‫latency is here.

107
00:05:49,890 --> 00:05:50,150
‫Right.

108
00:05:50,160 --> 00:05:55,410
‫What is the especially if you're like fetching the first limit, like give me the first one row or two

109
00:05:55,410 --> 00:05:55,830
‫rows.

110
00:05:55,870 --> 00:06:02,760
‫Doesn't matter if if the if the planner has to do a query and has to sort that cost is has to be done

111
00:06:02,760 --> 00:06:05,100
‫first, then before the select.

112
00:06:05,100 --> 00:06:05,580
‫Right.

113
00:06:05,760 --> 00:06:06,990
‫All right, guys, that's it for me today.

114
00:06:06,990 --> 00:06:08,700
‫Hope you enjoyed this lecture.

115
00:06:08,850 --> 00:06:12,540
‫I wanted to clarify that cost parameter and just hitting in the nail.

116
00:06:12,810 --> 00:06:13,260
‫Correct.

117
00:06:13,260 --> 00:06:14,070
‫My mistake.

118
00:06:14,430 --> 00:06:15,360
‫See you in the next one.