Проверил скрипт на большом объеме данных и понял, что не правильно использую LATERAL
Так будет быстрее
with periods(id, start_time, stop_time) as (
values(1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp),
(2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp),
(3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
(4, '2018-12-29 20:00:00'::timestamp, '2019-01-3 16:00:00'::timestamp)
), holy(dd,iswrk) AS (values ('2019-01-01'::date,false),('2019-01-02',false),('2019-01-03',false),('2019-01-04',false),('2019-01-07',false),
('2019-01-08',false),('2019-03-08',false),('2019-05-01',false),('2019-05-02',false),('2019-05-03',false),('2019-05-09',false),('2019-05-10',false),
('2019-06-12',false),('2019-11-04',false),('2018-01-01',false),('2018-01-02',false),('2018-01-03',false),('2018-01-04',false),('2018-01-05',false),
('2018-01-08',false),('2018-02-23',false),('2018-03-08',false),('2018-03-09',false),('2018-04-28',true),('2018-04-30',false),('2018-05-01',false),
('2018-05-02',false),('2018-05-09',false),('2018-06-09',true),('2018-06-11',false),('2018-06-12',false),('2018-11-05',false),('2018-12-29',true),
('2018-12-31',false))
SELECT id,start_time,stop_time, (count(gs.d))*interval '9 hour'
-CASE
WHEN count(d)=0 OR start_time::date<min(d) THEN interval '0 hour'
when start_time - min(d)>=interval '19 hour' THEN interval '9 hour'
when start_time - min(d)<=interval '10 hour' THEN interval '0 hour'
ELSE start_time-min(d)-interval '10 hour'
END
-CASE WHEN count(d)=0 OR stop_time::date>max(d) THEN interval '0 hour'
WHEN stop_time-max(d)>=interval '19 hour' THEN interval '0 hour'
WHEN stop_time-max(d)<=interval '10 hour' THEN interval '9 hour'
ELSE interval '19 hour'- (stop_time-max(d))
END
FROM periods
LEFT JOIN LATERAL (SELECT * FROM generate_series(start_time::date,stop_time::date,'1 day') gs(d) left join holy ON gs.d=holy.dd )gs(d,dd,iswrk) ON
CASE WHEN extract(isodow from gs.d) IN (6,7) THEN coalesce(iswrk,false) ELSE coalesce(iswrk,true) END
GROUP BY id,start_time,stop_time
ORDER BY id;
SELECT id,start_time,stop_time, (count(d))*interval '9 hour'
-CASE
WHEN count(d)=0 OR start_time::date<min(d) THEN interval '0 hour'
when start_time - min(d)>=interval '19 hour' THEN interval '9 hour'
when start_time-min(d)<=interval '10 hour' THEN interval '0 hour'
ELSE start_time -min(d) - interval '10 hour'
END
-CASE WHEN count(d)=0 OR stop_time::date>max(d) THEN interval '0 hour'
WHEN stop_time - max(d)>=interval '19 hour' THEN interval '0 hour'
WHEN stop_time - max(d)<=interval '10 hour' THEN interval '9 hour'
ELSE interval '19 hour' - ( stop_time - max(d))
END
FROM periods LEFT JOIN days
ON
d>=start_time::date
AND d<=stop_time::date
AND iswrk
GROUP BY id,start_time,stop_time
ORDER BY id
Учел заявки, которые сами закрылись в нерабочее время
SELECT id,start_time,stop_time, (count(d))*9
-CASE
WHEN count(d)=0 OR start_time::date<min(d) THEN 0
when extract(hour from start_time)>=19 THEN 9
when extract(hour from start_time)<=10 THEN 0
ELSE extract(hour from start_time)-10
END
-CASE WHEN count(d)=0 OR stop_time::date>max(d) THEN 0
WHEN extract(hour from stop_time)>=19 THEN 0
WHEN extract(hour from stop_time)<=10 THEN 9
ELSE 19-extract(hour from stop_time)
END
FROM periods LEFT JOIN days
ON
d>=start_time::date
AND d<=stop_time::date
AND iswrk
GROUP BY id,start_time,stop_time
ORDER BY id
with periods (id,start_time, stop_time) as(
VALUES(1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp),
(2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp),
(3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:00:00'::timestamp),
(4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp)
),
minimax AS (select min(start_time),max(stop_time) FROM periods),
days AS(SELECT gs.d,CASE WHEN extract(isodow from gs.d) IN (6,7) THEN coalesce(holy.iswrk,false) ELSE coalesce(holy.iswrk,true) END iswrk
FROM minimax
JOIN LATERAL generate_series(min::date,max::date,'24:00') gs(d) ON 1=1
left join (values ('2019-01-01'::date,false),('2019-01-02',false),('2019-01-03',false),('2019-01-04',false),('2019-01-07',false),('2019-01-08',false),('2019-03-08',false),
('2019-05-01',false),('2019-05-02',false),('2019-05-03',false),('2019-05-09',false),('2019-05-10',false),('2019-06-12',false),('2019-11-04',false),
('2018-01-01',false),('2018-01-02',false),('2018-01-03',false),('2018-01-04',false),('2018-01-05',false),('2018-01-08',false),('2018-02-23',false),
('2018-03-08',false),('2018-03-09',false),('2018-04-28',true),('2018-04-30',false),('2018-05-01',false),('2018-05-02',false),('2018-05-09',false),
('2018-06-09',true),('2018-06-11',false),('2018-06-12',false),('2018-11-05',false),('2018-12-29',true),('2018-12-31',false)) as holy(d,iswrk)
on gs.d=holy.d)
SELECT id,start_time,stop_time, (count(*)-2)*9+
CASE when extract(hour from start_time)>=19 THEN 0
when extract(hour from start_time)<=10 THEN 9
ELSE 19-extract(hour from start_time)
END+
CASE WHEN extract(hour from stop_time)>=19 THEN 9
WHEN extract(hour from stop_time)<=10 THEN 0
ELSE extract(hour from stop_time)-10
END
FROM periods JOIN days
ON
d>=start_time::date
AND d<=stop_time::date
AND iswrk
GROUP BY id,start_time,stop_time
ORDER BY id
Скорее всего Постгрес неверно строит план запроса. Подсказав ему не использовать nested loop(set enable_nestloop to off), мне удалось снизить время выполнения с 44 сек до 3.5 сек.
Такой вариант тоже пробовали, проблема в том, что некоторые функции работают напрямую с системными таблицами(pg_class/pg_attribute/pg_constraint) и на логгируемых таблицах они ломались.
К тому в варианте с представлениями удаленные/обновленные записи будут влиять на производительность при SELECT`ах
Еще раз нипишу минусы доп полей
1.Удаленные записи продолжают лежать в таблице(нужно вносить изменения в существующие функции, дополнительные расходы при выборках)
2.Если одна запись обновляется неоднократно, теряется история изменений
3.Могут возникнуть ошибки при добавлении новых ограничений на таблицу, т.к. логгировать предполагалось не все таблицы, то логгируемая таблица вполне может ссылаться на нелоггируемую
Вставки и изменения у нас обернуты в функции с достаточно сложной логикой, на их фоне влияние двойной записи на диск стремится к нулю.
Сильная потеря производительности может получиться только на таблицах с частыми обновлениями, или на таблицах с большими записями(т.к. там используется механизм TOAST и при обновлении такой строки постгрес сильно оптимизирует эту операцию)
В нашем случае проседание производительности практически нулевое, т.к. логгируются таблицы, которые не часто и не сильно изменяются.
Динамический SQL используется не при каждой операции, а только при изменении данных.
К тому же при вставке данных в таблицу логов не производится никаких проверок(внешние ключи)
Проседание производительности может получиться при массовой вставке, но мы отключаем логгирование в таких случаях
Тут не создаются таблицы, все генерируется на лету, поэтому нечего обновлять.
Скорее всего Постгрес неверно строит план запроса. Подсказав ему не использовать nested loop(set enable_nestloop to off), мне удалось снизить время выполнения с 44 сек до 3.5 сек.
К тому в варианте с представлениями удаленные/обновленные записи будут влиять на производительность при SELECT`ах
Насчет использования JSON не задумывались, можно попробовать
1.Удаленные записи продолжают лежать в таблице(нужно вносить изменения в существующие функции, дополнительные расходы при выборках)
2.Если одна запись обновляется неоднократно, теряется история изменений
3.Могут возникнуть ошибки при добавлении новых ограничений на таблицу, т.к. логгировать предполагалось не все таблицы, то логгируемая таблица вполне может ссылаться на нелоггируемую
Сильная потеря производительности может получиться только на таблицах с частыми обновлениями, или на таблицах с большими записями(т.к. там используется механизм TOAST и при обновлении такой строки постгрес сильно оптимизирует эту операцию)
Динамический SQL используется не при каждой операции, а только при изменении данных.
К тому же при вставке данных в таблицу логов не производится никаких проверок(внешние ключи)
Проседание производительности может получиться при массовой вставке, но мы отключаем логгирование в таких случаях