New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Regression: MIN/MAX with a join ignores possible index navigation [CORE4798] #5096
Comments
Commented by: @pavel-zotov Replacement JOIN condition from 'ON <expr>' to NATURAL or USING (x) unexpected improves performance of 3.0 from 58" to 17", but also makes performance of 2.5 very poor - down to 3.0 one. This is simplified DDL:recreate table test(x int); 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; Test:SQL> set plan on; Results for 3.0:ISQL: PLAN HASH (JOIN (B NATURAL, A INDEX (TEST_X)), C NATURAL) Trace: Table Natural Index Update Insert Results for 2.5:ISQL: PLAN MERGE (SORT (JOIN (A NATURAL, B INDEX (TEST_X))), SORT (C NATURAL)) Trace: Statement 35: Table Natural Index Update Insert Delete Backout |
Modified by: @dyemanovassignee: Dmitry Yemanov [ dimitr ] summary: Regression: ineffective plan and bad performance when doing inner join of three data sources on indexed field (regardless of this field uniquenes) => Regression: MIN/MAX with a join ignores possible index navigation |
Modified by: @dyemanovstatus: Open [ 1 ] => Resolved [ 5 ] resolution: Fixed [ 1 ] Fix Version: 3.0 Beta 2 [ 10586 ] |
Modified by: @pavel-zotovstatus: Resolved [ 5 ] => Resolved [ 5 ] QA Status: Done successfully Test Details: For table with 2500 rows statistics was: |
Modified by: @pavel-zotovstatus: Resolved [ 5 ] => Closed [ 6 ] |
Commented by: @pavel-zotov Denis, I've prepares test for CORE4428 and ready to commit it. It will check ratios between natural_reads, indexed_reads and page_fetches for all three queries. But this ticket still not has Resolution = 'fixed' . As soon as it will be in that state and if dimitr will not denied such addition, I will add this test. |
Commented by: @pavel-zotov Yes, I see it. New code will be added to the test for THIS ticket and there I'll put "reference message" for that - but first it (CORE4428) should be with resolution = 'fixed'. |
Commented by: @sim1984 Dmitry Yemanov, Yes of course. |
Submitted by: @pavel-zotov
Is duplicated by CORE4428
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
Commits: 70f16f1 FirebirdSQL/fbt-repository@547ed0b
====== Test Details ======
For table with 2500 rows statistics was:
3.0.0.31374 (Beta 1):
natural_reads 2500
indexed_reads 1839808
3.0.0.31852:
natural_reads 0
indexed_reads 6
The text was updated successfully, but these errors were encountered: