Force optimizer to consider usage of HASH JOIN when number of data sources is
three and more and they are joined on USING(<col>) or by NATURAL clauses
------------------------------------------------------------------------------------------------------------------------------------------------------
Key: CORE-4809
URL: http://tracker.firebirdsql.org/browse/CORE-4809
Project: Firebird Core
Issue Type: Improvement
Affects Versions: 3.0 Beta 2
Reporter: Pavel Zotov
Priority: Minor
recreate table tn(x int primary key using index tn_x);
commit;
insert into tn select row_number()over() from rdb$types;
commit;
set planonly;
----------- test `traditional` join form -----------------
select count(*)
from (select rdb$db_key||'' a from tn) r
join (select rdb$db_key||'' a from tn) s on r.a = s.a;
PLAN HASH (S TN NATURAL, R TN NATURAL)
select count(*)
from (select rdb$db_key||'' a from tn) r
join (select rdb$db_key||'' a from tn) s on r.a = s.a
join (select rdb$db_key||'' a from tn) t on s.a = t.a;
PLAN HASH (HASH (T TN NATURAL, S TN NATURAL), R TN NATURAL)
select count(*)
from (select rdb$db_key||'' a from tn) r
join (select rdb$db_key||'' a from tn) s on r.a = s.a
join (select rdb$db_key||'' a from tn) t on s.a = t.a
join (select rdb$db_key||'' a from tn) u on t.a = u.a;
PLAN HASH (HASH (HASH (U TN NATURAL, T TN NATURAL), S TN NATURAL), R TN NATURAL)
Conclusion: ALL OK for this form of joins, optimizer DOES take in account
ability to apply HJ regardless of data sources number.
----------- test join on named columns form -----------------
select count(*)
from (select rdb$db_key||'' a from tn) r
join (select rdb$db_key||'' a from tn) s using(a);
PLAN HASH (S TN NATURAL, R TN NATURAL)
select count(*)
from (select rdb$db_key||'' a from tn) r
join (select rdb$db_key||'' a from tn) s using(a)
join (select rdb$db_key||'' a from tn) t using(a);
PLAN JOIN (HASH (S TN NATURAL, R TN NATURAL), T TN NATURAL) ----------- NESTED
LOOPS on outer phase
select count(*)
from (select rdb$db_key||'' a from tn) r
join (select rdb$db_key||'' a from tn) s using(a)
join (select rdb$db_key||'' a from tn) t using(a)
join (select rdb$db_key||'' a from tn) u using(a);
PLAN JOIN (HASH (S TN NATURAL, R TN NATURAL), T TN NATURAL, U TN NATURAL)
----------- NESTED LOOPS on outer phase
----------- test natural join form -----------------
select count(*)
from (select rdb$db_key||'' a from tn) r
natural join (select rdb$db_key||'' a from tn) s;
PLAN HASH (S TN NATURAL, R TN NATURAL)
select count(*)
from (select rdb$db_key||'' a from tn) r
natural join (select rdb$db_key||'' a from tn) s
natural join (select rdb$db_key||'' a from tn) t;
PLAN JOIN (HASH (S TN NATURAL, R TN NATURAL), T TN NATURAL) ----------- NESTED
LOOPS on outer phase
select count(*)
from (select rdb$db_key||'' a from tn) r
natural join (select rdb$db_key||'' a from tn) s
natural join (select rdb$db_key||'' a from tn) t
natural join (select rdb$db_key||'' a from tn) u;
PLAN JOIN (HASH (S TN NATURAL, R TN NATURAL), T TN NATURAL, U TN NATURAL)
----------- NESTED LOOPS on outer phase
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
One dashboard for servers and applications across Physical-Virtual-Cloud
Widest out-of-the-box monitoring support with 50+ applications
Performance metrics, stats and reports that give you Actionable Insights
Deep dive visibility with transaction tracing using APM Insight.
http://ad.doubleclick.net/ddm/clk/290420510;117567292;y
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
