Is there a way to avoid excessive inlining when writing pure SQL functions,
without having to use PL/pgSQL?
The JOIN LATERAL and Nested Subqueries versions run much slower than the
PL/pgSQL version:
Execution Times:
JOIN LATERAL: 12198.010 ms
Nested Subqueries: 12250.077 ms
PL/pgSQL: 312.493 ms
The three functions below are equivalent, they all compute the Eastern date for
a given year.
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
$$;
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_plpgsql(year integer)
RETURNS date
LANGUAGE plpgsql
AS $$
-- Based on:
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;
$$;
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=12195.974..12195.974 rows=1 loops=1)
-> Function Scan on generate_series year (cost=0.00..26000.00 rows=100000
width=4) (actual time=15.840..12167.758 rows=100000 loops=1)
Planning Time: 0.262 ms
Execution Time: 12198.010 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=12248.316..12248.317 rows=1 loops=1)
-> Function Scan on generate_series year (cost=0.00..26000.00 rows=100000
width=4) (actual time=17.707..12219.500 rows=100000 loops=1)
Planning Time: 0.277 ms
Execution Time: 12250.077 ms
(4 rows)
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=311.107..311.108 rows=1 loops=1)
-> Function Scan on generate_series year (cost=0.00..26000.00 rows=100000
width=4) (actual time=12.369..296.221 rows=100000 loops=1)
Planning Time: 0.058 ms
Execution Time: 312.493 ms
(4 rows)
If we look at the plan for the lateral and subqueries versions, we can see how
the inlining expands to huge expressions.
Could this be the reason they run so much slower than the PL/pgSQL version?
PREPARE q_lateral AS SELECT make_date($1, easter_month, easter_day)
FROM (VALUES ($1 % 19, $1 / 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 (($1 + $1/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;
SET plan_cache_mode = 'force_generic_plan';
EXPLAIN (ANALYZE, VERBOSE) EXECUTE q_lateral(2020);
Result (cost=0.00..1.14 rows=1 width=4) (actual time=0.038..0.038 rows=1
loops=1)
Output: make_date($1, (3 + (((((((((($1 / 100) - (($1 / 100) / 4)) - (((8 *
($1 / 100)) + 13) / 25)) + (19 * ($1 % 19))) + 15) % 30) - (((((((($1 / 100) -
(($1 / 100) / 4)) - (((8 * ($1 / 100)) + 13) / 25)) + (19 * ($1 % 19))) + 15) %
30) / 28) * (1 - ((((((((($1 / 100) - (($1 / 100) / 4)) - (((8 * ($1 / 100)) +
13) / 25)) + (19 * ($1 % 19))) + 15) % 30) / 28) * (29 / ((((((($1 / 100) -
(($1 / 100) / 4)) - (((8 * ($1 / 100)) + 13) / 25)) + (19 * ($1 % 19))) + 15) %
30) + 1))) * ((21 - ($1 % 19)) / 11))))) - (((((($1 + ($1 / 4)) + ((((((($1 /
100) - (($1 / 100) / 4)) - (((8 * ($1 / 100)) + 13) / 25)) + (19 * ($1 % 19)))
+ 15) % 30) - (((((((($1 / 100) - (($1 / 100) / 4)) - (((8 * ($1 / 100)) + 13)
/ 25)) + (19 * ($1 % 19))) + 15) % 30) / 28) * (1 - ((((((((($1 / 100) - (($1 /
100) / 4)) - (((8 * ($1 / 100)) + 13) / 25)) + (19 * ($1 % 19))) + 15) % 30) /
28) * (29 / ((((((($1 / 100) - (($1 / 100) / 4)) - (((8 * ($1 / 100)) + 13) /
25)) + (19 * ($1 % 19))) + 15) % 30) + 1))) * ((21 - ($1 % 19)) / 11)))))) + 2)
- ($1 / 100)) + (($1 / 100) / 4)) % 7)) + 26) / 30)), (1 + ((((((((((($1 / 100)
- (($1 / 100) / 4)) - (((8 * ($1 / 100)) + 13) / 25)) + (19 * ($1 % 19))) + 15)
% 30) - (((((((($1 / 100) - (($1 / 100) / 4)) - (((8 * ($1 / 100)) + 13) / 25))
+ (19 * ($1 % 19))) + 15) % 30) / 28) * (1 - ((((((((($1 / 100) - (($1 / 100) /
4)) - (((8 * ($1 / 100)) + 13) / 25)) + (19 * ($1 % 19))) + 15) % 30) / 28) *
(29 / ((((((($1 / 100) - (($1 / 100) / 4)) - (((8 * ($1 / 100)) + 13) / 25)) +
(19 * ($1 % 19))) + 15) % 30) + 1))) * ((21 - ($1 % 19)) / 11))))) - (((((($1 +
($1 / 4)) + ((((((($1 / 100) - (($1 / 100) / 4)) - (((8 * ($1 / 100)) + 13) /
25)) + (19 * ($1 % 19))) + 15) % 30) - (((((((($1 / 100) - (($1 / 100) / 4)) -
(((8 * ($1 / 100)) + 13) / 25)) + (19 * ($1 % 19))) + 15) % 30) / 28) * (1 -
((((((((($1 / 100) - (($1 / 100) / 4)) - (((8 * ($1 / 100)) + 13) / 25)) + (19
* ($1 % 19))) + 15) % 30) / 28) * (29 / ((((((($1 / 100) - (($1 / 100) / 4)) -
(((8 * ($1 / 100)) + 13) / 25)) + (19 * ($1 % 19))) + 15) % 30) + 1))) * ((21 -
($1 % 19)) / 11)))))) + 2) - ($1 / 100)) + (($1 / 100) / 4)) % 7)) + 27) +
(((((((((($1 / 100) - (($1 / 100) / 4)) - (((8 * ($1 / 100)) + 13) / 25)) + (19
* ($1 % 19))) + 15) % 30) - (((((((($1 / 100) - (($1 / 100) / 4)) - (((8 * ($1
/ 100)) + 13) / 25)) + (19 * ($1 % 19))) + 15) % 30) / 28) * (1 - ((((((((($1 /
100) - (($1 / 100) / 4)) - (((8 * ($1 / 100)) + 13) / 25)) + (19 * ($1 % 19)))
+ 15) % 30) / 28) * (29 / ((((((($1 / 100) - (($1 / 100) / 4)) - (((8 * ($1 /
100)) + 13) / 25)) + (19 * ($1 % 19))) + 15) % 30) + 1))) * ((21 - ($1 % 19)) /
11))))) - (((((($1 + ($1 / 4)) + ((((((($1 / 100) - (($1 / 100) / 4)) - (((8 *
($1 / 100)) + 13) / 25)) + (19 * ($1 % 19))) + 15) % 30) - (((((((($1 / 100) -
(($1 / 100) / 4)) - (((8 * ($1 / 100)) + 13) / 25)) + (19 * ($1 % 19))) + 15) %
30) / 28) * (1 - ((((((((($1 / 100) - (($1 / 100) / 4)) - (((8 * ($1 / 100)) +
13) / 25)) + (19 * ($1 % 19))) + 15) % 30) / 28) * (29 / ((((((($1 / 100) -
(($1 / 100) / 4)) - (((8 * ($1 / 100)) + 13) / 25)) + (19 * ($1 % 19))) + 15) %
30) + 1))) * ((21 - ($1 % 19)) / 11)))))) + 2) - ($1 / 100)) + (($1 / 100) /
4)) % 7)) + 6) / 40)) % 31)))
Planning Time: 4.144 ms
Execution Time: 0.220 ms
PREPARE q_subqueries AS SELECT make_date($1, 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 *,
($1 + $1/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
$1 % 19 AS g,
$1 / 100 AS c
) AS Q1
) AS Q2
) AS Q3
) AS Q4
) AS Q5
) AS Q6
;
SET plan_cache_mode = 'force_generic_plan';
EXPLAIN (ANALYZE, VERBOSE) EXECUTE q_subqueries(2020);
Result (cost=0.00..1.14 rows=1 width=4) (actual time=0.043..0.044 rows=1
loops=1)
Output: make_date($1, (3 + (((((((((($1 / 100) - (($1 / 100) / 4)) - (((8 *
($1 / 100)) + 13) / 25)) + (19 * ($1 % 19))) + 15) % 30) - (((((((($1 / 100) -
(($1 / 100) / 4)) - (((8 * ($1 / 100)) + 13) / 25)) + (19 * ($1 % 19))) + 15) %
30) / 28) * (1 - ((((((((($1 / 100) - (($1 / 100) / 4)) - (((8 * ($1 / 100)) +
13) / 25)) + (19 * ($1 % 19))) + 15) % 30) / 28) * (29 / ((((((($1 / 100) -
(($1 / 100) / 4)) - (((8 * ($1 / 100)) + 13) / 25)) + (19 * ($1 % 19))) + 15) %
30) + 1))) * ((21 - ($1 % 19)) / 11))))) - (((((($1 + ($1 / 4)) + ((((((($1 /
100) - (($1 / 100) / 4)) - (((8 * ($1 / 100)) + 13) / 25)) + (19 * ($1 % 19)))
+ 15) % 30) - (((((((($1 / 100) - (($1 / 100) / 4)) - (((8 * ($1 / 100)) + 13)
/ 25)) + (19 * ($1 % 19))) + 15) % 30) / 28) * (1 - ((((((((($1 / 100) - (($1 /
100) / 4)) - (((8 * ($1 / 100)) + 13) / 25)) + (19 * ($1 % 19))) + 15) % 30) /
28) * (29 / ((((((($1 / 100) - (($1 / 100) / 4)) - (((8 * ($1 / 100)) + 13) /
25)) + (19 * ($1 % 19))) + 15) % 30) + 1))) * ((21 - ($1 % 19)) / 11)))))) + 2)
- ($1 / 100)) + (($1 / 100) / 4)) % 7)) + 26) / 30)), (1 + ((((((((((($1 / 100)
- (($1 / 100) / 4)) - (((8 * ($1 / 100)) + 13) / 25)) + (19 * ($1 % 19))) + 15)
% 30) - (((((((($1 / 100) - (($1 / 100) / 4)) - (((8 * ($1 / 100)) + 13) / 25))
+ (19 * ($1 % 19))) + 15) % 30) / 28) * (1 - ((((((((($1 / 100) - (($1 / 100) /
4)) - (((8 * ($1 / 100)) + 13) / 25)) + (19 * ($1 % 19))) + 15) % 30) / 28) *
(29 / ((((((($1 / 100) - (($1 / 100) / 4)) - (((8 * ($1 / 100)) + 13) / 25)) +
(19 * ($1 % 19))) + 15) % 30) + 1))) * ((21 - ($1 % 19)) / 11))))) - (((((($1 +
($1 / 4)) + ((((((($1 / 100) - (($1 / 100) / 4)) - (((8 * ($1 / 100)) + 13) /
25)) + (19 * ($1 % 19))) + 15) % 30) - (((((((($1 / 100) - (($1 / 100) / 4)) -
(((8 * ($1 / 100)) + 13) / 25)) + (19 * ($1 % 19))) + 15) % 30) / 28) * (1 -
((((((((($1 / 100) - (($1 / 100) / 4)) - (((8 * ($1 / 100)) + 13) / 25)) + (19
* ($1 % 19))) + 15) % 30) / 28) * (29 / ((((((($1 / 100) - (($1 / 100) / 4)) -
(((8 * ($1 / 100)) + 13) / 25)) + (19 * ($1 % 19))) + 15) % 30) + 1))) * ((21 -
($1 % 19)) / 11)))))) + 2) - ($1 / 100)) + (($1 / 100) / 4)) % 7)) + 27) +
(((((((((($1 / 100) - (($1 / 100) / 4)) - (((8 * ($1 / 100)) + 13) / 25)) + (19
* ($1 % 19))) + 15) % 30) - (((((((($1 / 100) - (($1 / 100) / 4)) - (((8 * ($1
/ 100)) + 13) / 25)) + (19 * ($1 % 19))) + 15) % 30) / 28) * (1 - ((((((((($1 /
100) - (($1 / 100) / 4)) - (((8 * ($1 / 100)) + 13) / 25)) + (19 * ($1 % 19)))
+ 15) % 30) / 28) * (29 / ((((((($1 / 100) - (($1 / 100) / 4)) - (((8 * ($1 /
100)) + 13) / 25)) + (19 * ($1 % 19))) + 15) % 30) + 1))) * ((21 - ($1 % 19)) /
11))))) - (((((($1 + ($1 / 4)) + ((((((($1 / 100) - (($1 / 100) / 4)) - (((8 *
($1 / 100)) + 13) / 25)) + (19 * ($1 % 19))) + 15) % 30) - (((((((($1 / 100) -
(($1 / 100) / 4)) - (((8 * ($1 / 100)) + 13) / 25)) + (19 * ($1 % 19))) + 15) %
30) / 28) * (1 - ((((((((($1 / 100) - (($1 / 100) / 4)) - (((8 * ($1 / 100)) +
13) / 25)) + (19 * ($1 % 19))) + 15) % 30) / 28) * (29 / ((((((($1 / 100) -
(($1 / 100) / 4)) - (((8 * ($1 / 100)) + 13) / 25)) + (19 * ($1 % 19))) + 15) %
30) + 1))) * ((21 - ($1 % 19)) / 11)))))) + 2) - ($1 / 100)) + (($1 / 100) /
4)) % 7)) + 6) / 40)) % 31)))
Planning Time: 2.443 ms
Execution Time: 0.235 ms
Thanks Andreas Karlsson for teaching me the PREPARE + SET plan_cache_mode =
'force_generic_plan'; + EXPLAIN trick, very useful.
Best regards,
Joel
