PostgreSQL的order by和limit

Question

我有一个表R，想要根据z列选择3个前任或继任者。我在z列上有一个B树索引。

SELECT key, z, x, y FROM R
    WHERE z <= some_value
    ORDER BY z DESC 
    LIMIT 3

检索“无时间”中的前置项，但

SELECT key, z, x, y FROM R
    WHERE z >= some_value
    ORDER BY z ASC
    LIMIT 3

该查询语句对于检索下一条记录是无用的，因为它开始扫描所有具有 z >= 某个值的行。Explain 提供了相同的查询解释。

为什么会这样，我可以怎么做来解决这个问题？

编辑： 实际上有两个索引，一个升序，一个降序。 以上两个查询的 Explain 结果显示： "Limit (cost=0.00..31.91 rows=3 width=20)" " -> Index Scan Backward using zreverse on r (cost=0.00..17727815.24 rows=1666667 width=20)" " Filter: (z >= some_value")

- PLaine

---

你能否为这两个查询提供 EXPLAIN 的输出结果？请参考 http://www.postgresql.org/docs/current/static/using-explain.html 了解如何使用该命令。 - Colin 't Hart

你试过使用explain analyze而不是仅使用explain吗？ - Clodoaldo Neto

问题出在用于计算某个值的简单函数上，为了清晰起见这里省略了它的具体内容... 它被定义为volatile，导致每行都要重新计算。 - PLaine

Answer 1

嗯...

在WHERE子句中使用的某个变量是通过一个简单的函数计算得出的，但该函数被错误地定义为易变(VOLATILE)。将其更改为稳定(STABLE)后问题得到了解决。

- PLaine

---

很高兴知道这个：根据 http://www.postgresql.org/docs/current/static/xfunc-volatility.html，优化器不会对标记为易变的函数返回的值做出任何假设，并为每一行执行此函数。如果标记为稳定，则优化器知道返回的值对于相同的输入将是相同的，并且可以优化执行函数的过程——例如索引扫描。 - Colin 't Hart

Answer 2

z上的索引是“普通”的B树索引，没有指定其他内容吗？还是按照“降序”创建的呢，即

create index <name> on R(z) desc;

?

很可能将相反顺序的索引添加到您现有的索引中可能会有所帮助。

- Colin 't Hart