Hi!
I have three tables, each with 175 mi of lines each one, two tables has two columns (col_id_1 and col_id_2) that together forms the PK and own has only one PK column .(col_id_1).
I want to create a table that is this three tables joined, all tables are sorted by the same KEY (col_id_1 and col_id_2), so I write my SQL as follows:
CREATE TABLE all_together AS
SELECT * FROM TABLE1 T1
JOIN TABLE2 T2 ON T1.col_id_1 = T2.col_id_1 AND T1.col_id_2 = T2.col_id_1
JOIN TABLE2 T3 ON T1.col_id_1 = T3.col_id_1
When I run an EXPLAIN in this query Vertica does not shows a SORT phase, instead of the information is that the inputs are presorted (due to the projections ordered by col_id_1/col_id_2 and col_id_1.
But the query execution gave me an execution event with this description: "Merge converted to union, followed by sort." and event_type "MERGE_CONVERTED_TO_UNION" meaning a SORT on T1
Why vertica are performing a SORT ?
Thanks in advance,
André
Why JOIN implies in MERGE_CONVERTED_TO_UNION
MERGE_CONVERTED_TO_UNION is an informational message found in query events. This operation is an internal decision of optimizer,whenever it finds a STORAGE MERGE operation after a MERGE or HASH join in a query plan execution. Every time there is a StorageMerge right on top of a Join (either MergeJoin or HashJoin) in a plan, we convert that StorageMerge into StorageUinon + Sort in LocalPlan. The reason we do that because StorageMerge and Join are co-routines and can case deadlock if their buffers are full and waiting for each other.
StorageMerge operation makes sure that all columns data have been materialized & there will not be any further materialization of data is needed in execution. If we can force an early materialization of data in a qery plan execution ,we can avoid this StorageMerge operation after a Join & hence in-turn no occurrence of MERGE_CONVERTED_TO_UNION followed by a SORT.
You can use early materialized joins to get away from this. Using early materialized joins is a good idea anyway, and you should consider making it the default for new deployments.
Sign up
Already have an account? Login
Welcome to the Rocket Forum!
Please log in or register:
Employee Login | Registration Member Login | RegistrationEnter your E-mail address. We'll send you an e-mail with instructions to reset your password.
77 4th Avenue
Waltham, MA 02451 USA
