Left to right no skipping and stops at the first range.

This we haven't looked at stops at the first range yet.

In the previous video we looked at forming a leftmost prefix and as we discussed, Postgres does have the ability to go from a traversal to a scan if you do skip over a column. While it is not ideal, Postgres does cover you. The same thing applies when it comes to range conditions.

I've created two indexes here. First last birth, which is first name, last name, birthday, and first birth last, which is first name, birthday, last name. Kind of hard to say.

If we did select * from users where, and then we're gonna create this query where first name = Aaron and last name = Francis and birthday < 1989 02 14. If we run that, we'll see we don't get anybody back. Let's just change this to 12 31 because I do know when my birthday is. There we go. Now if we did explain which index is it using, it is using first last birth. So it selected first name, last name, birthday instead of selecting first name, birthday, last name. Why is that? Because that is the most efficient usage of a B-tree index.

Imagine that we're the database and we are tasked with this query, right? We can do a direct traversal like that animation we did earlier, we can do a direct traversal of the nodes looking for Aaron and Francis, right? So left, right, left, right, left right, boom, we got 'em, Aaron and Francis. Now we encounter a condition that says birthday is less than 12 31 of 1989. We don't know how to directly traverse that.

What we do is we take the Aaron Francis and we go to the very first matching node that says Aaron Francis. And we just start looking, we just start scanning all the way across until we reach 12 31 of 1989. Then that is our chunk of rows that we go grab from the heap. The first time that Postgres encounters a range condition, immediately it starts scanning the index, which is why you want your leftmost prefix to be a commonly used strict equality conditions. Then as you move to the right of your index, you can have less commonly used equality or your range conditions. Because if you skip over a column or you encounter a range condition, then it starts scanning, which is fine, but not as efficient as a direct traversal on equality.

Left to right, no skipping, stops at the first range.

In other databases, the index will not be used after the first range, or if you skip a column. Thanks to Postgres, it will continue to be used, but it's just not as efficient as we've seen here. It will pick the one that is most efficient, which in all cases, equality on the left, range scans on the right.