Issue Details (XML | Word | Printable)

Key: CORE-6278
Type: Improvement Improvement
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Unassigned
Reporter: Dmitry Yemanov
Votes: 0
Watchers: 3
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Efficient table scans for DBKEY-based range conditions

Created: 07/Apr/20 06:37 AM   Updated: 08/Apr/20 11:17 AM
Component/s: Engine
Affects Version/s: None
Fix Version/s: 4.0 Beta 2

QA Status: Done successfully


 Description  « Hide
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.

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Dmitry Yemanov added a comment - 07/Apr/20 06:40 AM
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)


Karol Bieniaszewski added a comment - 07/Apr/20 08:12 AM
Sorry for off topic
but what are a use case for such range queries? Just curious.

Dmitry Yemanov added a comment - 07/Apr/20 08:24 AM
Parallel table scans, for example.