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

IIF function prevents the condition from being pushed into the union for better optimization [CORE4927] #5218

Closed
firebird-issue-importer opened this issue Sep 7, 2015 · 8 comments

Comments

@firebird-issue-importer
Copy link

@firebird-issue-importer firebird-issue-importer commented Sep 7, 2015

Submitted by: @pavel-zotov

Relate to CORE4937

Attachments:
join-with-unioned-view-ddl.zip

Run preparing script from attached zip.
There is table 'master', several tables with name like 'detail_NNNN' and view 'vd_union' that is defined as UNION of these detail tables.

Each table 'detail_NNNN' has compound index.
Outer join from derived table based on 'master' (as driving data source) and view 'vd_union' will produce NL (index scans with full matching).
In 3.0 explained plan will be like this:

    \-\>  Nested Loop Join \(outer\)
        \-\> Table "MASTER" as "D D" Full Scan
        \-\> Filter
            \-\> Union
                \-\> Filter
                    \-\> Table "DETAIL\_1000" as "QD Q" Access By ID
                        \-\> Bitmap
                            \-\> Index "D1000\_WSRS" Range Scan \(full match\)
                \-\> Filter
                    \-\> Table "DETAIL\_1200" as "QD Q" Access By ID
                        \-\> Bitmap
                            \-\> Index "D1200\_WSRS" Range Scan \(full match\)
                \-\> Filter
                    \-\> Table "DETAIL\_2000" as "QD Q" Access By ID
                        \-\> Bitmap
                            \-\> Index "D2000\_WSRS" Range Scan \(full match\)
                \-\> Filter
                    \-\> Table "DETAIL\_2100" as "QD Q" Access By ID
                        \-\> Bitmap
                            \-\> Index "D2100\_WSRS" Range Scan \(full match\)
                \-\> Filter
                    \-\> Table "DETAIL\_3300" as "QD Q" Access By ID
                        \-\> Bitmap
                            \-\> Index "D3300\_WSRS" Range Scan \(full match\)

Table 'master' from attached .zip contains such data that have matching only in ONE of detail tables - "detail_2100". NO other tables has any row that could be matched in 'master', so NO other tables except "detail_2100" should be scanned during execution of query with plan which is shown above.

Now run in 2.5 following.

TEST-1
#⁠#⁠#⁠#⁠#⁠#⁠

select count(*)
from (
select
d.dd_id,
d.ware_id,
2100 as snd_optype_id ------------------------ LITERAL HERE
from master d
) d
left join vd_union qd on
qd.ware_id = d.ware_id
and qd.snd_optype_id = d.snd_optype_id
and qd.rcv_optype_id is not distinct from 3300
and qd.snd_id = d.dd_id;

TRACE in 2.5:

PLAN JOIN (D D NATURAL(QD Q INDEX (D1000_WSRS))
PLAN (QD Q INDEX (D1200_WSRS))
PLAN (QD Q INDEX (D2000_WSRS))
PLAN (QD Q INDEX (D2100_WSRS))
PLAN (QD Q INDEX (D3300_WSRS)))
1 records fetched
6 ms, 1411 fetch(es)

Table Natural Index
****************************************************
MASTER 44
DETAIL_2100 440

-- so far so good. Engine scanned only ONE table and skipped all others.

TEST-2
#⁠#⁠#⁠#⁠#⁠#⁠

select count(*)
from (
select
d.dd_id,
d.ware_id,
iif(1 = 0, 3300, 2100) as snd_optype_id -------------------- EVALUATION EXPRESSION HERE
----- this was before: 2100 as snd_optype_id
from master d
) d
left join vd_union qd on
qd.ware_id = d.ware_id
and qd.snd_optype_id = d.snd_optype_id
and qd.rcv_optype_id is not distinct from 3300
and qd.snd_id = d.dd_id;

TRACE in 2.5:

PLAN JOIN (D D NATURAL(QD Q INDEX (D1000_WSRS))
PLAN (QD Q INDEX (D1200_WSRS))
PLAN (QD Q INDEX (D2000_WSRS))
PLAN (QD Q INDEX (D2100_WSRS))
PLAN (QD Q INDEX (D3300_WSRS)))
1 records fetched
6 ms, 1771 fetch(es)

Table Natural Index
****************************************************
MASTER 44
DETAIL_1000 20 <<<<<<<<<<<< ???
DETAIL_2000 30 <<<<<<<<<<<< ???
DETAIL_2100 440
DETAIL_3300 130 <<<<<<<<<<<< ???

One may see that engine did excessive scans. Tables DETAIL_1000, DETAIL_2000 and DETAIL_3300 were scanned despite that they do not contain data that could match join expression.

The only difference if test-1 & test-2 is evaluating expression inside driving DT ( "iif(1 = 0, 3300, 2100)" ).

NO such trouble in 3.0: its trace shows that engine avoids to do unnecessary scans regardless of any evaluation of columns that are involved into JOIN inside DT.

This is trace in 3.0 (for 2nd query):

  7 ms, 1411 fetch\(es\)

Table Natural Index
****************************************************
MASTER 44
DETAIL_2100 440

Can this improvement be backported to 2.5 ?

PS. Tested on: WI-V6.3.5.26926, WI-V6.3.0.32022

Commits: 718586f FirebirdSQL/fbt-repository@bbf9595

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Sep 7, 2015

Modified by: @pavel-zotov

Attachment: join-with-unioned-view-ddl.zip [ 12810 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Sep 7, 2015

Modified by: @dyemanov

Version: 2.5.3 Update 1 [ 10650 ]

Version: 2.1.7 [ 10651 ]

Version: 2.5.3 [ 10461 ]

Version: 2.1.6 [ 10460 ]

Version: 2.5.2 Update 1 [ 10521 ]

Version: 2.1.5 Update 1 [ 10522 ]

Version: 2.5.2 [ 10450 ]

Version: 2.5.1 [ 10333 ]

Version: 2.5.0 [ 10221 ]

summary: Make engine to avoid query some tables from "unioned" view if there are no data in these tables (in 2.5 only, and only when some of join conditions is result of evaluation inside driving data source) => IIF function prevents the condition from being pushed into the union for better optimization

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Sep 7, 2015

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Sep 7, 2015

Modified by: @dyemanov

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

resolution: Fixed [ 1 ]

Fix Version: 2.5.5 [ 10670 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Sep 9, 2015

Modified by: @pavel-zotov

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

QA Status: No test => Done successfully

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Sep 9, 2015

Modified by: @pavel-zotov

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

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Sep 14, 2015

Modified by: @dyemanov

issuetype: Improvement [ 4 ] => Bug [ 1 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Sep 23, 2015

Modified by: @dyemanov

Link: This issue relate to CORE4937 [ CORE4937 ]

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