问题描述
我有一个表，其中包含一个BIGSERIAL类型的 id列。我也为该列提供索引（降序排列，BTREE，唯一）。
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).
我经常需要从表中检索最后10、20、30个条目百万个条目，例如：
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
我本以为这是一个很明显的例子：这个特定字段有一个索引，排序顺序匹配，我只需要比较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:
但是它仍然不起作用。有人对我有指导吗？也许我只是再也看不到树木的阿...了……：-（
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... :-(
推荐答案
好吧，大声说出来试图收集更多信息以解决我的问题，显然让我再次看到了问题所在，在上面提到的文档中，下面这句话是这样的：
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:
以升序存储的索引先为空可以满足
ORDER BY x ASC NULLS FIRST或ORDER BY x DESC NULLS LAST取决于
扫描的方向
这是问题。我在索引中指定了排序顺序，但忽略了NULLS FIRST和LAST。
This was the problem. I specified the sort order in the index but I ignored the NULLS FIRST vs. LAST.
如果您在查询中未明确提及Postgres，则默认值为NULLS FIRST，因此Postgres发现的是ORDER BY DESC NULLS FIRST组合，但我的索引未涵盖该组合。重要的是SORT ORDER和NULLS的组合。
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.
2种可能的解决方案：
The 2 possible solutions:
- 要么在查询中相应地提到NULLS FIRST / LAST，以使其与索引匹配
- 。 ..或将索引更改为NULLS FIRST（这就是我所做的事情）
现在Postgres正在执行正确的索引扫描，并且只进行触摸查询过程中有10个元素，不是全部。
Now Postgres is doing a proper index scan and only touches 10 elements during the query, not all of them.
这篇关于没有WHERE的PostgreSQL查询仅ORDER BY和LIMIT不使用索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！