大于等于ALL()且等于MAX()速度 [英] Greater than or equal to ALL() and equal to MAX() speed

问题描述

我在 PostgreSQL 中有一个名为 product 的关系，它包含 2 个字段:id 和 quantity，我想找到 数量最高的商品的id.据我所知，有两种方法:

SELECT id FROM product WHERE数量>= ALL(SELECT数量 FROM product)

或

SELECT id FROM product WHEREquantity = (SELECT MAX(quantity) FROM product)

它们的执行速度有什么不同吗?

解决方案

如果任何行具有 quantity IS NULL(如 戈登演示).
只有当所有行都具有 quantity IS NULL 时，第二个查询才会失败.所以在大多数情况下它应该是可用的.(而且速度更快.)

如果您需要在 Postgres 12 或更早版本中进行 NULL 安全查询，即 NULL 是有效结果，请考虑:

SELECT id，数量来自产品WHERE 数量与 (SELECT MAX(quantity) FROM product) 不同；

或者，可能更快:

SELECT id，数量从  (SELECT *, rank() OVER (ORDER BY 数量 DESC NULLS LAST) AS rnk来自产品) 子哪里 rnk = 1;

见:

• TOP n 的 PostgreSQL 等效项有关系:限制有关系"?

Postgres 13 添加了标准 SQL 子句 WITH TIES:

SELECT id来自产品ORDER BY 数量 DESC NULLS LAST用领带取前 1 行；

db<>fiddle 这里/p>

适用于任意数量的 NULL 值.

手册:

<块引用>

SQL:2008 引入了不同的语法来实现相同的结果，PostgreSQL 也支持.它是:

OFFSET start { ROW |行 }取{第一个|下一个 } [ 计数 ] { 行 |行 } { 仅 |与领带 }

在此语法中，start 或 count 值是标准要求的是文字常量、参数或变量名；作为一个PostgreSQL 扩展，允许其他表达式，但会通常需要用括号括起来以避免歧义.如果count 在 FETCH 子句中省略，默认为 1.WITH TIES选项用于返回与最后一个相关联的任何其他行根据ORDER BY 子句放入结果集中；ORDER BY 是在这种情况下是强制性的.ROW 和 ROWS 以及 FIRST 和 NEXT 是不影响这些从句效果的干扰词.

值得注意的是，WITH TIES 不能与(非标准)短语法 LIMIT n 一起使用.

这是最快的解决方案.比您当前的任何查询都快.对于性能来说更重要的是:在(数量)上有一个索引.或者更专业的覆盖索引以允许仅索引扫描(但要快一点):

CREATE INDEX ON product (quantity DESC NULLS LAST) INCLUDE (id);

见:

• 在 PostgreSQL 中覆盖索引是否有助于 JOIN 列?

我们需要 NULLS LAST 以将 NULL 值按降序排列在最后.见:

• 按列 ASC 排序，但首先是 NULL 值?

I have a relation in PostgreSQL named product which contains 2 fields: id and quantity, and I want to find the id of the products with the highest quantity. As far as I know, there are 2 ways of doing it:

SELECT id FROM product WHERE quantity >= ALL(SELECT quantity FROM product)

or

SELECT id FROM product WHERE quantity = (SELECT MAX(quantity) FROM product)

Is there any difference in their speed of execution?

解决方案

The first query fails if any row has quantity IS NULL (as Gordon demonstrates).
The second query only fails if all rows have quantity IS NULL. So it should be usable in most cases. (And it's faster.)

If you need a NULL-safe query in Postgres 12 or older, i.e., NULL is a valid result, then consider:

SELECT id, quantity
FROM   product
WHERE  quantity IS NOT DISTINCT FROM (SELECT MAX(quantity) FROM product);

Or, probably faster:

SELECT id, quantity
FROM  (
   SELECT *, rank() OVER (ORDER BY quantity DESC NULLS LAST) AS rnk
   FROM   product
   ) sub
WHERE  rnk = 1;

See:

• PostgreSQL equivalent for TOP n WITH TIES: LIMIT "with ties"?

Postgres 13 adds the standard SQL clause WITH TIES:

SELECT id
FROM   product
ORDER  BY quantity DESC NULLS LAST
FETCH  FIRST 1 ROWS WITH TIES;

db<>fiddle here

Works with any amount of NULL values.

The manual:

SQL:2008 introduced a different syntax to achieve the same result, which PostgreSQL also supports. It is:

OFFSET start { ROW | ROWS }
FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } { ONLY | WITH TIES }

In this syntax, the start or count value is required by the standard to be a literal constant, a parameter, or a variable name; as a PostgreSQL extension, other expressions are allowed, but will generally need to be enclosed in parentheses to avoid ambiguity. If count is omitted in a FETCH clause, it defaults to 1. The WITH TIES option is used to return any additional rows that tie for the last place in the result set according to the ORDER BY clause; ORDER BY is mandatory in this case. ROW and ROWS as well as FIRST and NEXT are noise words that don't influence the effects of these clauses.

Notably, WITH TIES cannot be used with the (non-standard) short syntax LIMIT n.

It's the fastest possible solution. Faster than either of your current queries. More important for performance: have an index on (quantity). Or a more specialized covering index to allow index-only scans (a bit faster, yet):

CREATE INDEX ON product (quantity DESC NULLS LAST) INCLUDE (id);

See:

• Do covering indexes in PostgreSQL help JOIN columns?

We need NULLS LAST to keep NULL values last in descending order. See:

• Sort by column ASC, but NULL values first?

这篇关于大于等于ALL()且等于MAX()速度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！