(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:
- primary key ('pk'),
text
(btree) - another
text
(btree) - a
timestamp with time zone
(btree) - a
tsvector
(gin) - 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
:
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 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.)