Skip to content
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

Closed
firebird-automations opened this issue May 18, 2015 · 14 comments

Comments

@firebird-automations
Copy link
Collaborator

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

@firebird-automations
Copy link
Collaborator Author

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);
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;
commit;
create index test_x on test(x);
commit;
exit;

Test:

SQL> set plan on;
SQL> out nul; select min(a.x) from test a natural join test b natural join test c; out;

Results for 3.0:

ISQL: PLAN HASH (JOIN (B NATURAL, A INDEX (TEST_X)), C NATURAL)

Trace:
select min(a.x) from test a natural join test b natural join test c
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Select Expression
-> Aggregate
-> Filter
-> Hash Join (inner)
-> 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)
-> Record Buffer (record length: 25)
-> Table "TEST" as "C" Full Scan
1 records fetched
18077 ms, 68 read(s), 546879 fetch(es)

Table Natural Index Update Insert
**************************************************************************
TEST 10000 255720

Results for 2.5:

ISQL: PLAN MERGE (SORT (JOIN (A NATURAL, B INDEX (TEST_X))), SORT (C NATURAL))

Trace:
2015-05-18T02:18:11.9370 (1680:0129DF74) EXECUTE_STATEMENT_FINISH
C:\FBTESTING\QA\FBT-REPO\TMP\E25.FDB (ATT_12, SYSDBA:NONE, NONE, TCPv4:127.0.0.1)
C:\1INSTALL\FIREBIRD\fb25sC\bin\isql.exe:3408
(TRA_141, CONCURRENCY | WAIT | READ_WRITE)

Statement 35:
-------------------------------------------------------------------------------
select min(a.x) from test a natural join test b natural join test c
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN MERGE (SORT (JOIN (A NATURAL, B INDEX (TEST_X))), SORT (C NATURAL))
1 records fetched
14988 ms, 67 read(s), 543281 fetch(es)

Table Natural Index Update Insert Delete Backout
*********************************************************************************************
TEST 10000 253906

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: 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

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Version: 3.0 Beta 1 [ 10332 ]

Version: 3.0 Beta 2 [ 10586 ] =>

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 3.0 Beta 2 [ 10586 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: Done successfully

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Closed [ 6 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @sim1984

This ticket also solves another ticket CORE4428. In fact it is one and the same ticket. Please close CORE4428, too

@firebird-automations
Copy link
Collaborator Author

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.
Tables T1 and T2 are filled with different records count: 100:100'000; 1'000:100'000; 10'000: 100'000; 50'000:100'000 and 100'000:100'000 rows. Total test time is about 1 minute.
Build #⁠31852 shows that for all mentioned cases ratio is very close to 1.000, i.e. all works fine.

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

Unless Denis objects, I think we may close CORE4428 in favor of this one, all the tests should be added for this ticket.

@firebird-automations
Copy link
Collaborator Author

Commented by: @sim1984

Pavel,

CORE4428 is a duplicate of the ticket, see its essence.

@firebird-automations
Copy link
Collaborator Author

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'.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue is duplicated by CORE4428 [ CORE4428 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

CORE4428 is resolved as a duplicate and linked to this ticket.

@firebird-automations
Copy link
Collaborator Author

Commented by: @sim1984

Dmitry Yemanov,

Yes of course.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants