1
00:00:00,150 --> 00:00:10,680
‫So now, if I do explain and allies select Count Starr from grades, parts where G equal 30.

2
00:00:11,130 --> 00:00:14,070
‫So now which table is going to where are we going to head?

3
00:00:14,100 --> 00:00:17,900
‫Guys, we should head the first partition.

4
00:00:17,910 --> 00:00:18,180
‫Right.

5
00:00:18,710 --> 00:00:19,460
‫Let's check this out.

6
00:00:19,890 --> 00:00:27,630
‫So we says, hey, index can only on what's for you G 003 G index on this partition.

7
00:00:27,630 --> 00:00:29,940
‫So only had one partition.

8
00:00:30,240 --> 00:00:30,540
‫Right.

9
00:00:30,630 --> 00:00:32,900
‫And Tokers one second takeaway still.

10
00:00:33,210 --> 00:00:33,750
‫Yeah.

11
00:00:33,750 --> 00:00:35,460
‫This is not much compared to what.

12
00:00:35,730 --> 00:00:36,870
‫To the original table.

13
00:00:36,870 --> 00:00:37,140
‫Right.

14
00:00:37,260 --> 00:00:38,310
‫The original table.

15
00:00:38,790 --> 00:00:39,560
‫You're going to see guys.

16
00:00:39,780 --> 00:00:40,710
‫It's the same thing.

17
00:00:41,160 --> 00:00:44,490
‫Hosain, you say that partition are great and now we're going to be faster.

18
00:00:45,000 --> 00:00:45,750
‫Not really.

19
00:00:46,560 --> 00:00:53,790
‫If you think about it, my EMAC here is that sixteen ram, sixteen of Ram and the docker container doesn't

20
00:00:53,790 --> 00:00:54,330
‫have a limit.

21
00:00:54,330 --> 00:00:56,460
‫I believe I didn't have a limit.

22
00:00:56,470 --> 00:01:01,780
‫So it's going to use all the memory it needs and that is the same identical query.

23
00:01:01,860 --> 00:01:05,700
‫There is no bounding limit, there's no memory, but there is no Eobard.

24
00:01:06,060 --> 00:01:09,630
‫If I limited the container to like a five hundred megs.

25
00:01:09,840 --> 00:01:10,140
‫Right.

26
00:01:10,350 --> 00:01:18,000
‫And the index was so large such that it doesn't fit a memory, then you can start seeing the difference

27
00:01:18,480 --> 00:01:22,230
‫hitting that huge index versus hitting the smaller index.

28
00:01:22,470 --> 00:01:24,350
‫But now you don't see it, guys.

29
00:01:25,050 --> 00:01:30,970
‫So how about we show you the the the sizes of these indexes now compared to what we have here?

30
00:01:31,140 --> 00:01:35,080
‫So overall, I remember the query select relational.

31
00:01:35,600 --> 00:01:35,870
‫All right.

32
00:01:35,910 --> 00:01:43,320
‫So the function called PJI relation size, that's a function that gives you the size of the relation

33
00:01:43,320 --> 00:01:47,070
‫and that's an index on table or anything that's going to or idee.

34
00:01:47,070 --> 00:01:48,090
‫That's Object ID.

35
00:01:48,330 --> 00:01:50,730
‫And then I'm going to also select the relation name.

36
00:01:52,710 --> 00:02:02,100
‫And from PJI classes, the table order by exactly the same thing, relation, size or ID.

37
00:02:04,700 --> 00:02:07,460
‫Descending because I want the biggest ones first.

38
00:02:08,060 --> 00:02:13,250
‫All right, so you can see this, that our original table, Gray's original and it's around what?

39
00:02:15,700 --> 00:02:22,870
‫That's around that's bitts, right, so you divide it by ten thousand one hundred thousand, so you

40
00:02:22,870 --> 00:02:26,740
‫get this one megabyte, so on three hundred sixty two megabyte, which is not big.

41
00:02:26,890 --> 00:02:27,240
‫Right.

42
00:02:27,550 --> 00:02:30,100
‫But multiply that four instead of 10 million.

43
00:02:30,100 --> 00:02:31,360
‫Make it 100 million.

44
00:02:31,360 --> 00:02:32,820
‫Twenty five hundred million.

45
00:02:32,830 --> 00:02:34,610
‫Then you can still see this increase.

46
00:02:34,960 --> 00:02:36,330
‫And these are the other partition.

47
00:02:36,470 --> 00:02:37,870
‫That's the the the.

48
00:02:40,310 --> 00:02:44,270
‫Is the rest of my partitions, partition, one partition, do all the partition, and these are the

