Issue Details (XML | Word | Printable)

Key: CORE-4798
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Dmitry Yemanov
Reporter: Pavel Zotov
Votes: 0
Watchers: 4
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Regression: MIN/MAX with a join ignores possible index navigation

Created: 17/May/15 10:55 PM   Updated: 02/Jun/15 01:10 PM
Component/s: Engine
Affects Version/s: 3.0 Beta 1
Fix Version/s: 3.0 Beta 2

Issue Links:
Duplicate
 

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


 Description  « Hide
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

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Pavel Zotov added a comment - 17/May/15 11:19 PM
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

Simonov Denis added a comment - 02/Jun/15 06:36 AM
This ticket also solves another ticket CORE-4428. In fact it is one and the same ticket. Please close CORE-4428, too

Pavel Zotov added a comment - 02/Jun/15 12:35 PM
Denis,

I've prepares test for CORE-4428 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.

Dmitry Yemanov added a comment - 02/Jun/15 12:58 PM
Unless Denis objects, I think we may close CORE-4428 in favor of this one, all the tests should be added for this ticket.

Simonov Denis added a comment - 02/Jun/15 12:58 PM
Pavel,

CORE-4428 is a duplicate of the ticket, see its essence.

Pavel Zotov added a comment - 02/Jun/15 01:06 PM
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 (CORE-4428) should be with resolution = 'fixed'.

Dmitry Yemanov added a comment - 02/Jun/15 01:10 PM
CORE-4428 is resolved as a duplicate and linked to this ticket.

Simonov Denis added a comment - 02/Jun/15 01:10 PM
Dmitry Yemanov,

Yes of course.