Press "Enter" to skip to content

Indexes and Sorts

Chad Callihan reminds us that sort order can matter for indexes:

When you’re working on an index for a query ordering by one column in ascending order and another column in descending order, do you have your index created to match? Did you know you can specify ASC or DESC in an index? Let’s go through a scenario where ordering in an index makes a noticeable difference.

This is particularly important for window functions—the optimizer can sometimes be smart enough to recognize that a value is in reverse order and not need to use a sort operator, but as soon as you drop that OVER() clause in, if things aren’t in the exact order they need, you get a sort operator thrown in for free. Or, well, the “your query is now a little bit slower” version of free.