Performance issues

Vivekanand Joshi <vjoshi@xxxxxxxxxxxxxxxxxxx> · Fri, 13 Mar 2015 17:14:25 +0530

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
Â 