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
Comments
Modified by: @dyemanovdescription: Test case: (1) Select from a derived table select * from ( -- 42 reads from RDB$RELATIONS / 4 reads from RDB$RELATION_FIELDS (2) Select from a view recreate view v select * from vvv -- 42 reads from RDB$RELATIONS / 343 (!) reads from RDB$RELATION_FIELDS => Test case: (1) Select from a derived table select * from ( -- 42 reads from RDB$RELATIONS / 4 reads from RDB$RELATION_FIELDS (2) Select from a view recreate view v select * from vvv -- 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. |
Modified by: @dyemanovComponent: Documentation [ 10011 ] assignee: Dmitry Yemanov [ dimitr ] Component: Engine [ 10000 ] => |
Modified by: @dyemanovVersion: 3.0 Initial [ 10301 ] Fix Version: 3.0 Alpha 1 [ 10331 ] Fix Version: 2.5.3 [ 10461 ] |
Modified by: @pcisarstatus: Resolved [ 5 ] => Closed [ 6 ] |
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 SQL> out nul; SQL> select * from v where id=0; ------------------------------- test #1: select from VIEW SQL> select * from ( ------------------------------------------------- test #2: select from DT SQL> out; Trace:2015-02-20T17:38:01.5980 (11724:01DEC7D4) EXECUTE_STATEMENT_FINISH Statement 112: Table Natural Index Update In 2015-02-20T17:38:16.4270 (11724:01DEC7D4) EXECUTE_STATEMENT_FINISH Statement 114: Table Natural Index Update In So, again 343 indexed reads but for select from DT rather than view. On WI-T3.0.0.316552015-02-20T17:45:42.3020 (7476:01B046E8) EXECUTE_STATEMENT_FI Statement 75: Table Natural Index Update 2015-02-20T17:46:02.3330 (7476:01B046E8) EXECUTE_STATEMENT_FI Statement 76: Table Natural Index Update |
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. Here is the script that I've run against WI-T3.0.0.31721: set count on; ------------------ derived table-2 --------------- ------------------ derived table-3 --------------- ------------------ view-1 ----------------- ------------------ view-2 ----------------- ------------------ view-3 ----------------- DDL of each these views is result of copy-paste inner parts from corresponded derived tables: ------------------ view-1 --------------- ------------------ view-2 --------------- ------------------ view-3 --------------- Trace:Statement 25: Table Natural Index Statement 27: Statement 28: Statement 30: Statement 31: Statement 32: |
Modified by: @pavel-zotovAttachment: core_3981_test.zip [ 12689 ] |
Modified by: @pavel-zotovstatus: 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). |
Commented by: @pavel-zotov WI-T4.0.0.141 - problem still exists, statistics from trace is the same. |
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.
The text was updated successfully, but these errors were encountered: