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)
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Key: CORE-4927
URL: http://tracker.firebirdsql.org/browse/CORE-4927
Project: Firebird Core
Issue Type: Improvement
Components: Engine
Affects Versions: 2.5.4
Reporter: Pavel Zotov
Priority: Minor
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
--
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
------------------------------------------------------------------------------
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
