Перейти к основному содержанию
`Ctrl`+`K`

# SQL запросы

## Просматривайте план выполнения запроса, чтобы узнать проблемные места

План запроса – последовательность шагов в виде дерева для получения результата SQL-запроса. Каждый шаг – операция: извлечение или обработка данных, сканирование индекса, пересечение или объединение множеств, построение битовых карт или другая.

Чтобы построить план, оптимизатор использует:

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

- Алгоритмы - оптимизации выбирает план с наименьшей оценочной стоимостью. Число возможных планов (называемых пространством планов) для запроса, содержащим несколько операций, является огромным - слишком большим, чтобы алгоритм рассмотрел каждый возможный план, поэтому используются эвристики для сокращения числа планов, оцениваемых оптимизатором.

- Эвристику.

Получить план запрос можно в dBeaver с помощью операции на панели слева `Получить план выполнения`, а так же с помощью команды `explain` перед запросом.
Алгоритм.
Пример:

    explain analyze
    SELECT w.*
    FROM stk_internalwarrant w
    WHERE w.idstateinmc = 300
    AND   EXISTS (SELECT 1 FROM stk_internalwarrantdet d WHERE d.idinternalwarrant = w.id AND d.idcons IS null)

    --Вывод
    Hash Semi Join  (cost=13.79..52.69 rows=14 width=3485)
      Hash Cond: (w.id = d.idinternalwarrant)
      ->  Seq Scan on stk_internalwarrant w  (cost=0.00..38.44 rows=32 width=3485)
            Filter: (idstateinmc = '300'::numeric)
      ->  Hash  (cost=12.25..12.25 rows=123 width=8)
            ->  Index Scan using idx_stk_internalwarrantdet_idcons on stk_internalwarrantdet d  (cost=0.15..12.25 rows=123 width=8)
                  Index Cond: (idcons IS NULL)

- Использование EXPLAIN.

- Курс «PostgreSQL для начинающих»: #4 — Анализ запросов.

## При написании сложных запросов дробите его на части и используйте подзапросы

При построении сложных запросов, дробите его на части - основной запрос с условиями и с минимальным кол-вом соединений можно вынести в `with` или в нижнеуровневый запрос.

Если часть данных надо брать из доп. таблиц и при этом понадобится группировка, то лучше сделать это подзапросом, либо с помощью `left join lateral`. Тщательно анализируйте условия фильтрации, большинство условий старайтесь выносить на нижний уровень, чтобы как можно меньше было данных для последующих соединений. Если условия фильтрации идут по нескольким полям, но индексы есть только по отдельным полям, то создавайте составной индекс.

Минимизируйте кол-во соединяемых таблиц, можно использовать денормализованные таблицы или столбцы. Пример:

    --использование with
    WITH t as (
           SELECT t.*
           from   Prc_PriceListRegFull t
           where  t.idPRiceList = 1
           and    t.sFindKey in (select unnest(array['a','b','c']))
        )
        SELECT t.*
        from   t
        where not exists(select 1
                         from t t1
                         where t1.idPRiceList = t.idPRiceList
                         and   t1.sFindKey = t.sFindKey
                         and   t1.dDoc > t.dDoc
                         and   t1.dFrom <= t.dFrom
                         and   t1.dTo >= t.dTo
                         )

    --подзапрос с использованием jsonb_build_object, чтобы использовать сразу несколько столбцов
    SELECT c.jStkRem->>'sMasterCode' as sMasterRem
          ,c.idOrder
          ,cast(c.jStkRem->>'nQtyBase' as numeric)  as nQtyBase
          ,c.nQtyBase      as nQtyStockCard
          ,c.sNumSrcDoc
          ,c.dSrcDoc
          ,c.sHLSrcDoc
    from (select c.*
                 ,(select jsonb_build_object(
                              'nQtyBase', sum(rc.nQtyBaseMsr)
                              ,'sStockCode' ,string_agg(distinct ss.sCode, ', ')
                              ,'sStockCaption', string_agg(distinct ss.sCaption, ', ')
                              ,'sMasterCode', string_agg(distinct mm.sMnemoCode_dz, ', ')
                            )
                     from   (select sum(rc.nQtyBaseMsr) nQtyBaseMsr
                                    ,rc.idStock
                                    ,rc.gidMaster
    			             from   Stk_RegConsMat rc
    			             where  rc.idGds = c.idGds
    			             and    rc.gidsuite = c.gid
    			             and    rc.idCons =  getgidid(c.gidCons)
    			             and    rc.stype = 'mov'
    			             group by rc.idStock
                                    ,rc.gidMaster
                             having sum(rc.nQtyBaseMsr) > 0
    			            ) rc
                     left join Stk_Stock ss on ss.id = rc.idStock
                     left join Stk_Master mm on mm.gidref = rc.gidMaster
                    ) jStkRem
          from   Gds_StockCard c
          ) as c

    --использование join lateral
    select t.id
            ,t.idNeedObj
            ,t8.nQtySumBySpec
            ,t8.nSumNoTaxBySpec
    from Prs_PurchTrans t
    left join lateral(select sum(t7.nQty) as nQtySumBySpec
                       ,sum(t7.nSumNoTax) as nSumNoTaxBySpec
                from Prs_PurchTrans t7 where t7.gidSrcObj = t.gidSrcObj
              ) as t8 on true

