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

Sub-optimal predicate checking while selecting from a view [CORE3981] #4314

Closed
firebird-automations opened this issue Nov 14, 2012 · 13 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @dyemanov

Is related to CORE1245
Relate to CORE3986
Relate to CORE4873

Attachments:
core_3981_test.zip

Test case:

(1) Select from a derived table

select * from (
select r.rdb$relation_name, abs(r.rdb$relation_id) as id
from rdb$relations r natural left join rdb$relation_fields rf
where rdb$relation_id < 128
)
where id = 0

-- 42 reads from RDB$RELATIONS / 4 reads from RDB$RELATION_FIELDS

(2) Select from a view

recreate view v
as
select r.rdb$relation_name, abs(r.rdb$relation_id) as id
from rdb$relations r natural left join rdb$relation_fields rf
where rdb$relation_id < 128;

select * from vvv
where id = 0

-- 42 reads from RDB$RELATIONS / 343 (!) reads from RDB$RELATION_FIELDS

The problem is that the outer predicate is checked after both left and right records are fetched while it's enough to check it earlier, as soon as the left record is fetched.

Commits: 60d8b9f 88bdceb 606ae96 cf1212c

====== Test Details ======

Seems that ticket need to be reopened. Trace for WI-V2.5.5.26913 and WI-V3.0.0.31931 shows still bad statistics for case of selecting from derived table (343 and 457 indexed reads from RDB$RELATION_FIELDS).
Wait for reply from dimitr, letter 12-jul-2015 09:55.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

description: Test case:

(1) Select from a derived table

select * from (
select r.rdb$relation_name, abs(r.rdb$relation_id) as id
from rdb$relations r natural left join rdb$relation_fields rf
where rdb$relation_id < 128
)
where id = 0

-- 42 reads from RDB$RELATIONS / 4 reads from RDB$RELATION_FIELDS

(2) Select from a view

recreate view v
as
select r.rdb$relation_name, abs(r.rdb$relation_id) as id
from rdb$relations r natural left join rdb$relation_fields rf
where rdb$relation_id < 128;

select * from vvv
where id = 0

-- 42 reads from RDB$RELATIONS / 343 (!) reads from RDB$RELATION_FIELDS

=>

Test case:

(1) Select from a derived table

select * from (
select r.rdb$relation_name, abs(r.rdb$relation_id) as id
from rdb$relations r natural left join rdb$relation_fields rf
where rdb$relation_id < 128
)
where id = 0

-- 42 reads from RDB$RELATIONS / 4 reads from RDB$RELATION_FIELDS

(2) Select from a view

recreate view v
as
select r.rdb$relation_name, abs(r.rdb$relation_id) as id
from rdb$relations r natural left join rdb$relation_fields rf
where rdb$relation_id < 128;

select * from vvv
where id = 0

-- 42 reads from RDB$RELATIONS / 343 (!) reads from RDB$RELATION_FIELDS

The problem is that the outer predicate is checked after both left and right records are fetched while it's enough to check it earlier, as soon as the left record is fetched.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue is related to CORE1245 [ CORE1245 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Component: Documentation [ 10011 ]

assignee: Dmitry Yemanov [ dimitr ]

Component: Engine [ 10000 ] =>

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Version: 3.0 Initial [ 10301 ]

Fix Version: 3.0 Alpha 1 [ 10331 ]

