我有一个postgres模式，如下所示：

CREATE TABLE rows
(
  id bigint NOT NULL,
  start_year integer
);

CREATE TABLE calculations
(
  id bigint NOT NULL,
  row_id bigint NOT NULL,
  year integer,
  calculation numeric(23,7)
);

INSERT INTO rows (id, start_year)
VALUES
  (1, 2020),
  (2, 2021);
  
INSERT INTO calculations (id, row_id, year, calculation)
VALUES
  (1, 1, 2019, 0),
  (2, 1, 2020, 100),
  (3, 1, 2021, 900),
  (4, 1, 2022, 300),
  (5, 1, 2023, 500),
  (6, 2, 2019, 220),
  (7, 2, 2020, 111),
  (8, 2, 2021, 222),
  (9, 2, 2024, 333),
  (10, 2, 2025, 444);

一个带有select的SQL视图，如下所示：

SELECT
  row.id,
  calc1.calculation as calc1,
  calc2.calculation as calc2,
  calc3.calculation as calc3
FROM
  rows row
  LEFT JOIN calculations calc1 on calc1.row_id = row.id and calc1.year = row.start_year
  LEFT JOIN calculations calc2 on calc2.row_id = row.id and calc2.year = row.start_year + 1
  LEFT JOIN calculations calc3 on calc3.row_id = row.id and calc3.year = row.start_year + 2;

实际上这两个表都很大。SQL查询需要大约10秒的时间来执行，而且大部分时间都被计算占用了。到目前为止，我唯一设法优化它的是：

SELECT
  row.id,
  calc.calculation->(row.start_year)::text as calc1,
  calc.calculation->(row.start_year+1)::text as calc2,
  calc.calculation->(row.start_year+2)::text as calc3
FROM
  rows row
  LEFT JOIN (select row_id, json_object_agg(year, calculation) as calculation
                    from calculations
                    group by row_id) calc on calc.row_id = row.id

现在它的性能提升了2倍，但还不够。它会查询不需要的年份值。当我将此查询替换为获取第一年、第二年和第三年时，它的工作速度要快得多。因此，我想知道是否有其他方法可以将这些JOIN合并为一个，从而提高性能。http：//sqlfiddle.com/#！17/8 ff 004/4