PostgreSQL 9.5 – Why Newest Index Not Used for ORDER BY?

indexoptimizationperformancepostgresqlpostgresql-9.5postgresql-performance

(Follow up from this post: Why is my PostgreSQL expression index not being used when I ORDER BY in a subquery?)

PostgreSQL 9.5.

I can't divulge full details, but table has 22 columns and 5 indexes:

  1. primary key ('pk'), text (btree)
  2. another text (btree)
  3. a timestamp with time zone (btree)
  4. a tsvector (gin)
  5. my latest one, a bigint (btree)

(From the previous post you know that I was trying to avoid creating this additional column, and just use an expression index–adding two integer columns together–without success. The bigint column here probably could have been just 'integer' but I made a mistake creating it; it took about an hour to add the column, populate it, and reindex, so I'm hoping this isn't relevant but mentioning it just in case it is.)

All are btree except the tsvector.

The following queries all take only 12ms and use just an Index Scan:

  1. SELECT pk FROM table ORDER BY pk DESC LIMIT 10
  2. SELECT pk FROM table ORDER BY text_column DESC LIMIT 10
  3. SELECT pk FROM table ORDER BY timestamp_column DESC LIMIT 10

But if I try to use my new bigint index for ORDER BY:

SELECT pk FROM table ORDER BY bigint_column DESC LIMIT 10

…it takes 2.7s and uses Limit -> Sort -> Seq Scan.

My "cheating" method is the closest I seem to be able to get to making use of the index:

SELECT pk
FROM table
WHERE bigint_column > 1000000
ORDER BY bigint_column DESC LIMIT 10

This takes 12ms and uses Limit -> Sort -> Bitmap Heap Scan (bigint_column > 1000000) -> Bitmap Index Scan (bigint_column > 1000000).

This is after a VACUUM ANALYZE after adding the index.

I thought it strange that my expression index was not being used in the other question. Now it's just a plain old column (I haven't even added what would be the necessary triggers to actually go this route.)

Why isn't my newest index being used, when the other three work "just fine"? (As pointed out in the comments at https://dba.stackexchange.com/a/183290/28774 , an Index-Only Scan would be even better. I fail to see why all of these queries wouldn't use at least an Index Scan, let alone an Index-Only Scan, instead of a full Seq Scan.)

The index definition has DESC NULLS LAST (although it's a non-nullable column.)

Best Answer

In PostgreSQL, an index which is DESC NULLS LAST cannot be used to satisfy an ORDER BY which is DESC NULLS FIRST (which includes ordering by simply DESC because that implies NULLS FIRST). This is the case even if the column is defined to be NOT NULL.

You could either rebuild the index, or (since you know the column is not null) you can add NULLS LAST to your query's ORDER BY to make it match the existing index.

Note that PostgreSQL does know how to follow an index backwards, so a default index (which is implicitly ASC NULLS LAST) would also be able to satisfy your DESC NULLS FIRST query. Because of this, it is rarely important to specify DESC in an index, but it can be important to specify which end the NULLS sort to.

Related Question