(Follow up from this post: Why is my PostgreSQL expression index not being used when I ORDER BY in a subquery?)
I can’t divulge full details, but
table has 22 columns and 5 indexes:
- primary key (‘pk’),
timestamp with time zone(btree)
- my latest one, a
(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
The following queries all take only 12ms and use just an
SELECT pk FROM table ORDER BY pk DESC LIMIT 10
SELECT pk FROM table ORDER BY text_column DESC LIMIT 10
SELECT pk FROM table ORDER BY timestamp_column DESC LIMIT 10
But if I try to use my new
bigint index for
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.)
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
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.
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.