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?
Inserting into a table backed by pre-join projection HASH JOIN problem
+1
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.
+2@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:
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
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
