Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / PostgreSQL Новый топик    Ответить
 Кэширование результатов функции  [new]
Swa111
Member

Откуда:
Сообщений: 126
Здравствуйте!

У функции (пусть будет fnc) на входе есть 2 параметра.

Результат функции используется в запросе в нескольких местах. Запрос следующего вида

select 
  tblResult.id,
  case when Какое-то условие then tblResult.sPeriod2 else dCalcPeriod end date1,
  case when Какое-то условие then tblResult.sPeriod3 else dCalcPeriod + interval '1' day end date2,
  ...
from 
  tblResult,
  fnc(idUser, dPeriod1) dCalcPeriod --Есть строки когда результат может и не понадобится, 
--но чаще всего будет нужен сразу для обоих столбцов date1 и date2
where 
  tblResult.dPeriod between trunc(:CurrentPeriod,'YYYY') and :CurrentPeriod


уникальных пар idUser, dPeriodFrom от общего числа строк обычно менее 1%. Суть вопроса в следующем: как можно организовать кэширование результатов функции? Что бы она вызвалась не 2000 раз, а только 20.

сейчас сделано следующим образом сделана обертка над функций

  declare 
    dvRes date;
  begin
     begin
       select dDate2 into strict dvRes from fnc_cache where idUser = idpUser and dDate1 = dpDate;
       return dvRes;
     exception
        when no_data_found then perform null;
        when others then 
          create temp table fnc_cache (idUser number(15), dDate1 date, dDate2 date) on commit drop;
          create index ix_fnc_cache on fnc_cache(idUser, dDate1);
     end;

     dvRes := fnc(idpUser, dpDate);
     insert into fnc_cache values(idpUser, dpDate, dvRes );
     return dvRes;
  end;


В целом выигрыш по скорости есть, но уж слишком большие накладные расходы на создание временной таблицы и как я понял на открытие новой транзакции во внутреннем Begin - End.

Каким еще образом можно увеличить быстродействие? по таймингами сканирование таблицы tblResult занимает примерно 6 сек, возвращает 1300 строк с 12 уникальными парами idUser, dPeriod1. 1300 вызовов функции fnc - 4-6 сек. 1300 вызовов функции fnc_cache - 2-3 сек.
26 янв 20, 21:31    [22066826]     Ответить | Цитировать Сообщить модератору
 Re: Кэширование результатов функции  [new]
Troglodit
Member

Откуда:
Сообщений: 499
Swa111,
Если вы знаете как сделать сразу результат всех уникальных строк для fnc_cache, то можно
сделать mat. view для запроса, который этот результат возвращает и обновлять view по крону через интервал или еще как-нибудь.
вот вам и кэш, плюс к матвью еще индексы повесить можно если значений много.
26 янв 20, 23:30    [22066870]     Ответить | Цитировать Сообщить модератору
 Re: Кэширование результатов функции  [new]
Melkij
Member

Откуда: Санкт-Петербург
Сообщений: 976
Для начала проверить что функция объявлена как stable. Если вы хотите её кэшировать - вероятно по логике она stable, а не volatile. Проверьте что вы сообщили об этом планировщику.
Затем посмотреть на функцию. Возможно переписать на language sql (с учётом прочих ограничений) для включения function inline
27 янв 20, 00:43    [22066895]     Ответить | Цитировать Сообщить модератору
 Re: Кэширование результатов функции  [new]
Swa111
Member

Откуда:
Сообщений: 126
Troglodit,

В принципе вариантов конечное множество, но не хотелось бы делать лишних расчетов которые и не пригодятся в текущем периоде.

Melkij,

Stable не помогло. Либо нужно как то по другому запрос записать. в таком виде она все равно вызывается для каждой строки.
27 янв 20, 06:56    [22066919]     Ответить | Цитировать Сообщить модератору
 Re: Кэширование результатов функции  [new]
Maxim Boguk
Member

Откуда: Melbourne, Австралия
Сообщений: 4071
Swa111
Troglodit,

В принципе вариантов конечное множество, но не хотелось бы делать лишних расчетов которые и не пригодятся в текущем периоде.

Melkij,

Stable не помогло. Либо нужно как то по другому запрос записать. в таком виде она все равно вызывается для каждой строки.


Вот как то так эта задача решается без лишнего мозголомства:

WITH _res AS (
    SELECT * FROM tblResult
    where 
    tblResult.dPeriod between trunc(:CurrentPeriod,'YYYY') and :CurrentPeriod  
),
_distinct_res AS (
    select distinct idUser, dPeriod1 FROM _res
),
_fnc_res AS (
    select fnc(idUser, dPeriod1), * FROM _distinct_res
)

