• SQL: задача о рабочем времени
    0
    Проверил скрипт на большом объеме данных и понял, что не правильно использую 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;

  • SQL: задача о рабочем времени
    0
    минуты и секунды
    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 

  • SQL: задача о рабочем времени
    0
    Учел заявки, которые сами закрылись в нерабочее время
     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


  • SQL: задача о рабочем времени
    0
    Скрытый текст
    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
    

  • Трехмерный движок внутри запроса SQL
    +4

    Тут не создаются таблицы, все генерируется на лету, поэтому нечего обновлять.

  • Трехмерный движок внутри запроса SQL
    +4

    Скорее всего Постгрес неверно строит план запроса. Подсказав ему не использовать nested loop(set enable_nestloop to off), мне удалось снизить время выполнения с 44 сек до 3.5 сек.

  • Аудит изменения данных PostgreSQL
    0
    Нет, старая таблица с логами переименовывается и создается новая.
  • Аудит изменения данных PostgreSQL
    0
    Такой вариант тоже пробовали, проблема в том, что некоторые функции работают напрямую с системными таблицами(pg_class/pg_attribute/pg_constraint) и на логгируемых таблицах они ломались.
    К тому в варианте с представлениями удаленные/обновленные записи будут влиять на производительность при SELECT`ах
  • Аудит изменения данных PostgreSQL
    0
    Приложение обращается к таблицам через обертки из функций, сами функции обращаются напрямую в таблицы.
  • Аудит изменения данных PostgreSQL
    0
    При изменении схемы данных, возникнет исключение и таблица с логами будет пересоздана.
    Насчет использования JSON не задумывались, можно попробовать
  • Аудит изменения данных PostgreSQL
    0
    Еще раз нипишу минусы доп полей
    1.Удаленные записи продолжают лежать в таблице(нужно вносить изменения в существующие функции, дополнительные расходы при выборках)
    2.Если одна запись обновляется неоднократно, теряется история изменений
    3.Могут возникнуть ошибки при добавлении новых ограничений на таблицу, т.к. логгировать предполагалось не все таблицы, то логгируемая таблица вполне может ссылаться на нелоггируемую
  • Аудит изменения данных PostgreSQL
    +1
    Вставки и изменения у нас обернуты в функции с достаточно сложной логикой, на их фоне влияние двойной записи на диск стремится к нулю.
    Сильная потеря производительности может получиться только на таблицах с частыми обновлениями, или на таблицах с большими записями(т.к. там используется механизм TOAST и при обновлении такой строки постгрес сильно оптимизирует эту операцию)
  • Аудит изменения данных PostgreSQL
    0
    В нашем случае проседание производительности практически нулевое, т.к. логгируются таблицы, которые не часто и не сильно изменяются.
    Динамический SQL используется не при каждой операции, а только при изменении данных.
    К тому же при вставке данных в таблицу логов не производится никаких проверок(внешние ключи)
    Проседание производительности может получиться при массовой вставке, но мы отключаем логгирование в таких случаях