- Табличные выражения.

- Запросы WITH.

## Используйте индексы, чтобы ускорить получение запрашиваемых данных

Индекс в таблице базы данных — это дополнительная структура данных, которая создаётся наряду с данными в таблице.

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

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

Вам, возможно, придётся только периодически запускать команду `ANALYZE` для обновления статистических данных, на основе которых планировщик запросов принимает решения. Создание индекса для большой таблицы может занимать много времени. По умолчанию PostgreSQL позволяет параллельно с созданием индекса выполнять чтение (операторы SELECT) таблицы, но операции записи (INSERT, UPDATE и DELETE) блокируются до окончания построения индекса.

Составной индекс - это индексы создаваемый по нескольким столбцам таблицы.

Частичный индекс — это индекс, который строится по подмножеству строк таблицы, определяемому условным выражением (оно называется предикатом частичного индекса). Такой индекс содержит записи только для строк, удовлетворяющих предикату. Частичные индексы довольно специфичны, но в ряде ситуаций они могут быть очень полезны.

Примеры:

    --частиный индекс
    CREATE INDEX IF NOT EXISTS idx_stk_cons_bneedcheckin_IsNullIdRes ON stk_cons (bneedcheckin) WHERE idcontrolresult IS NULL;
    --составной индекс
    CREATE INDEX IF NOT EXISTS idx_stk_regmovmat_gdsmastertype ON stk_regmovmat (idGds, gidMaster, stype)
    --GIN индекс для jSonb поля
    create index if not exists idx_Cnt_ContractSumDistr_jBbbObjAttrs on Cnt_ContractSumDistr using gin (jBbbObjAttrs)

- Индексы.

## Используйте параметры при передаче значений в запрос

