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

[Ashutosh B. <ash...@en...>]

There is GROUP BY clause that needs to be evaluated before the result can
be ordered. Thus GROUP BY is sent to the datanode but not ORDER BY.


On Thu, Oct 17, 2013 at 4:31 PM, Sandeep Gupta <gup...@gm...>wrote:

> 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
>>
>
>


-- 
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company