If you can believe it, there's even another option we have, and that's controlling where nulls end up in our result set. By default, nulls are treated as larger than any other value, but we can change that both in the query and in the index construction! We can do anything we want. Let me show you.

Let's start out by proving that nulls are greater than every other value because I'll tell you what, I often forget that. This is explicitly saying order by birthday ascending, which means all those nulls would be at the bottom if they are of the greatest value. Let's switch it around to descending, which should, there you go, put it at the top. Birthday being null comes after every other birthday because it is treated as the largest value. You can change this. You can say, in this case, nulls last, and the null will go to the very, very bottom. In this case, it's filtered out because we have a limit of 10 on here.

Now, if we did say nulls first, that doesn't do a whole lot because nulls first is the default when it is descending. When it is ascending, you can say, "Yeah, do it ascending, "but instead put all the nulls at the top "and then do the ascending value." Likewise, this is useless because that is the default behavior. We're gonna leave it as nulls first, and then we can create an index that mirrors that.

We'll do create index, we'll call it birthday_nulls_first, null first is fine on users. We're gonna say birthday. And we can say ascending, which is fine. That's the default, but we'll go ahead and leave it for clarity's sake. We'll say nulls first. If we run that, and then get out of there, and then we run this, it's pretty fast. That doesn't tell us anything. Let's take a look at the explain index scan on birthday_null_first users. If we switch this to nulls last, whap-whap, we're back to a sequence scan on users; switch it back to nulls first, and we're good; switch this to descending, and we're not good, descending nulls last.

We are using a backward index scan, so those rules about forward and backwards still pertain. If you were to switch both of these things, then you're fine because that can read the index the opposite direction. If you skip just one of them, then the nulls are in the wrong spot, everything is in the wrong spot, so it's not going to work. If you find yourself reaching for nulls first or nulls_last in a query relatively often, you might consider creating an index that represents that same exact order.