10 million records in s_f_promotion_history table.
*From:* Varadharajan Mukundan [mailto:srinath...@gmail.com] *Sent:* Friday, March 13, 2015 6:29 PM *To:* vjo...@zetainteractive.com *Cc:* pgsql-performance@postgresql.org *Subject:* Re: [PERFORM] Performance issues Hi Vivekanand, >From the query plan, we can see that good amount of time is spent in this line -> 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))" Can you try creating (partial) index based on the filter fields? ( Good tutorial @ https://devcenter.heroku.com/articles/postgresql-indexes). Did you try doing a VACUUM ANALYZE? Other approach worth trying it out is partitioning the public.s_f_promotion_history table by date (BTW, what is the size and number of rows in this table?). On Fri, Mar 13, 2015 at 12:44 PM, Vivekanand Joshi < vjo...@zetainteractive.com> wrote: 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 [image: Zeta Interactive] 185 Madison Ave. New York, NY 10016 www.zetainteractive.com -- Thanks, M. Varadharajan ------------------------------------------------ "Experience is what you get when you didn't get what you wanted" -By Prof. Randy Pausch in "The Last Lecture" My Journal :- www.thinkasgeek.wordpress.com
