Indices
创建索引x.id
and y.id
- 如果这些是您的主键,您可能已经拥有了。
多列索引也可能有所帮助,尤其是仅索引扫描 https://wiki.postgresql.org/wiki/Index-only_scans在 9.2+ 页中:
CREATE INDEX y_mult_idx ON y (id DESC, val)
然而,在我的测试中,一开始并没有使用这个索引。必须添加(否则毫无意义)val
to ORDER BY
让查询规划器相信排序顺序是匹配的。查看查询3.
该指数在这种综合设置中几乎没有什么区别。但对于具有更多列的表,检索val
表格变得越来越昂贵,使得“覆盖”指数更具吸引力。
Queries
1)简单
SELECT DISTINCT ON (x.id)
x.id, y.val
FROM x
JOIN y ON y.id <= x.id
ORDER BY x.id, y.id DESC;
SQL 小提琴。 http://sqlfiddle.com/#!12/38903/7
该技术的更多解释DISTINCT
在这个相关的答案中:
- 选择每个 GROUP BY 组中的第一行? https://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group/7630564#7630564
我运行了一些测试,因为我怀疑第一个查询不能很好地扩展。对于小表来说速度很快,但是对于较大的表则不好。 Postgres 不会优化该计划,而是从(有限的)交叉连接开始,成本为O(N²)
.
2) Fast
这个查询仍然相当简单并且扩展性非常好:
SELECT x.id, y.val
FROM x
JOIN (SELECT *, lead(id, 1, 2147483647) OVER (ORDER BY id) AS next_id FROM y) y
ON x.id >= y.id
AND x.id < y.next_id
ORDER BY 1;
窗口函数lead() http://www.postgresql.org/docs/current/interactive/functions-window.html是有帮助的。我利用该选项提供默认值来覆盖最后一行的极端情况:2147483647
is the 最大可能的整数 http://www.postgresql.org/docs/current/interactive/datatype-numeric.html。适应您的数据类型。
3)非常简单并且几乎一样快
SELECT x.id
,(SELECT val FROM y WHERE id <= x.id ORDER BY id DESC, val LIMIT 1) AS val
FROM x;
通常情况下,相关子查询往往会很慢。但是这个可以只从(覆盖)索引中选择一个值,并且在其他方面非常简单,因此可以竞争。
额外的ORDER BY
item val
(粗体强调)似乎毫无意义。但添加它可以让查询规划者相信使用多列索引是可以的y_mult_idx
从上面开始,因为排序顺序匹配。请注意
使用 y_mult_idx 仅索引扫描..
in the EXPLAIN
output.
测试用例
经过激烈的辩论和多次更新后,我收集了迄今为止发布的所有查询,并制作了一个测试用例以进行快速概述。我只使用 1000 行,因此 SQLfiddle 不会因较慢的查询而超时。但前 4 个(Erwin 2、Clodoaldo、a_horse、Erwin 3)在我所有的本地测试中都是线性扩展的。
再次更新以包含我的最新添加内容,现在改进格式和按性能排序:
大 SQL 小提琴比较性能。 http://sqlfiddle.com/#!12/6d7e1/4