Menu
▾
▴
Re: [Postgres-xc-developers] Bug in FQS
|
From: Ahsan H. <ahs...@en...> - 2013-07-19 06:45:42
|
Hi Pavan, Can you review the patch from Ashutosh and see if that addresses the issue you have raised. -- Ahsan On Thu, Jul 18, 2013 at 1:01 PM, Ashutosh Bapat < ash...@en...> wrote: > Hi, > There was a small mistake in calling pgxc_is_join_reducible(), which > checks whether a given join is reducible or not; the arguments were passed > in wrong order. Thus a LEFT_JOIN turned into INNER_JOIN. Attached is the > patch to fix the issue. The patch is based on master, and should be > backpatched to stable branch. > > > On Thu, Jul 11, 2013 at 2:18 PM, Pavan Deolasee <pav...@gm...>wrote: > >> Hello All, >> >> The following case shows a bug in the FQS logic. I am using fairly recent >> master branch (may be a few days old). So if this has been fixed recently, >> please let me know: >> >> test=# CREATE TABLE ltab_repl (a int, b char(10)) DISTRIBUTE BY >> REPLICATION; >> CREATE TABLE >> test=# CREATE TABLE rtab (ar int, br char(10)); >> CREATE TABLE >> test=# INSERT INTO ltab_repl SELECT generate_series(1,5), 'foo'; >> INSERT 0 5 >> test=# INSERT INTO rtab SELECT generate_series(1,4), 'bar'; >> INSERT 0 4 >> test=# SELECT * FROM ltab_repl ; >> a | b >> ---+------------ >> 1 | foo >> 2 | foo >> 3 | foo >> 4 | foo >> 5 | foo >> (5 rows) >> >> test=# SELECT * FROM rtab; >> ar | br >> ----+------------ >> 1 | bar >> 2 | bar >> 3 | bar >> 4 | bar >> (4 rows) >> >> >> test=# set enable_fast_query_shipping TO off; >> SET >> test=# SELECT * FROM ltab_repl LEFT JOIN rtab ON (a = ar); >> a | b | ar | br >> ---+------------+----+------------ >> 1 | foo | 1 | bar >> 2 | foo | 2 | bar >> 3 | foo | 3 | bar >> 4 | foo | 4 | bar >> 5 | foo | | >> (5 rows) >> >> test=# set enable_fast_query_shipping TO on; >> SET >> test=# SELECT * FROM ltab_repl LEFT JOIN rtab ON (a = ar); >> a | b | ar | br >> ---+------------+----+------------ >> 1 | foo | 1 | bar >> 2 | foo | 2 | bar >> 3 | foo | | >> 4 | foo | | >> 5 | foo | | >> 1 | foo | | >> 2 | foo | | >> 3 | foo | 3 | bar >> 4 | foo | 4 | bar >> 5 | foo | | >> (10 rows) >> >> test=# EXPLAIN VERBOSE SELECT * FROM ltab_repl LEFT JOIN rtab ON (a = ar); >> QUERY >> PLAN >> >> ---------------------------------------------------------------------------------------------------------------------------------------------- >> Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 >> width=0) >> Output: ltab_repl.a, ltab_repl.b, rtab.ar, rtab.br >> Node/s: d1, d2 >> Remote query: SELECT ltab_repl.a, ltab_repl.b, rtab.ar, rtab.br FROM >> (public.ltab_repl LEFT JOIN public.rtab ON ((ltab_repl.a = rtab.ar))) >> (4 rows) >> >> >> As you would see the query is giving a wrong result when FQS is turned >> ON. I don't think its correct to push down the query as it is to the >> datanodes. What's happening really is that each datanode is returning 5 >> rows each (since ltab_repl is a replicated table, contains 5 rows and is at >> the left side of the left join) and they are being appended together. >> >> I think I'd suggested in the past to run regression by turning these >> various optimization GUCs on/off and comparing the results. While we might >> see some row ordering issues when these GUCs are turned on/off, the final >> result should remain the same. Such an exercise will help to uncover such >> bugs. >> >> Thanks, >> Pavan >> >> -- >> Pavan Deolasee >> http://www.linkedin.com/in/pavandeolasee >> >> >> ------------------------------------------------------------------------------ >> See everything from the browser to the database with AppDynamics >> Get end-to-end visibility with application monitoring from AppDynamics >> Isolate bottlenecks and diagnose root cause in seconds. >> Start your free trial of AppDynamics Pro today! >> >> http://pubads.g.doubleclick.net/gampad/clk?id=48808831&iu=/4140/ostg.clktrk >> _______________________________________________ >> Postgres-xc-developers mailing list >> Pos...@li... >> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers >> >> > > > -- > Best Wishes, > Ashutosh Bapat > EntepriseDB Corporation > The Postgres Database Company > > > ------------------------------------------------------------------------------ > See everything from the browser to the database with AppDynamics > Get end-to-end visibility with application monitoring from AppDynamics > Isolate bottlenecks and diagnose root cause in seconds. > Start your free trial of AppDynamics Pro today! > http://pubads.g.doubleclick.net/gampad/clk?id=48808831&iu=/4140/ostg.clktrk > _______________________________________________ > Postgres-xc-developers mailing list > Pos...@li... > https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers > > -- Ahsan Hadi Snr Director Product Development EnterpriseDB Corporation The Enterprise Postgres Company Phone: +92-51-8358874 Mobile: +92-333-5162114 Website: www.enterprisedb.com EnterpriseDB Blog: http://blogs.enterprisedb.com/ Follow us on Twitter: http://www.twitter.com/enterprisedb This e-mail message (and any attachment) is intended for the use of the individual or entity to whom it is addressed. This message contains information from EnterpriseDB Corporation that may be privileged, confidential, or exempt from disclosure under applicable law. If you are not the intended recipient or authorized to receive this for the intended recipient, any use, dissemination, distribution, retention, archiving, or copying of this communication is strictly prohibited. If you have received this e-mail in error, please notify the sender immediately by reply e-mail and delete this message. |