При написании запросов в ASelect или ATSQL для передачи параметров не используйте прямую «врезку» значений в тело запроса, нужно передавать через аргументы. При написании запроса в `onRefresh` или `selectStatement` для Avi, к параметрам выборки или фильтра можно обращаться через двоеточие.
Пример:

    //До
    for (rv <- new ASelect{
      val id: NLongColumn = asNLong("id")
      SQL(s"""select o2.id
            from mes_WorkOrderOperation o1
            join Mct_WorkStructureOperation o2 on o1.idWorkStructureOperation = o2.id
            where o1.idWorkOrder in (${idaWork.mkString(",")})
              and coalesce(o1.bAccept,0) = 0
              and o1.dFactEndDate is null
              and not exists (select 1 from mct_WorkStructureOperation c where c.idParent = o2.id)""")
    }) {
      val rop = Mct_WorkStructureOperationApi().load(rv.id())
      Mct_WorkStructureOperationApi().setidResource(rop, idResource)
    }
    //После
    for (rv <- new ASelect{
      val id: NLongColumn = asNLong("id")
      SQL(s"""select o2.id
            from mes_WorkOrderOperation o1
            join Mct_WorkStructureOperation o2 on o1.idWorkStructureOperation = o2.id
            where o1.idWorkOrder in (select unnest({idaWork}))
              and coalesce(o1.bAccept,0) = 0
              and o1.dFactEndDate is null
              and not exists (select 1 from mct_WorkStructureOperation c where c.idParent = o2.id)""")
      on(Symbol("idaWork") -> LongPgArray(idaWork))
    }) {
      val rop = Mct_WorkStructureOperationApi().load(rv.id())
      Mct_WorkStructureOperationApi().setidResource(rop, idResource)
    }
    //или так
    for (rv <- new ASelect{
      val id: NLongColumn = asNLong("id")
      SQL"""select o2.id
            from mes_WorkOrderOperation o1
            join Mct_WorkStructureOperation o2 on o1.idWorkStructureOperation = o2.id
            where o1.idWorkOrder in (select unnest(${LongPgArray(idaWork)}))
              and coalesce(o1.bAccept,0) = 0
              and o1.dFactEndDate is null
              and not exists (select 1 from mct_WorkStructureOperation c where c.idParent = o2.id)"""
    }) {
      val rop = Mct_WorkStructureOperationApi().load(rv.id())
      Mct_WorkStructureOperationApi().setidResource(rop, idResource)
    }

## При группировке минимизируйте кол-во группируемых полей

Чем больше группируемые полей, тем дольше будет выполняться запрос.
Пример:

    // До
    select t.idgds
          ,g.smnemocode_dz as idGdsMC
          ,g.sheadline_dz as idGdsHL
          ,greatest(sum(case when ft.scode in ('20', '25') then t.nQtyRelease end), sum(case when ft.scode in ('30') then t.nQtyRelease end)) as nQtyRelease
    from   Mes_WorkOrderOperation o
    join   Mes_WorkOrderOperationGds t on t.idworkorderoperation = o.id
    join   Mct_MaterialFulfillmentType ft on ft.id = t.idmaterialfulfillmenttype
    join   bs_goods  g on g.id = t.idgds
    where  --@resultCondition
    and    ft.scode in ('20', '25', '30')
    group by t.idgds
          ,g.smnemocode_dz
          ,g.sheadline_dz
    //После
    select t.idgds
          ,max(g.smnemocode_dz) as idGdsMC
          ,max(g.sheadline_dz) as idGdsHL
          ,greatest(sum(case when ft.scode in ('20', '25') then t.nQtyRelease end), sum(case when ft.scode in ('30') then t.nQtyRelease end)) as nQtyRelease
    from   Mes_WorkOrderOperation o
    join   Mes_WorkOrderOperationGds t on t.idworkorderoperation = o.id
    join   Mct_MaterialFulfillmentType ft on ft.id = t.idmaterialfulfillmenttype
    join   bs_goods  g on g.id = t.idgds
    where  --@resultCondition
    and    ft.scode in ('20', '25', '30')
    group by t.idgds

## Фильтруйтесь по индексируемым полям, вместо служебных полей

Например, при фильтрации по мнемокоду надо использовать поле, которое является мнемокодом, а не служебное smnemocode_dz. Потому что индекс создается на поле, которое является мнемокодом. Пример:

    // До
    select crt.id from oil_contrasroletype crt where crt.smnemocode_dz = 'Surveyor'
    // После
    select crt.id from oil_contrasroletype crt where crt.scode = 'Surveyor'

## При соединении таблиц по идентификаторам, избегайте различных преобразований

При добавлении в запросе различных преобразований на индексируемые поля не будет использоваться индекс и может существенно замедлиться запрос.
Пример:

    // До
    select t.id
    from bs_goods t
    join  msr_measureitem mm on coalesce(t.idmeasureitem,0) = coalesce(mm.id,0)
    where t.id = 1
    // После
    select t.id
    from bs_goods t
    left join  msr_measureitem mm on t.idmeasureitem = mm.id
    where t.id = 1

## Используйте правильное приведение типов, в соответствие, с типом данных поля

