Another thing that might be helpful when creating an index, especially a composite index, is creating it in the order that you want to read it. If you're commonly sorting by a certain column descending, you can create an index over that column in descending order.

Let me show you. If we did select * from pg_indexes where table name = 'users', I think we're back down to just the primary key and let's create one on the created at column. We'll do create index created_at on users(created_at) and if we create that and then do select * from users order by created_at limit 10, and we're gonna throw out and explain on the front of that. You'll see we are just scanning the index and then limiting it. If we change this from ascending, which is the default to descending, you'll see index scan backwards using created_at on users.

Postgres has the ability to read the index from front to back or to start at the end, and read from back to front. It does have the ability to do a backwards index scan, which is very good. Really, it doesn't really matter. You can put it kind of in any order and Postgres will look at it and use it in whichever order makes sense. However, when you have a multi-column index and maybe you're sorting them in different directions, you don't get that benefit. You have to create your index in a certain way.

Looking at this table, let's go ahead and create a composite index on birthday and created_at, and we're gonna order by both of these things. If we do birthday and created_at, we'll get this new index, and then we can do select * from users order by birthday, created_at. If we explain that with a limit 10 on it, we should see an index scan using birthday_created_at. That's totally fine. By default, this is ascending, so this shouldn't change anything at all, but if we change these both to descending, you'll see index scan backwards. We're still totally fine. That's still totally fine, because that index was created with birthday and created_at. As long as you switch them both together, the index can be read front to back or back to front.

The problem comes when you switch one but not the other. If we were to do this and, say, birthday is ascending or birthday is descending, created_at is ascending, then you see we have an incremental sort. It pulls out a block and then it has to do some sorting in there. We can switch this around to, say, birthday is ascending, created is descending, and you see we still have the exact same problem. That's because the index is one singular structure. It can't read part of it forward and part of it backwards. It has to do this gathering phase and then this incremental sorting phase after. You can create your index in different orders.

Let's go ahead and drop index birthday_created_at. Then just to be explicit, I'm gonna say that this is ascending, which is the default, and this is descending. After that index is created, if we match this up, ascending, ascending, descending, descending, then we are good, and you'll see that we've lost that incremental sort, which is a good thing. Interestingly, you can still switch them both. You just have to switch them both. This index should be read in a backwards order, because it is the complete opposite of the order that it was created in. You see now it is reading, it is scanning that index backward. Again, you can't just flip one of them or you get that incremental sort back in the picture. Not as big of a deal for a single index, because you don't really have that problem of trying to match up two different columns.

For a composite index where you're using the last column to order and you're putting it in a descending order, this might be what you're looking for.