Partial Sort Merge Node
The partial sort merge (PSM) is a cross between a full sort merge and a cart-prod. The inner tree must be in sorted order. The outer tree can be sorted or partially sorted. The outer tree in PSM scenarios can always be derived from an ISAM table. Comparisons proceed as for the full sort merge until an outer value is found to be out of order. At that point the inner loop is restarted. Because ISAM tables are reasonably well ordered (depending on how many rows have been added because the last reorganization), the number of inner loop restarts is typically small.
A typical abbreviated example of a QEP diagram involving a PSM is shown below:
Join
/ \
proj-rest sort
/ /
table proj-rest
/
table
This node is displayed with the following information on a QEP diagram:
• A label identifying it as a PSM join node, along with the columns 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 following example shows a QEP diagram with a PSM join:
select a.col2, b.col2 from arel a, brel b
where a.col1 = b.col2;
QUERY PLAN 6,1, no timeout, of main query
PSM Join(col1)
Heap
Pages 1 Tups 156
D9 C26
/ \
Proj-rest Proj-rest
Heap Sort on(col1)
Pages 1 Tups 156 Pages 1 Tups 156
D1 C1 D8 C1
/ /
brel arel
Isam(col2) Hashed(NU)
Pages 3 Tups 156 Pages 70 Tups 156