select 
  tblResult.id,
  case when Какое-то условие then tblResult.sPeriod2 else dCalcPeriod end date1,
  case when Какое-то условие then tblResult.sPeriod3 else dCalcPeriod + interval '1' day end date2,
  ...
from _res AS tblResult
join _fnc_res USING (idUser, dPeriod1);


Как раз то что вы хотите получится.
27 янв 20, 08:18    [22066943]     Ответить | Цитировать Сообщить модератору
 Re: Кэширование результатов функции  [new]
Troglodit
Member

Откуда:
Сообщений: 499
Maxim Boguk,
если я правильно понял, то автору нужны посчитанные результаты не на один запрос.
В вашем примере, при повторном запуске функция опять пересчитает данные, если я не ошибаюсь.
Автору нужен кэш.
27 янв 20, 12:00    [22067054]     Ответить | Цитировать Сообщить модератору
 Re: Кэширование результатов функции  [new]
Swa111
Member

Откуда:
Сообщений: 126
Maxim Boguk,

благодарю, такой вариант тоже использовал, но по каким то причинам время выполнения запроса стало даже больше чем с вызовом функций для каждой строки. Хотя количество вызовов самой функции сократилось.

Вчера пока описывал задачу, понял где функция кеша тормозила. Основная проблема была во внутренней транзакции. Вынес создание таблицы с расчитанными значениями в отдельную функцию. В итоге в таком варианте ручного кэша заработало как хотелось.

declare 
    dvRes date;
    idvuser number;
  begin
       select dDate2, iduser into dvRes, idvuser from fnc_cache where idUser = idpUser and dDate1 = dpDate;
       
  if idvuser is null then --результат может быть пустым
     dvRes := fnc(idpUser, dpDate);
     insert into fnc_cache values(idpUser, dpDate, dvRes );
 end if;

     return dvRes;
  end;
27 янв 20, 12:20    [22067073]     Ответить | Цитировать Сообщить модератору
 Re: Кэширование результатов функции  [new]
qwwq
Member

Откуда:
Сообщений: 2889
Troglodit
Maxim Boguk,
если я правильно понял, то автору нужны посчитанные результаты не на один запрос.
В вашем примере, при повторном запуске функция опять пересчитает данные, если я не ошибаюсь.
Автору нужен кэш.

аффтару не нужен кеш.

аффтару может быть надо принудить запрос к хеш-джойну от (?дистинктной) таблицы(материализованного CTE) рез-тов, вместо предположительного нестед-лупа с многократным пересчетом предположительно дорогой ф-ии.

до тех пор, пока вместо планов запроса он размахивает домыслами -- точнее сказать нельзя.
27 янв 20, 12:32    [22067087]     Ответить | Цитировать Сообщить модератору
 Re: Кэширование результатов функции  [new]
Troglodit
Member

Откуда:
Сообщений: 499
qwwq

аффтару не нужен кеш.

аффтару может быть надо принудить запрос к хеш-джойну от (?дистинктной) таблицы(материализованного CTE) рез-тов, вместо предположительного нестед-лупа с многократным пересчетом предположительно дорогой ф-ии.

до тех пор, пока вместо планов запроса он размахивает домыслами -- точнее сказать нельзя.


Я исходил из
автор
как можно организовать кэширование результатов функции?


Сообщение было отредактировано: 27 янв 20, 14:02
27 янв 20, 14:02    [22067166]     Ответить | Цитировать Сообщить модератору
 Re: Кэширование результатов функции  [new]
Maxim Boguk
Member

Откуда: Melbourne, Австралия
Сообщений: 4071
Swa111
Maxim Boguk,

благодарю, такой вариант тоже использовал, но по каким то причинам время выполнения запроса стало даже больше чем с вызовом функций для каждой строки. Хотя количество вызовов самой функции сократилось.



Тогда надо на explain analyze смотреть. Может вы чего то недосмотрели.
27 янв 20, 14:23    [22067185]     Ответить | Цитировать Сообщить модератору
 Re: Кэширование результатов функции  [new]
Swa111
Member

Откуда:
Сообщений: 126
Maxim Boguk,

Еще раз благодарю за внимание, сегодня узнал как можно посмотреть план выполнения запроса, который находится внутри функции. Нашел место из за которого медленно работал и запрос переписал как Вы предложили + недостающие индексы. Пересмотрели логику функции и оказалось что ее можно привести к одному запросу используя преимущества plpgsql (раньше требовалась совместимость с ораклом).
27 янв 20, 19:39    [22067409]     Ответить | Цитировать Сообщить модератору
Все форумы / PostgreSQL Ответить