1 00:00:00,060 --> 00:00:02,640 Michael: Hello and welcome to Postgres FM, a weekly show about 2 00:00:02,640 --> 00:00:03,580 all things PostgreSQL. 3 00:00:03,740 --> 00:00:05,280 I am Michael, founder of pgMustard. 4 00:00:05,280 --> 00:00:07,860 This is my co-host Nikolay, founder of Postgres.AI. 5 00:00:07,980 --> 00:00:10,100 Hey Nikolay, what are we talking about today? 6 00:00:10,280 --> 00:00:11,060 Nikolay: Hi Michael. 7 00:00:11,280 --> 00:00:13,880 It's your proposal actually, Limit, right? 8 00:00:13,940 --> 00:00:18,400 But before that, let's mention a little bit what our companies 9 00:00:18,420 --> 00:00:21,980 are doing because I think last time we did it 90 episodes ago. 10 00:00:22,580 --> 00:00:23,540 Michael: Right, sure. 11 00:00:23,680 --> 00:00:27,180 Nikolay: Yeah, so pgMustard, by the way, I wanted to tell you 12 00:00:27,180 --> 00:00:28,280 yesterday but forgot. 13 00:00:28,660 --> 00:00:33,620 During some session, people asked me about pgMustard suddenly 14 00:00:34,200 --> 00:00:36,300 and asked, can I recommend? 15 00:00:37,060 --> 00:00:37,720 Michael: What did you say? 16 00:00:37,720 --> 00:00:37,900 I 17 00:00:37,900 --> 00:00:39,300 Nikolay: said, yes, of course. 18 00:00:40,080 --> 00:00:41,080 And became shy. 19 00:00:41,760 --> 00:00:44,020 I should recommend it before they ask, right? 20 00:00:44,340 --> 00:00:45,210 Michael: It's good that they ask. 21 00:00:45,210 --> 00:00:48,980 Nikolay: So yeah, We discussed some plan and they wanted... 22 00:00:50,920 --> 00:00:56,720 Obviously we discussed how important it is to use buffers, our 23 00:00:56,720 --> 00:00:58,300 last week topic. 24 00:00:59,040 --> 00:01:02,360 If you haven't listened to it, please do. 25 00:01:02,360 --> 00:01:04,620 I mean, not you, but our listeners, right? 26 00:01:04,820 --> 00:01:08,240 Because buffers are super important and there are many aspects. 27 00:01:09,340 --> 00:01:13,280 And I actually found pgMustard as a good thing to visualize 28 00:01:13,340 --> 00:01:14,080 and suggest. 29 00:01:14,200 --> 00:01:17,800 It's not my idea, it's an idea from my customer. 30 00:01:20,940 --> 00:01:24,120 I personally think visualization is not the strongest pgMustard 31 00:01:24,920 --> 00:01:25,320 feature. 32 00:01:25,320 --> 00:01:30,040 Strongest is a list of recommendations based on heuristics, which 33 00:01:30,040 --> 00:01:31,220 is quite a good thing. 34 00:01:31,360 --> 00:01:34,660 So, and I know you improve it all the time, already many years. 35 00:01:34,900 --> 00:01:36,820 So it's great. 36 00:01:36,820 --> 00:01:37,320 Yeah. 37 00:01:38,100 --> 00:01:39,440 And what about me? 38 00:01:39,440 --> 00:01:42,340 My company is now currently creating bot. 39 00:01:43,740 --> 00:01:49,340 Check out our latest, it's only like a few months old, but our 40 00:01:49,340 --> 00:01:52,740 blog post, my blog post, where I described how we build it. 41 00:01:52,740 --> 00:01:55,060 And currently we are rebuilding it fully. 42 00:01:55,640 --> 00:01:56,660 It will be interesting. 43 00:01:57,040 --> 00:01:58,200 The release will be soon. 44 00:01:58,200 --> 00:01:59,480 Yeah, it's quite interesting. 45 00:01:59,760 --> 00:02:03,400 We bring it to web from Slack. 46 00:02:03,480 --> 00:02:09,520 I think you will live in Slack still, but it will be in web. 47 00:02:09,620 --> 00:02:11,880 It's just easier to use it there. 48 00:02:12,260 --> 00:02:17,080 And we started using different models, including Gemini 1.5 with 49 00:02:17,080 --> 00:02:19,740 1 million context window. 50 00:02:20,240 --> 00:02:23,260 So yeah, we change our reg approach right now. 51 00:02:23,260 --> 00:02:24,840 And it's kind of interesting. 52 00:02:25,440 --> 00:02:28,180 It knows already more than 1 million documents about Postgres, 53 00:02:28,180 --> 00:02:28,940 it's huge. 54 00:02:29,320 --> 00:02:32,340 So yeah, that's probably enough about Postgres.AI and pgMustard. 55 00:02:32,520 --> 00:02:34,040 Then let's talk about limit. 56 00:02:35,020 --> 00:02:35,800 Michael: Nice, yeah. 57 00:02:36,040 --> 00:02:37,100 That's cool to hear. 58 00:02:37,120 --> 00:02:40,020 Maybe we'll hear more about that another time about the differences 59 00:02:40,080 --> 00:02:41,080 between the models. 60 00:02:41,200 --> 00:02:46,960 Yes, so I suggested this topic and it came out of a tweet a long 61 00:02:46,960 --> 00:02:50,720 time ago that I found myself recommending over and over again 62 00:02:50,720 --> 00:02:55,580 to people because it was a phenomenon I saw and most people didn't 63 00:02:55,760 --> 00:02:58,820 understand or didn't intuitively understand, including myself. 64 00:02:59,060 --> 00:03:03,540 So this was a tweet from Christophe Pettus and the headline was, 65 00:03:04,200 --> 00:03:07,260 limit considered harmful or something along those lines. 66 00:03:07,260 --> 00:03:10,280 And it was a really good kind of initial tweet for a thread. 67 00:03:10,280 --> 00:03:11,640 I was like, what? 68 00:03:11,640 --> 00:03:12,600 Limit's good. 69 00:03:13,140 --> 00:03:14,860 There's so many good things about limit. 70 00:03:14,860 --> 00:03:16,960 It's really helpful for so many performance reasons. 71 00:03:16,960 --> 00:03:18,580 What's harmful about it? 72 00:03:18,580 --> 00:03:23,100 And he goes on to describe 1 specific case where it can cause 73 00:03:23,440 --> 00:03:27,720 plan flips and really, really slow queries in cases where you 74 00:03:27,720 --> 00:03:30,260 would expect it to be helpful in performance. 75 00:03:30,340 --> 00:03:34,040 So, that was the trigger for this topic, but it might be nice 76 00:03:34,040 --> 00:03:38,520 to discuss all the positives of using limit Well, at least when 77 00:03:38,520 --> 00:03:41,280 we should be using it whether we should be using it that kind 78 00:03:41,280 --> 00:03:41,940 of thing 79 00:03:42,980 --> 00:03:47,620 Nikolay: Yeah, well first of all, I guess we will be fighting 80 00:03:47,620 --> 00:03:50,420 a little bit in this episode because we have different opinions. 81 00:03:51,380 --> 00:03:52,260 Let's start. 82 00:03:53,760 --> 00:03:57,220 I cannot be on the side of Christophe Pettus in that tweet. 83 00:03:57,700 --> 00:04:01,720 I think there is huge potential because limit, This is what every 84 00:04:01,720 --> 00:04:04,400 one of us is using all the time, right? 85 00:04:04,400 --> 00:04:05,520 The limit is everywhere. 86 00:04:06,100 --> 00:04:10,780 So if there are some cases when it's harmful, it's interesting. 87 00:04:11,580 --> 00:04:16,600 But my regular approach is if you don't limit, you're in danger. 88 00:04:17,360 --> 00:04:21,400 Limitless queries are harmful because you don't know if you tested. 89 00:04:21,420 --> 00:04:24,560 You know, my favorite topic, testing and experiments and how 90 00:04:24,560 --> 00:04:29,140 to test and the idea that if you test on small databases, you 91 00:04:29,140 --> 00:04:29,640 miss. 92 00:04:30,020 --> 00:04:32,060 It's not, It's like it's bad testing. 93 00:04:32,480 --> 00:04:36,540 It's anti-pattern unless you know what you're doing and do it 94 00:04:36,540 --> 00:04:38,660 before you test on full-size databases. 95 00:04:39,640 --> 00:04:43,400 So this is exactly when it's not even about performance. 96 00:04:43,460 --> 00:04:49,020 It's just if you test on small databases, small tables, and some 97 00:04:49,020 --> 00:04:53,540 queries showing some results on a page that don't have a limit. 98 00:04:54,400 --> 00:04:55,960 And we start usually with it. 99 00:04:55,960 --> 00:05:00,420 If we prototype something, first we do it like forget about pagination, 100 00:05:00,600 --> 00:05:02,140 Let's show everything here. 101 00:05:02,360 --> 00:05:07,080 But then you test, it works well, like 1,000 rows on 1 page is 102 00:05:07,080 --> 00:05:11,180 not a problem, probably depending on how heavy they are in markup. 103 00:05:12,100 --> 00:05:17,220 But then project grows and some users, for example, have 10,000 104 00:05:17,360 --> 00:05:21,100 posts or, I don't know, comments, and you show all of them on 105 00:05:21,100 --> 00:05:23,860 1 page, and you have problems. 106 00:05:23,860 --> 00:05:27,420 And this is a postponed problem if you start a new project, right? 107 00:05:27,880 --> 00:05:31,260 So limitless queries can be harmful quite easily, and this is 108 00:05:31,260 --> 00:05:32,460 a straightforward idea. 109 00:05:32,780 --> 00:05:38,500 If you limit, at least you tell yourself what to expect. 110 00:05:38,540 --> 00:05:40,640 Okay, I expect 15 rows, for example. 111 00:05:40,640 --> 00:05:43,200 Okay, we know it's good. 112 00:05:43,860 --> 00:05:47,780 We know how many buffers we should expect in the very efficient 113 00:05:47,880 --> 00:05:48,380 query. 114 00:05:48,680 --> 00:05:54,360 15 times the width of row plus some internal buffer operations, 115 00:05:55,240 --> 00:05:57,440 continuing the topic we had last week. 116 00:05:57,840 --> 00:05:58,940 Right, and that's it. 117 00:05:58,940 --> 00:06:01,240 If you don't have limit, you don't know what to expect. 118 00:06:01,240 --> 00:06:02,540 Maybe it's a billion rows. 119 00:06:04,540 --> 00:06:07,840 Michael: Yeah, so I agree and I think pagination is quite a good 120 00:06:07,840 --> 00:06:11,200 place to start because when people think like if you look it 121 00:06:11,200 --> 00:06:14,340 up limit in the docs for example, you're also going to learn 122 00:06:14,340 --> 00:06:17,380 about offset and it's a really common concept. 123 00:06:19,120 --> 00:06:21,580 Well yeah, so I'm a big fan of... 124 00:06:21,580 --> 00:06:24,640 I'm going to say friend of the podcast, Markus Winand, previous 125 00:06:24,660 --> 00:06:25,160 guest. 126 00:06:25,240 --> 00:06:25,600 Yeah. 127 00:06:25,600 --> 00:06:27,620 I've always wanted to say friend of the podcast. 128 00:06:27,720 --> 00:06:28,860 So maybe I finally got to say... 129 00:06:28,860 --> 00:06:30,100 Nikolay: It was a great episode. 130 00:06:31,740 --> 00:06:32,380 Michael: Thank you. 131 00:06:32,380 --> 00:06:33,440 And yeah, it was... 132 00:06:33,820 --> 00:06:38,800 So he has a page on his website called no offset and I just about 133 00:06:38,800 --> 00:06:39,060 trying 134 00:06:39,060 --> 00:06:42,600 Nikolay: to I just used the offset hashtag on Twitter. 135 00:06:42,600 --> 00:06:46,220 I use it all the time because when we discuss when somebody mentions 136 00:06:46,260 --> 00:06:51,040 offset and this is not an expert who is doing offset on purpose, 137 00:06:51,760 --> 00:06:58,940 knowing why, for example, index-only scans before you deal with 138 00:06:58,940 --> 00:07:00,460 regular index scan. 139 00:07:00,520 --> 00:07:04,420 There you probably want offset sometimes, but very rare actually. 140 00:07:05,200 --> 00:07:08,260 So I always use this hashtag no offset. 141 00:07:08,480 --> 00:07:09,220 It's good. 142 00:07:09,920 --> 00:07:12,680 Michael: I'm sure most people listening know exactly what these 143 00:07:12,680 --> 00:07:17,720 things do, but just for clarity, When we use limit, we might 144 00:07:17,720 --> 00:07:21,720 add limit 10 to the end of our query, limit 20, limit some number. 145 00:07:22,360 --> 00:07:26,800 And that will, as the name suggests, limit the result set to 146 00:07:26,800 --> 00:07:27,240 that many. 147 00:07:27,240 --> 00:07:27,700 That many rows. 148 00:07:27,700 --> 00:07:29,020 Nikolay: Limit 0. 149 00:07:29,100 --> 00:07:29,980 I use sometimes. 150 00:07:30,140 --> 00:07:31,420 Do you use limit 0? 151 00:07:32,220 --> 00:07:33,020 Michael: No, but I've seen quite 152 00:07:33,020 --> 00:07:34,580 a lot of offset 0. 153 00:07:34,640 --> 00:07:37,760 Nikolay: I use limit 0 in 2 cases. 154 00:07:37,780 --> 00:07:41,340 When I, for example, create a table, I select specific columns 155 00:07:41,740 --> 00:07:42,540 limit 0. 156 00:07:42,700 --> 00:07:45,720 For example, sometimes just bootstrap some table. 157 00:07:46,340 --> 00:07:48,740 I don't want to repeat all data types. 158 00:07:48,740 --> 00:07:51,340 Sometimes it's like you're dealing with CSV or something. 159 00:07:51,340 --> 00:07:54,920 And also when I do tests, for example, presence of column, you 160 00:07:54,920 --> 00:07:58,820 can just select that column from table limit 0, not caring about 161 00:07:58,820 --> 00:07:59,320 anything. 162 00:08:00,120 --> 00:08:01,860 And yeah, kind of assert. 163 00:08:02,700 --> 00:08:03,200 So, 164 00:08:03,460 --> 00:08:06,180 Michael: yeah, I've, I've seen that really cool use case. 165 00:08:06,180 --> 00:08:12,100 I've seen offset 0 used a few times, so offset is, so limit limits 166 00:08:12,100 --> 00:08:13,760 the first N results. 167 00:08:13,940 --> 00:08:15,780 I've never thought of using 0 for that. 168 00:08:15,780 --> 00:08:16,420 It's cool. 169 00:08:16,960 --> 00:08:21,300 Offset will jump you forward that many before you start limiting. 170 00:08:21,340 --> 00:08:25,280 So you could offset, you take the first 10, throw those away, 171 00:08:25,280 --> 00:08:27,180 and then limit will take you the next 10. 172 00:08:27,180 --> 00:08:31,740 So if you do limit 10, offset 10, you get the second set of 10. 173 00:08:31,840 --> 00:08:37,100 Nikolay: Yeah, and offset 0 it's either some random generated, 174 00:08:37,240 --> 00:08:37,500 right? 175 00:08:37,500 --> 00:08:40,400 Because it was offset n where n is 0. 176 00:08:40,400 --> 00:08:44,440 Or it's also there was some performance trick, some hack, right? 177 00:08:44,440 --> 00:08:44,940 Yes. 178 00:08:44,940 --> 00:08:45,960 I already forgot. 179 00:08:45,960 --> 00:08:48,080 Michael: It's quite, yeah, I think it's an optimization. 180 00:08:48,080 --> 00:08:50,640 It is or was an optimization fence. 181 00:08:51,500 --> 00:08:55,780 So it can, it can trick the planner into not doing certain optimizations, 182 00:08:57,180 --> 00:09:00,460 which I guess we'll get onto a little bit, but there is a line 183 00:09:00,460 --> 00:09:01,100 in the doc. 184 00:09:01,100 --> 00:09:03,280 Nikolay: That trick or it was mitigated. 185 00:09:03,340 --> 00:09:05,760 Michael: Well, something in the docs made me think it might... 186 00:09:05,760 --> 00:09:06,920 I think it's still... 187 00:09:06,980 --> 00:09:11,120 I think they still work, because I remember reading in a hacker's 188 00:09:11,120 --> 00:09:13,260 thread, or it was in a thread somewhere in the mailing list. 189 00:09:13,260 --> 00:09:16,480 Nikolay: Yeah, certainly, it has been used, but it was so long 190 00:09:16,480 --> 00:09:20,820 ago, that's why I'm wondering if it's still actual. 191 00:09:22,420 --> 00:09:26,980 Michael: So I read a line in the docs in preparation that the 192 00:09:26,980 --> 00:09:30,400 offset 0 is the same as omitting the offset clause, but I don't 193 00:09:30,400 --> 00:09:33,340 think that's quite true because of the planner implications, but 194 00:09:33,340 --> 00:09:35,280 in terms of the results, that's true. 195 00:09:35,280 --> 00:09:35,740 In the results, you 196 00:09:35,740 --> 00:09:36,540 Nikolay: won't change it. 197 00:09:36,600 --> 00:09:41,180 Semantically, it's true, but physical execution might be affected 198 00:09:41,480 --> 00:09:43,260 with the presence of offset 0. 199 00:09:44,120 --> 00:09:44,620 Michael: Yeah. 200 00:09:45,060 --> 00:09:48,900 So the reason I brought this up entirely was because we were 201 00:09:48,900 --> 00:09:50,280 talking about pagination, right? 202 00:09:50,980 --> 00:09:55,580 We can pack both common ways of paginating a used limit. 203 00:09:56,240 --> 00:09:59,020 1 is the kind of crude way of doing it. 204 00:09:59,020 --> 00:10:01,920 And a lot of our ORMs, a lot of tools implemented it this way, 205 00:10:01,920 --> 00:10:06,580 at least initially, was to the first set of, let’s say, 20 rows 206 00:10:06,580 --> 00:10:08,740 that you want, just add limit 20. 207 00:10:08,800 --> 00:10:11,860 The next set, they just did offset 20, limit 20. 208 00:10:12,100 --> 00:10:14,680 And then the next 1, offset 40, limit 20. 209 00:10:15,060 --> 00:10:19,660 And the problem with that is performance degrades linearly as 210 00:10:19,660 --> 00:10:20,080 you go. 211 00:10:20,080 --> 00:10:23,000 And if you want to do a large, you want to get to page 20 or 212 00:10:23,000 --> 00:10:27,100 page 100, that can be quite an inefficient query. 213 00:10:27,780 --> 00:10:31,560 And Markus wrote a great post about why we should instead be 214 00:10:31,560 --> 00:10:35,140 doing key set pagination for predictable performance and efficiency. 215 00:10:35,240 --> 00:10:37,220 Nikolay: Right, its explanation is simple, it's just because 216 00:10:37,220 --> 00:10:41,180 internally it fetches everything until that and discards. 217 00:10:41,460 --> 00:10:41,920 Exactly. 218 00:10:41,920 --> 00:10:43,200 It's like a lot of inefficiency. 219 00:10:43,980 --> 00:10:48,540 It grows with the depth of your originating, right? 220 00:10:49,400 --> 00:10:53,240 Michael: So yeah, so what we were talking about in terms of good 221 00:10:53,240 --> 00:10:59,640 is where ID is above, like, where some parameter is greater than 222 00:10:59,640 --> 00:11:03,280 some, like, the last result you fetched and then still limiting 223 00:11:03,340 --> 00:11:06,420 by 20 or 21 depending on exactly how you want to do. 224 00:11:06,420 --> 00:11:07,880 Like that's something like that. 225 00:11:09,060 --> 00:11:14,540 Nikolay: So limit 0 can be a problem or limit 10 can be a problem, 226 00:11:14,540 --> 00:11:14,920 right? 227 00:11:14,920 --> 00:11:17,440 Let's talk about this problem maybe, right? 228 00:11:17,440 --> 00:11:21,760 Because I hope I already explained why limit is good in terms 229 00:11:21,760 --> 00:11:25,900 of performance, because you limit, you understand yourself what 230 00:11:25,900 --> 00:11:29,380 to expect, and you know what is efficient and what's not if you 231 00:11:29,380 --> 00:11:31,660 look at buffer operation numbers. 232 00:11:32,140 --> 00:11:35,640 Michael: I also think there are a couple of cases, like even 233 00:11:35,640 --> 00:11:39,840 simpler cases, like it is, chances are you don't want, like if 234 00:11:39,840 --> 00:11:42,720 your query could return a hundred thousand rows, do you really 235 00:11:42,720 --> 00:11:42,940 want 236 00:11:42,940 --> 00:11:43,520 Nikolay: all of them? 237 00:11:43,520 --> 00:11:43,680 Right, right. 238 00:11:43,680 --> 00:11:47,240 Michael: Or do you, you might only be interested in the top 10 239 00:11:47,240 --> 00:11:51,300 or maybe your user might only care about the most recent 10. 240 00:11:51,820 --> 00:11:56,000 And returning all 100,000, even if the query is quite fast on 241 00:11:56,000 --> 00:12:00,560 the server side, sending all that data over the wire is inefficient. 242 00:12:00,660 --> 00:12:03,980 So there are, I think it's kind of like almost all more fundamental 243 00:12:04,040 --> 00:12:08,360 reason that limit is good for returning a subset of rows, exactly 244 00:12:08,360 --> 00:12:09,240 what it's designed for. 245 00:12:09,240 --> 00:12:11,760 Nikolay: It's close to our topic about delete and batching. 246 00:12:11,960 --> 00:12:16,040 By the way, someone asked on YouTube comments, how about updates? 247 00:12:16,180 --> 00:12:19,700 And I explained it's very similar, but with additional things. 248 00:12:19,960 --> 00:12:25,220 So if you don't batch and send the whole result set in 1 shot, 249 00:12:25,600 --> 00:12:29,840 what if not only just it's a lot of memory and so on, but resiliency, 250 00:12:30,060 --> 00:12:36,040 reliability, what if it fails in the end of processing, you lose 251 00:12:36,040 --> 00:12:36,960 everything, right? 252 00:12:37,280 --> 00:12:39,060 And retry will be huge again. 253 00:12:39,060 --> 00:12:43,760 So it's better to go in smaller steps with retry logic. 254 00:12:44,080 --> 00:12:49,080 And limit, of course, is the key tool to split the batches. 255 00:12:49,080 --> 00:12:51,580 So generation, batching, we need it. 256 00:12:52,200 --> 00:12:56,200 And also, in general, common sense, if I limit again, like I 257 00:12:56,200 --> 00:13:00,620 know how much I want, this is my request, I want 15, for example, 258 00:13:00,620 --> 00:13:08,160 or 2,500, and if you scan many more parts of the database than these 259 00:13:08,160 --> 00:13:12,660 15 records or 25 records plus additional internal like index 260 00:13:12,660 --> 00:13:15,520 pages and so on, then I'm not considering this as efficient work, 261 00:13:15,520 --> 00:13:16,020 right? 262 00:13:16,320 --> 00:13:22,620 So it sets a simple expectation and metric for efficiency. 263 00:13:23,320 --> 00:13:27,680 Efficient versus not efficient comparison of queries, right? 264 00:13:28,840 --> 00:13:30,780 Michael: Yeah, I think that makes tons of sense. 265 00:13:30,920 --> 00:13:34,460 Like in application code, for example, you are suggesting that 266 00:13:34,540 --> 00:13:38,840 you're kind of communicating with future developers what the 267 00:13:38,840 --> 00:13:40,220 expectation is here. 268 00:13:40,580 --> 00:13:43,500 I think this is where we're going to start to differ in opinion, 269 00:13:43,500 --> 00:13:47,720 though, because I think this is exactly on the Let's say on an 270 00:13:47,720 --> 00:13:52,860 ad hoc query, I've sat next to people working on production systems 271 00:13:52,860 --> 00:13:57,340 that were quite scared and saw them and didn't think this was 272 00:13:57,340 --> 00:13:59,700 a problem, actually was learning from them at the time. 273 00:14:00,660 --> 00:14:04,060 They were only expecting a single row back and they added a limit 274 00:14:04,060 --> 00:14:05,500 1 at the end of the query. 275 00:14:05,500 --> 00:14:08,040 Nikolay: For example, primary key lookup, right? 276 00:14:09,160 --> 00:14:12,560 Michael: Yeah, I think this might have even been like, I can't 277 00:14:12,560 --> 00:14:14,820 remember exactly what they were doing, but they definitely added 278 00:14:14,820 --> 00:14:16,340 Nikolay: a primary key or a unique key. 279 00:14:16,340 --> 00:14:19,840 It cannot hurt because they are, it's like if you don't trust 280 00:14:19,840 --> 00:14:24,020 Postgres unique keys, you add limit 1 just as a sort, basically. 281 00:14:24,020 --> 00:14:28,940 You think, okay, I must check, it should fail or something if 282 00:14:28,940 --> 00:14:30,100 it returns more. 283 00:14:31,780 --> 00:14:34,160 Michael: Yeah, I actually can't remember exactly why. 284 00:14:34,160 --> 00:14:38,680 The thing I remembered was, oh that's quite a clever way of not 285 00:14:38,680 --> 00:14:39,650 having a runaway... 286 00:14:39,650 --> 00:14:41,960 Nikolay: Actually, I remember I did a different thing. 287 00:14:41,960 --> 00:14:44,880 I did limit 2 on purpose. 288 00:14:45,660 --> 00:14:49,580 So if something goes wrong and I have 2 rows and I don't remember 289 00:14:49,640 --> 00:14:54,220 where, but somewhere I should have an error or exception, catch 290 00:14:54,220 --> 00:14:55,060 it and process. 291 00:14:55,600 --> 00:14:56,880 So limit 2 was... 292 00:14:57,620 --> 00:15:01,680 Well, it should be unique, so I expect 1 row, but I add limit 293 00:15:01,680 --> 00:15:06,100 2 on purpose to check later that it's not 2. 294 00:15:06,820 --> 00:15:07,720 Michael: Yeah, I like it. 295 00:15:07,720 --> 00:15:11,600 Nikolay: But yeah, so anyway, like in my opinion, the limit is 296 00:15:11,600 --> 00:15:15,800 always, I would add it like as a must for everything. 297 00:15:17,300 --> 00:15:17,440 I 298 00:15:17,440 --> 00:15:20,820 Michael: think the fear was in this case, a runaway sequential 299 00:15:20,920 --> 00:15:25,940 scan across a huge table that might start to eat resources and 300 00:15:25,940 --> 00:15:26,400 cores. 301 00:15:26,400 --> 00:15:28,940 I think this was a very, very on fire server. 302 00:15:28,940 --> 00:15:32,220 Nikolay: I'm thinking now and I think maybe I would just even 303 00:15:32,220 --> 00:15:35,760 consider this as a rule, mandatory, like limit everywhere and 304 00:15:35,760 --> 00:15:38,760 the only thing we cannot limit is, for example, SELECT COUNT 305 00:15:38,760 --> 00:15:39,780 * FROM something. 306 00:15:41,400 --> 00:15:45,260 This basically deals with all rows, returns just 1 number and 307 00:15:45,260 --> 00:15:47,000 we cannot limit there, unfortunately. 308 00:15:47,320 --> 00:15:48,060 There, okay. 309 00:15:48,160 --> 00:15:50,320 Michael: I mean, you can add limit, you're just still going to 310 00:15:50,320 --> 00:15:51,220 get the count. 311 00:15:51,600 --> 00:15:54,440 Nikolay: Yeah, limit will be useless in this case. 312 00:15:54,440 --> 00:15:55,420 Yeah, definitely. 313 00:15:55,920 --> 00:15:58,580 Michael: The interesting thing, though, and I was looking back 314 00:15:58,580 --> 00:16:03,240 at just for fun, could I find a version of the Postgres docs 315 00:16:03,240 --> 00:16:04,440 that didn't have limit in it. 316 00:16:04,440 --> 00:16:06,220 I'm sure it's as old as time. 317 00:16:06,220 --> 00:16:11,220 But it was in an old version of the docs, I got back to 7.1, 318 00:16:11,520 --> 00:16:13,400 because that's the oldest one online. 319 00:16:14,380 --> 00:16:18,120 And there was a really, like, I got way more than I deserved 320 00:16:18,120 --> 00:16:18,960 looking back at that. 321 00:16:18,960 --> 00:16:23,200 And it said, as of Postgres 7.0, the query optimizer takes LIMIT 322 00:16:23,200 --> 00:16:26,520 into account when generating a query plan, which I thought might 323 00:16:26,520 --> 00:16:27,460 be a nice segue. 324 00:16:27,560 --> 00:16:32,420 So before 7.0, there wouldn't have been any danger in adding 325 00:16:32,560 --> 00:16:34,160 LIMIT to your query. 326 00:16:35,080 --> 00:16:35,840 I object. 327 00:16:36,740 --> 00:16:37,700 Nikolay: Mind your language. 328 00:16:37,800 --> 00:16:39,100 This language is wrong. 329 00:16:41,660 --> 00:16:43,660 There is no danger in adding LIMIT. 330 00:16:43,660 --> 00:16:46,480 Let's already jump to the core of the topic. 331 00:16:46,580 --> 00:16:48,600 There is no danger in adding LIMIT. 332 00:16:48,600 --> 00:16:53,840 There is danger in having bad statistics or expecting some wrong, 333 00:16:53,840 --> 00:16:59,740 like forming expectations based on indexes which cannot support 334 00:17:00,160 --> 00:17:03,240 your ORDER BY with filtering and LIMIT. 335 00:17:04,860 --> 00:17:10,440 By the way, when you describe this case, focus on ORDER BY as 336 00:17:10,440 --> 00:17:12,940 well, because without ORDER BY, this case is impossible. 337 00:17:12,980 --> 00:17:16,500 It's not about LIMIT, it's about ORDER BY plus LIMIT, which forms 338 00:17:16,500 --> 00:17:20,240 expectations, like I mean, gives planner the ability to use some 339 00:17:20,240 --> 00:17:21,660 index, right? 340 00:17:21,660 --> 00:17:22,640 Yeah, so let's 341 00:17:22,640 --> 00:17:25,800 Michael: talk about, well, for anybody thinking we've jumped, 342 00:17:25,800 --> 00:17:28,880 I shared some examples with Nikolay before the call, so that 343 00:17:29,540 --> 00:17:30,480 he will talk about that. 344 00:17:30,480 --> 00:17:30,940 I saw 345 00:17:30,940 --> 00:17:33,340 Nikolay: this example before you like a long ago. 346 00:17:33,740 --> 00:17:34,820 I saw it live. 347 00:17:35,080 --> 00:17:35,780 I just. 348 00:17:36,100 --> 00:17:37,400 Michael: Yeah, well, that's good. 349 00:17:37,640 --> 00:17:42,660 So, let's back up and try and explain the problem or the edge 350 00:17:42,660 --> 00:17:44,360 case or whatever you want to call it. 351 00:17:45,060 --> 00:17:49,060 So, exactly the same query, if you run it with and without limit 352 00:17:49,440 --> 00:17:55,120 some number, can be dramatically slower with limit the number 353 00:17:55,120 --> 00:17:59,340 added than it is without the limit at all, which I found really 354 00:17:59,340 --> 00:17:59,840 counterintuitive. 355 00:18:00,400 --> 00:18:03,660 And yes, it relies on some specific cases, but those specific 356 00:18:03,840 --> 00:18:05,910 things are not as rare in the real world. 357 00:18:05,910 --> 00:18:07,080 Nikolay: It's not rare, I agree. 358 00:18:07,820 --> 00:18:10,940 Michael: Yeah, so that person sitting in front of that console, 359 00:18:11,440 --> 00:18:16,660 hoping that limit some number is going to make it safe and guarantee 360 00:18:16,920 --> 00:18:17,820 fast execution. 361 00:18:18,060 --> 00:18:20,580 Nikolay: Same logic is expected, Yes, I agree. 362 00:18:20,740 --> 00:18:23,400 Michael: Can actually shoot themselves in the foot and make things 363 00:18:23,400 --> 00:18:24,940 loads worse for themselves. 364 00:18:25,520 --> 00:18:28,680 And I actually think there might be other advice you could follow. 365 00:18:29,340 --> 00:18:33,640 I'd much rather they added additional where clauses or other 366 00:18:33,640 --> 00:18:35,500 things than an additional limit. 367 00:18:35,740 --> 00:18:37,320 So that's potentially controversial. 368 00:18:37,660 --> 00:18:38,860 I'd love your take on it. 369 00:18:38,860 --> 00:18:40,900 Let's try to explain how this can happen. 370 00:18:41,180 --> 00:18:50,660 So the simplest case is to limit by a relatively rare condition 371 00:18:51,760 --> 00:18:55,780 and then order by something else that you have indexed. 372 00:18:55,800 --> 00:18:57,620 So for example, created at. 373 00:18:57,740 --> 00:19:03,460 So if we think about a, let's say, a software as a service business, 374 00:19:03,780 --> 00:19:07,300 and maybe you have a customer that cancelled a few years ago, 375 00:19:07,300 --> 00:19:11,980 and you want to look at their most recent event or something 376 00:19:11,980 --> 00:19:12,680 like that. 377 00:19:12,980 --> 00:19:17,320 And so you're looking at by company A, order by, created at, 378 00:19:17,320 --> 00:19:20,520 descending, limit 1, something like that. 379 00:19:20,740 --> 00:19:21,780 Nikolay: Or limit 10. 380 00:19:23,000 --> 00:19:24,400 Michael: Or limit 10, yeah, exactly. 381 00:19:24,420 --> 00:19:26,920 Nikolay: So it can happen, the problem can happen with not only 382 00:19:26,920 --> 00:19:27,420 1. 383 00:19:27,440 --> 00:19:28,780 It can be some small number. 384 00:19:28,780 --> 00:19:29,280 Yeah. 385 00:19:29,440 --> 00:19:30,900 Michael: It could be a big number. 386 00:19:31,020 --> 00:19:32,300 Nikolay: Or relatively small. 387 00:19:32,400 --> 00:19:35,640 The key is that it's small enough to have a flip. 388 00:19:36,020 --> 00:19:36,520 Michael: Yeah. 389 00:19:36,660 --> 00:19:39,400 Well, Christophe made a good point that I haven't tested yet. 390 00:19:39,400 --> 00:19:43,660 He thinks it's worse around the point of the actual number of 391 00:19:43,660 --> 00:19:44,160 rows. 392 00:19:44,640 --> 00:19:48,120 There are some cases where, let's say that company had a thousand 393 00:19:48,120 --> 00:19:53,160 events, the limit of a thousand would be problematic. 394 00:19:54,140 --> 00:19:58,720 So the problem comes from not knowing, like Postgres assumes 395 00:19:58,940 --> 00:20:03,080 things are randomly distributed unless it knows better, unless 396 00:20:03,080 --> 00:20:05,100 it has statistics to suggest otherwise. 397 00:20:05,540 --> 00:20:09,840 And if it thinks that the table that contains these events is 398 00:20:09,840 --> 00:20:15,720 going to have events from any company in any order, because it 399 00:20:15,720 --> 00:20:20,360 knows it only has to fetch 1 or 10 events, it now has this optimization 400 00:20:20,600 --> 00:20:23,940 where it might be quicker for it to scan backwards through. 401 00:20:24,240 --> 00:20:25,700 Nikolay: You missed a very important thing. 402 00:20:25,840 --> 00:20:26,880 Let me add things. 403 00:20:26,880 --> 00:20:33,120 So we talk about, for example, you took some SAS system, some 404 00:20:33,120 --> 00:20:37,400 users, and users have some activities, let's say, like comments 405 00:20:37,440 --> 00:20:39,360 or, I don't know, orders, anything. 406 00:20:39,640 --> 00:20:41,500 So we have orders table, for example. 407 00:20:42,180 --> 00:20:45,080 This is what you showed, if I'm not mistaken. 408 00:20:45,080 --> 00:20:45,700 I remember. 409 00:20:46,700 --> 00:20:47,320 So, orders. 410 00:20:47,320 --> 00:20:50,720 And we have user ID, we have created that, we have ID of order, 411 00:20:51,140 --> 00:20:51,640 enough. 412 00:20:52,800 --> 00:20:57,940 The key here is we want to select orders for this specific user 413 00:20:58,680 --> 00:21:04,780 in chronological order, like order by created at desc, so like 414 00:21:04,780 --> 00:21:09,180 in reverse order, the newest first, the latest first, right? 415 00:21:09,520 --> 00:21:14,620 And then the key here is not to forget, our developers, or we 416 00:21:14,620 --> 00:21:18,660 are developers, We don't know about multi-column indexes. 417 00:21:20,220 --> 00:21:25,060 So we created index on created at, we created index on userId, 418 00:21:25,680 --> 00:21:26,400 and on ID. 419 00:21:26,920 --> 00:21:30,400 Okay, let's simplify assumption. 420 00:21:30,600 --> 00:21:32,860 We like to index all columns. 421 00:21:35,160 --> 00:21:35,880 It's worse. 422 00:21:36,040 --> 00:21:39,720 And when we do it, we use only single column indexes always, 423 00:21:39,720 --> 00:21:40,120 right? 424 00:21:40,120 --> 00:21:41,920 This is quite a classic example. 425 00:21:41,920 --> 00:21:46,180 I can imagine it happens in the life of every engineer during 426 00:21:46,400 --> 00:21:49,820 5 years, for example, of development, definitely at least once 427 00:21:49,820 --> 00:21:51,440 it should happen, I would say. 428 00:21:51,600 --> 00:21:53,800 99% of developers. 429 00:21:53,840 --> 00:21:55,460 It happened with me many times. 430 00:21:55,580 --> 00:22:00,260 And with my products, my databases, and also with others when 431 00:22:00,260 --> 00:22:01,180 I observe them. 432 00:22:01,240 --> 00:22:04,900 Actually, recently we had a similar case, maybe a few months 433 00:22:04,900 --> 00:22:05,780 ago, I remember. 434 00:22:06,220 --> 00:22:10,660 So, the key is Postgres needs to choose, okay, I have filter, 435 00:22:11,100 --> 00:22:16,640 user ID, and I have order by created at desc limit 1 or limit 436 00:22:16,640 --> 00:22:17,140 10. 437 00:22:17,860 --> 00:22:19,180 And I have 2 indexes. 438 00:22:19,540 --> 00:22:25,120 So I have 2 opportunities here to save in terms of IO operations. 439 00:22:26,140 --> 00:22:29,820 Of course, Postgres should avoid sequential scan here. 440 00:22:30,240 --> 00:22:34,040 It can be 1 user of many, of millions, so it can be a huge table. 441 00:22:34,040 --> 00:22:36,760 So we don't want to scan the whole table, so we need to choose 442 00:22:37,200 --> 00:22:41,180 only 1 index or 2 of them, maybe combine with bitmap scan and 443 00:22:41,180 --> 00:22:41,760 so on. 444 00:22:41,760 --> 00:22:46,420 But 2 opportunities here, filtering by user ID and also create 445 00:22:46,640 --> 00:22:49,620 order by created at desc limit 1 or 10. 446 00:22:49,780 --> 00:22:51,560 And the key here is order by. 447 00:22:51,580 --> 00:22:56,180 Without order by, the limit, it will be fast always because, 448 00:22:56,320 --> 00:23:01,920 I mean, order by is the key to consider index on created at. 449 00:23:02,700 --> 00:23:05,420 Michael: Yeah, We need both the equality check and the order 450 00:23:05,420 --> 00:23:08,220 by to make it a choice for the planner. 451 00:23:08,740 --> 00:23:11,920 Either it has to filter on the equality condition with one of the 452 00:23:11,920 --> 00:23:16,120 indexes, take the data and sort it and return the order, or it 453 00:23:16,120 --> 00:23:20,440 has to choose the index that's ordered and take the first row 454 00:23:20,440 --> 00:23:22,740 it reaches that matches the equality condition. 455 00:23:22,920 --> 00:23:25,830 Nikolay: It can be not necessarily equality, it can be between 456 00:23:25,830 --> 00:23:26,720 one and the other. 457 00:23:26,720 --> 00:23:29,280 Michael: It could be any, like, yeah, so sorry, it doesn't have 458 00:23:29,280 --> 00:23:31,920 to be, it doesn't also have to be an order by the way, it could 459 00:23:31,920 --> 00:23:35,200 be a different, it has to be two separate conditions and those 460 00:23:35,200 --> 00:23:36,080 have to fall. 461 00:23:36,700 --> 00:23:38,460 Nikolay: They fight between each other, right? 462 00:23:38,560 --> 00:23:38,850 Yes. 463 00:23:38,850 --> 00:23:39,840 How do they fight? 464 00:23:39,840 --> 00:23:43,620 They fight based on costs, based on statistics and what Planner 465 00:23:43,620 --> 00:23:45,060 expects in terms of cost. 466 00:23:45,060 --> 00:23:49,280 And the key also, like, important point here is that Planner 467 00:23:49,280 --> 00:23:50,020 has two costs. 468 00:23:50,020 --> 00:23:54,980 Startup cost, which can be very small, it means the first row 469 00:23:55,080 --> 00:23:56,500 can be returned very fast. 470 00:23:56,820 --> 00:23:58,300 And also the full cost. 471 00:23:58,680 --> 00:24:03,320 Full cost is the most important when Planner, like, the full 472 00:24:03,320 --> 00:24:08,080 cost is what planner uses to make the final decision choosing 473 00:24:08,080 --> 00:24:10,200 between a variety of plans, right? 474 00:24:10,200 --> 00:24:10,940 Full cost. 475 00:24:11,680 --> 00:24:17,880 So our case, index on user ID, if we use just it, it means Planner 476 00:24:17,980 --> 00:24:22,660 thinks, okay, I will choose all rows for this user, but then 477 00:24:22,660 --> 00:24:25,020 I need to order by them in memory, right? 478 00:24:25,280 --> 00:24:31,420 It means startup cost cannot be close to 0 because ordering and 479 00:24:31,420 --> 00:24:33,480 so on, it will take some time. 480 00:24:33,520 --> 00:24:37,060 And once done, only once done, we can return all the rows. 481 00:24:37,060 --> 00:24:40,580 I assume like in this case, startup cost and the final full cost, 482 00:24:40,580 --> 00:24:43,880 they are very close to each other, but far from 0 probably, right? 483 00:24:44,380 --> 00:24:49,660 But the other option, order by created at desc limit 1 or 10. 484 00:24:50,220 --> 00:24:54,060 This option means that we immediately use created at index, we 485 00:24:54,060 --> 00:24:57,900 start fetching rows based on there, but we need to filter out 486 00:24:57,980 --> 00:25:04,020 irrelevant rows owned by different other users, right? 487 00:25:04,020 --> 00:25:05,600 We only need our user ID. 488 00:25:05,600 --> 00:25:10,040 It means we start returning rows quite quick, but only if we 489 00:25:10,040 --> 00:25:11,920 find our user ID quite quick. 490 00:25:12,880 --> 00:25:14,400 There is uncertainty here. 491 00:25:14,440 --> 00:25:18,060 But for Planner, it's quite certain because it has some expectations 492 00:25:18,320 --> 00:25:19,060 about distribution. 493 00:25:19,340 --> 00:25:20,460 You mentioned it. 494 00:25:20,860 --> 00:25:24,440 Probably, a planner can think, okay, I will be lucky because 495 00:25:24,440 --> 00:25:28,220 this user maybe is not real user. 496 00:25:28,460 --> 00:25:31,400 Maybe it's still super active, by the way, because we start from 497 00:25:31,400 --> 00:25:32,940 the very fresh rows. 498 00:25:33,760 --> 00:25:37,260 The latest row will be considered the first and if our user ID 499 00:25:37,260 --> 00:25:42,880 is there, it means we already found one row in the first step already. 500 00:25:43,260 --> 00:25:44,480 And we go, go, go. 501 00:25:44,480 --> 00:25:48,840 But if this user is super inactive last year, for example, in 502 00:25:48,840 --> 00:25:53,240 reality, we need to scan the whole year to reach the first row 503 00:25:53,240 --> 00:25:54,840 of that user to return. 504 00:25:55,440 --> 00:25:58,640 And here is exactly where the planner can be very wrong. 505 00:25:58,680 --> 00:26:01,112 I mean, it simply doesn't know. 506 00:26:01,204 --> 00:26:02,888 It doesn't know. 507 00:26:02,980 --> 00:26:08,720 There is no correlation in statistics between user IDs and created 508 00:26:08,720 --> 00:26:09,220 that. 509 00:26:09,280 --> 00:26:15,900 And also, the planner doesn't know who is actively active and 510 00:26:15,900 --> 00:26:16,620 who is not. 511 00:26:16,620 --> 00:26:17,840 The planner doesn't know. 512 00:26:17,860 --> 00:26:19,560 So it can be very hard. 513 00:26:20,460 --> 00:26:20,940 Michael: Yeah. 514 00:26:20,940 --> 00:26:22,000 So there are multiple. 515 00:26:22,140 --> 00:26:26,120 So I completely agree that you brought up multi-column indexes. 516 00:26:26,120 --> 00:26:28,820 In the case that I shared with you and the case we just discussed, 517 00:26:29,220 --> 00:26:35,080 a multi-column index on the user ID or whatever it was and then 518 00:26:35,080 --> 00:26:39,220 created that, ordered, would make this query efficient and predictable 519 00:26:39,760 --> 00:26:40,660 all the time. 520 00:26:40,920 --> 00:26:42,780 And so, if this was an application... 521 00:26:46,020 --> 00:26:46,780 Yes, yes, yes. 522 00:26:46,780 --> 00:26:49,640 But if this is an application query, and this was showing up 523 00:26:49,640 --> 00:26:52,800 in pg_stat_statements as a problem, and we needed to optimize 524 00:26:52,800 --> 00:26:55,640 it, and it was one of our biggest concerns, we didn't mind about 525 00:26:55,640 --> 00:26:58,660 the overhead of adding another index, great, we can add this 526 00:26:58,660 --> 00:27:03,080 index, remove the one on user ID or whatever we had, like, And 527 00:27:03,080 --> 00:27:04,340 it would be fast all the time. 528 00:27:04,340 --> 00:27:07,540 But I was talking about a case where we're sitting in front of 529 00:27:07,540 --> 00:27:12,260 a console on production trying to stop ourselves from running 530 00:27:12,880 --> 00:27:16,720 a large query or a slow query and adding that limit, even though 531 00:27:16,720 --> 00:27:18,560 we didn't think it would make a difference, even though we think 532 00:27:18,560 --> 00:27:22,480 we're only going to get one or two rows returned, if we add it, we're 533 00:27:22,480 --> 00:27:25,440 taking a risk that it could actually make things worse. 534 00:27:25,440 --> 00:27:28,660 Because we don't, like, if we don't check, and this is actually 535 00:27:28,660 --> 00:27:30,240 maybe what I would recommend. 536 00:27:30,240 --> 00:27:31,320 I don't know about you. 537 00:27:31,320 --> 00:27:34,640 But if you add explain before the query and just check what index 538 00:27:34,640 --> 00:27:36,900 it's going to use, does it make sense? 539 00:27:37,740 --> 00:27:40,900 If we saw it was going to do a backwards scan on created at, 540 00:27:40,900 --> 00:27:43,300 maybe that would be a sign that it was a bad idea. 541 00:27:43,580 --> 00:27:48,540 The tricky part is this is an issue because of a cost underestimation. 542 00:27:49,400 --> 00:27:52,700 So it's always going to look like a cheap query in cost. 543 00:27:52,900 --> 00:27:56,420 Like it, the reason it's being chosen, if it's a bad plan, if 544 00:27:56,420 --> 00:27:59,920 it was a slow plan, is because the plan is thinking it will be 545 00:27:59,920 --> 00:28:00,060 cheap. 546 00:28:00,060 --> 00:28:02,140 It's because it thinks it can abort early. 547 00:28:02,320 --> 00:28:04,600 So costs won't give you a clue here. 548 00:28:04,600 --> 00:28:08,440 You'd have to be attuned to the fact of which indexes it's scanning. 549 00:28:09,000 --> 00:28:09,840 And is that enough? 550 00:28:09,840 --> 00:28:11,680 Is that a good idea for this query? 551 00:28:12,500 --> 00:28:15,240 So I'm talking about production cases, like where you're doing 552 00:28:15,240 --> 00:28:19,120 ad hoc queries, not so much application queries where you should 553 00:28:19,120 --> 00:28:19,620 optimize. 554 00:28:19,760 --> 00:28:24,180 Nikolay: Yeah, for example, we have to return our table and allow 555 00:28:24,180 --> 00:28:27,380 users to order by all columns in both directions. 556 00:28:28,480 --> 00:28:30,740 It's a nightmare to support in terms of performance. 557 00:28:31,240 --> 00:28:36,660 And if you have many of such tables or maybe you allow users 558 00:28:36,660 --> 00:28:41,660 to create tables, any tables, and you want to hide this performance 559 00:28:41,660 --> 00:28:47,280 optimization from them, some website builder or mobile app builder 560 00:28:47,320 --> 00:28:52,220 and you can say, okay, users, developers, your users are developers 561 00:28:52,260 --> 00:28:57,540 of website or mobile app and you say, I allow you to create tables, 562 00:28:57,780 --> 00:28:59,140 I will take care of performance. 563 00:28:59,540 --> 00:29:02,720 In tables, you're allowed to create columns, I will take care 564 00:29:02,720 --> 00:29:03,360 of performance. 565 00:29:03,840 --> 00:29:09,060 And you can put some element on your UI for your users, millions 566 00:29:09,060 --> 00:29:13,580 of users, and you can allow them to order by any column in any 567 00:29:13,580 --> 00:29:14,080 direction. 568 00:29:14,540 --> 00:29:15,900 It's a super nightmare. 569 00:29:16,360 --> 00:29:17,460 Absolutely nightmare. 570 00:29:17,460 --> 00:29:17,720 Yes. 571 00:29:17,720 --> 00:29:22,260 Because obviously you want to create index on every column and 572 00:29:22,260 --> 00:29:25,640 also you think, okay, I'm smart, I know about key set pagination, 573 00:29:25,760 --> 00:29:31,020 you get your pagination, you then allow this. 574 00:29:31,020 --> 00:29:33,260 This is exactly where you can encounter it, right? 575 00:29:33,260 --> 00:29:34,320 Because this limit... 576 00:29:34,440 --> 00:29:38,300 So I wanted to emphasize again, like I described, 1 index has 577 00:29:38,300 --> 00:29:39,880 very low startup cost. 578 00:29:40,640 --> 00:29:41,980 It's creating that index. 579 00:29:42,980 --> 00:29:44,560 And why is it low? 580 00:29:45,400 --> 00:29:48,680 Because Postgres doesn't know for arbitrary user. 581 00:29:48,740 --> 00:29:52,420 We probably want this query to work not for 1 user but for arbitrary 582 00:29:52,420 --> 00:29:53,340 user, right? 583 00:29:54,240 --> 00:29:57,780 So in this case, you probably think Postgres doesn't know for 584 00:29:57,780 --> 00:30:02,140 this particular user how fresh activity is or orders are, right? 585 00:30:02,140 --> 00:30:04,620 So maybe, but it assumes something. 586 00:30:04,920 --> 00:30:07,700 It can be slightly more, slightly less, depends. 587 00:30:08,420 --> 00:30:09,680 But it assumes something. 588 00:30:09,860 --> 00:30:12,140 For second row, it's 2 times more. 589 00:30:12,560 --> 00:30:14,540 10 rows to 10 times more. 590 00:30:16,460 --> 00:30:19,060 But once we find it, we don't need to do anything. 591 00:30:20,500 --> 00:30:24,400 We can traverse, create an index and just filter out irrelevant 592 00:30:25,320 --> 00:30:26,540 other users, right? 593 00:30:26,600 --> 00:30:28,380 We just need to find our users. 594 00:30:28,380 --> 00:30:32,980 Once we find our N, 1 or 10 rows, we are happy. 595 00:30:32,980 --> 00:30:34,380 This is our final cost. 596 00:30:34,440 --> 00:30:39,560 And then if we don't use a limit, our final cost is our expectation 597 00:30:39,920 --> 00:30:40,760 for this user. 598 00:30:40,760 --> 00:30:42,760 This statistic probably is present. 599 00:30:42,940 --> 00:30:46,040 Probably, unless we don't have statistics at all. 600 00:30:46,300 --> 00:30:46,920 At all. 601 00:30:47,440 --> 00:30:51,180 Postgres expects for this user ID, I expect this number of rows, 602 00:30:51,180 --> 00:30:53,220 and this forms our total cost. 603 00:30:53,840 --> 00:30:57,100 Probably if it expects a lot of rows, total cost will be high 604 00:30:57,100 --> 00:30:59,200 and it won't start using this approach. 605 00:30:59,240 --> 00:31:02,640 In this case, it switches to the more reliable approach, I will 606 00:31:02,640 --> 00:31:05,960 fetch all the rows and do a memory sort. 607 00:31:07,400 --> 00:31:15,600 So the key here, if we limit, this is probably why Michael mentioned 608 00:31:15,600 --> 00:31:18,420 that the boundary is this limit. 609 00:31:19,040 --> 00:31:22,680 It's not the actual number of rows, but its statistics, suppose 610 00:31:22,680 --> 00:31:25,400 this expectation for number of rows for this user ID. 611 00:31:26,040 --> 00:31:33,280 So when our limit goes down, Below it, below this number, our 612 00:31:33,820 --> 00:31:39,260 traversal with filtering out irrelevant users becomes more attractive 613 00:31:39,400 --> 00:31:43,000 because total cost starts looking good there. 614 00:31:43,520 --> 00:31:45,080 But it's a trick, right? 615 00:31:45,080 --> 00:31:50,740 Because the problem here, it's a trick and of course, it's super 616 00:31:50,740 --> 00:31:51,660 interesting phenomenon. 617 00:31:51,960 --> 00:31:53,560 I would say this is a phenomenon. 618 00:31:53,560 --> 00:31:58,100 It's not a harmful situation because there are many cases when 619 00:31:58,100 --> 00:32:00,560 limit doesn't help. 620 00:32:00,860 --> 00:32:07,200 For example, we don't have indexes at all, we say, well, no. 621 00:32:07,200 --> 00:32:08,420 Michael: No, that can really help. 622 00:32:08,420 --> 00:32:10,280 Nikolay: It can help, it can help, yeah, yeah, yeah, because 623 00:32:10,280 --> 00:32:11,720 filtering out, it can help. 624 00:32:11,720 --> 00:32:15,780 But it's easy to imagine when limit is useless in this. 625 00:32:16,640 --> 00:32:18,660 Michael: Actually, that's another time I use limit. 626 00:32:19,460 --> 00:32:20,780 So the docs are quite 627 00:32:22,540 --> 00:32:23,600 Nikolay: on the measure. 628 00:32:24,060 --> 00:32:27,800 In general case, it improves performance because you will stop 629 00:32:27,800 --> 00:32:28,300 earlier. 630 00:32:28,620 --> 00:32:33,980 But on edge case, when our record is the latest in scanning. 631 00:32:34,340 --> 00:32:35,440 It doesn't help. 632 00:32:35,440 --> 00:32:37,100 Here we deal with a similar situation. 633 00:32:37,440 --> 00:32:40,920 In general it's good, but we have some edge case which not only 634 00:32:40,920 --> 00:32:45,400 doesn't help, but it decreases performance. 635 00:32:45,840 --> 00:32:47,740 But it's an edge case and it's phenomenal. 636 00:32:47,760 --> 00:32:51,900 It's not a harmful limit, especially because you cannot do this 637 00:32:51,900 --> 00:32:53,040 without order by. 638 00:32:53,100 --> 00:32:56,320 And especially because the problem here is not limit, but lack 639 00:32:56,320 --> 00:32:58,820 of proper index to call an index. 640 00:33:00,100 --> 00:33:05,740 Michael: And again, we're talking about a one-off query rather 641 00:33:05,740 --> 00:33:09,180 than an ongoing optimization challenge. 642 00:33:09,320 --> 00:33:12,980 I'm talking about it's just an interesting phenomenon that, and 643 00:33:12,980 --> 00:33:15,860 I don't think most people expect this, that by adding limit you 644 00:33:15,860 --> 00:33:17,160 can make the query slower. 645 00:33:17,160 --> 00:33:19,040 And that's true. 646 00:33:19,160 --> 00:33:20,620 It can be a lot slower. 647 00:33:21,020 --> 00:33:24,500 So I just don't want people to use that as their safety mechanism. 648 00:33:24,780 --> 00:33:27,040 Nikolay: You know, when you understand what's happening inside, 649 00:33:27,440 --> 00:33:30,480 adding limit is clear why it's wrong. 650 00:33:30,480 --> 00:33:31,110 It's clear. 651 00:33:31,110 --> 00:33:35,580 You just force the planner to choose the wrong index here. 652 00:33:36,580 --> 00:33:37,000 Right? 653 00:33:37,000 --> 00:33:37,740 That's it. 654 00:33:37,760 --> 00:33:41,900 But of course for people who don't dive inside the execution 655 00:33:42,400 --> 00:33:47,220 and planning process, they of course are very surprised and think 656 00:33:47,220 --> 00:33:48,300 limit is bad. 657 00:33:48,520 --> 00:33:51,840 But remove order by and you won't see this effect, right? 658 00:33:53,100 --> 00:33:55,040 Michael: But the docs, and this is what I wanted to say, the 659 00:33:55,040 --> 00:33:59,060 docs quite often when you're reading about limit will say it's 660 00:33:59,060 --> 00:34:02,080 really sensible to use order by with limit because otherwise 661 00:34:02,080 --> 00:34:04,960 you're getting things in a like an undetermined 662 00:34:04,960 --> 00:34:06,220 Nikolay: Again, 1 second. 663 00:34:06,220 --> 00:34:10,360 If we talk about arbitrary user ID, what if this user ID is super 664 00:34:10,360 --> 00:34:14,600 active and created a thousand orders right today? 665 00:34:15,060 --> 00:34:18,060 In this case, it's right. 666 00:34:18,180 --> 00:34:21,220 So that's why I say this is not even an edge case, it's a corner 667 00:34:21,220 --> 00:34:21,720 case. 668 00:34:22,360 --> 00:34:26,080 You have several things in the corner here. 669 00:34:26,120 --> 00:34:29,280 And observing various strange effects, phenomena, of course, 670 00:34:29,540 --> 00:34:30,320 it makes sense. 671 00:34:30,320 --> 00:34:32,500 But create proper indexes, that's it. 672 00:34:32,880 --> 00:34:37,160 But at least let's correct this harmful title. 673 00:34:37,200 --> 00:34:42,300 Let's say limit 1 with order by considered harmful. 674 00:34:42,740 --> 00:34:43,680 Order by limit. 675 00:34:43,680 --> 00:34:45,320 Michael: It's not just order by, though. 676 00:34:45,320 --> 00:34:47,780 It could be another condition that you add. 677 00:34:47,840 --> 00:34:51,540 Nikolay: Lack of multi-column indexes order by limit considered 678 00:34:51,540 --> 00:34:52,040 harmful. 679 00:34:52,060 --> 00:34:54,660 This is the full truth. 680 00:34:54,860 --> 00:34:56,920 This is the truth actually here. 681 00:34:57,380 --> 00:34:58,300 Not just limit. 682 00:34:59,180 --> 00:35:01,560 Michael: I was hoping you'd understand my point of view on this. 683 00:35:01,560 --> 00:35:02,780 It feels like you haven't. 684 00:35:02,960 --> 00:35:07,060 I do think that in a lot of real world cases, people don't have 685 00:35:07,060 --> 00:35:11,420 perfect stats or they have data that's correlated strongly in 686 00:35:11,420 --> 00:35:12,040 certain ways. 687 00:35:12,040 --> 00:35:16,100 For example, like user ID and account ID being extremely tightly 688 00:35:16,100 --> 00:35:16,300 coupled. 689 00:35:16,300 --> 00:35:19,340 Or you know, the age old city and country codes. 690 00:35:19,640 --> 00:35:24,060 And Postgres assumes that all of these stats are uncorrelated 691 00:35:24,520 --> 00:35:25,240 by default. 692 00:35:25,940 --> 00:35:29,680 And if you don't, you can add, you can create statistics for 693 00:35:29,680 --> 00:35:30,320 these things. 694 00:35:30,320 --> 00:35:33,360 But in a lot of cases, we have a lot of skewed distributions 695 00:35:33,580 --> 00:35:37,360 in databases that we haven't created extra statistics for. 696 00:35:37,360 --> 00:35:40,520 And most of the time, the plan is great and can really can work 697 00:35:40,520 --> 00:35:41,760 things out nicely. 698 00:35:42,800 --> 00:35:47,640 But by having this limit optimization, it can flip plans that 699 00:35:47,640 --> 00:35:48,800 we're not expecting to flip. 700 00:35:48,800 --> 00:35:53,900 And the reason I found it so powerful is we're using it exactly 701 00:35:54,000 --> 00:35:58,880 in the cases where we're most scared of doing a big scan on production. 702 00:35:59,380 --> 00:36:03,080 If that's what you're really scared of doing, And this is something 703 00:36:03,080 --> 00:36:03,400 that can 704 00:36:03,400 --> 00:36:03,940 Nikolay: be quite... 705 00:36:03,940 --> 00:36:07,360 Why do you not agree with all this concept that is harmful? 706 00:36:07,360 --> 00:36:12,100 Because imagine we don't have an index on user ID at all. 707 00:36:12,340 --> 00:36:14,280 In this case, if... 708 00:36:15,900 --> 00:36:20,240 In this case, created that shows bad performance because the 709 00:36:20,240 --> 00:36:25,820 user became inactive last year, we need to scan the whole year. 710 00:36:26,040 --> 00:36:31,020 But at least we have some approach to scan, right? 711 00:36:31,620 --> 00:36:36,260 Created that adds some order to scanning. 712 00:36:36,400 --> 00:36:38,920 Without it, we only have a sequential scan, right? 713 00:36:39,140 --> 00:36:39,840 That's it. 714 00:36:39,840 --> 00:36:42,320 So the problem is we don't have a proper index. 715 00:36:42,340 --> 00:36:46,260 Now, okay, it turns out we have an index on user ID. 716 00:36:48,940 --> 00:36:54,720 It happened to be helping, but what if some user has 50% of whole 717 00:36:54,720 --> 00:36:56,960 row like records, millions of records. 718 00:36:56,960 --> 00:36:58,380 Michael: So bear with me. 719 00:36:58,980 --> 00:37:02,380 This is why I'm suggesting instead of running the query on production 720 00:37:02,500 --> 00:37:06,840 by adding a limit 1 or something to make it feel safe, instead 721 00:37:06,860 --> 00:37:11,280 of adding limit 1, put just 1 word, explain, in front of the 722 00:37:11,280 --> 00:37:14,200 query, not explain analyze, not explain analyze buffers, just 723 00:37:14,200 --> 00:37:17,840 explain your query without the limit and see what the query plan 724 00:37:17,840 --> 00:37:18,680 is before you run it. 725 00:37:18,680 --> 00:37:21,960 If you're scared of what it's going to do in production, do that 726 00:37:21,960 --> 00:37:23,440 instead of adding the limit. 727 00:37:23,440 --> 00:37:25,640 And you'll see which indexes it's using, whether you're 728 00:37:25,640 --> 00:37:26,760 Nikolay: missing one or whether you're not. 729 00:37:26,760 --> 00:37:30,920 But I cannot add limit because I don't want my page to blow up 730 00:37:30,920 --> 00:37:32,740 because of many, many rows returned. 731 00:37:32,880 --> 00:37:33,780 ALEX LUCASSE-COLOMBRE-CAMPOYS 732 00:37:33,780 --> 00:37:37,620 Michael: Well, add limit 1 and put explain in front just to check 733 00:37:38,040 --> 00:37:39,300 what it's going to do. 734 00:37:40,520 --> 00:37:42,680 Because that's what's really going to check. 735 00:37:42,720 --> 00:37:43,440 That's what's going 736 00:37:43,440 --> 00:37:43,855 Nikolay: to actually keep safe. 737 00:37:43,855 --> 00:37:45,560 SLAVOJ ŽIŽEKERJÁNAS You will check the player behavior, right? 738 00:37:45,560 --> 00:37:46,220 ALEX LUCASSE-COLOMBRE-CAMPOYS 739 00:37:46,220 --> 00:37:48,540 Michael: Yeah, you're checking what you're actually scared of 740 00:37:48,540 --> 00:37:51,580 instead of assuming you're going to be safe. 741 00:37:51,580 --> 00:37:54,160 It's like, I understand your point that it's almost never going 742 00:37:54,160 --> 00:37:57,320 to be a problem, but imagine if a safety on a gun, for example, 743 00:37:57,700 --> 00:37:59,320 almost always kept you safe. 744 00:38:00,040 --> 00:38:03,660 It's like a safety feature that's almost always good, but sometimes 745 00:38:03,840 --> 00:38:07,460 it's going to actually shoot you when you press that safety catch. 746 00:38:07,540 --> 00:38:10,700 Not when you pull the trigger, but when you press the catch. 747 00:38:11,720 --> 00:38:16,940 Nikolay: Again, in my world, I add limit 1 to limit things. 748 00:38:17,080 --> 00:38:19,420 If it doesn't work, it means I don't have an index. 749 00:38:20,500 --> 00:38:24,340 If there is some index which would help me when I don't have 750 00:38:24,340 --> 00:38:27,760 a limit, I don't care because I do need limit 1 anyway. 751 00:38:29,240 --> 00:38:31,560 Michael: And maybe you'll hit some like statement timeout or 752 00:38:31,560 --> 00:38:34,600 you'll cancel your query quickly or there was some other safety 753 00:38:34,600 --> 00:38:37,060 mechanism in place that will stop you from hurting yourself. 754 00:38:37,060 --> 00:38:42,680 Nikolay: But still, if my limit behaves not good, I'm looking 755 00:38:43,080 --> 00:38:44,360 which index will help. 756 00:38:44,380 --> 00:38:48,040 And I'm not considering index on user ID as helpful here because 757 00:38:48,040 --> 00:38:51,420 I don't want to deal with a lot of records and memory because 758 00:38:51,420 --> 00:38:53,040 I need only 15 records. 759 00:38:53,360 --> 00:38:57,860 So that's why I jump straight to multi-column indexes here. 760 00:38:58,140 --> 00:38:59,160 And as we discussed... 761 00:38:59,160 --> 00:38:59,940 Michael: I agree. 762 00:39:01,400 --> 00:39:04,440 I agree that to optimize this query, but we're not talking about 763 00:39:04,440 --> 00:39:07,120 optimizing the query, we're talking about should you use limit 764 00:39:07,120 --> 00:39:09,980 as a safety feature when you don't need it? 765 00:39:10,200 --> 00:39:12,540 Nikolay: Why I don't, why might I not? 766 00:39:12,880 --> 00:39:18,780 Michael: Let's say you know there's only going to be 1 row, should 767 00:39:18,780 --> 00:39:19,980 you add limit 1. 768 00:39:20,540 --> 00:39:22,620 Nikolay: How do you know it's only 1 row? 769 00:39:23,400 --> 00:39:24,340 Unique index? 770 00:39:24,640 --> 00:39:27,080 If you have unique index, this problem won't happen. 771 00:39:27,920 --> 00:39:32,520 Michael: Let's say a company could set up multiple things, but 772 00:39:32,520 --> 00:39:33,740 they generally set up 1. 773 00:39:33,740 --> 00:39:37,920 Like how many websites are you gonna have for each organization 774 00:39:38,240 --> 00:39:41,400 you don't have a unique index on it people can add a second okay 775 00:39:41,700 --> 00:39:43,700 but almost everybody else for 776 00:39:43,700 --> 00:39:45,220 Nikolay: you for example right 777 00:39:46,980 --> 00:39:50,460 Michael: yeah email addresses yeah it's probably gonna be less 778 00:39:50,460 --> 00:39:54,720 than 10 like you don't have any you know but you maybe you're 779 00:39:54,720 --> 00:39:58,080 not certain it's going to be 1, but you're pretty sure it's going 780 00:39:58,080 --> 00:39:58,180 to 781 00:39:58,180 --> 00:39:58,840 Nikolay: be 1. 782 00:39:58,840 --> 00:40:02,220 But am I having up-to-date stats or no? 783 00:40:03,700 --> 00:40:08,580 Because stats will say, they will form an expectation to Postgres 784 00:40:08,940 --> 00:40:11,880 if it's a low number of rows, it would prefer. 785 00:40:12,520 --> 00:40:15,060 Michael: I think I'm struggling because it's a simplified example, 786 00:40:15,060 --> 00:40:17,960 but if you've got lots of joins going on there could be all sorts 787 00:40:17,960 --> 00:40:19,260 of other like complexities. 788 00:40:19,440 --> 00:40:20,900 Nikolay: Joins are tricky by the way. 789 00:40:20,900 --> 00:40:25,380 The same problem is becoming much more tricky but I don't think 790 00:40:25,380 --> 00:40:26,040 limit this problem. 791 00:40:26,040 --> 00:40:28,940 I think the problem is a lack of proper indexes and sometimes 792 00:40:28,940 --> 00:40:30,060 you cannot create them. 793 00:40:30,060 --> 00:40:34,300 Imagine user id is present in 1 table but created that is present 794 00:40:34,300 --> 00:40:35,420 in different table. 795 00:40:36,180 --> 00:40:39,020 Michael: So you're saying you've never sat in front of a database 796 00:40:39,160 --> 00:40:42,560 where you don't know all of the indexes in the system, you don't 797 00:40:42,560 --> 00:40:45,720 know the state of stats and wanted to run a query and not being 798 00:40:45,720 --> 00:40:47,740 scared that it might take a long time. 799 00:40:48,540 --> 00:40:52,760 Nikolay: Well, when I do this, I add limit, I understand that 800 00:40:52,760 --> 00:40:55,460 if I lack proper index, that will help. 801 00:40:56,760 --> 00:41:00,300 I might be dealing with sequential scan or wrong index have been 802 00:41:00,300 --> 00:41:01,560 scanned almost fully, right? 803 00:41:01,560 --> 00:41:02,700 I understand that. 804 00:41:02,900 --> 00:41:05,220 If you don't understand that, that's strange. 805 00:41:06,580 --> 00:41:06,860 Right? 806 00:41:06,860 --> 00:41:10,620 Michael: Well, but then, so this is my, that's kind of my, like, 807 00:41:10,900 --> 00:41:11,400 takeaway. 808 00:41:12,040 --> 00:41:13,680 Instead of adding limit 1 809 00:41:15,400 --> 00:41:16,158 Nikolay: and hoping that I'm okay. 810 00:41:16,158 --> 00:41:20,540 Let me give you an example I saw before what we discussed today. 811 00:41:20,740 --> 00:41:23,200 So we have full-text search, for example. 812 00:41:24,120 --> 00:41:28,300 And we have rare words and we have very frequent words present 813 00:41:28,320 --> 00:41:31,220 in the majority of records in TSVector. 814 00:41:31,220 --> 00:41:32,640 Yeah, that's a nice example. 815 00:41:32,940 --> 00:41:33,300 Right. 816 00:41:33,300 --> 00:41:39,660 And then we have created that OR ID and we want to find all matching 817 00:41:39,960 --> 00:41:43,520 documents and return only 15 latest, for example. 818 00:41:43,580 --> 00:41:46,400 In this case, Postgres must choose between need to choose, must 819 00:41:46,400 --> 00:41:53,200 choose between ordering by bit 3 and then applying a filter to 820 00:41:53,200 --> 00:41:58,200 add science, right, TS vector, add TS query. 821 00:41:59,100 --> 00:42:04,220 Or it might decide, okay, I need to choose, I need to extract 822 00:42:04,300 --> 00:42:08,580 all documents and then order by memory and then limit. 823 00:42:10,000 --> 00:42:11,980 Same problem, completely same problem. 824 00:42:11,980 --> 00:42:16,440 And I saw terrible cases when I thought, wow, like, why do you 825 00:42:16,440 --> 00:42:17,000 do this? 826 00:42:17,000 --> 00:42:22,160 Why do you think this word is super frequent? 827 00:42:22,640 --> 00:42:27,740 So instead of using gin, you decide to use B-tree to order by limit 828 00:42:27,740 --> 00:42:29,000 15, for example. 829 00:42:29,200 --> 00:42:34,820 And then you end up scanning almost everything, finding my 1 830 00:42:34,820 --> 00:42:36,960 or 2 records only in the end of scanning. 831 00:42:37,120 --> 00:42:38,700 It's absolutely the same situation. 832 00:42:39,240 --> 00:42:42,620 But the problem to me is not limit there at all. 833 00:42:42,980 --> 00:42:46,000 I put limit, I see the problem, I understand. 834 00:42:46,000 --> 00:42:47,800 Limit didn't help, I understand why. 835 00:42:47,800 --> 00:42:51,380 Because we need to combine both things in 1 single index and 836 00:42:51,380 --> 00:42:54,220 have only 1 single index scan. 837 00:42:54,240 --> 00:42:57,840 Unfortunately, this is impossible there, right, in this particular 838 00:42:57,860 --> 00:42:58,180 case. 839 00:42:58,180 --> 00:43:01,580 Because for that you need to use a RAM index and it can be not 840 00:43:01,580 --> 00:43:02,080 available. 841 00:43:02,160 --> 00:43:04,040 Nardius, for example, it's not present. 842 00:43:05,900 --> 00:43:10,380 Michael: Or like work out some middle ground, take way more rows 843 00:43:10,380 --> 00:43:13,680 than you want and then sort them, that kind of thing. 844 00:43:13,680 --> 00:43:17,380 Nikolay: Right, so I mean We sit on the edge here. 845 00:43:19,380 --> 00:43:21,340 We might be lucky, but we are not. 846 00:43:21,820 --> 00:43:22,540 And what else? 847 00:43:22,540 --> 00:43:23,240 What can I say? 848 00:43:23,240 --> 00:43:27,420 It's not a problem of limit, it's a problem of lack of indexing. 849 00:43:29,600 --> 00:43:30,640 Michael: Let's try and wrap up. 850 00:43:30,640 --> 00:43:34,320 And I think I agree with you that to solve the performance issue, 851 00:43:34,320 --> 00:43:37,540 you don't get rid of the limit, you add a good index or you get 852 00:43:37,540 --> 00:43:41,000 your stats, like you work out your stats correlation, maybe create 853 00:43:41,000 --> 00:43:42,340 some statistics there. 854 00:43:42,340 --> 00:43:45,780 Maybe you just need to run analyze on that table or maybe globally, 855 00:43:46,100 --> 00:43:46,640 like that. 856 00:43:46,640 --> 00:43:50,860 There's lots of ways of solving it so that that query doesn't 857 00:43:50,860 --> 00:43:52,360 run slowly again in future. 858 00:43:52,640 --> 00:43:55,320 I just wanted to publicize and share that. 859 00:43:55,320 --> 00:43:58,100 And by the way, I wanted to shout out Frank Pashoe, who got in 860 00:43:58,100 --> 00:44:02,140 touch recently when I shared this Twitter thread again, to say, 861 00:44:02,860 --> 00:44:07,000 I think we agreed that it's an optimization problem. 862 00:44:07,000 --> 00:44:10,760 And yeah, he would completely be agreeing with you right now. 863 00:44:10,760 --> 00:44:13,240 I just found it really interesting and quite counterintuitive. 864 00:44:13,520 --> 00:44:16,660 Like probably it sounds like more than you did, that it could, 865 00:44:17,940 --> 00:44:20,640 like just by adding it could make a query slower. 866 00:44:20,640 --> 00:44:21,020 Nikolay: Right. 867 00:44:21,020 --> 00:44:22,920 Michael: Even though that's the sign of other issues. 868 00:44:23,100 --> 00:44:23,480 So I wanted 869 00:44:23,480 --> 00:44:23,615 Nikolay: to clarify that. 870 00:44:23,615 --> 00:44:25,540 It's an interesting phenomenon, for sure. 871 00:44:25,960 --> 00:44:29,480 And adding a limit is not a performance optimization tool. 872 00:44:29,540 --> 00:44:32,800 Performance optimization tool is order by limit and improper 873 00:44:32,800 --> 00:44:34,240 index, like together. 874 00:44:34,940 --> 00:44:35,440 Yes. 875 00:44:35,490 --> 00:44:41,260 And at the limit, I should not expect quick execution. 876 00:44:41,680 --> 00:44:45,140 I just form expectations for myself and I can then say, okay, 877 00:44:45,140 --> 00:44:49,300 this is not what I want and find a better index, which is sometimes 878 00:44:49,300 --> 00:44:53,800 not possible, for example, GIN versus B-tree and so on. 879 00:44:53,800 --> 00:44:59,760 But by the way, to be fair, with GIN statistics already, Postgres 880 00:44:59,760 --> 00:45:04,380 is quite often right, so it knows when to use B-tree and quickly 881 00:45:04,380 --> 00:45:09,840 find proper results, and it knows when it's better to find everything 882 00:45:09,840 --> 00:45:11,100 and order by memory. 883 00:45:11,160 --> 00:45:15,020 So it's not that bad, but also not ideal, not just single index 884 00:45:15,020 --> 00:45:15,520 scan. 885 00:45:16,840 --> 00:45:18,020 Michael: Yeah, cool. 886 00:45:19,740 --> 00:45:20,020 Nikolay: Okay. 887 00:45:20,020 --> 00:45:21,080 Michael: Thanks so much, Nikolay. 888 00:45:21,180 --> 00:45:21,660 Take care. 889 00:45:21,660 --> 00:45:22,040 Nikolay: Thank you.