16. Using the Query Optimizer : Types of Nodes in a QEP : Join Nodes in a QEP : Cartesian Product Node
 
Share this page                  
Cartesian Product Node
The Cartesian product, or cart-prod, strictly follows the un-optimized join model, with each row in the outer node compared to all rows from the inner node. This does not mean that all rows are actually read, only that all rows that satisfy the conditions of the query are compared.
A typical abbreviated example of a QEP diagram involving a cart-prod is shown below:
          Cart-Prod
        /       \
    proj-rest table
      /
   table
This node is displayed with the following information on a QEP diagram:
A label identifying it as a cart-prod join node, along with the column(s) on which processing is done
If an outer join has been requested, one of the following labels indicating the type of join:
[LEFT JOIN]
[FULL JOIN]
[RIGHT JOIN]
Storage structure (which is usually heap)
Total number of pages and tuples
Query cost information
Optionally, sort information (whether the data produced by this node is already sorted or requires sorting)
The cart-prod is most frequently observed in disjoint queries (that is, when use of correlation variables and table names are mixed in the query). However, the following cases can also generate cart-prods, without adversely affecting performance:
Queries involving ORs that can usually be decomposed into a sequence of smaller queries
No join specification (a disjoint table or no WHERE clause, so that there is no relationship between the two tables)
Small tables or amounts of data
Non_equijoins, such as a query with the following WHERE clause:
WHERE r1.col1 > r2.col2
Cart-prods are sometimes associated with substantial estimates for disk I/O usage and affect performance adversely.
This example shows a QEP diagram with a cart-prod join node resulting from the following simple disjoint query:
SELECT arel.col1 FROM arel, arel a
WHERE a.col1 = 99;
QUERY PLAN 7,1, no timeout, of main query
                    Cart-Prod
                    Heap
                    Pages 1 Tups 243
                    D9 C4
                   /                \
          Proj-rest               Proj-rest
          Sorted(NU)              Heap
          Pages 1 Tups 2        Pages 1 Tups 156
          D1 C0                   D8 C1
          /                        /
     arel                      arel
     Hashed(col1)              Hashed(NU)
     Pages 70 Tups 156        Pages 70 Tups 156