The Vertica Forum is getting a makeover! The site will be undergoing maintenance from Tuesday 8/13 to Friday 8/16. We appreciate your patience and cooperation during this time.

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?

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.

  • Jim_KnicelyJim_Knicely Administrator

    @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

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.