72423
I have a table that contains an 'id' column of type BIGSERIAL. I also have an index for this one column (sort order descending, BTREE, unique).
I often need to retrieve the last 10, 20, 30 entries from a table of millions of entries, like this:
SELECT * FROM table ORDER BY id DESC LIMIT 10
I would have thought it's a pretty clear case: there's an index for this particular field, sort order matches, and I need only 10 entries compared to millions in the whole table, this query definitely uses an index scan.
But it doesn't it does a sequential scan over the whole table.
I try to dig deeper, didn't find anything unusual. The Postgres doc at https://www.postgresql.org/docs/9.6/static/indexes-ordering.html says:
An important special case is ORDER BY in combination with LIMIT n: an explicit sort will have to process all the data to identify the first n rows, but if there is an index matching the ORDER BY, the first n rows can be retrieved directly, without scanning the remainder at all.
But it still doesn't work. Does anybody have any pointers for me? Maybe I'm just not seeing the forrest for the trees anymore... :-(
Answer1:
Ok, saying it out loud and trying to gather more information to put into my question apparently made me see the forrest again, I found the actual problem. Further down in the doc I mentioned above is this sentence:
An index stored in ascending order with nulls first can satisfy either ORDER BY x ASC NULLS FIRST or ORDER BY x DESC NULLS LAST depending on which direction it is scanned in.
This was the problem. I specified the sort order in the index but I ignored the NULLS FIRST vs. LAST.
Postgres default is NULLS FIRST if you don't mention it explicitly in your query. So what Postgres found was the combination ORDER BY DESC NULLS FIRST which wasn't covered by my index. The combination of both SORT ORDER and NULLS is what matters.
The 2 possible solutions:
-
<li>Either mention NULLS FIRST/LAST accordingly in the query so that it matches the index</li>
<li>...or change the index to NULLS FIRST (which is what I did)</li>
</ul>
Now Postgres is doing a proper index scan and only touches 10 elements during the query, not all of them.
Answer2:
If you need to get last 10 entries in table you can use this:
SELECT *
FROM table
WHERE id >= (SELECT MAX(id) FROM table) - 10
ORDER BY id DESC
And similarly for 20 and 30 entries. This looks not so clear, but works fast as long as you have index for 'id' column.
