[
https://issues.apache.org/jira/browse/IGNITE-25366?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Konstantin Orlov reassigned IGNITE-25366:
-----------------------------------------
Assignee: Konstantin Orlov
> Documentation. Describe output of EXPLAIN command
> -------------------------------------------------
>
> Key: IGNITE-25366
> URL: https://issues.apache.org/jira/browse/IGNITE-25366
> Project: Ignite
> Issue Type: Improvement
> Components: documentation ai3
> Reporter: Konstantin Orlov
> Assignee: Konstantin Orlov
> Priority: Major
> Labels: ignite-3
>
> Let's update documentation with page describing general concept of EXPLAIN
> output, which is provided below.
> Every relational operator is described with a name and set of attributes:
> {code}
> NameOfTheRelationalOperator
> attribute1: value1
> attribute2: value2
> {code}
> Let’s take a look at few examples:
> {code}
> TableScan // Full table access
> table: PUBLIC.EMP1 // Name of the table in question
> fields: [NAME, SALARY] // List of columns to return
> est: (rows=1) // Estimated number of rows returned
> IndexScan // Index scan
> table: PUBLIC.TEST_TBL // Name of the table in question
> index: IDX_DESC // Name of the index in question
> type: SORTED // Type of the index
> fields: [C1] // List of columns to return
> collation: [C1 DESC] // Collation of the index aka order of
> sorting
> est: (rows=1) // Estimated number of rows returned
> Sort
> collation: [C1 DESC NULLS LAST] // Collation to sort input rows
> est: (rows=1) // Estimated number of rows returned
> {code}
> Name represents a particular algorithm used to execute relation operators
> ({{TableScan}} vs {{IndexScan}}, {{HashJoin}} vs {{MergeJoin}} vs
> {{NestedLoopJoin}}, etc). The set of attributes depends on the particular
> relational operator.
> A query plan is represented by a tree-like structure which is the composition
> of nodes described above. This tree describes a data flow, where rows are
> passed from leaves to a plan root node (root node is the topmost node, it
> also has no indentation). Let's take a look at few examples:
> {code}
> // simple ordered select where desired order matches collation of existing
> index
> EXPLAIN PLAN FOR SELECT c1 FROM test_tbl ORDER BY c1 DESC NULLS FIRST
> Exchange
> distribution: single
> est: (rows=1)
> IndexScan
> table: PUBLIC.TEST_TBL
> index: IDX_DESC
> type: SORTED
> fields: [C1]
> collation: [C1 DESC]
> est: (rows=1)
> // similar query, but desired order doesn't match index collation. Mind the
> additional
> // SORT node.
> EXPLAIN PLAN FOR SELECT c1 FROM test_tbl ORDER BY c1 DESC NULLS LAST
> Exchange
> distribution: single
> est: (rows=1)
> Sort
> collation: [C1 DESC NULLS LAST]
> est: (rows=1)
> TableScan
> table: PUBLIC.TEST_TBL
> fields: [C1]
> est: (rows=1)
> // Mind the "fetch" attribute of Sort node. It denotes TopN sort algorithm,
> // implying that only N nodes will be kept in memory. This also implies
> // that only N rows from every node will be transferred over Exchange.
> SELECT * FROM test ORDER BY pk FETCH FIRST ? ROWS ONLY
> Limit
> fetch: ?0
> est: (rows=1)
> Exchange
> distribution: single
> est: (rows=1)
> Sort
> collation: [PK ASC]
> fetch: ?0
> est: (rows=1)
> TableScan
> table: PUBLIC.TEST
> fields: [PK, COL0]
> est: (rows=1)
> // Similar query, but Limit node wasn't pushed down the exchange. This implies
> // that the whole dataset will be transferred over an Exchange.
> SELECT * FROM test OFFSET ? ROWS FETCH FIRST ? ROWS ONLY
> Limit
> offset: ?0
> fetch: ?1
> est: (rows=1)
> Exchange
> distribution: single
> est: (rows=1)
> TableScan
> table: PUBLIC.TEST
> fields: [PK, COL0]
> est: (rows=1)
> // More complex plan
> EXPLAIN PLAN FOR SELECT
> U.UserName, P.ProductName, R.ReviewText, R.Rating
> FROM Users U, Reviews R, Products P
> WHERE U.UserID = R.UserID
> AND R.ProductID = P.ProductID
> AND P.ProductName = 'Product_' || ?::varchar;
> Project
> fields: [USERNAME, PRODUCTNAME, REVIEWTEXT, RATING]
> exprs: [USERNAME, PRODUCTNAME, REVIEWTEXT, RATING]
> est: (rows=16650)
> HashJoin
> condition: =(USERID0, USERID)
> joinType: inner
> est: (rows=16650)
> HashJoin
> condition: =(PRODUCTID, PRODUCTID0)
> joinType: inner
> est: (rows=16650)
> Exchange
> distribution: single
> est: (rows=50000)
> TableScan
> table: PUBLIC.REVIEWS
> fields: [PRODUCTID, USERID, REVIEWTEXT, RATING]
> est: (rows=50000)
> Exchange
> distribution: single
> est: (rows=1)665
> TableScan
> table: PUBLIC.PRODUCTS
> filters: =(PRODUCTNAME, ||(_UTF-8'Product_', CAST(?0):VARCHAR
> CHARACTER SET "UTF-8"))
> fields: [PRODUCTID, PRODUCTNAME]
> est: (rows=1665)
> Exchange
> distribution: single
> est: (rows=10000)
> TableScan
> table: PUBLIC.USERS
> fields: [USERID, USERNAME]
> est: (rows=10000)
> {code}
> We also should provide exhaustive list of relational operators their
> attributes.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
