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