Menu
▾
▴
Re: [Postgres-xc-general] Looks like co-ordinator is my perf bottleneck....somebody help...
|
From: Mason S. <ms...@tr...> - 2014-02-01 21:47:11
|
On Sat, Feb 1, 2014 at 2:40 PM, Nirmal Sharma <sha...@gm...>wrote: > Hi Mason, > > This is the actual query that i was running. > > select coalesce(fgpc.date_id,fgcd.date_id) > date_id, > fgpc.m_ad_grp_pub_key m_ad_grp_pub_key, > fgpc.m_kw_pub_key m_kw_pub_key, > kws.expr_names, > kws.expr_values, > kws.m_ad_grp_semid, > sum(fgpc.m_imps) m_imps, > sum(fgpc.m_clicks) m_clicks, > sum(fgpc.m_cost) m_cost, > > sum(fgpc.m_conv_1pc) m_conv_1pc, > sum(fgpc.m_conv_mpc) m_conv_mpc, > avg(fgpc.m_cnv_rate_1pc) m_cnv_rate_1pc, > avg(fgpc.m_cnv_rate_mpc) m_cnv_rate_mpc, > avg(fgpc.m_avg_cpc) m_avg_cpc, > avg(fgpc.m_max_cpc) m_max_cpc, > avg(fgpc.m_firstpage_cpc) m_firstpage_cpc, > avg(fgpc.m_topofpage_cpc) m_topofpage_cpc, > avg(fgpc.m_avg_cpm) m_avg_cpm, > avg(fgpc.m_max_cpm) m_max_cpm, > avg(fgpc.m_max_cpa_pct) m_max_cpa_pct, > avg(fgpc.m_avg_pos) m_avg_pos, > avg(fgpc.m_lowest_pos) m_lowest_pos, > avg(fgpc.m_highest_pos) m_highest_pos, > avg(fgpc.m_quality_score) m_quality_score, > avg(fgpc.m_view_thru_conv) > m_view_thru_conv, > sum(fgcd.m_revenue) m_revenue, > sum(fgcd.m_conversions) m_conversions, > sum(coalesce(kws.m_new_kw_bid, > kws.m_kw_bid)) m_kw_total_bid, > max(coalesce(kws.m_new_kw_bid, > kws.m_kw_bid)) m_kw_max_bid, > min(coalesce(kws.m_new_kw_bid, > kws.m_kw_bid)) m_kw_min_bid > from > bidw.fact_msn_kw_perf_daily fgpc > full outer join bidw.fact_msn_kw_conversion_daily fgcd on > fgpc.m_ad_grp_pub_key = fgcd.m_ad_grp_pub_key and fgpc.m_kw_pub_key = > fgcd.m_kw_pub_key and fgpc.date_id = fgpc.date_id > join biods.msn_keyword_sup kws on > fgpc.m_kw_pub_key = kws.m_kw_pub_key and fgpc.m_ad_grp_pub_key = > kws.m_ad_grp_pub_key > where > coalesce(fgpc.date_id,fgcd.date_id) between > 20131201 and 20140119 > group by > > coalesce(fgpc.date_id,fgcd.date_id),fgpc.m_ad_grp_pub_key,fgpc.m_kw_pub_key,kws.expr_names,kws.expr_values,kws.m_ad_grp_semid > ; > > *This is the explain plan for the same.* > explain analyze verbose select ...... > .... > > * Data Node Scan on "__REMOTE_GROUP_QUERY__" (cost=0.00..2.50 rows=1000 > width=908) (actual time=1672.149..8281.918 rows=605575 loops=1)* > Output: (COALESCE((fgpc.date_id)::bigint, fgcd.date_id)), > fgpc.m_ad_grp_pub_key, fgpc.m_kw_pub_key, kws.expr_names, kws.expr_values, > kws.m_ad_grp_semid, (sum(fgpc.m_imps)), (sum(fgpc.m_clicks)), > (sum(fgpc.m_cost)), (sum(fgpc.m_conv_1pc)), (sum(fgpc.m_conv_mpc)), > (avg(fgpc.m_cnv_rate_1pc)), (avg(fgpc.m_cnv_ra > te_mpc)), (avg(fgpc.m_avg_cpc)), (avg(fgpc.m_max_cpc)), > (avg(fgpc.m_firstpage_cpc)), (avg(fgpc.m_topofpage_cpc)), > (avg(fgpc.m_avg_cpm)), (avg(fgpc.m_max_cpm)), (avg(fgpc.m_max_cpa_pct)), > (avg(fgpc.m_avg_pos)), (avg(fgpc.m_lowest_pos)), (avg(fgpc.m_highest_pos)), > (avg(fgpc.m_quality_score)), (avg(fgpc.m_view_thr > u_conv)), (sum(fgcd.m_revenue)), (sum(fgcd.m_conversions)), > (sum(COALESCE(kws.m_new_kw_bid, kws.m_kw_bid))), > (max(COALESCE(kws.m_new_kw_bid, kws.m_kw_bid))), > (min(COALESCE(kws.m_new_kw_bid, kws.m_kw_bid))) > *Node/s: d11, d12, d13, d14, d15, d16* > Remote query: SELECT COALESCE((l.a_1)::bigint, l.a_23), l.a_2, l.a_3, > r.a_1, r.a_2, r.a_3, sum(l.a_4), sum(l.a_5), sum(l.a_6), sum(l.a_7), > sum(l.a_8), pg_catalog.numeric_avg(avg(l.a_9)), > pg_catalog.numeric_avg(avg(l.a_10)), pg_catalog.numeric_avg(avg(l.a_11)), > pg_catalog.numeric_avg(avg(l.a_12)), pg_catalog. > numeric_avg(avg(l.a_13)), pg_catalog.numeric_avg(avg(l.a_14)), > pg_catalog.numeric_avg(avg(l.a_15)), pg_catalog.numeric_avg(avg(l.a_16)), > pg_catalog.numeric_avg(avg(l.a_17)), pg_catalog.numeric_avg(avg(l.a_18)), > pg_catalog.numeric_avg(avg(l.a_19)), pg_catalog.numeric_avg(avg(l.a_20)), > pg_catalog.numeric_avg(avg( > l.a_21)), pg_catalog.numeric_avg(avg(l.a_22)), sum(l.a_24), sum(l.a_25), > sum(COALESCE(r.a_4, r.a_5)), max(COALESCE(r.a_4, r.a_5)), > min(COALESCE(r.a_4, r.a_5)) FROM ((SELECT l.a_1, l.a_2, l.a_3, l.a_4, > l.a_5, l.a_6, l.a_7, l.a_8, l.a_9, l.a_10, l.a_11, l.a_12, l.a_13, l.a_14, > l.a_15, l.a_16, l.a_17, l.a_18, l.a_ > 19, l.a_20, l.a_21, l.a_22, r.a_1, r.a_2, r.a_3 FROM ((SELECT > fgpc.date_id, fgpc.m_ad_grp_pub_key, fgpc.m_kw_pub_key, fgpc.m_imps, > fgpc.m_clicks, fgpc.m_cost, fgpc.m_conv_1pc, fgpc.m_conv_mpc, > fgpc.m_cnv_rate_1pc, fgpc.m_cnv_rate_mpc, fgpc.m_avg_cpc, fgpc.m_max_cpc, > fgpc.m_firstpage_cpc, fgpc.m_topofpage_cpc, f > gpc.m_avg_cpm, fgpc.m_max_cpm, fgpc.m_max_cpa_pct, fgpc.m_avg_pos, > fgpc.m_lowest_pos, fgpc.m_highest_pos, fgpc.m_quality_score, > fgpc.m_view_thru_conv FROM ONLY bidw.fact_msn_kw_perf_daily fgpc WHERE > true) l(a_1, a_2, a_3, a_4, a_5, a_6, a_7, a_8, a_9, a_10, a_11, a_12, > a_13, a_14, a_15, a_16, a_17, a_18, a_19, > a_20, a_21, a_22) LEFT JOIN (SELECT fgcd.date_id, fgcd.m_revenue, > fgcd.m_conversions, fgcd.m_ad_grp_pub_key, fgcd.m_kw_pub_key FROM ONLY > bidw.fact_msn_kw_conversion_daily fgcd WHERE true) r(a_1, a_2, a_3, a_4, > a_5) ON (((l.a_1 = l.a_1) AND (l.a_2 = r.a_4) AND (l.a_3 = r.a_5)))) WHERE > ((COALESCE((l.a_1)::bigint, > r.a_1) >= 20131201) AND (COALESCE((l.a_1)::bigint, r.a_1) <= 20140119))) > l(a_1, a_2, a_3, a_4, a_5, a_6, a_7, a_8, a_9, a_10, a_11, a_12, a_13, > a_14, a_15, a_16, a_17, a_18, a_19, a_20, a_21, a_22, a_23, a_24, a_25) > JOIN (SELECT kws.expr_names, kws.expr_values, kws.m_ad_grp_semid, > kws.m_new_kw_bid, kws.m_kw_bi > d, kws.m_kw_pub_key, kws.m_ad_grp_pub_key FROM ONLY biods.msn_keyword_sup > kws WHERE true) r(a_1, a_2, a_3, a_4, a_5, a_6, a_7) ON (true)) WHERE > ((l.a_2 = r.a_7) AND (l.a_3 = r.a_6)) GROUP BY 1, 2, 3, 4, 5, 6 > * Total runtime: 8378.080 ms* > (5 rows) > > > > *This is the actual time taken by the query:* > > [postgres@sv4-pgxc-db04 test]$ time psql -d mydb -f "test.sql" > a.out > > *real 0m23.533s* > user 0m15.705s > sys 0m0.748s > > Now i dont know why is it taking that much time. > Try adding LIMIT with different amounts for example to see how that impacts time. Also, try enabling statement logging (log_statement = all in postgresql.conf) on the data nodes to see how long it takes on each node. Also, the statement was rewritten in XC, with relations converted into SELECTs, so try running the rewritten version directly to see how long it takes. Thanks, Mason |