49
00:02:44,270 --> 00:02:50,650
‫sizes, though not something that large, this is also one 26 megabyte.

50
00:02:50,930 --> 00:02:55,250
‫Apparently, this is zero to thirty five is a very popular grade.

51
00:02:56,450 --> 00:02:57,080
‫That's bad.

52
00:02:57,420 --> 00:02:58,360
‫Everybody is failing.

53
00:02:58,730 --> 00:03:04,700
‫So the original index is around, what, sixty nine megabyte.

54
00:03:05,060 --> 00:03:09,580
‫That's a big large and existing nine megabytes six now.

55
00:03:09,680 --> 00:03:11,920
‫It was nothing for my for my Mac.

56
00:03:11,930 --> 00:03:13,250
‫That's why we didn't see a difference.

57
00:03:13,490 --> 00:03:19,010
‫But increase the number, the size of the rows will increase the number of the index and that will slow

58
00:03:19,010 --> 00:03:20,750
‫things down even more so.

59
00:03:21,700 --> 00:03:28,150
‫But look at this guy, the individual indexes are way smaller than the large indexes or so the individual

60
00:03:28,150 --> 00:03:30,730
‫index, which is the zero thirty five.

61
00:03:30,730 --> 00:03:30,930
‫Right.

62
00:03:30,930 --> 00:03:34,030
‫That's the first politician is on only twenty four megabytes.

63
00:03:34,290 --> 00:03:37,180
‫So sounds like, what, three times smaller.

64
00:03:37,960 --> 00:03:42,670
‫And obviously queering this index is way faster than queering this index.

65
00:03:42,670 --> 00:03:42,930
‫Right.

66
00:03:43,120 --> 00:03:45,040
‫The size and the index matter.

67
00:03:45,040 --> 00:03:48,340
‫Where is the last and the scatter indexes and all that stuff guys.

68
00:03:48,650 --> 00:03:50,710
‫So a really really really matter.

69
00:03:50,720 --> 00:03:52,660
‫Guys this, this a little bit information.

70
00:03:54,210 --> 00:03:58,380
‫And the final thing I'm going to show here is there's a feature in Postgres, I want you to make sure

71
00:03:58,380 --> 00:04:02,470
‫that it's on and it's called Let Me Get It Right Here.

72
00:04:03,000 --> 00:04:05,010
‫It's called enable partition pruning.

73
00:04:05,150 --> 00:04:05,490
‫Right.

74
00:04:06,300 --> 00:04:06,840
‫Let's start.

75
00:04:06,900 --> 00:04:07,260
‫Let's see.

76
00:04:07,260 --> 00:04:08,160
‫What's the value of that?

77
00:04:08,160 --> 00:04:15,270
‫Enable partition pruning the value of whenever partition and pruning is on.

78
00:04:15,270 --> 00:04:18,290
‫And you want this on guys by default if it's off.

79
00:04:19,470 --> 00:04:22,110
‫Well, how about I show you what happened if this is off.

80
00:04:23,110 --> 00:04:23,360
‫Right.

81
00:04:23,550 --> 00:04:28,890
‫So let's sit let's sit this two off and this is off.

82
00:04:28,890 --> 00:04:33,840
‫And I do explain analyze what I really need to analyze.

83
00:04:33,840 --> 00:04:35,310
‫Just explain, select, count.

84
00:04:37,660 --> 00:04:45,580
‫Start from grades, pass the partition table where G equal 30, so which tradition this is supposed

85
00:04:45,580 --> 00:04:48,820
‫to partition one but nope, look at this stuff.

86
00:04:49,830 --> 00:04:55,620
‫It had partition one and partition two, it had partition three and had partition four, and I have

87
00:04:55,620 --> 00:04:59,020
‫no idea why do we have this option to begin with?

88
00:04:59,670 --> 00:05:03,880
‫Maybe it's a safeguard somewhere and prosperous, but this is dangerous.

89
00:05:04,200 --> 00:05:06,690
‫So if this is off, you just waste it.

90
00:05:06,720 --> 00:05:13,020
‫This is the whole partition is useless because you had you had all the four indexes, all the four partitions.

91
00:05:13,860 --> 00:05:19,590
‫Compare this to say it to on actually execute the same query again.

92
00:05:19,980 --> 00:05:20,460
‫And look at this.

93
00:05:20,460 --> 00:05:21,780
‫We only had one partition.

94
00:05:21,960 --> 00:05:27,600
‫So make sure that this this thing, that partition and pruning is always enabled.

95
00:05:27,610 --> 00:05:32,160
‫Otherwise you will not take advantage of this thing, guys.