Regression: ineffective plan and bad performance when doing inner join of three
data sources on indexed field (regardless of this field uniquenes)
--------------------------------------------------------------------------------------------------------------------------------------------------
Key: CORE-4798
URL: http://tracker.firebirdsql.org/browse/CORE-4798
Project: Firebird Core
Issue Type: Bug
Components: Engine
Affects Versions: 3.0 Beta 2
Reporter: Pavel Zotov
DDL:
====
create or alter view vmon as
select r.mon$record_seq_reads seq_reads,r.mon$record_idx_reads idx_reads
from mon$attachments a left join mon$record_stats r on
a.mon$stat_id=r.mon$stat_id
where a.mon$attachment_id = current_connection;
commit;
recreate table test(x int);
commit;
set term ^;
execute block as begin execute statement 'drop sequence g'; when any do begin
end end^
set term ;^
commit;
create sequence g;
commit;
insert into test select rand()*100 from (select 1 i from rdb$types rows 50) a,
(select 1 i from rdb$types rows 100) b; -------------[ 1 ]
-- insert into test select gen_id(g,1) from (select 1 i from rdb$types rows 50)
a, (select 1 i from rdb$types rows 100) b; ------------- [ 2 ]
commit;
create index test_x on test(x);
commit;
set list on;
select count(*) from test;
select rdb$statistics from rdb$indices where rdb$index_name = upper('test_x');
TEST:
=====
SQL> set plan on;
SQL> out nul; select min(a.x) from test a join test b on a.x = b.x join test c
on b.x = c.x; out;
Result in 2.5:
==========
PLAN JOIN (A ORDER TEST_X, B INDEX (TEST_X), C INDEX (TEST_X))
Trace:
--------
2015-05-18T01:44:41.9680 (1680:0129DF74) EXECUTE_STATEMENT_FINISH
C:\FBTESTING\QA\FBT-REPO\TMP\E25.FDB (ATT_4, SYSDBA:NONE, NONE,
TCPv4:127.0.0.1)
C:\1INSTALL\FIREBIRD\fb25sC\bin\isql.exe:4008
(TRA_19, CONCURRENCY | WAIT | READ_WRITE)
Statement 35:
-------------------------------------------------------------------------------
select min(a.x) from test a join test b on a.x = b.x join test c on b.x = c.x
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN JOIN (A ORDER TEST_X, B INDEX (TEST_X), C INDEX (TEST_X))
1 records fetched
0 ms, 3 read(s), 15 fetch(es)
Table Natural Index Update Insert
Delete Backout Purge Expunge
***************************************************************************************************************
TEST 3
Result in 3.0:
==========
PLAN JOIN (B NATURAL, A INDEX (TEST_X), C INDEX (TEST_X))
2015-05-18T01:42:52.9530 (1776:01D63A68) EXECUTE_STATEMENT_FINISH
C:\FBTESTING\QA\FBT-REPO\TMP\E30.FDB (ATT_12, SYSDBA:NONE, NONE,
TCPv4:127.0.0.1)
C:\1INSTALL\FIREBIRD\fb30sc\isql.exe:3764
(TRA_65, CONCURRENCY | WAIT | READ_WRITE)
Statement 25:
-------------------------------------------------------------------------------
select min(a.x) from test a join test b on a.x = b.x join test c on b.x = c.x
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Select Expression
-> Aggregate
-> Nested Loop Join (inner)
-> Table "TEST" as "B" Full Scan
-> Filter
-> Table "TEST" as "A" Access By ID
-> Bitmap
-> Index "TEST_X" Range Scan (full match)
-> Filter
-> Table "TEST" as "C" Access By ID
-> Bitmap
-> Index "TEST_X" Range Scan (full match)
1 records fetched
58370 ms, 68 read(s), 27538123 fetch(es)
Table Natural Index Update Insert
Delete Backout Purge Expunge
***************************************************************************************************************
TEST 5000 13371384
Result when change
insert into test select rand()*100 from (select 1 i from rdb$types rows 50) a,
(select 1 i from rdb$types rows 100) b; -------------[ 1 ]
on:
insert into test select gen_id(g,1) from (select 1 i from rdb$types rows 50) a,
(select 1 i from rdb$types rows 100) b; ------------- [ 2 ]
-- similar.
PS. WI-T3.0.0.31839
--
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
