Why is my PostgreSQL expression index not being used when I ORDER BY in a subquery?
Query optimizers generally work by figuring out how to limit the number of possible rows for the query as quickly as possible, based on what it knows about the tables and their data, and the options it has on how to get to that data.

One of the factors it has to consider is how many pages it will have to load to get to the data you need. Reading pages from disk is one of the most expensive things a DBMS does, so if it can deal with fewer pages, that's generally going to be a good thing.

Your query doesn't normally include any check on a+b. So, that index will not help with the selection of the data, and there's no reason to load it in.

Once the rows to use have been identified, is it useful to use the (a+b) DESC index to sort them? I can only see two ways to do this:


Use the index backwards: instead of identifying rows based on values for a+b, we look through the index starting at the max value for a+b, and check each associated row to see if it's one of our candidates, stopping when we get ten rows. This requires that we load an unknown number or pages of the index, and then look up rows to see if they exist in the candidate data. Barring situations where we know almost all rows are candidates, I can't see this as being fast.
Calculate the a+b values for the candidate rows, and then use the index to sort: Since one of the ways to match the index against the values of a+b from the table is to sort the calculated values of a+b from the table, it would make more sense to do exactly what it sounds like the engine did: calculate a+b from the candidate rows and sort that directly, without bothering to load the index pages as well.


When you add a search criteria that involves a+b, the picture changes completely. Instead of having to use the index backwards (finding a+b values based on the rows), it actually needs to find rows based on a+b values. If it can keep the data in the sorted order while checking the other criteria, then yes, it would use that sorted order, and not have to perform a separate step to sort the data.

You should also note that, if your primary key is a clustered index, then the data is actually stored based on that value; that has an impact on how easy it is to sort by that.

Also note that the entire set of returned data has to be sorted by a+b to find the top ten a+b values.

If this query is critical, then you may be able to use variation on a covering index. Normally, this is an index that includes all the columns used in the query (whether in the SELECT list, in table joins in the FROM clause, in the WHERE clause, the ORDER BY - all the columns used).

Note that every column in the query does not necessarily have to be used as a part of the index itself. The actual index should cover enough columns to be useful in narrowing down the rows to be returned. Additional columns are generally included at the "leaf" level - they aren't used to sort the rows from the table, but the data is available in the index.

If all the columns in the query that come from this table are part of the index, or included at the leaf level, then there's no need to actually refer to the table; the covering index has all the data needed, and will normally be smaller than the full table, so we can get everything we need from the index, and load fewer pages.

It sounds like you're bringing back all the columns from the table, so a true covering index won't necessarily help much. However, an index that covers the columns except for those only in the SELECT list might work for you. By this, I mean all the columns from this table used in JOIN clauses, the WHERE clause, any GROUP BY or HAVING clauses, and (in this case) even those in the ORDER BY clause.

Using such an index, the engine could find all the rows it needs to return without referring to the actual table. Since we've included the columns from the ORDER BY clause, it should even be able to determine the 10 rows that need to be returned (thanks to your LIMIT clause). With only ten rows involved, the engine might find it faster to identify the ten rows using the index, and then to look those 10 rows up in the actual table to get the full set of values to be returned.

When a number of columns that aren't in any index it's using are involved in the identification of the actual rows to return, the engine is more likely to rely on a table scan for at least part of the query. It has to retrieve the final result set from the table, so if it thinks it will have to load more pages if it uses the index and then looks up the rows in the table, instead of going straight to the table, it might well choose to do that.

Again, please remember this is based on almost no specific information. This will work best if there are many columns (or even a few very large columns) that aren't needed in the query to identify the rows to be returned, but are only part of the ultimate SELECT list.

With respect to this statement:


  However, the index expressions are not recomputed during an indexed search, since they are already stored in the index.


I think you may be generalizing it a bit too much. Without the expression index, to search for all rows where a+b &gt;= 12000000, a+b has to be calculated for each row. By having this value indexed, we can easily identify rows where the value of a+b meets our criteria. However, this does not mean that the value of a+b is also pre-computed at the table level. If there's no way to use the index to identify our rows, then (as discussed early in this answer) the index won't be used at all. And, if the index is not used, then a+b does still have to be calculated. Indexes are designed to find specific rows based on the indexed values, not to find the indexed values based on specific rows.