Avoid excessive inlining?

Поиск
Список
Период
Сортировка
От Joel Jacobson
Тема Avoid excessive inlining?
Дата
Msg-id e70cde40-a7ef-4894-9f1d-453ba13a0ced@www.fastmail.com
обсуждение исходный текст
Ответы Re: Avoid excessive inlining?
Список pgsql-general
Дерево обсуждения
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

В списке pgsql-general по дате отправления:

Предыдущее
От: "Lu, Dan"
Дата:
Сообщение: RE: Upgrade check failed from 11.5 to 12.1
Следующее
От: Lars Vonk
Дата:
Сообщение: Posts not appearing in this mailinglist