Different plan between 9.6 and 9.4 when using "Group by"

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 

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.

收发邮件者请注意：
本邮件含涉密信息，请保守秘密，若误收本邮件，请务必通知发送人并直接删去，不得使用、传播或复制本邮件。
进出邮件均受到本公司合规监控。邮件可能发生被截留、被修改、丢失、被破坏或包含计算机病毒等不安全情况。
********************************************************************************************************************************

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux