如何简化包含许多内部选择的选择查询并提高 PostgreSQL 中的性能 [英] How simplify a select query that contains many inner-selects and increase performance in PostgreSQL
本文介绍了如何简化包含许多内部选择的选择查询并提高 PostgreSQL 中的性能的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
下面给出的是我的选择查询
SELECT
gtab04.Productid,
gtab04.Product,
gtab04.SaleUnit ,
gtab04.Packing,
gtab04.ConvFact,
gtab04.PTR,
gtab04.MRP,
gtab04.PRate,
gtab04.PTR1,
gtab04.PTR2,
gtab04.Location,
0 As ManufId,
gtab07.PatentId,
gtab07.Patent,
gtab07.PatentCd,
gtab15.TaxId,
gtab15.TaxName,
gtab15.TaxType,
gtab15.TaxRate,
gtab15.TxOMRP,
''::text As Manufacture,
CASE WHEN EXISTS(
SELECT gtab10.Productid FROM gtab10 INNER JOIN gtab09 ON gtab09.TranId =
gtab10.TranId WHERE gtab10.Productid = gtab04.Productid AND
gtab10.BatchId = gtab05.BatchId AND gtab09.acyrid = 7 limit 1) THEN 1 ELSE 0 END
AS
StkEntered,
(SELECT SUM(gtab10.qty) FROM gtab10 INNER JOIN gtab09 ON gtab10.TranId =
gtab09.TranId WHERE gtab10.Productid = gtab04.Productid AND
gtab10.BatchId = gtab05.BatchId AND gtab09.vrid = 6 AND gtab09.acyrid = 7)
+
(SELECT SUM(gtab10.qty) FROM gtab10 INNER JOIN gtab09 ON gtab10.TranId =
gtab09.TranId WHERE gtab10.Productid = gtab04.Productid AND
gtab10.BatchId = gtab05.BatchId AND gtab09.vrid in (10,11,23,42,35) AND
gtab09.trdate < Cast('2014-06-01' AS timestamp) AND gtab09.acyrid = 7)
AS
OpeningInQty
(SELECT SUM(gtab10.qty)FROM gtab10 INNER JOIN gtab09 ON gtab10.TranId =
gtab09.TranId WHERE gtab04.Productid = gtab10.Productid AND
gtab10.BatchId = gtab05.BatchId AND gtab09.vrid in( 12,32,33,44 ,45 ,46, 47 ,48 ,
49,18 , 34 ,25,27 ,15,26,24 , 43 ,36) AND gtab09.trdate < Cast('2014-06-01' AS
timestamp) AND gtab09.acyrid = 7) AS
OpeningOutQty,
0 AS PurchQty,
0 AS SRetQty,
0 AS PerInQty,
0 AS SaleQty,
0 AS StockInQty,
0 AS StockOutQty,
0 AS SaleAmt,
0 AS DamageQty,
0 AS PRetQty,
0 AS PerOutQty,
(SELECT SUM(gtab10.qty) FROM gtab10 INNER JOIN gtab09 ON gtab10.TranId =
gtab09.TranId WHERE gtab04.Productid = gtab10.Productid AND
gtab10.BatchId = gtab05.BatchId AND gtab09.vrid in (12 ,32 ,33 ,44 ,45 ,46 ,47,48
,49) AND (gtab09.trdate BETWEEN '2014-05-01' AND '2014-05-31')
) AS
PrMthSaleQty,
(SELECT (SUM(gtab10.qty * gtab10.ptr) ) FROM gtab10 INNER JOIN gtab09 ON
gtab10.TranId = gtab09.TranId WHERE gtab04.Productid = gtab10.Productid AND
gtab10.BatchId = gtab05.BatchId AND gtab09.vrid in( 12, 32 , 33 , 44 ,45 ,46 , 47
,48 , 49) AND (FreeOrRpl = 0 OR FreeOrRpl = 2) AND (gtab09.trdate BETWEEN '2014-
05-01' AND '2014-05-31')
) AS
PrMthSaleAmt,
gtab04.LandCost,
gtab05.PTR AS BatchPTR,
(case when (
gtab05.Fqty - gtab05.FIQty)>0 then (gtab05.Fqty - gtab05.FIQty) else 0 end) as
IssdFreeQty,
gtab05.MRP AS BatchMRP,
gtab05.PRate AS BatchPRate,
gtab04.StdPack,
(date_part('day', (Select Min(Expiry) From gtab05 WHERE gtab05.Productid =
gtab04.Productid And gtab05.Qty > gtab05.IQty)-Cast('2014-06-20' AS
timestamp))) AS
ExpDys,
(SELECT gtab09.TrDate FROM gtab09 INNER JOIN gtab10 ON gtab09.TranId
=gtab10.TranId
where gtab09.VrId in (12,32,33,44,45,46, 47,48,49) And
gtab10.ProductId =gtab04.ProductId order by TrDate desc limit 1) As
LastSaleDate ,
GTAB05.BatchId,
0 AS FreeSaleQty,
0 AS ReplSaleQty
FROM
gtab04 INNER JOIN gtab15 ON gtab04.TaxId = gtab15.TaxId LEFT JOIN gtab05 ON
gtab04.Productid = gtab05.Productid INNER JOIN gtab07 ON gtab07.Patentid =
gtab04.Patentid WHERE (gtab04.Masked = False AND gtab04.Banned = false) AND
gtab04.patentid in (321, 313 , 267 , 431) ORDER BY Patent, gtab04.Product
解释分析
正如您在上面的选择中看到的，我使用了许多内部选择，同时将这些内部选择添加到主select 整体性能会变慢，需要很多时间来执行.我认为(不确定)我的选择查询计划差.
请帮助改进这个Query.
EXPLAIN ANALYZE
as you can see in the above select i have used many inner selects, while adding these these inner selects to the main select the overall performance will slow down taking many time to execute.
i think (no am sure) that my select query plan is poor.
Please help to improve this Query.
编辑
推荐答案
优化:
- 在单个选择中获取所有聚合计算 - 按查询分组.
- 用左连接替换子选择
- 基于外部条件的内部查询中的额外限制.
这样获取:
with gtab4_5 as (
select
gtab04.Productid, gtab05.BatchId
FROM
gtab04
LEFT JOIN gtab05
ON gtab04.Productid = gtab05.Productid
WHERE (gtab04.Masked = False AND gtab04.Banned = false)
AND gtab04.patentid in (321, 313 , 267 , 431)
)
SELECT
gtab04.Productid,
gtab04.Product,
gtab04.SaleUnit ,
gtab04.Packing,
gtab04.ConvFact,
gtab04.PTR,
gtab04.MRP,
gtab04.PRate,
gtab04.PTR1,
gtab04.PTR2,
gtab04.Location,
0 As ManufId,
gtab07.PatentId,
gtab07.Patent,
gtab07.PatentCd,
gtab15.TaxId,
gtab15.TaxName,
gtab15.TaxType,
gtab15.TaxRate,
gtab15.TxOMRP,
''::text As Manufacture,
stats_ags.StkEntered AS StkEntered,
stats_ags.OpeningInQty as OpeningInQty,
stats_ags.OpeningOutQty as OpeningOutQty,
0 AS PurchQty,
0 AS SRetQty,
0 AS PerInQty,
0 AS SaleQty,
0 AS StockInQty,
0 AS StockOutQty,
0 AS SaleAmt,
0 AS DamageQty,
0 AS PRetQty,
0 AS PerOutQty,
stats_ags.PrMthSaleQty as PrMthSaleQty,
stats_ags.PrMthSaleAmt as PrMthSaleAmt,
gtab04.LandCost,
gtab05.PTR AS BatchPTR,
(case when (
gtab05.Fqty - gtab05.FIQty)>0 then (gtab05.Fqty - gtab05.FIQty) else 0 end) as
IssdFreeQty,
gtab05.MRP AS BatchMRP,
gtab05.PRate AS BatchPRate,
gtab04.StdPack,
(date_part('day', (Select Min(Expiry) From gtab05 WHERE gtab05.Productid =
gtab04.Productid And gtab05.Qty > gtab05.IQty)-Cast('2014-06-20' AS
timestamp))) AS
ExpDys,
stats_ags.LastSaleDate as LastSaleDate ,
GTAB05.BatchId,
0 AS FreeSaleQty,
0 AS ReplSaleQty
FROM
gtab04
INNER JOIN gtab15
ON gtab04.TaxId = gtab15.TaxId
LEFT JOIN gtab05
ON gtab04.Productid = gtab05.Productid
INNER JOIN gtab07
ON gtab07.Patentid = gtab04.Patentid
left join (
SELECT gtab10_9.Productid, gtab10_9.BatchId,
max( case
when gtab10_9.acyrid = 7
then gtab10_9.Productid else null
end) as StkEntered,
SUM( case
when gtab10_9.acyrid = 7 and (
gtab10_9.vrid = 6
or (
gtab10_9.vrid in (10,11,23,42,35)
AND gtab10_9.trdate < Cast('2014-06-01' AS timestamp)
) )
then gtab10_9.qty else 0
end) as OpeningInQty,
SUM( case
when gtab10_9.acyrid = 7
and gtab10_9.vrid in( 12,32,33,44 ,45 ,46, 47 ,48 , 49,18 , 34 ,25,27 ,15,26,24 , 43 ,36)
AND gtab10_9.trdate < Cast('2014-06-01' AS timestamp)
then gtab10_9.qty else 0
end) as OpeningOutQty,
SUM( case
when gtab10_9.acyrid = 7
and gtab10_9.vrid in(12 ,32 ,33 ,44 ,45 ,46 ,47,48,49)
AND gtab10_9.trdate BETWEEN '2014-05-01' AND '2014-05-31'
then gtab10_9.qty else 0
end) as PrMthSaleQty,
SUM( case
when gtab10_9.acyrid = 7
and gtab10_9.vrid in( 12, 32 , 33 , 44 ,45 ,46 , 47,48 , 49)
and (gtab10_9.FreeOrRpl = 0 OR gtab10_9.FreeOrRpl = 2)
AND gtab10_9.trdate BETWEEN '2014-05-01' AND '2014-05-31'
then gtab10_9.qty * gtab10_9.ptr else 0
end) as PrMthSaleAmt,
MAX( case
when gtab10_9.acyrid = 7
and gtab10_9.VrId in (12,32,33,44,45,46, 47,48,49)
and (gtab10_9.FreeOrRpl = 0 OR gtab10_9.FreeOrRpl = 2)
then gtab10_9.TrDate else null
end) as LastSaleDate
FROM (
SELECT gtab10.*, gtab09.*
FROM gtab10
INNER JOIN gtab09 ON gtab09.TranId = gtab10.TranId
inner join gtab4_5 on gtab4_5.Productid = gtab10.Productid and gtab4_5.BatchId = gtab10.BatchId
) gtab10_9
group by gtab10_9.Productid, gtab10_9.BatchId
) stats_ags
on stats_ags.Productid = gtab04.Productid
and stats_ags.BatchId = gtab05.BatchId
WHERE (gtab04.Masked = False AND gtab04.Banned = false)
AND gtab04.patentid in (321, 313 , 267 , 431)
ORDER BY Patent, gtab04.Product
我的查询结果:
Sort (cost=82928.96..82931.84 rows=1152 width=306) (actual time=447.433..450.191 rows=2421 loops=1)
Sort Key: gtab07.patent, gtab04.product
Sort Method: external merge Disk: 680kB
与原始变体:
Sort (cost=2796544.62..2796547.50 rows=1152 width=278) (actual time=47865.883..47868.570 rows=2421 loops=1)
Sort Key: gtab07.patent, gtab04.product
Sort Method: external merge Disk: 680kB
排序和行数的大小仍然与假定的相同......
Size for sort and rows count still same as suposed to be...
基于您的样本数据 PG 9.3 的测量
Measures based on your samples data PG 9.3
这篇关于如何简化包含许多内部选择的选择查询并提高 PostgreSQL 中的性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
