不久前,我问了这个关于在 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);