Fix Version: 2.5.3 [ 10461 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

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

resolution: Fixed [ 1 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue relate to CORE3986 [ CORE3986 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

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

@firebird-automations
Copy link
Collaborator Author

Commented by: @pavel-zotov

It seems that now statistics for these cases is "flipped".

On WI-V2.5.4.26838:

SQL> create or alter view v as
CON> select r.rdb$relation_name, abs(r.rdb$relation_id) as id
CON> from rdb$relations r natural left join rdb$relation_fields rf
CON> where rdb$relation_id < 128;

SQL> out nul;

SQL> select * from v where id=0; ------------------------------- test #⁠1: select from VIEW

SQL> select * from ( ------------------------------------------------- test #⁠2: select from DT
CON> select r.rdb$relation_name, abs(r.rdb$relation_id) as id
CON> from rdb$relations r natural left join rdb$relation_fields rf
CON> where rdb$relation_id < 128
CON> )
CON> where id = 0 ;

SQL> out;

Trace:

2015-02-20T17:38:01.5980 (11724:01DEC7D4) EXECUTE_STATEMENT_FINISH
e25 (ATT_3, SYSDBA:NONE, NONE, TCPv4:127.0.0.1)
C:\MIX\firebird\fb30\isql.exe:16388
(TRA_13, CONCURRENCY | WAIT | READ_WRITE)

Statement 112:
-------------------------------------------------------------------
select * from v where id=0
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN JOIN (V R INDEX (RDB$INDEX_1), V RF INDEX (RDB$INDEX_4))
1 records fetched
0 ms, 96 fetch(es)

Table Natural Index Update In
*******************************************************************
RDB$RELATION_FIELDS 4
RDB$RELATIONS 42

2015-02-20T17:38:16.4270 (11724:01DEC7D4) EXECUTE_STATEMENT_FINISH
e25 (ATT_3, SYSDBA:NONE, NONE, TCPv4:127.0.0.1)
C:\MIX\firebird\fb30\isql.exe:16388
(TRA_13, CONCURRENCY | WAIT | READ_WRITE)

Statement 114:
-------------------------------------------------------------------
select * from (
select r.rdb$relation_name, abs(r.rdb$relation_id) as id
from rdb$relations r natural left join rdb$relation_fields rf
where rdb$relation_id < 128
)
where id = 0
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN JOIN (R INDEX (RDB$INDEX_1), RF INDEX (RDB$INDEX_4))
1 records fetched
1 ms, 856 fetch(es)

Table Natural Index Update In
*******************************************************************
RDB$RELATION_FIELDS 343
RDB$RELATIONS 42

So, again 343 indexed reads but for select from DT rather than view.

On WI-T3.0.0.31655

2015-02-20T17:45:42.3020 (7476:01B046E8) EXECUTE_STATEMENT_FI
e30 (ATT_6, SYSDBA:NONE, NONE, TCPv4:127.0.0.1)
C:\MIX\firebird\fb30\isql.exe:18484
(TRA_5, CONCURRENCY | WAIT | READ_WRITE)

Statement 75:
-------------------------------------------------------------
select * from v where id=0
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN JOIN (V R INDEX (RDB$INDEX_1), V RF INDEX (RDB$INDEX_4))
1 records fetched
0 ms, 1 read(s), 112 fetch(es)

Table Natural Index Update
*************************************************************
RDB$RELATION_FIELDS 4
RDB$RELATIONS 50

2015-02-20T17:46:02.3330 (7476:01B046E8) EXECUTE_STATEMENT_FI
e30 (ATT_6, SYSDBA:NONE, NONE, TCPv4:127.0.0.1)
C:\MIX\firebird\fb30\isql.exe:18484
(TRA_5, CONCURRENCY | WAIT | READ_WRITE)

Statement 76:
-------------------------------------------------------------
select * from (
select r.rdb$relation_name, abs(r.rdb$relation_id) as id
from rdb$relations r natural left join rdb$relation_fields rf
where rdb$relation_id < 128
)
where id = 0
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN JOIN (R INDEX (RDB$INDEX_1), RF INDEX (RDB$INDEX_4))
1 records fetched
2 ms, 4 read(s), 1114 fetch(es)

Table Natural Index Update
*************************************************************
RDB$RELATION_FIELDS 456
RDB$RELATIONS 50

@firebird-automations
Copy link
Collaborator Author

Commented by: @pavel-zotov

IMHO, ticket should be reopened.

Seems that the problem is somehow related to EVALUATION of columns inside derived table or view.
Please see attached .zip - there is scripts with DDL and for running.

Here is the script that I've run against WI-T3.0.0.31721:

set count on;
------------------ derived table-1 ---------------
select *
from (
select m.rname, http://m.id
from trel m natural left join tfld d
)
where id = 0;

------------------ derived table-2 ---------------
select *
from (
select m.rname, abs(http://m.id) as id
from trel m natural left join tfld d
)
where id = 0;

------------------ derived table-3 ---------------
select *
from (
select m.rname, http://m.id + 0 as id
from trel m natural left join tfld d
)
where id = 0;

------------------ view-1 -----------------
select * from vtest1 where id = 0;

------------------ view-2 -----------------
select * from vtest2 where id = 0;

------------------ view-3 -----------------
select * from vtest3 where id = 0;

DDL of each these views is result of copy-paste inner parts from corresponded derived tables:

------------------ view-1 ---------------
create or alter view vtest1 as
select *
from (
select m.rname, http://m.id
from trel m natural left join tfld d
)
;

------------------ view-2 ---------------
create or alter view vtest2 as
select *
from (
select m.rname, abs(http://m.id) as id
from trel m natural left join tfld d
)
;

------------------ view-3 ---------------
create or alter view vtest3 as
select *
from (
select m.rname, http://m.id + 0 as id
from trel m natural left join tfld d
)
;

Trace:

Statement 25:
----------------------------------------------------
select *
from (
select m.rname, http://m.id
from trel m natural left join tfld d
)
where id = 0
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN JOIN (M NATURAL, D INDEX (TFLD_NAME))
4 records fetched
0 ms, 2 read(s), 113 fetch(es)

Table Natural Index
****************************************************
TREL 50
TFLD 4

Statement 27:
----------------------------------------------------
select *
from (
select m.rname, abs(http://m.id) as id
from trel m natural left join tfld d
)
where id = 0
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN JOIN (M NATURAL, D INDEX (TFLD_NAME))
4 records fetched
7 ms, 9 read(s), 1115 fetch(es)
Table Natural Index
****************************************************
TREL 50
TFLD 456

Statement 28:
---------------------------------------------------
select *
from (
select m.rname, http://m.id + 0 as id
from trel m natural left join tfld d
)
where id = 0
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN JOIN (M NATURAL, D INDEX (TFLD_NAME))
4 records fetched
7 ms, 1115 fetch(es)
Table Natural Index
***************************************************
TREL 50
TFLD 456

Statement 30:
--------------------------------------------------------
select * from vtest1 where id = 0
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN JOIN (VTEST1 M NATURAL, VTEST1 D INDEX (TFLD_NAME))
4 records fetched
0 ms, 113 fetch(es)
Table Natural Index U
********************************************************
TREL 50
TFLD 4

Statement 31:
---------------------------------------------------------
select * from vtest2 where id = 0
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN JOIN (VTEST2 M NATURAL, VTEST2 D INDEX (TFLD_NAME))
4 records fetched
7 ms, 1115 fetch(es)
Table Natural Index Up
*********************************************************
TREL 50
TFLD 456

Statement 32:
---------------------------------------------------------
select * from vtest3 where id = 0
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN JOIN (VTEST3 M NATURAL, VTEST3 D INDEX (TFLD_NAME))
4 records fetched
7 ms, 1115 fetch(es)
Table Natural Index Up
*********************************************************
TREL 50
TFLD 456

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

Attachment: core_3981_test.zip [ 12689 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

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

QA Status: Deferred

Test Details: Seems that ticket need to be reopened. Trace for WI-V2.5.5.26913 and WI-V3.0.0.31931 shows still bad statistics for case of selecting from derived table (343 and 457 indexed reads from RDB$RELATION_FIELDS).
Wait for reply from dimitr, letter 12-jul-2015 09:55.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue relate to CORE4873 [ CORE4873 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @pavel-zotov

WI-T4.0.0.141 - problem still exists, statistics from trace is the same.

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