View Optimization and Primary Key

edited February 2017 in General Discussion


An interesting business case for view optimization :
1/ We just migrated from Vertica 7.1 to 7.2

2/ in the system table v_catalog.CONSTRAINT_COLUMNS, I find some primary keys but all with the field "is_enabled" at "False", that's a priori logic since these tables were created in 7.1

3/In the Vertica documentation, we can read "View Optimization : If you query a view and your query only includes columns from a subset of the tables that are joined in that view, Vertica executes that query by expanding it to include only those tables. This optimization requires one of the following conditions to be true: l Join columns are foreign and primary keys. l The join is a left or right outer join on columns with unique values"


So, does Vertica optimize the view even if the primary_key contraint are not enabled?

Giving that I have 19 left outer join in my view...


  • Options

    Well, I enabled the PK and I get a huge improvement ( time divided by 6 on a specific query)

Leave a Comment

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