PL/pgSQL vs Subqueries vs LATERAL

easter.sql

CREATE OR REPLACE FUNCTION easter_plpgsql(year integer)
RETURNS date
LANGUAGE plpgsql
AS $$
-- https://github.com/christopherthompson81/pgsql_holidays/blob/master/utils/easter.pgsql
DECLARE
g CONSTANT integer := Year % 19;
c CONSTANT integer := Year / 100;
h CONSTANT integer := (c - c/4 - (8*c + 13)/25 + 19*g + 15) % 30;
i CONSTANT integer := h - (h/28)*(1 - (h/28)*(29/(h + 1))*((21 - g)/11));
j CONSTANT integer := (Year + Year/4 + i + 2 - c + c/4) % 7;
p CONSTANT integer := i - j;
BEGIN
RETURN make_date(
  Year,
  3 + (p + 26)/30,
  1 + (p + 27 + (p + 6)/40) % 31
);
END;
$$;

CREATE OR REPLACE FUNCTION easter_nested_subqueries(year integer)
RETURNS DATE
LANGUAGE sql
AS $$
SELECT make_date(Year, easter_month, easter_day)
FROM (
  SELECT *,
    3 + (p + 26)/30 AS easter_month,
    1 + (p + 27 + (p + 6)/40) % 31 AS easter_day
  FROM (
    SELECT *,
      i - j AS p
    FROM (
      SELECT *,
      (Year + Year/4 + i + 2 - c + c/4) % 7 AS j
      FROM (
        SELECT *,
          h - (h/28)*(1 - (h/28)*(29/(h + 1))*((21 - g)/11)) AS i
        FROM (
          SELECT *,
            (c - c/4 - (8*c + 13)/25 + 19*g + 15) % 30 AS h
          FROM (
            SELECT
              Year % 19 AS g,
              Year / 100 AS c
          ) AS Q1
        ) AS Q2
      ) AS Q3
    ) AS Q4
  ) AS Q5
) AS Q6
$$;

CREATE OR REPLACE FUNCTION easter_lateral(year integer)
RETURNS DATE
LANGUAGE sql
AS $$
SELECT make_date(Year, easter_month, easter_day)
FROM (VALUES (Year % 19, Year / 100)) AS Q1(g,c)
JOIN LATERAL (VALUES ((c - c/4 - (8*c + 13)/25 + 19*g + 15) % 30)) AS Q2(h) ON TRUE
JOIN LATERAL (VALUES (h - (h/28)*(1 - (h/28)*(29/(h + 1))*((21 - g)/11)))) AS Q3(i) ON TRUE
JOIN LATERAL (VALUES ((Year + Year/4 + i + 2 - c + c/4) % 7)) AS Q4(j) ON TRUE
JOIN LATERAL (VALUES (i - j)) AS Q5(p) ON TRUE
JOIN LATERAL (VALUES (3 + (p + 26)/30, 1 + (p + 27 + (p + 6)/40) % 31)) AS Q6(easter_month, easter_day) ON TRUE
$$;

/*

joel=# EXPLAIN ANALYZE SELECT MAX(easter) FROM (SELECT easter_plpgsql(year) AS easter FROM generate_series(1,100000) AS year) AS x;
                                                                QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=27250.00..27250.01 rows=1 width=4) (actual time=314.168..314.169 rows=1 loops=1)
   ->  Function Scan on generate_series year  (cost=0.00..26000.00 rows=100000 width=4) (actual time=17.261..299.752 rows=100000 loops=1)
 Planning Time: 0.065 ms
 Execution Time: 321.153 ms
(4 rows)

joel=# EXPLAIN ANALYZE SELECT MAX(easter) FROM (SELECT easter_nested_subqueries(year) AS easter FROM generate_series(1,100000) AS year) AS x;
                                                                 QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=27250.00..27250.01 rows=1 width=4) (actual time=12358.992..12358.993 rows=1 loops=1)
   ->  Function Scan on generate_series year  (cost=0.00..26000.00 rows=100000 width=4) (actual time=16.772..12329.873 rows=100000 loops=1)
 Planning Time: 0.198 ms
 Execution Time: 12363.131 ms
(4 rows)

joel=# EXPLAIN ANALYZE SELECT MAX(easter) FROM (SELECT easter_lateral(year) AS easter FROM generate_series(1,100000) AS year) AS x;
                                                                 QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=27250.00..27250.01 rows=1 width=4) (actual time=12232.371..12232.371 rows=1 loops=1)
   ->  Function Scan on generate_series year  (cost=0.00..26000.00 rows=100000 width=4) (actual time=17.516..12203.773 rows=100000 loops=1)
 Planning Time: 0.268 ms
 Execution Time: 12239.180 ms
(4 rows)

*/