How is the performance of Joins in Vertica?

rajatpaliwal86

I heard that joins in Vertica a bit expensive. Is this true?
We're designing the table's schema for our project and for fields which can have multiple values- we are planning to store them in a separate table which can be JOINed with the main table to reflect one-many relationship- thats what a standard practice in SQL. Is this good to follow in Vertica too without any major performance loss?

Bryan_H

I can offer the following advice to improve performance on this type of structure:

• Ensure you have valid Primary Key and Foreign Key constraints. Vertica will use these during optimization.
• As with any Vertica table, optimized projections are essential to performance. I suggest loading a sample set of data (5-10% of the expected production data set, ideally) and run some queries. Check the query plan with EXPLAIN, then run DataBase Designer on the test queries. After projections are deployed, then test the queries again and verify that the new projections appear in the EXPLAIN plan.

If you continue to find any performance issues after adding PK/FK and new projections, we will need more detail such as the EXPLAIN plan from queries run with PK/FK and DBD projections to suggest further improvements.