作者热门文章
- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
我有一些大型 sql 查询,在 SELECT
block 中有许多计算列。此外,还有那些计算列之一的排序,并且限制只有 100 行。但是 postgres 计算每一行的所有列,而不仅仅是 100。
让我举例说明。
让我们创建一些测试表:
CREATE TABLE test_main(col1 INTEGER);
并用一些随机数据填充它:
DO
$do$
BEGIN
FOR r IN 1..100000 LOOP
INSERT INTO test_main(col1) VALUES (trunc(random()*1000));
END LOOP;
END
$do$;
然后创建一些额外的表:
CREATE TABLE test_main_agg1(
col1 INTEGER,
val INTEGER
);
CREATE TABLE test_main_agg2(
col1 INTEGER,
val INTEGER
);
也填写它:
DO
$do$
DECLARE
r test_main%rowtype;
BEGIN
FOR r IN SELECT * FROM test_main LOOP
FOR i IN 1..5 LOOP
INSERT INTO test_main_agg1(col1, val) VALUES (r.col1, trunc(random()*1000));
INSERT INTO test_main_agg2(col1, val) VALUES (r.col1, trunc(random()*1000));
END LOOP;
END LOOP;
END
$do$;
当然,还要创建一些索引:
CREATE INDEX test_main_indx ON test_main(col1);
CREATE INDEX test_main_agg1_val_indx ON test_main_agg1(col1,val);
CREATE INDEX test_main_agg2_val_indx ON test_main_agg2(col1,val);
现在,如果我们执行这个查询:
SELECT col1,
(SELECT MAX(val) FROM test_main_agg1 g WHERE g.col1=m.col1) max_val1,
(SELECT MAX(val) FROM test_main_agg2 g WHERE g.col1=m.col1) max_val2
FROM test_main m
LIMIT 100;
因为有索引所以会很快。如果我们添加 ORDER BY col1
它仍然会很快。但是,如果我们将使用 ORDER BY max_val1
,则大约需要 2 秒。如果我们对使用 `ORDER BY max_val1 的查询运行 EXPLAIN ANALYZE
,我们将看到以下行:
SubPlan 4
-> Result (cost=4.06..4.07 rows=1 width=0) (actual time=0.011..0.011 rows=1 loops=100000)
InitPlan 3 (returns $3)
-> Limit (cost=0.42..4.06 rows=1 width=4) (actual time=0.010..0.010 rows=1 loops=100000)
-> Index Only Scan Backward using test_main_agg2_val_indx on test_main_agg2 g_1 (cost=0.42..1818.25 rows=500 width=4) (actual time=0.010..0.010 rows=1 loops=100000)
Index Cond: ((col1 = m.col1) AND (val IS NOT NULL))
Heap Fetches: 100000
这意味着,postgres 计算 100000 行的 max_val2
,而不是仅计算 100 行。我理解为什么 postgres 需要计算 max_val1
,而不是 max_val2
。
也许有一些提示或类似的东西告诉 postgres 在执行排序和限制后计算列?
最佳答案
LIMIT
限制整个查询的输出,而不是主查询中子查询的输出。如果您只想要最多 100 行,则需要先选择它们,然后在该子集上应用 max():
SELECT col1,
(SELECT MAX(val) FROM test_main_agg1 g WHERE g.col1=m.col1) max_val1,
(SELECT MAX(val) FROM test_main_agg2 g WHERE g.col1=m.col1) max_val2
FROM (
select val, col1
from test_main
LIMIT 100
) m;
请注意,limit
没有 ORDER BY
没有任何意义。关系数据库中的行没有顺序。因此,表格中没有“前 100 行”这样的东西,除非您指定了排序顺序。
关于postgresql - 如何让 Postgres 在订购后计算列?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/49672206/
我是一名优秀的程序员,十分优秀!