[Postgres-xl-bugs] postgres-XL can't push down the computing of window function to data nodes?

Mason S masonlists at gmail.com
Wed Mar 11 16:40:50 PDT 2015


On Sat, Mar 7, 2015 at 5:16 AM, Kaijiang Chen <chenkaijiang at gmail.com>
wrote:

> Hi,
>
> I have 1 coordinator and 2 data nodes. I create a table as:
>
> create table kakou (uid int, timest as int, kkname as text) distribute by
> hash(uid);
>
> explain select uid,name1,time1,name2,time2 from
>
> ( select r1.uid as uid, r1.kkname as name1,r1.timest as time1, r2.kkname
> as name2, r2.timest as time2, rank()
> over (*partition by r1.uid* order by r1.timest desc)
> as pos from kakou as r1 join kakou as r2 on r1.uid = r2.uid where
> r1.kkname < r2.kkname and abs(r1.timest-r2.timest) < 30 and r1.timest>10
> and r2.timest>10 )
>
> as tmp1 where pos=1;
>
> I got:
>
>  Subquery Scan on tmp1  (cost=34.17..86.31 rows=1 width=76)
>    Filter: (tmp1.pos = 1)
> *   ->  WindowAgg  (cost=34.17..85.28 rows=83 width=76)*
> *         ->  Remote Subquery Scan on all (dn1,dn2)  (cost=34.17..83.82
> rows=83 *width=76)
>                ->  Sort  (cost=86.47..86.68 rows=83 width=76)
>                      Sort Key: r1.uid, r1.timest
>                      ->  Hash Join  (cost=34.17..83.82 rows=83 width=76)
>                            Hash Cond: (r1.uid = r2.uid)
>                            Join Filter: ((r1.kkname < r2.kkname) AND
> (abs((r1.timest - r2.timest)) < 30))
>                            ->  Bitmap Heap Scan on kakou r1
>  (cost=7.25..22.09 rows=387 width=40)
>                                  Recheck Cond: (timest > 10)
>                                  ->  Bitmap Index Scan on kakou_timest
>  (cost=0.00..7.15 rows=387 width=0)
>                                        Index Cond: (timest > 10)
>                            ->  Hash  (cost=22.09..22.09 rows=387 width=40)
>                                  ->  Bitmap Heap Scan on kakou r2
>  (cost=7.25..22.09 rows=387 width=40)
>                                        Recheck Cond: (timest > 10)
>                                        ->  Bitmap Index Scan on
> kakou_timest  (cost=0.00..7.15 rows=387 width=0)
>                                              Index Cond: (timest > 10)
>
> I think the computing of the WindowAgg can be done in 2 data nodes because
> the WindowAgg uses r1.uid (the distribution key) as the aggregate key. But
> in the above XL's plan, the WindowAgg is done in coordinator.
>
> Pushing down the WindowAgg to data nodes should be a performance boost.
>

Thank you.

There may be some tree walking code we can leverage to check if we pushed
down joins, and if so, and the partition clause is over the distribution
column, to push down the WindowAgg.

For now I have added this here:

https://sourceforge.net/p/postgres-xl/tickets/52/

 Thanks,

Mason
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.postgres-xl.org/private.cgi/postgres-xl-bugs-postgres-xl.org/attachments/20150311/e756e1e0/attachment.htm>


More information about the postgres-xl-bugs mailing list