Re: Performance issues - Mailing list pgsql-performance

Hi Team,

 

I am a novice to this territory. We are trying to migrate few jasper reports from Netezza to PostgreSQL.

 

I have one report ready with me but queries are taking too much time. To be honest, it is not giving any result  most of the time.

 

The same query in Netezza is running in less than 2-3 seconds.

 

========================================================================================================

 

This is the query :

 

 

SELECT

                    COUNT(DISTINCT TARGET_ID)

                FROM

                    S_V_F_PROMOTION_HISTORY_EMAIL PH

                    INNER JOIN S_V_D_CAMPAIGN_HIERARCHY CH

                        ON PH.TOUCHPOINT_EXECUTION_ID = CH.TOUCHPOINT_EXECUTION_ID

                WHERE

                    1=1

                    AND SEND_DT >= '2014-03-13'

                    AND SEND_DT <= '2015-03-14'

 

Statistics:

 

Select Count(1) from S_V_F_PROMOTION_HISTORY_EMAIL

4559289

Time: 16781.409 ms

 

Select count(1) from S_V_D_CAMPAIGN_HIERARCHY;

count

-------

45360

(1 row)

 

Time: 467869.185 ms

==================================================================

EXPLAIN PLAN FOR QUERY:

 

"Aggregate  (cost=356422.36..356422.37 rows=1 width=8)"

"  Output: count(DISTINCT base.target_id)"

"  ->  Nested Loop  (cost=68762.23..356422.36 rows=1 width=8)"

"        Output: base.target_id"

"        Join Filter: (base.touchpoint_execution_id = tp_exec.touchpoint_execution_id)"

"        ->  Nested Loop  (cost=33927.73..38232.16 rows=1 width=894)"

"              Output: camp.campaign_id, camp.campaign_name, camp.initiative, camp.objective, camp.category_id, "CATEGORY".category_name, camp_exec.campaign_execution_id, camp_exec.campaign_execution_name, camp_exec.group_id, grup.group_name, camp_exec.star (...)"

"              Join Filter: (tp_exec.touchpoint_execution_id = valid_executions.touchpoint_execution_id)"

"              CTE valid_executions"

"                ->  Merge Join  (cost=30420.45..31971.94 rows=1 width=8)"

"                      Output: s_f_touchpoint_execution_status_history_2.touchpoint_execution_id"

"                      Merge Cond: ((s_f_touchpoint_execution_status_history_2.touchpoint_execution_id = s_f_touchpoint_execution_status_history_1_1.touchpoint_execution_id) AND (s_f_touchpoint_execution_status_history_2.creation_dt = (max(s_f_touchpoint_ex (...)"

"                      ->  Sort  (cost=17196.30..17539.17 rows=137149 width=16)"

"                            Output: s_f_touchpoint_execution_status_history_2.touchpoint_execution_id, s_f_touchpoint_execution_status_history_2.creation_dt"

"                            Sort Key: s_f_touchpoint_execution_status_history_2.touchpoint_execution_id, s_f_touchpoint_execution_status_history_2.creation_dt"

"                            ->  Seq Scan on public.s_f_touchpoint_execution_status_history s_f_touchpoint_execution_status_history_2  (cost=0.00..5493.80 rows=137149 width=16)"

"                                  Output: s_f_touchpoint_execution_status_history_2.touchpoint_execution_id, s_f_touchpoint_execution_status_history_2.creation_dt"

"                                  Filter: (s_f_touchpoint_execution_status_history_2.touchpoint_execution_status_type_id = ANY ('{3,4}'::integer[]))"

"                      ->  Sort  (cost=13224.15..13398.43 rows=69715 width=16)"

"                            Output: s_f_touchpoint_execution_status_history_1_1.touchpoint_execution_id, (max(s_f_touchpoint_execution_status_history_1_1.creation_dt))"

"                            Sort Key: s_f_touchpoint_execution_status_history_1_1.touchpoint_execution_id, (max(s_f_touchpoint_execution_status_history_1_1.creation_dt))"

"                            ->  HashAggregate  (cost=6221.56..6918.71 rows=69715 width=16)"

"                                  Output: s_f_touchpoint_execution_status_history_1_1.touchpoint_execution_id, max(s_f_touchpoint_execution_status_history_1_1.creation_dt)"

"                                  Group Key: s_f_touchpoint_execution_status_history_1_1.touchpoint_execution_id"

"                                  ->  Seq Scan on public.s_f_touchpoint_execution_status_history s_f_touchpoint_execution_status_history_1_1  (cost=0.00..4766.04 rows=291104 width=16)"

"                                        Output: s_f_touchpoint_execution_status_history_1_1.touchpoint_execution_id, s_f_touchpoint_execution_status_history_1_1.touchpoint_execution_status_type_id, s_f_touchpoint_execution_status_history_1_1.status_message (...)"

"              ->  Nested Loop Left Join  (cost=1955.80..6260.19 rows=1 width=894)"

"                    Output: tp_exec.touchpoint_execution_id, tp_exec.start_dt, tp_exec.message_type_id, tp_exec.content_id, wave_exec.wave_execution_id, wave_exec.wave_execution_name, wave_exec.start_dt, camp_exec.campaign_execution_id, camp_exec.campaign_ (...)"

"                    ->  Nested Loop  (cost=1955.67..6260.04 rows=1 width=776)"

"                          Output: tp_exec.touchpoint_execution_id, tp_exec.start_dt, tp_exec.message_type_id, tp_exec.content_id, wave_exec.wave_execution_id, wave_exec.wave_execution_name, wave_exec.start_dt, camp_exec.campaign_execution_id, camp_exec.cam (...)"

"                          ->  Nested Loop Left Join  (cost=1955.54..6259.87 rows=1 width=658)"

"                                Output: tp_exec.touchpoint_execution_id, tp_exec.start_dt, tp_exec.message_type_id, tp_exec.content_id, wave_exec.wave_execution_id, wave_exec.wave_execution_name, wave_exec.start_dt, camp_exec.campaign_execution_id, camp_ex (...)"

"                                ->  Nested Loop Left Join  (cost=1955.40..6259.71 rows=1 width=340)"

"                                      Output: tp_exec.touchpoint_execution_id, tp_exec.start_dt, tp_exec.message_type_id, tp_exec.content_id, wave_exec.wave_execution_id, wave_exec.wave_execution_name, wave_exec.start_dt, camp_exec.campaign_execution_id, c (...)"

"                                      ->  Nested Loop Left Join  (cost=1955.27..6259.55 rows=1 width=222)"

"                                            Output: tp_exec.touchpoint_execution_id, tp_exec.start_dt, tp_exec.message_type_id, tp_exec.content_id, wave_exec.wave_execution_id, wave_exec.wave_execution_name, wave_exec.start_dt, camp_exec.campaign_execution (...)"

"                                            ->  Nested Loop  (cost=1954.99..6259.24 rows=1 width=197)"

"                                                  Output: tp_exec.touchpoint_execution_id, tp_exec.start_dt, tp_exec.message_type_id, tp_exec.content_id, wave_exec.wave_execution_id, wave_exec.wave_execution_name, wave_exec.start_dt, camp_exec.campaign_exe (...)"

"                                                  ->  Nested Loop  (cost=1954.71..6258.92 rows=1 width=173)"

"                                                        Output: tp_exec.touchpoint_execution_id, tp_exec.start_dt, tp_exec.message_type_id, tp_exec.content_id, wave_exec.wave_execution_id, wave_exec.wave_execution_name, wave_exec.start_dt, camp_exec.campai (...)"

"                                                        Join Filter: (camp_exec.campaign_id = wave.campaign_id)"

"                                                        ->  Nested Loop  (cost=1954.42..6254.67 rows=13 width=167)"

"                                                              Output: tp_exec.touchpoint_execution_id, tp_exec.start_dt, tp_exec.message_type_id, tp_exec.content_id, wave_exec.wave_execution_id, wave_exec.wave_execution_name, wave_exec.start_dt, wave_exec. (...)"

"                                                              ->  Hash Join  (cost=1954.13..6249.67 rows=13 width=108)"

