[PERFORM] Different plan between 9.6 and 9.4 when using "Group by" - Mailing list pgsql-performance
| From | 梁海安（Killua Leung） |
|---|---|
| Subject | [PERFORM] Different plan between 9.6 and 9.4 when using "Group by" |
| Date | |
| Msg-id | C7033BC7E7882D4BBE21532AE40A020001B4E24DEE@PAMAILMBXA02.paicdom.local Whole thread Raw |
| Responses |
Re: [PERFORM] Different plan between 9.6 and 9.4 when using "Group by"
(Jeff Janes <jeff.janes@gmail.com>)
|
| List | pgsql-performance |
Hi team:
The following SQL is very slow in 9.6.1 for the plan has a “sort” node.
SQL text:
explain(analyze, buffers, verbose, timing)WITH m as
(SELECT date,accumulation,prod_type,IF,plan_code, mapping_code, channel, VARIABLE, up_load_data
FROM sdm_actu_fore_up_act_nb
WHERE fk_sdm_actu_fore_up_act_nb = 'd626e902-b3c5-495f-938f-3c6c74fa18da' ) ,
a as
(SELECT date,accumulation,prod_type,IF,plan_code, mapping_code, channel, VARIABLE, up_load_data
FROM m
WHERE date = '1' AND VARIABLE ='FYP_FAC') ,
b as
(SELECT date,mapping_code,channel,up_load_data
FROM SDM_ACTU_FORE_UP_FYP_PROD
WHERE FK_sdm_actu_fore_project_result = 'b9eece0c-60cc-403f-992f-9db9e9b78ee1'
AND date >= '2017-01-31' ) ,
n as
(SELECT a.plan_code,a.mapping_code,a.channel,a.variable,b.date,
CASE WHEN (a.up_load_data::numeric) = 0 THEN 0 ELSE b.up_load_data/(a.up_load_data::numeric) END AS fdyz
FROM a, b
WHERE /*a.plan_code = b.plan_code
and*/ a.mapping_code = b.mapping_code
AND a.channel=b.channel )
SELECT 'b9eece0c-60cc-403f-992f-9db9e9b78ee1' FK_sdm_actu_fore_project_result,
m.plan_code,
m.mapping_code,
m.accumulation,
m.channel,
m.prod_type,
m.if,
m.variable,
'PROF-IF' AS TYPE,
((date_trunc('month',add_months((n.date)::date,(m.date::numeric)))- interval '1 day')::date)::text,
sum((m.up_load_data::numeric)*n.fdyz)
FROM m, n
WHERE m.mapping_code = n.mapping_code AND m.channel = n.channel
GROUP BY m.plan_code,
m.mapping_code,
m.accumulation,
m.channel,
m.prod_type,
m.if,
m.variable,
((date_trunc('month',add_months((n.date)::date,(m.date::numeric)))- interval '1 day')::date)::text
;
===========
Plan in 9.6.2:
QUERY PL
AN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=437554.59..437556.52 rows=22 width=352) (actual time=175322.440..192068.748 rows=1072820 loops=1)
Output: 'b9eece0c-60cc-403f-992f-9db9e9b78ee1', m.plan_code, m.mapping_code, m.accumulation, m.channel, m.prod_type, m.if, m.variable, 'PROF-IF', ((((date_trunc('month'::text, ((((n.date
)::date + ((((m.date)::numeric)::text || 'months'::text))::interval))::date)::timestamp with time zone) - '1 day'::interval))::date)::text), sum(((m.up_load_data)::numeric * n.fdyz))
Group Key: m.plan_code, m.mapping_code, m.accumulation, m.channel, m.prod_type, m.if, m.variable, ((((date_trunc('month'::text, ((((n.date)::date + ((((m.date)::numeric)::text || 'months
'::text))::interval))::date)::timestamp with time zone) - '1 day'::interval))::date)::text)
Buffers: shared hit=29835, temp read=168320 written=168320
CTE m
-> Bitmap Heap Scan on public.sdm_actu_fore_up_act_nb (cost=22340.45..386925.95 rows=866760 width=60) (actual time=124.239..368.762 rows=895056 loops=1)
Output: sdm_actu_fore_up_act_nb.date, sdm_actu_fore_up_act_nb.accumulation, sdm_actu_fore_up_act_nb.prod_type, sdm_actu_fore_up_act_nb.if, sdm_actu_fore_up_act_nb.plan_code, sdm_
actu_fore_up_act_nb.mapping_code, sdm_actu_fore_up_act_nb.channel, sdm_actu_fore_up_act_nb.variable, sdm_actu_fore_up_act_nb.up_load_data
Recheck Cond: (sdm_actu_fore_up_act_nb.fk_sdm_actu_fore_up_act_nb = 'd626e902-b3c5-495f-938f-3c6c74fa18da'::text)
Heap Blocks: exact=23005
Buffers: shared hit=29402
-> Bitmap Index Scan on ix_sdm_actu_fore_up_act_nb (cost=0.00..22123.76 rows=866760 width=0) (actual time=119.406..119.406 rows=895056 loops=1)
Index Cond: (sdm_actu_fore_up_act_nb.fk_sdm_actu_fore_up_act_nb = 'd626e902-b3c5-495f-938f-3c6c74fa18da'::text)
Buffers: shared hit=6397
CTE a
-> CTE Scan on m m_1 (cost=0.00..21669.00 rows=22 width=288) (actual time=3.972..743.152 rows=289 loops=1)
Output: m_1.date, m_1.accumulation, m_1.prod_type, m_1.if, m_1.plan_code, m_1.mapping_code, m_1.channel, m_1.variable, m_1.up_load_data
Filter: ((m_1.date = '1'::text) AND (m_1.variable = 'FYP_FAC'::text))
Rows Removed by Filter: 894767
Buffers: shared hit=23004
CTE b
-> Bitmap Heap Scan on public.sdm_actu_fore_up_fyp_prod (cost=124.14..5052.60 rows=2497 width=33) (actual time=2.145..4.566 rows=4752 loops=1)
Output: sdm_actu_fore_up_fyp_prod.date, sdm_actu_fore_up_fyp_prod.mapping_code, sdm_actu_fore_up_fyp_prod.channel, sdm_actu_fore_up_fyp_prod.up_load_data
Recheck Cond: (sdm_actu_fore_up_fyp_prod.fk_sdm_actu_fore_project_result = 'b9eece0c-60cc-403f-992f-9db9e9b78ee1'::text)
Filter: (sdm_actu_fore_up_fyp_prod.date >= '2017-01-31'::text)
Heap Blocks: exact=315
Buffers: shared hit=433
-> Bitmap Index Scan on ix_sdm_actu_fore_up_fyp_prod (cost=0.00..123.52 rows=4746 width=0) (actual time=1.863..1.863 rows=14256 loops=1)
Index Cond: (sdm_actu_fore_up_fyp_prod.fk_sdm_actu_fore_project_result = 'b9eece0c-60cc-403f-992f-9db9e9b78ee1'::text)
Buffers: shared hit=118
CTE n
-> Hash Join (cost=0.77..69.46 rows=1 width=192) (actual time=745.835..756.304 rows=4764 loops=1)
Output: a.plan_code, a.mapping_code, a.channel, a.variable, b.date, CASE WHEN ((a.up_load_data)::numeric = '0'::numeric) THEN '0'::numeric ELSE (b.up_load_data / (a.up_load_data)
::numeric) END
Hash Cond: ((b.mapping_code = a.mapping_code) AND (b.channel = a.channel))
Buffers: shared hit=23437
-> CTE Scan on b (cost=0.00..49.94 rows=2497 width=128) (actual time=2.147..6.445 rows=4752 loops=1)
Output: b.date, b.mapping_code, b.channel, b.up_load_data
Buffers: shared hit=433
-> Hash (cost=0.44..0.44 rows=22 width=160) (actual time=743.661..743.661 rows=289 loops=1)
Output: a.plan_code, a.mapping_code, a.channel, a.variable, a.up_load_data
Buckets: 1024 Batches: 1 Memory Usage: 29kB
Buffers: shared hit=23004
-> CTE Scan on a (cost=0.00..0.44 rows=22 width=160) (actual time=3.974..743.380 rows=289 loops=1)
Output: a.plan_code, a.mapping_code, a.channel, a.variable, a.up_load_data
Buffers: shared hit=23004
-> Sort (cost=23837.58..23837.64 rows=22 width=320) (actual time=175322.411..178986.480 rows=14620032 loops=1)
Output: m.plan_code, m.mapping_code, m.accumulation, m.channel, m.prod_type, m.if, m.variable, ((((date_trunc('month'::text, ((((n.date)::date + ((((m.date)::numeric)::text || 'mon
ths'::text))::interval))::date)::timestamp with time zone) - '1 day'::interval))::date)::text), m.up_load_data, n.fdyz
Sort Key: m.plan_code, m.mapping_code, m.accumulation, m.channel, m.prod_type, m.if, m.variable, ((((date_trunc('month'::text, ((((n.date)::date + ((((m.date)::numeric)::text || 'm
onths'::text))::interval))::date)::timestamp with time zone) - '1 day'::interval))::date)::text)
Sort Method: external merge Disk: 1346544kB
Buffers: shared hit=29835, temp read=168320 written=168320
-> Hash Join (cost=0.04..23837.09 rows=22 width=320) (actual time=884.588..27338.979 rows=14620032 loops=1)
Output: m.plan_code, m.mapping_code, m.accumulation, m.channel, m.prod_type, m.if, m.variable, (((date_trunc('month'::text, ((((n.date)::date + ((((m.date)::numeric)::text ||
'months'::text))::interval))::date)::timestamp with time zone) - '1 day'::interval))::date)::text, m.up_load_data, n.fdyz
Hash Cond: ((m.mapping_code = n.mapping_code) AND (m.channel = n.channel))
Buffers: shared hit=29835
-> CTE Scan on m (cost=0.00..17335.20 rows=866760 width=288) (actual time=124.243..263.402 rows=895056 loops=1)
Output: m.date, m.accumulation, m.prod_type, m.if, m.plan_code, m.mapping_code, m.channel, m.variable, m.up_load_data
Buffers: shared hit=6398
-> Hash (cost=0.02..0.02 rows=1 width=128) (actual time=760.302..760.302 rows=4764 loops=1)
Output: n.date, n.fdyz, n.mapping_code, n.channel
Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 389kB
Buffers: shared hit=23437
-> CTE Scan on n (cost=0.00..0.02 rows=1 width=128) (actual time=745.838..759.139 rows=4764 loops=1)
Output: n.date, n.fdyz, n.mapping_code, n.channel
Buffers: shared hit=23437
Planning time: 0.383 ms
Execution time: 192187.911 ms
(65 rows)
Time: 192192.814 ms
==========
Plan in 9.4.1
QUERY PL
AN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=478276.30..478278.89 rows=47 width=352) (actual time=92967.646..93660.910 rows=1072820 loops=1)
Output: 'b9eece0c-60cc-403f-992f-9db9e9b78ee1', m.plan_code, m.mapping_code, m.accumulation, m.channel, m.prod_type, m.if, m.variable, 'PROF-IF', ((((date_trunc('month'::text, ((((n.date
)::date + ((((m.date)::numeric)::text || 'months'::text))::interval))::date)::timestamp with time zone) - '1 day'::interval))::date)::text), sum(((m.up_load_data)::numeric * n.fdyz))
Group Key: m.plan_code, m.mapping_code, m.accumulation, m.channel, m.prod_type, m.if, m.variable, (((date_trunc('month'::text, ((((n.date)::date + ((((m.date)::numeric)::text || 'months'
::text))::interval))::date)::timestamp with time zone) - '1 day'::interval))::date)::text
Buffers: shared hit=30869, temp read=8103 written=8102
CTE m
-> Bitmap Heap Scan on public.sdm_actu_fore_up_act_nb (cost=37491.97..421474.67 rows=942376 width=60) (actual time=158.435..465.865 rows=895056 loops=1)
Output: sdm_actu_fore_up_act_nb.date, sdm_actu_fore_up_act_nb.accumulation, sdm_actu_fore_up_act_nb.prod_type, sdm_actu_fore_up_act_nb.if, sdm_actu_fore_up_act_nb.plan_code, sdm_
actu_fore_up_act_nb.mapping_code, sdm_actu_fore_up_act_nb.channel, sdm_actu_fore_up_act_nb.variable, sdm_actu_fore_up_act_nb.up_load_data
Recheck Cond: (sdm_actu_fore_up_act_nb.fk_sdm_actu_fore_up_act_nb = 'd626e902-b3c5-495f-938f-3c6c74fa18da'::text)
Heap Blocks: exact=23006
Buffers: shared hit=30422
-> Bitmap Index Scan on ix_sdm_actu_fore_up_act_nb (cost=0.00..37256.38 rows=942376 width=0) (actual time=153.180..153.180 rows=895056 loops=1)
Index Cond: (sdm_actu_fore_up_act_nb.fk_sdm_actu_fore_up_act_nb = 'd626e902-b3c5-495f-938f-3c6c74fa18da'::text)
Buffers: shared hit=7416
CTE a
-> CTE Scan on m m_1 (cost=0.00..23559.40 rows=24 width=288) (actual time=5.386..1227.412 rows=289 loops=1)
Output: m_1.date, m_1.accumulation, m_1.prod_type, m_1.if, m_1.plan_code, m_1.mapping_code, m_1.channel, m_1.variable, m_1.up_load_data
Filter: ((m_1.date = '1'::text) AND (m_1.variable = 'FYP_FAC'::text))
Rows Removed by Filter: 894767
Buffers: shared hit=23005, temp written=8101
CTE b
-> Bitmap Heap Scan on public.sdm_actu_fore_up_fyp_prod (cost=221.97..7251.24 rows=2575 width=33) (actual time=2.623..6.318 rows=4752 loops=1)
Output: sdm_actu_fore_up_fyp_prod.date, sdm_actu_fore_up_fyp_prod.mapping_code, sdm_actu_fore_up_fyp_prod.channel, sdm_actu_fore_up_fyp_prod.up_load_data
Recheck Cond: (sdm_actu_fore_up_fyp_prod.fk_sdm_actu_fore_project_result = 'b9eece0c-60cc-403f-992f-9db9e9b78ee1'::text)
Filter: (sdm_actu_fore_up_fyp_prod.date >= '2017-01-31'::text)
Heap Blocks: exact=327
Buffers: shared hit=447
-> Bitmap Index Scan on ix_sdm_actu_fore_up_fyp_prod (cost=0.00..221.32 rows=4920 width=0) (actual time=2.313..2.313 rows=14256 loops=1)
Index Cond: (sdm_actu_fore_up_fyp_prod.fk_sdm_actu_fore_project_result = 'b9eece0c-60cc-403f-992f-9db9e9b78ee1'::text)
Buffers: shared hit=120
CTE n
-> Hash Join (cost=0.84..71.70 rows=2 width=224) (actual time=1230.640..1245.947 rows=4764 loops=1)
Output: a.plan_code, a.mapping_code, a.channel, a.variable, b.date, CASE WHEN ((a.up_load_data)::numeric = 0::numeric) THEN 0::numeric ELSE (b.up_load_data / (a.up_load_data)::nu
meric) END
Hash Cond: ((b.mapping_code = a.mapping_code) AND (b.channel = a.channel))
Buffers: shared hit=23452, temp written=8101
-> CTE Scan on b (cost=0.00..51.50 rows=2575 width=128) (actual time=2.626..8.904 rows=4752 loops=1)
Output: b.date, b.mapping_code, b.channel, b.up_load_data
Buffers: shared hit=447
-> Hash (cost=0.48..0.48 rows=24 width=160) (actual time=1227.982..1227.982 rows=289 loops=1)
Output: a.plan_code, a.mapping_code, a.channel, a.variable, a.up_load_data
Buckets: 1024 Batches: 1 Memory Usage: 21kB
Buffers: shared hit=23005, temp written=8101
-> CTE Scan on a (cost=0.00..0.48 rows=24 width=160) (actual time=5.387..1227.668 rows=289 loops=1)
Output: a.plan_code, a.mapping_code, a.channel, a.variable, a.up_load_data
Buffers: shared hit=23005, temp written=8101
-> Hash Join (cost=0.07..25917.88 rows=47 width=352) (actual time=1410.018..61022.859 rows=14620032 loops=1)
Output: m.plan_code, m.mapping_code, m.accumulation, m.channel, m.prod_type, m.if, m.variable, (((date_trunc('month'::text, ((((n.date)::date + ((((m.date)::numeric)::text || 'mont
hs'::text))::interval))::date)::timestamp with time zone) - '1 day'::interval))::date)::text, m.up_load_data, n.fdyz
Hash Cond: ((m.mapping_code = n.mapping_code) AND (m.channel = n.channel))
Buffers: shared hit=30869, temp read=8103 written=8102
-> CTE Scan on m (cost=0.00..18847.52 rows=942376 width=288) (actual time=158.442..558.052 rows=895056 loops=1)
Output: m.date, m.accumulation, m.prod_type, m.if, m.plan_code, m.mapping_code, m.channel, m.variable, m.up_load_data
Buffers: shared hit=7417, temp read=8103 written=1
-> Hash (cost=0.04..0.04 rows=2 width=128) (actual time=1251.514..1251.514 rows=4764 loops=1)
Output: n.date, n.fdyz, n.mapping_code, n.channel
Buckets: 1024 Batches: 1 Memory Usage: 325kB
Buffers: shared hit=23452, temp written=8101
-> CTE Scan on n (cost=0.00..0.04 rows=2 width=128) (actual time=1230.643..1249.718 rows=4764 loops=1)
Output: n.date, n.fdyz, n.mapping_code, n.channel
Buffers: shared hit=23452, temp written=8101
Planning time: 0.666 ms
Execution time: 93783.172 ms
(60 rows)
Time: 93790.518 ms
********************************************************************************************************************************
The information in this email is confidential and may be legally privileged. If you have received this email in error or are not the intended recipient, please immediately notify the sender and delete this message from your computer. Any use, distribution, or copying of this email other than by the intended recipient is strictly prohibited. All messages sent to and from us may be monitored to ensure compliance with internal policies and to protect our business.
Emails are not secure and cannot be guaranteed to be error free as they can be intercepted, amended, lost or destroyed, or contain viruses. Anyone who communicates with us by email is taken to accept these risks.
收发邮件者请注意：
本邮件含涉密信息，请保守秘密，若误收本邮件，请务必通知发送人并直接删去，不得使用、传播或复制本邮件。
进出邮件均受到本公司合规监控。邮件可能发生被截留、被修改、丢失、被破坏或包含计算机病毒等不安全情况。
********************************************************************************************************************************
pgsql-performance by date: