From: jimmy <mpokky@126.com>
To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Why the sql is not executed in parallel mode
Date: Wed, 19 Sep 2018 09:53:28 +0800 (CST)
Message-ID: <34fb335d.2bc0.165ef883f52.Coremail.mpokky@126.com> (raw)
Why the sql is not executed in parallel mode, does the sql has some problem?
with sql1 as
(select a.*
from snaps a
where a.f_date between to_date('2018-03-05', 'yyyy-MM-dd') and
to_date('2018-03-11', 'yyyy-MM-dd')
),
sql2 as
(select '1' as pId, PM_TO as pValue, type_code as typeCode, version_no as versionNo,
bs as bs, l.order_rule as orderRule
from sql1, qfpl l
where PM_TO is not null
and l.pid = 1
union all
select '2' as pId,
PRTO as pValue,
type_code as typeCode, version_no as versionNo,
bs as bs, l.order_rule as orderRule
from sql1, qfpl l
where PRTO is not null
and l.pid = 2
union all
select '3' as pId,
PRATO as pValue,
type_code as typeCode, version_no as versionNo,
bs as bs, l.order_rule as orderRule
from sql1, qfpl l
where PRATO is not null
and l.pid = 3
),
sql4 as (
select typeCode, pId, orderRule, versionNo,
row_number() over(partition by pId, typeCode order by pValue) as rnn
from sql2
),
sql5 as (
select sql4.typeCode as typeCode,
sql4.pId as pId,
sql4.orderRule as orderRule,
t.pValue as pValue,
sql4.versionNo as versionNo
from sql4,
(select sql2.typeCode,sql2.pId,sql2.orderRule,
(case when sql2.orderRule = 1 then
PERCENTILE_DISC(0.05) WITHIN GROUP(ORDER BY sql2.pValue)
else
PERCENTILE_DISC(0.95) WITHIN GROUP(ORDER BY sql2.pValue)
end) as pValue,
(case when sql2.orderRule = 1 then
(case when round(count(1) * 0.05) - 1 < 0 then 1
else round(count(1) * 0.05)
end)
else
(case when round(count(1) * 0.95) - 1 < 0 then 1
else round(count(1) * 0.95)
end)
end) as rnn
from sql2
group by sql2.typeCode, sql2.pId, sql2.orderRule) t
where sql4.typeCode = t.typeCode
and sql4.pId = t.pId
and sql4.orderRule = t.orderRule
and sql4.rnn = t.rnn
),
sql6 as (
select sql2.pId, sql2.typeCode as typeCode, count(1) as fCount
from sql2, sql5
where sql2.pId = sql5.pId
and sql2.typeCode = sql5.typeCode
and ((sql2.orderRule = 2 and sql2.pValue >= sql5.pValue) or
(sql2.orderRule = 1 and sql2.pValue <= sql5.pValue))
and sql2.pId != '22'
group by sql2.pId, sql2.typeCode
union
select sql5.pId, sql5.typeCode, 0 as fCount
from sql5
where sql5.pId = '22'
group by sql5.pId, sql5.typeCode
)
select sql5.pId,
sql5.typeCode,
(case when sql5.pId = '22' then
(select p.d_chn
from qlp p
where p.version_no = sql5.versionNo
and p.cno = sql5.pValue
and (p.typeCode = sql5.typeCode or p.typeCode is null))
else
sql5.pValue || ''
end) pValue,
sql6.fCount,
(case when d.delta = 'Y' then d.dy_val
else d.y_val
end) yVal,
(case when d.is_delta = 'Y' then d.dr_val
else d.r_val
end) rVal,
f.p_no pNo,
f.p_name ||(case when f.unit = '' then ''
else '('|| f.unit ||')'
end) pName,
f.pe_name || (case when f.unit = '' then ''
else '(' || f.unit || ')'
end) peName,
c.fp_name fpName,
f.order_rule as orderRule,
f.pflag pFlag,
f.pdesc as pDesc
from sql5, sql6, qfpl f, qpa d,qfp c
where sql5.pId = sql6.pId
and sql5.typeCode = sql6.typeCode
and sql5.pId = f.pid||''
and f.deleted = 0
and f.pid = d.pid
and sql5.typeCode = d.typeCode
and f.fp_id = c.fp_id
order by f.t_sort, c.fp_id,f.p_no
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mpokky@126.com, pgsql-general@lists.postgresql.org
Subject: Re: Why the sql is not executed in parallel mode
In-Reply-To: <34fb335d.2bc0.165ef883f52.Coremail.mpokky@126.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox