如何使用性能最高的窗口函数在联接值上构建聚合?

Question

我在使用窗口函数在连接值上构建聚合时遇到了问题。简化后看起来像这样：

我得到了以下表格：

CREATE TABLE movies (
id SERIAL,
name VARCHAR,
year INT,
genre VARCHAR,
country VARCHAR
);
CREATE TABLE tags (
id SERIAL,
name VARCHAR
);
CREATE TABLE movies_tags (
id SERIAL,
movie_id INT,
tag_id INT
);

现在我想做以下语句：

SELECT m.*, array_agg(t.name) AS tags
FROM movies m
LEFT JOIN movies_tags mt ON mt.movie_id = m.id
LEFT JOIN tags t ON t.id = mt.tag_id
ORDER BY m.name
LIMIT 10

由于选择中的聚合，所有电影都会与所有标签联接，然后再从该大联接中选择前 10 个。我的目标是出于性能原因，仅在前 10 部电影上获得聚合。所以我所做的是这样的：

WITH top_movies AS (
SELECT m.*
FROM movies m
ORDER BY m.name
LIMIT 10
)
SELECT tm.*, array_agg(t.name) AS tags
FROM top_movies tm
LEFT JOIN movies_tags mt ON mt.movie_id = tm.id
LEFT JOIN tags t ON t.id = mt.tag_id

性能要好得多。但我有另一个问题。最终目标是创建一种可重用组件的形式，例如Postgres中的函数或ORM中的命名查询，例如Rails的活动记录，之后我可以根据自己的需要动态修改，例如：

SELECT * FROM my_top_movies_with_tags() AS tm
WHERE tm.country = 'USA' AND tm.year <= 1995
LIMIT 10;

因此，我必须修改我的 SQL 语句，即电影选择是外部查询，但它仍然将标签的连接限制为我想要的前 n 部电影。

为了实现这一点，我尝试了横向连接并做了这个：

SELECT m.*, lat.tags FROM movies m
LATERAL (
SELECT array_agg(t.name) AS tags
FROM movies_tags mt
JOIN tags t ON t.id = mt.tag_id
WHERE mt.movie_id = m.id
) AS lat
ORDER BY m.name
LIMIT 10;

这使我能够灵活地在之后动态修改它，但性能要差得多。

还有其他方法可以实现我不知道的目标吗？

我的目标总结如下：

1. 仅在LIMIT的电影集上构建聚合(array_agg)，而不是在整个电影表上。
2. 通过附加WHERE、ORDER和LIMIT语句来保持可修改。
3. 表现良好。

Answer 1

使用 row_number 模拟LIMIT怎么样？

SELECT * FROM (
SELECT 
m.*, 
array_agg(t.name) AS tags,
row_number() OVER(ORDER BY m.name) AS rownum
FROM 
movies m
LEFT JOIN movies_tags mt ON mt.movie_id = m.id
LEFT JOIN tags t ON t.id = mt.tag_id
--There're must be a GROUP BY here
) AS tmp
WHERE rownum <= 10;

此外，在对性能关键查询使用 CTE 时，请考虑本文。

Answer 2

您可以将表单输入添加到临时表，并使用此表进行筛选。

CREATE TEMP TABLE temp_inputs
(
country VARCHAR(80),
year int
)
ON COMMIT DELETE ROWS;

WITH top_movies AS (
SELECT m.*
FROM movies m
ORDER BY m.name
LIMIT 10
)
SELECT tm.*, array_agg(t.name) AS tags
FROM tmovies tm, temp_inputs
LEFT JOIN movies_tags mt ON mt.movie_id = tm.id
LEFT JOIN tags t ON t.id = mt.tag_id
and tm.country = temp_inputs.country AND tm.year <=  temp_inputs.year