Skip to content
New issue

Have a question about this project? # for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “#”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? # to your account

Add LEVEL column to PLG$PROF_RECORD_SOURCES and PLG$PROF_RECORD_SOURCE_STATS_VIEW #7687

Closed
asfernandes opened this issue Jul 28, 2023 · 1 comment

Comments

@asfernandes
Copy link
Member

In most cases, each plan's line is put in a record of PLG$PROF_RECORD_SOURCES.

But that is not always true.

For example:

SQL> select first 1 * from rdb$database order by 1;

Select Expression
    -> First N Records
        -> Refetch
            -> Sort (record length: 36, key length: 12)
                -> Table "RDB$DATABASE" Full Scan

Here Refetch and Sort is put in the same record, with makes it difficult to reconstruct indentation.

Adding a LEVEL column to this table and views makes it easy to do it.

@asfernandes asfernandes self-assigned this Jul 28, 2023
asfernandes added a commit that referenced this issue Jul 28, 2023
…LG$PROF_RECORD_SOURCE_STATS_VIEW.

Also avoid start collect profiling data for a record source not from its initial node.
mrotteveel added a commit to mrotteveel/firebird-documentation that referenced this issue Jul 31, 2023
mrotteveel added a commit to mrotteveel/firebird-documentation that referenced this issue Aug 8, 2023
@pavel-zotov
Copy link

pavel-zotov commented Aug 23, 2023

=== QA issue ===

Test checks ability to reconstruct explained plan (as it looks in ISQL) using recursive query to PLG$PROF_RECORD_SOURCES snapshot and its LEVEL column.

Currently there is no way to reconstruct explained plan when SUB-queries present: there is no such column as "block_id" for lines that belong to different parts of 'compound' query.
Such lines (from different 'blocks') have the same record_source_id and parent_record_source_id, and this causes 'blocks' to be displayed in arbitrary order.
For example, query from #7688:

select 1, (select 2 from rdb$database b) from rdb$database a;

-- has following explained plan:

  Sub-query
      -> Singularity Check
          -> Table "RDB$DATABASE" as "B" Full Scan
  Select Expression
      -> Table "RDB$DATABASE" as "A" Full Scan

So, we can see there
block N1:

  Sub-query
      -> Singularity Check
          -> Table "RDB$DATABASE" as "B" Full Scan

and block N2:

  Select Expression
      -> Table "RDB$DATABASE" as "A" Full Scan

But PLG$PROF* table can store them either like this:

    5760       0       1  <null> Select Expression
    5760       1       2       1   -> Table "RDB$DATABASE" as "A" Full Scan
    5760       0       3  <null> Sub-query
    5760       1       4       3   -> Singularity Check
    5760       2       5       4     -> Table "RDB$DATABASE" as "B" Full Scan

Or like this:

    5760       0       3  <null> Sub-query
    5760       1       4       3   -> Singularity Check
    5760       2       5       4     -> Table "RDB$DATABASE" as "B" Full Scan
    5760       0       1  <null> Select Expression
    5760       1       2       1   -> Table "RDB$DATABASE" as "A" Full Scan

I could not find how properly to join/order final query so that output will exactly match to ISQL explained plan.
Because of this, test soon can be changed so that only TOP-level access paths be checked (without requirement to check their order).

# for free to join this conversation on GitHub. Already have an account? # to comment
Projects
None yet
Development

No branches or pull requests

2 participants