Inserting into a table backed by pre-join projection HASH JOIN problem
Hi!
When inserting to a table that is backed by pre-join projection, our inserter failed with JOIN INNER DID NOT FIT IN MEMORY.
After researching, we noticed that Vertica internally does a HASH JOIN instead of MERGE JOIN (in EXPLAIN output) that could explain why it did not fit in memory.
Reproducible script can be found here and you can run it in vsql.
Explain of the first "insert into ... values ..." results in HASH JOIN, however explain of the second "insert into ... select" results in correct MERGE JOIN.
We think that also in the first case there should be a MERGE JOIN and thus this is a bug. Can you please confirm?
When inserting to a table that is backed by pre-join projection, our inserter failed with JOIN INNER DID NOT FIT IN MEMORY.
After researching, we noticed that Vertica internally does a HASH JOIN instead of MERGE JOIN (in EXPLAIN output) that could explain why it did not fit in memory.
Reproducible script can be found here and you can run it in vsql.
Explain of the first "insert into ... values ..." results in HASH JOIN, however explain of the second "insert into ... select" results in correct MERGE JOIN.
We think that also in the first case there should be a MERGE JOIN and thus this is a bug. Can you please confirm?
0
Comments
Starting with release 9.1 and continuing with release 9.2, Vertica has removed support for the following projections:
Release 9.1: Projection buddies with different SELECT and ORDER BY clauses. All projection buddies must specify columns in the same order. Projections with non-compliant buddies are regarded by the Vertica database as unsafe.
Release 9.2: Pre-join and range segmentation projections. If a table’s only super-projection is one of these projection types, the projection is also regarded as unsafe.
The pre-upgrade script and details on usage are available for download here.
@qinchaofeng is correct in that Pre-Join projections are no longer supported.
@Grega_Kešpret - You must be running an older version of Vertica
Check out the "Pre-Join Projection" replacement feature "Flattened Tables" here:
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/AnalyzingData/FlattenedTables/FlattenedTables.htm
In the mean time for your case:
To facilitate a merge join, create projections for the joined tables that are sorted on the join predicate columns. The join predicate columns should be the first columns in the ORDER BY clause.
Projection: mab_test.test_1_super_by_a is ordered by a, b
Projection: mab_test.test_2_super_by_a is ordered by a, c, d