"                                                                    Output: tp_exec.touchpoint_execution_id, tp_exec.start_dt, tp_exec.message_type_id, tp_exec.content_id, wave_exec.wave_execution_id, wave_exec.wave_execution_name, wave_exec.start_dt, wave (...)"

"                                                                    Hash Cond: ((tp_exec.touchpoint_id = tp.touchpoint_id) AND (wave_exec.wave_id = tp.wave_id))"

"                                                                    ->  Hash Join  (cost=1576.83..4595.51 rows=72956 width=90)"

"                                                                          Output: tp_exec.touchpoint_execution_id, tp_exec.start_dt, tp_exec.message_type_id, tp_exec.content_id, tp_exec.touchpoint_id, wave_exec.wave_execution_id, wave_exec.wave_execution_n (...)"

"                                                                          Hash Cond: (tp_exec.wave_execution_id = wave_exec.wave_execution_id)"

"                                                                          ->  Seq Scan on public.s_d_touchpoint_execution tp_exec  (cost=0.00..1559.56 rows=72956 width=42)"

"                                                                                Output: tp_exec.touchpoint_execution_id, tp_exec.wave_execution_id, tp_exec.touchpoint_id, tp_exec.channel_type_id, tp_exec.content_id, tp_exec.message_type_id, tp_exec.start_d (...)"

"                                                                          ->  Hash  (cost=1001.37..1001.37 rows=46037 width=56)"

"                                                                                Output: wave_exec.wave_execution_id, wave_exec.wave_execution_name, wave_exec.start_dt, wave_exec.campaign_execution_id, wave_exec.wave_id"

"                                                                                ->  Seq Scan on public.s_d_wave_execution wave_exec  (cost=0.00..1001.37 rows=46037 width=56)"

"                                                                                      Output: wave_exec.wave_execution_id, wave_exec.wave_execution_name, wave_exec.start_dt, wave_exec.campaign_execution_id, wave_exec.wave_id"

"                                                                    ->  Hash  (cost=212.72..212.72 rows=10972 width=26)"

"                                                                          Output: tp.touchpoint_id, tp.touchpoint_name, tp.wave_id, tp.channel_type_id"

"                                                                          ->  Seq Scan on public.s_d_touchpoint tp  (cost=0.00..212.72 rows=10972 width=26)"

"                                                                                Output: tp.touchpoint_id, tp.touchpoint_name, tp.wave_id, tp.channel_type_id"

"                                                              ->  Index Scan using s_d_campaign_execution_idx on public.s_d_campaign_execution camp_exec  (cost=0.29..0.37 rows=1 width=67)"

"                                                                    Output: camp_exec.campaign_execution_id, camp_exec.campaign_id, camp_exec.group_id, camp_exec.campaign_execution_name, camp_exec.start_dt, camp_exec.creation_dt"

"                                                                    Index Cond: (camp_exec.campaign_execution_id = wave_exec.campaign_execution_id)"

"                                                        ->  Index Scan using s_d_wave_pkey on public.s_d_wave wave  (cost=0.29..0.31 rows=1 width=22)"

"                                                              Output: wave.wave_id, wave.campaign_id, wave.wave_name, wave.creation_dt, wave.modified_dt"

"                                                              Index Cond: (wave.wave_id = wave_exec.wave_id)"

"                                                  ->  Index Scan using s_d_campaign_pkey on public.s_d_campaign camp  (cost=0.29..0.32 rows=1 width=40)"

"                                                        Output: camp.campaign_id, camp.campaign_name, camp.objective, camp.initiative, camp.category_id, camp.creation_dt, camp.modified_dt"

"                                                        Index Cond: (camp.campaign_id = camp_exec.campaign_id)"

"                                            ->  Index Scan using s_d_content_pkey on public.s_d_content content  (cost=0.28..0.30 rows=1 width=33)"

"                                                  Output: content.content_id, content.content_name, content.creation_dt, content.channel_type_id, content.modified_dt"

"                                                  Index Cond: (tp_exec.content_id = content.content_id)"

"                                      ->  Index Scan using s_d_message_type_pkey on public.s_d_message_type message_type  (cost=0.13..0.15 rows=1 width=120)"

