[Postgres-xl-general] complex query

武二亮 wuel at neusoft.com
Mon Mar 2 18:42:42 PST 2015


Hi Mason,
I've tried the patch.
                                                                                      QUERY PLAN 
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Remote Subquery Scan on all (src84,src85,src86)  (cost=200.00..9826404.07 rows=1 width=2015)
   ->  Nested Loop  (cost=200.00..9826404.07 rows=1 width=2015)
         Join Filter: (((kc24.aae072)::text = (kc60.aae072)::text) AND ((kc24.bka135)::text = (kc60.bka135)::text))
         ->  Remote Subquery Scan on all (src84,src85,src86)  (cost=200.00..9820131.16 rows=299 width=1158)
               Distribute results by R
               ->  Nested Loop  (cost=100.00..9818990.94 rows=299 width=1158)
                     Join Filter: ((kc21.akc193)::text = (ka06.akc193)::text)
                     ->  Nested Loop  (cost=100.00..9817230.45 rows=1138 width=1132)
                           ->  Remote Subquery Scan on all (src84,src85,src86)  (cost=100.00..5616017.11 rows=272986 width=591)
                                 Distribute results by R
                                 ->  Seq Scan on kc24  (cost=0.00..5130548.00 rows=272986 width=591)
                                       Filter: ((ake010 >= '2014-01-01'::date) AND (ake010 <= '2015-01-01'::date) AND ((COALESCE(bkc380, '0'::character varying))::text
 = '0'::text))
                           ->  Index Scan using pk_kc21 on kc21  (cost=0.00..15.38 rows=1 width=541)
                                 Index Cond: (((akb020)::text = (kc24.akb020)::text) AND ((akc190)::text = (kc24.akc190)::text))
                     ->  Materialize  (cost=0.00..2.55 rows=103 width=26)
                           ->  Seq Scan on ka06  (cost=0.00..2.03 rows=103 width=26)
         ->  Index Scan using pk_kc60 on kc60  (cost=0.00..20.96 rows=1 width=857)
               Index Cond: (((akb020)::text = (kc21.akb020)::text) AND ((akc190)::text = (kc21.akc190)::text))
(18 rows)






From: 武二亮
Date: 2015-03-03 08:36
To: Mason Sharp
CC: postgres-xl-general; 赵伟
Subject: Re: Re: [Postgres-xl-general] complex query
Hi Mason,
All of the tables are distributed on the same set of datanodes in the cluster(hash by akc190).
I will try the patch,then provide feedback.
Thanks.

to Andrei,
The data types of akc190 is character varying(24) .

wuel






From: Mason Sharp
Date: 2015-03-03 04:54
To: 武二亮
CC: postgres-xl-general; 赵伟
Subject: Re: [Postgres-xl-general] complex query




On Sun, Mar 1, 2015 at 9:30 PM, 武二亮 <wuel at neusoft.com> wrote:

Hi,
I've made a  query test on Postgres-XL, but the response time is very long.  Is there a crossing datanode join in the query plan? How can I optimize it ?
SQL:
select * from kc21, kc24, ka06, kc60
where kc21.akb020 = kc24.akb020
and kc21.akc190 = kc24.akc190
and kc24.akb020 = kc60.akb020
and kc24.akc190 = kc60.akc190
and kc24.aae072 = kc60.aae072
and kc24.bka135 = kc60.bka135
and COALESCE(kc24.bkc380, '0') = '0'
and kc24.ake010 between date'2014-1-1'and date '2015-1-1'
and kc21.akc193 = ka06.akc193;

Query plan :
Remote Subquery Scan on all (src84,src85,src86)  (cost=0.00..9339794.74 rows=1 width=2015)
   ->  Nested Loop  (cost=0.00..9339794.74 rows=1 width=2015)
         Join Filter: (((kc24.aae072)::text = (kc60.aae072)::text) AND ((kc24.bka135)::text = (kc60.bka135)::text))
         ->  Nested Loop  (cost=0.00..9333521.84 rows=299 width=1158)
               Join Filter: ((kc21.akc193)::text = (ka06.akc193)::text)
               ->  Nested Loop  (cost=0.00..9331761.34 rows=1138 width=1132)
                     ->  Remote Subquery Scan on all (src84,src85,src86)  (cost=100.00..5293347.66 rows=272986 width=591)
                           ->  Seq Scan on kc24  (cost=0.00..5130548.00 rows=272986 width=591)
                                 Filter: ((ake010 >= '2014-01-01'::date) AND (ake010 <= '2015-01-01'::date) AND ((COALESCE(bkc380, '0'::character varying))::text = '0'
::text))
                     ->  Materialize  (cost=100.00..115.93 rows=1 width=541)
                           ->  Remote Subquery Scan on all (src84,src85,src86)  (cost=100.00..115.93 rows=1 width=541)
                                 ->  Index Scan using pk_kc21 on kc21  (cost=0.00..15.38 rows=1 width=541)
                                       Index Cond: (((akb020)::text = (kc24.akb020)::text) AND ((akc190)::text = (kc24.akc190)::text))
               ->  Materialize  (cost=0.00..2.55 rows=103 width=26)
                     ->  Seq Scan on ka06  (cost=0.00..2.03 rows=103 width=26)
         ->  Index Scan using pk_kc60 on kc60  (cost=0.00..20.96 rows=1 width=857)
               Index Cond: (((akb020)::text = (kc21.akb020)::text) AND ((akc190)::text = (kc21.akc190)::text))

The tables of kc21,kc24 and kc60 are distributed by hash akc190, every table have 50,000,000 rows. The table of ka06 is replication table,have 103 rows.



If the distribution scheme is as you say, then this looks like a planner bug. I have not seen a bad plan like this one. Are all of the tables distributed on the same set of datanodes (all in the cluster?) 


Note that there is a separate planner bug that we have a patch for, but it introduces test regressions. Could you please try and see if it helps your particular issue and provide feedback? Again, try out in a test environment as it will cause some other problems, but it is useful to know that it helps.


Thanks,


Mason
---------------------------------------------------------------------------------------------------
Confidentiality Notice: The information contained in this e-mail and any accompanying attachment(s) 
is intended only for the use of the intended recipient and may be confidential and/or privileged of 
Neusoft Corporation, its subsidiaries and/or its affiliates. If any reader of this communication is 
not the intended recipient, unauthorized use, forwarding, printing,  storing, disclosure or copying 
is strictly prohibited, and may be unlawful.If you have received this communication in error,please 
immediately notify the sender by return e-mail, and delete the original message and all copies from 
your system. Thank you. 
---------------------------------------------------------------------------------------------------
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.postgres-xl.org/pipermail/postgres-xl-general-postgres-xl.org/attachments/20150303/1f84528e/attachment.htm>


More information about the postgres-xl-general mailing list