不久前,我问了这个关于在 Postgres 中有效选择列的独特排列的问题。现在我有一个关于如何执行此操作的后续问题,此外还能够使用ASC
/DESC
跨列的任意组合对任何列进行排序。
该表包含数亿行,虽然我上一个问题的公认答案比传统方法快几个数量级,但无法以临时方式对列进行排序使我无法充分利用此查询(我真的需要它来“分页”,用LIMIT
/OFFSET
分成小块)。有没有办法做到这一点?上一个答案的作者善意地建议了一种解决方法(更改显式 where 子句的行比较),我尝试过,但它似乎不起作用(或者我误解了它)。
给出以下通用查询:
WITH RECURSIVE cte AS (
(
SELECT col1, col2, col3, col4
FROM tbl
ORDER BY 1,2,3,4
LIMIT 1
)
UNION ALL
SELECT l.*
FROM cte c
CROSS JOIN LATERAL (
SELECT t.col1, t.col2, t.col3, t.col4
FROM tbl t
WHERE (t.col1, t.col2, t.col3, t.col4) > (c.col1, c.col2, c.col3, c.col4)
ORDER BY 1,2,3,4
LIMIT 1
) l
)
SELECT * FROM cte
有没有办法以临时方式对列进行排序,同时保持性能?例如:
ORDER BY by col1 DESC, col2 ASC, col3 ASC, col4 DESC
假设每列都有一个索引,以及所有 4 列的组合索引。
Postgres 版本是 15.4。
该表是只读的,因为数据不能/不会被修改,但会被添加。以下是CREATE TABLE
复制我有问题的表的脚本(或多或少):
CREATE TABLE tbl (id SERIAL primary key, col1 integer NOT NULL, col2 integer NOT NULL, col3 integer NOT NULL, col4 integer NOT NULL);
INSERT INTO tbl (col1, col2, col3, col4) SELECT (random()*1000)::int AS col1, (random()*1000)::int AS col2, (random()*1000)::int AS col3, (random()*1000)::int AS col4 FROM generate_series(1,10000000);
CREATE INDEX ON tbl (col1);
CREATE INDEX ON tbl (col2);
CREATE INDEX ON tbl (col3);
CREATE INDEX ON tbl (col4);
CREATE INDEX ON tbl (col1, col2, col3, col4);
基本上:不行。
行值比较适用于排序
ASCENDNG
顺序DESCENDING
,但不适用于嵌套字段之间的方向混合。看:然而 ...
数字类型的解决方法
模拟索引跳跃扫描
请注意,模拟索引跳过扫描仅对于列集有许多重复项的大表有意义。但随后它可能会产生数量级的差异。
基本:
对于交替排序顺序,有一些针对具有多列表达式索引的数字数据类型(或任何可以“反转”的类型)的解决方法。
col1 DESC
基本上,您操作的是(col1 * -1) ASC
-ASC
作为默认方向,而不是。对于你的例子:
那么查询就变成:
小提琴
Postgres 无法使用仅索引扫描,因为
col1
和col4
隐藏在表达式后面。根据许多存储和 RAM 因素,使用覆盖索引可能是值得的。看:同样的查询。
但这仍然是“有效选择列的唯一排列”的主题。
分页
您提到了分页(使用
LIMIT
/OFFSET
)。您可以根据上面的查询进行查询。喜欢:应该执行正常。对于临时使用来说意义有限。
如果表不是只读的或者当
OFFSET
+LIMIT
变大时,键集分页更有意义。看:用行号具体化唯一的行
要在大型只读表上重复使用,请考虑使用带有行号的物化视图。在您的情况下,“将添加行”可能有意义,也可能没有意义。您可以使用上面的查询来创建 MV:
然后,索引和查询就很简单了:
小提琴
所有这一切都有很多细微差别......