postgres-xl left join takes too long to execute

Question

Postgres-XL 9.5r1.6 consists of a gtm, a coordinator and two datanodes.

There are three tables a, b and c which implements a many-to-many relationship:

create table a(id int, name text, uid int) distribute by hash(uid);
create table b(id int, name text, uid int) distribute by hash(uid);
create table c(id int, aname text, bname text, uid int) distribute by hash(uid);

when run following query on coordinator it takes inexplicable time of 20000 milliseconds! but on either datanodes execution time is hardly more than 20 milliseconds.

select a.name, b.name

from 
       a left join c
       on a.name=c.aname

          left join b
          on c.bname=b.name
where
       a.name='cf82c96b77b8aa5277da6d55c4e4e66e';

explain plan on coordinator:

Remote Subquery Scan on all (dn_1,dn_2)  (cost=8.33..17.78 rows=1 width=66)


 ->  Nested Loop Left Join  (cost=8.33..17.78 rows=1 width=66)
         Join Filter: ((a.name)::text = (c.aname)::text)
         ->  Remote Subquery Scan on all (dn_1,dn_2)  (cost=100.15..108.21 rows=1 width=33)
               Distribute results by H: name
               ->  Index Only Scan using code_idx on a  (cost=0.15..8.17 rows=1 width=33)
                     Index Cond: (name = 'cf82c96b77b8aa5277da6d55c4e4e66e'::text)
         ->  Materialize  (cost=108.18..109.72 rows=1 width=115)
               ->  Remote Subquery Scan on all (dn_1,dn_2)  (cost=108.18..109.72 rows=1 width=115)
                     Distribute results by H: aname
                     ->  Hash Right Join  (cost=8.18..9.60 rows=1 width=115)
                           Hash Cond: ((b.name)::text = (c.bname)::text)
                           ->  Remote Subquery Scan on all (dn_1,dn_2)  (cost=100.00..102.44 rows=30 width=33)
                                 Distribute results by H: name
                                 ->  Seq Scan on b  (cost=0.00..1.30 rows=30 width=33)
                           ->  Hash  (cost=108.41..108.41 rows=1 width=244)
                                 ->  Remote Subquery Scan on all (dn_1,dn_2)  (cost=100.15..108.41 rows=1 width=244)
                                       Distribute results by H: bname
                                       ->  Index Only Scan using code_idxcfc on c  (cost=0.15..8.17 rows=1 width=244)
                                             Index Cond: (aname = 'cf82c96b77b8aa5277da6d55c4e4e66e'::text)

some other guy already hit this problem and asked here but with no answer or hint. I'm just hoping this time the question gets some insight.

ps: I tried to fill the three tables in a way that related rows from a and b which form table c only come from same datanode. But the execution time showed no improvment. Other point worth noting is that when condition in where clause (a.name='cf82c96b77b8aa5277da6d55c4e4e66e') is always false, then the execution time drop low less than few milliseconds.

Gordon Linoff · Answer 1 · 2018-07-11T12:26:04.860

0

For this query:

select a.name, b.name
from a left join
     c
     on a.name = c.aname left join
     b
     on c.bname = b.name
where a.name = 'cf82c96b77b8aa5277da6d55c4e4e66e';

You want indexes on a(name), b(name), and c(name). Your partitions are not going to help this query and you should only keep them if the tables are really big.

edited Jul 11 '18 at 12:26

answered Jul 10 '18 at 14:19

Gordon Linoff

1,242,037
58
646
786

Actually I already tried building indexes with no tangible impact on the execution time, And indeed tables are growing fast (social network application db) so clustering was an indispensable approach. – Ali Jul 11 '18 at 04:44

score 0 · Answer 2 · answered Mar 30 '20 at 21:26

0

This is due to the nested loop, set it to false. XL will use hash join and then it will return results fastly

answered Mar 30 '20 at 21:26

Islacine

105
2
9

postgres-xl left join takes too long to execute

2 Answers2