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