При добавлении в запросе условий с не верными типами данных на индексируемые поля не будет использоваться индекс и может существенно замедлиться запрос.
Пример:

    // До
    select c.id, c.sName
    from oco_query t
    join btk_class c on c.id = cast(substring(t.gidobject,'^[^/]*')as numeric)
    // После
    select c.id, c.sName
    from oco_query t
    join btk_class c on c.id = cast(substring(t.gidobject,'^[^/]*')as bigint)

Пример:

    // До
    select *
    from   stm_actin t
    where  to_char(t.dDocIn,'yyyy') >= '2024'
    // После
    select *
    from   stm_actin t
    where  t.dDocIn >= to_date('01.01.2024','dd.mm.yyyy')

## Добавляйте защиту от зацикливания в рекурсивных запросах

Пример:

    // До
    with recursive dw_graph as
               (select t.id
                     ,t.idClass
                     ,t.gidSrc
                     ,t.gidRec
                     ,t.gidSrc as gid
                     ,t.gidRec as gidDoc
               from Bts_DocLink t
               where t.gidSrc = :super$$gid
               union all
               select t.id
                     ,t.idClass
                     ,t.gidSrc
                     ,t.gidRec
                     ,t.gidSrc as gid
                     ,t.gidRec as gidDoc
               from Bts_DocLink t
               ,dw_graph  sg
               where t.gidSrc = sg.gidRec
               )
    SELECT * FROM dw_graph
    // После
    with recursive dw_graph as
               (select t.id
                     ,t.idClass
                     ,t.gidSrc
                     ,t.gidRec
                     ,t.gidSrc as gid
                     ,array[''||t.gidRec] as path
                     ,t.gidRec as gidDoc
               from Bts_DocLink t
               where t.gidSrc = :super$$gid
               union all
               select t.id
                     ,t.idClass
                     ,t.gidSrc
                     ,t.gidRec
                     ,t.gidSrc as gid
                     ,path || cast(t.gidRec as varchar)
                     ,t.gidRec as gidDoc
               from Bts_DocLink t
               ,dw_graph  sg
               where t.gidSrc = sg.gidRec
               and t.gidRec != any(path)
               )
    SELECT * FROM dw_graph

## Используйте not exists вместо not in

Фильтрация будет срабатывать без использования индекса и то что находится внутри not in, в большинстве случаев тоже.
Пример:

    // До
    select emp.gid as gidEmployee
          , per.idStockOut as idStock
          , row_number() over (order by emp.gid) nCountCreated
          , count(emp.gid) over () as nTotalCount
    from Bs_Employee emp
    join Bs_Person per on per.id = emp.idPerson
    where per.idStockOut is not null
    and emp.id not in (select r.idEmployee from Ppm_PpmNeedReg r where r.idEmployee is not null)
    and per.idStockOut is not null
    and emp.gid in (select det.gidEmployee
                      from Ppm_GoodsNeedDet det
                      join Bs_Goods gds on gds.id = det.idGds
                      where det.idDoc = $idvGoodsNeed
                      and gds.idObjectTypeSuite in (select unnest(${LongPgArray(Ppm_ObjectTypeGoodsNeedOverrideApi().getidaSuiteType(ropGoodsNeed.get(_.idObjectType)))}))
                      )
    order by emp.gid
    // После
    select emp.gid as gidEmployee
          , per.idStockOut as idStock
          , row_number() over (order by emp.gid) nCountCreated
          , count(emp.gid) over () as nTotalCount
    from Bs_Employee emp
    join Bs_Person per on per.id = emp.idPerson
    where per.idStockOut is not null
    and not exists (select 1 from Ppm_PpmNeedReg r where r.idEmployee = emp.id) -- так смотрим новых сотрудников
    and per.idStockOut is not null
    and emp.gid in (select det.gidEmployee
                      from Ppm_GoodsNeedDet det
                      join Bs_Goods gds on gds.id = det.idGds
                      where det.idDoc = $idvGoodsNeed
                      and gds.idObjectTypeSuite in (select unnest(${LongPgArray(Ppm_ObjectTypeGoodsNeedOverrideApi().getidaSuiteType(ropGoodsNeed.get(_.idObjectType)))}))
                      )
    order by emp.gid