Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Path ID Negative 1 and 0 in Execution Engine Profiles

edited August 11 in General Discussion

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

Answers

  • HibikiHibiki Employee

    @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.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.