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)

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

При построении сложных запросов, дробите его на части - основной запрос с условиями и с минимальным кол-вом соединений можно вынести в 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

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

Индекс в таблице базы данных — это дополнительная структура данных, которая создаётся наряду с данными в таблице. Индексы используются для ускорения процесса извлечения данных и повышения производительности запросов. Они обеспечивают эффективный способ обращения к упорядоченным записям. Когда индекс создан, никакие дополнительные действия не требуются: система сама будет обновлять его при изменении данных в таблице и сама будет использовать его в запросах, где, по её мнению, это будет эффективнее, чем сканирование всей таблицы. Вам, возможно, придётся только периодически запускать команду 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')

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