Re: [Postgres-xc-general] Looks like co-ordinator is my perf bottleneck....somebody help...

[Nirmal S. <sha...@gm...>]

Yes you are absolutely right.
If I run the same query directly on nodes then it runs very fast. It is running slow when I run from coordinator. How am I going to resolve this tuple handling on coordinator?
Please advise. 

Sent from my iPad

> On Feb 2, 2014, at 9:32 AM, Mason Sharp <ms...@tr...> wrote:
> 
> 
> 
> 
>> On Sun, Feb 2, 2014 at 12:49 AM, Nirmal Sharma <sha...@gm...> wrote:
>> This is the explain plan for the query with limit 10000.
>> 
>> 
>> Limit  (cost=0.00..2.50 rows=1000 width=908) (actual time=1586.926..1836.081 rows=10000 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(f
>> gpc.m_cnv_rate_1pc)), (avg(fgpc.m_cnv_rate_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_thru_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, kw
>> s.m_kw_bid)))
>>    ->  Data Node Scan on "__REMOTE_LIMIT_QUERY__"  (cost=0.00..2.50 rows=1000 width=908) (actual time=1586.924..1834.118 rows=10000 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_rate_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_lowe
>> st_pos)), (avg(fgpc.m_highest_pos)), (avg(fgpc.m_quality_score)), (avg(fgpc.m_view_thru_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_b
>> id, 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.nume
>> ric_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, fgpc.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_bi
>> d, kws.m_kw_bid, 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 LIMIT 10000::bigint
>>  Total runtime: 2194.762 ms
>> (7 rows)
>> 
> 
> If you run the generated query on the nodes directly (through EXECUTE DIRECT) is the time similarly slow? If so, then it points to the query rewrite that is the problem. If it is fast, then it may mean an issue in tuple handling on the coordinator.
> 
>  
> -- 
> Mason Sharp
> 
> TransLattice - http://www.translattice.com
> Distributed and Clustered Database Solutions
> 
>