Path ID Negative 1 and 0 in Execution Engine Profiles

mferrall

Hello, I've been exploring query profiling, and ran into two Path ID's that I don't see in our Query Explain Plan, 0 and -1.

For 0, I see nodes sending data to a single node, I assume this is where data is sent to the query originator?

However, Path ID -1 is more confusing. For some basic queries, I see 'Root'. Does that account for transfer to the client?

In other queries, for example, in the one below where I force a Merge Join (doing this for understanding, not for production queries), the only sort operation I see occurs in PathId -1. However, I'd expect it to occur on a path id associated with the join. I also see filter operations, which I'd expect to see associated with reading from disk.

My best guess at this moment is that -1 is used for paths that aren't expressed as individual paths, but are 'implied paths', ie the sort operations, filter operation, and root. Is that correct, or does it indicate something else?

Query

SELECT /* + SYNTACTIC_JOIN*/
    dim.account_name
  , COUNT(*)
FROM
   fact
   JOIN /*+JTYPE(FM)*/ dim
        ON fact.account_id = da.account_id
WHERE
    fact.date > '2022-08-01'
GROUP BY
    dim.account_name;

Summary of the -1 Path ID in Excel

Hibiki

@mferrall Thank you for sharing this information. Sort and StorageUnion don't appear in path ID = -1. We will fix it in the future release.