Pull to refresh
5
0

Пользователь

Send message
Проверил скрипт на большом объеме данных и понял, что не правильно использую 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`ах
Приложение обращается к таблицам через обертки из функций, сами функции обращаются напрямую в таблицы.
При изменении схемы данных, возникнет исключение и таблица с логами будет пересоздана.
Насчет использования JSON не задумывались, можно попробовать
Еще раз нипишу минусы доп полей
1.Удаленные записи продолжают лежать в таблице(нужно вносить изменения в существующие функции, дополнительные расходы при выборках)
2.Если одна запись обновляется неоднократно, теряется история изменений
3.Могут возникнуть ошибки при добавлении новых ограничений на таблицу, т.к. логгировать предполагалось не все таблицы, то логгируемая таблица вполне может ссылаться на нелоггируемую
Вставки и изменения у нас обернуты в функции с достаточно сложной логикой, на их фоне влияние двойной записи на диск стремится к нулю.
Сильная потеря производительности может получиться только на таблицах с частыми обновлениями, или на таблицах с большими записями(т.к. там используется механизм TOAST и при обновлении такой строки постгрес сильно оптимизирует эту операцию)
В нашем случае проседание производительности практически нулевое, т.к. логгируются таблицы, которые не часто и не сильно изменяются.
Динамический SQL используется не при каждой операции, а только при изменении данных.
К тому же при вставке данных в таблицу логов не производится никаких проверок(внешние ключи)
Проседание производительности может получиться при массовой вставке, но мы отключаем логгирование в таких случаях

Information

Rating
Does not participate
Location
Москва, Москва и Московская обл., Россия
Registered
Activity