Re: [Postgres-xc-general] pushing order by clause to datanodes

[Sandeep G. <gup...@gm...>]

Hi Ashutosh,


  Attached below is the query and the corresponding query plan. I am using
version 1.1.
Thanks for taking a look at this.

-Sandeep

SELECT exposed_time effectedDate, ROUND(COUNT(a.pid)/10) COUNT FROM
public.vt_demography_info_xc d, public.ses_vt_20130805_xc a WHERE
d.pid=a.pid AND d.countyid='50015' AND d.age BETWEEN 5 AND 18 AND
d.gender=1 GROUP BY exposed_time ORDER BY exposed_time;


                                   QUERY PLAN (Coordinator)

 Sort  (cost=10000000005.03..10000000005.03 rows=1 width=8)
   Output: a.exposed_time, (round(((count((count(a.pid))) /
10))::double precision))
   Sort Key: a.exposed_time
   ->  HashAggregate  (cost=5.00..5.02 rows=1 width=8)
         Output: a.exposed_time, round(((count((count(a.pid))) /
10))::double precision)
         ->  Data Node Scan on "__REMOTE_GROUP_QUERY__"
(cost=0.00..0.00 rows=1000 width=8)
               Output: a.exposed_time, (count(a.pid))
               Node/s: datanode1, datanode10, datanode11, datanode12,
datanode13, datanode14, datanode15, datanode16, datanode2, datanode3,
datanode4, datanode5, datanode6, datanode7, datanode8, datanode9
               Remote query: SELECT r.a_1, count(r.a_2) FROM ((SELECT
d.pid FROM ONLY public.vt_demography_info_xc d WHERE ((d.age >= 5) AND
(d.age <= 18) AND ((d.countyid)::text = '50015'::text) AND (d.gender =
1))) l(a_1) JOIN (SELECT a.exposed_time, a.pid FROM ONLY
public.ses_vt_20130805_xc a WHERE ((a.exposed_time >= 4667) AND
(a.exposed_time <= 5031))) r(a_1, a_2) ON (true)) WHERE (l.a_1 =
r.a_2) GROUP BY 1
(9 rows)

                                     QUERY PLAN (Datanode)

 GroupAggregate  (cost=0.00..47862.29 rows=225 width=8)
   Output: a.exposed_time, round(((count(a.pid) / 10))::double precision)
   ->  Nested Loop  (cost=0.00..47856.05 rows=460 width=8)
         Output: a.exposed_time, a.pid
         ->  Index Scan using et_ses on public.ses_vt_20130805_xc a
(cost=0.00..7283.10 rows=129583 width=8)
               Output: a.pid, a.rep, a.exposed_time,
a.infectious_time, a.recovered_time
               Index Cond: ((a.exposed_time >= 4667) AND
(a.exposed_time <= 5031))
         ->  Index Scan using pid_demo on public.vt_demography_info_xc
d  (cost=0.00..0.30 rows=1 width=4)
               Output: d.pid, d.hid, d.age, d.gender, d.zipode,
d.blockgroupid, d.longitude, d.lattitude, d.county, d.countyid
               Index Cond: (d.pid = a.pid)
               Filter: ((d.age >= 5) AND (d.age <= 18) AND
((d.countyid)::text = '50015'::text) AND (d.gender = 1))
(11 rows)




On Thu, Oct 17, 2013 at 12:12 AM, Ashutosh Bapat <
ash...@en...> wrote:

> Sandeep,
> It would be nice if you mention the version of XC in your mail. Sort push
> down is available from 1.1 onwards. If you do not see sort getting pushed
> down in 1.1, please report detailed definitions of the tables, query and
> the EXPLAIN output.
>
>
> On Thu, Oct 17, 2013 at 1:09 AM, Sandeep Gupta <gup...@gm...>wrote:
>
>> Hi,
>>
>>  In an another query that requires the result to be aggregated and
>> ordered by a field (lets say timeo)
>> the query planner currently pulls  the results and then performs a sort
>> with hash aggregate.
>>
>> The table at the datanodes are clustered by timeo. I was wondering if it
>> possible
>> for query planner to push down the order by clause at the datanode and
>> then perform
>> sort-merge aggregate at the coordinator. Surely, that would be a better
>> query plan.
>>
>> We have tried enable_sort=off etc. but that doesn't work.
>>
>> Thanks.
>> Sandeep
>>
>>
>>
>>
>> ------------------------------------------------------------------------------
>> October Webinars: Code for Performance
>> Free Intel webinars can help you accelerate application performance.
>> Explore tips for MPI, OpenMP, advanced profiling, and more. Get the most
>> from
>> the latest Intel processors and coprocessors. See abstracts and register >
>>
>> http://pubads.g.doubleclick.net/gampad/clk?id=60135031&iu=/4140/ostg.clktrk
>> _______________________________________________
>> Postgres-xc-general mailing list
>> Pos...@li...
>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-general
>>
>>
>
>
> --
> Best Wishes,
> Ashutosh Bapat
> EnterpriseDB Corporation
> The Postgres Database Company
>