Sql запросы
Contents
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')