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

Efficient table scans for DBKEY-based range conditions [CORE6278] #6520

Closed
firebird-issue-importer opened this issue Apr 7, 2020 · 6 comments
Closed

Comments

@firebird-issue-importer
Copy link

@firebird-issue-importer firebird-issue-importer commented Apr 7, 2020

Submitted by: @dyemanov

Currently queries with conditions like (RDB$DB_KEY = :param) use a very fast lookup of single record based on its number decoded from DBKEY. But if some range condition is used (e.g. RDB$DB_KEY > :param), then a full table scan is performed. However, if the lower/upper limits for the retrieval are provided, then a "partial" table scan could be performed instead.

The logical record number space is sequential and the full table scan just iterates starting with the zero number until the last record is seen. It can be modified to start with the given "lowest" record number and stop as soon as the last fetched record number is bigger than the given "upper" record number.

Commits: 3ce4605

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Apr 7, 2020

Commented by: @dyemanov

Some examples of how it could be reported in the detailed query plans:

select count(*) from q;

Select Expression
-> Aggregate
-> Filter
-> Table "Q" Full Scan

select count(*) from q where rdb$db_key > x'8700000001000000';

Select Expression
-> Aggregate
-> Filter
-> Table "Q" Full Scan (lower bound)

select count(*) from q where rdb$db_key <= x'8700000001000000';

Select Expression
-> Aggregate
-> Filter
-> Table "Q" Full Scan (upper bound)

select count(*) from q where rdb$db_key >= x'8700000001000000' and rdb$db_key <= x'8700000005000000';

Select Expression
-> Aggregate
-> Filter
-> Table "Q" Full Scan (lower bound, upper bound)

select count(*) from q where rdb$db_key between x'8700000001000000' and x'8700000005000000';

Select Expression
-> Aggregate
-> Filter
-> Table "Q" Full Scan (lower bound, upper bound)

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Apr 7, 2020

Modified by: @dyemanov

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

resolution: Fixed [ 1 ]

Fix Version: 4.0 Beta 2 [ 10888 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Apr 7, 2020

Commented by: @livius2

Sorry for off topic
but what are a use case for such range queries? Just curious.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Apr 7, 2020

Commented by: @dyemanov

Parallel table scans, for example.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Apr 8, 2020

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 Apr 8, 2020

Modified by: @pavel-zotov

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
1 participant