"                                            Output: message_type.message_type_id, message_type.message_type_name, message_type.creation_dt, message_type.modified_dt"

"                                            Index Cond: (tp_exec.message_type_id = message_type.message_type_id)"

"                                ->  Index Scan using s_d_group_pkey on public.s_d_group grup  (cost=0.13..0.15 rows=1 width=320)"

"                                      Output: grup.group_id, grup.group_name, grup.creation_dt, grup.modified_dt"

"                                      Index Cond: (camp_exec.group_id = grup.group_id)"

"                          ->  Index Scan using d_channel_pk on public.s_d_channel_type channel  (cost=0.13..0.15 rows=1 width=120)"

"                                Output: channel.channel_type_id, channel.channel_type_name"

"                                Index Cond: (channel.channel_type_id = tp.channel_type_id)"

"                    ->  Index Scan using s_d_category_pkey on public.s_d_category "CATEGORY"  (cost=0.13..0.15 rows=1 width=120)"

"                          Output: "CATEGORY".category_id, "CATEGORY".category_name, "CATEGORY".creation_dt, "CATEGORY".modified_dt"

"                          Index Cond: (camp.category_id = "CATEGORY".category_id)"

"              ->  CTE Scan on valid_executions  (cost=0.00..0.02 rows=1 width=8)"

"                    Output: valid_executions.touchpoint_execution_id"

"        ->  Nested Loop Left Join  (cost=34834.49..318190.14 rows=2 width=148)"

"              Output: base.promo_hist_id, base.audience_member_id, base.target_id, base.touchpoint_execution_id, base.contact_group_id, base.content_version_execution_id, base.sent_ind, CASE WHEN (email.sbounce_ind IS NOT NULL) THEN (email.sbounce_ind)::in (...)"

"              CTE valid_executions"

"                ->  Nested Loop  (cost=33089.13..34834.20 rows=1 width=8)"

"                      Output: s_f_touchpoint_execution_status_history.touchpoint_execution_id"

"                      ->  Nested Loop  (cost=33088.84..34833.88 rows=1 width=16)"

"                            Output: s_f_touchpoint_execution_status_history.touchpoint_execution_id, tpe.touchpoint_id"

"                            ->  Unique  (cost=33088.42..34825.42 rows=1 width=8)"

"                                  Output: s_f_touchpoint_execution_status_history.touchpoint_execution_id"

"                                  ->  Merge Join  (cost=33088.42..34825.42 rows=1 width=8)"

"                                        Output: s_f_touchpoint_execution_status_history.touchpoint_execution_id"

"                                        Merge Cond: ((s_f_touchpoint_execution_status_history.touchpoint_execution_id = s_f_touchpoint_execution_status_history_1.touchpoint_execution_id) AND (s_f_touchpoint_execution_status_history.creation_dt = (max(s_f_t (...)"

"                                        ->  Sort  (cost=19864.28..20268.98 rows=161883 width=16)"

"                                              Output: s_f_touchpoint_execution_status_history.touchpoint_execution_id, s_f_touchpoint_execution_status_history.creation_dt"

"                                              Sort Key: s_f_touchpoint_execution_status_history.touchpoint_execution_id, s_f_touchpoint_execution_status_history.creation_dt"

"                                              ->  Seq Scan on public.s_f_touchpoint_execution_status_history  (cost=0.00..5857.68 rows=161883 width=16)"

"                                                    Output: s_f_touchpoint_execution_status_history.touchpoint_execution_id, s_f_touchpoint_execution_status_history.creation_dt"

"                                                    Filter: (s_f_touchpoint_execution_status_history.touchpoint_execution_status_type_id = ANY ('{3,4,6}'::integer[]))"

"                                        ->  Sort  (cost=13224.15..13398.43 rows=69715 width=16)"

"                                              Output: s_f_touchpoint_execution_status_history_1.touchpoint_execution_id, (max(s_f_touchpoint_execution_status_history_1.creation_dt))"

"                                              Sort Key: s_f_touchpoint_execution_status_history_1.touchpoint_execution_id, (max(s_f_touchpoint_execution_status_history_1.creation_dt))"

"                                              ->  HashAggregate  (cost=6221.56..6918.71 rows=69715 width=16)"

"                                                    Output: s_f_touchpoint_execution_status_history_1.touchpoint_execution_id, max(s_f_touchpoint_execution_status_history_1.creation_dt)"

"                                                    Group Key: s_f_touchpoint_execution_status_history_1.touchpoint_execution_id"

"                                                    ->  Seq Scan on public.s_f_touchpoint_execution_status_history s_f_touchpoint_execution_status_history_1  (cost=0.00..4766.04 rows=291104 width=16)"

"                                                          Output: s_f_touchpoint_execution_status_history_1.touchpoint_execution_id, s_f_touchpoint_execution_status_history_1.touchpoint_execution_status_type_id, s_f_touchpoint_execution_status_history_1.st (...)"

"                            ->  Index Scan using s_d_touchpoint_execution_pkey on public.s_d_touchpoint_execution tpe  (cost=0.42..8.44 rows=1 width=16)"

"                                  Output: tpe.touchpoint_execution_id, tpe.wave_execution_id, tpe.touchpoint_id, tpe.channel_type_id, tpe.content_id, tpe.message_type_id, tpe.start_dt, tpe.creation_dt"

"                                  Index Cond: (tpe.touchpoint_execution_id = s_f_touchpoint_execution_status_history.touchpoint_execution_id)"

"                      ->  Index Only Scan using s_d_touchpoint_pkey on public.s_d_touchpoint tp_1  (cost=0.29..0.32 rows=1 width=8)"

"                            Output: tp_1.touchpoint_id, tp_1.channel_type_id"

"                            Index Cond: ((tp_1.touchpoint_id = tpe.touchpoint_id) AND (tp_1.channel_type_id = 1))"

"              ->  Nested Loop  (cost=0.00..283350.22 rows=2 width=74)"

"                    Output: base.promo_hist_id, base.audience_member_id, base.target_id, base.touchpoint_execution_id, base.contact_group_id, base.content_version_execution_id, base.sent_ind, base.send_dt, base.creation_dt, base.modified_dt"

"                    Join Filter: (base.touchpoint_execution_id = valid_executions_1.touchpoint_execution_id)"

"                    ->  CTE Scan on valid_executions valid_executions_1  (cost=0.00..0.02 rows=1 width=8)"

"                          Output: valid_executions_1.touchpoint_execution_id"

"                    ->  Seq Scan on public.s_f_promotion_history base  (cost=0.00..283334.00 rows=1296 width=74)"

"                          Output: base.promo_hist_id, base.target_id, base.audience_member_id, base.touchpoint_execution_id, base.contact_group_id, base.content_version_execution_id, base.sent_ind, base.send_dt, base.creation_dt, base.modified_dt"

"                          Filter: ((base.send_dt >= '2014-03-13 00:00:00'::timestamp without time zone) AND (base.send_dt <= '2015-03-14 00:00:00'::timestamp without time zone))"

"              ->  Index Scan using s_f_promotion_history_email_pk1 on public.s_f_promotion_history_email email  (cost=0.29..2.83 rows=1 width=90)"

"                    Output: email.promo_hist_id, email.target_id, email.audience_member_id, email.touchpoint_execution_id, email.contact_group_id, email.sbounce_ind, email.hbounce_ind, email.opened_ind, email.clicked_ind, email.unsubscribe_ind, email.unsub (...)"

"                    Index Cond: (base.promo_hist_id = email.promo_hist_id)"

"                    Filter: (base.audience_member_id = email.audience_member_id)"

 

=================================================================================================

Questions here are :

 

Is the query written correctly as per the PostgreSQL?

Am I missing anything here?

 

Total Memory : 8 GB

shared_buffers = 2GB

work_mem = 64MB

maintenance_work_mem = 700MB

effective_cache_size = 4GB

Any kind of help is appreciated.

 

Warm Regards,

Vivekanand Joshi
+919654227927

 

185 Madison Ave. New York, NY 10016

www.zetainteractive.com