View Optimization and Primary Key
Hello,
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"
https://my.vertica.com/docs/7.2.x/PDF/HP_Vertica_7.2.x_Analyzing_Data.pdf
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...
Comments
Well, I enabled the PK and I get a huge improvement ( time divided by 6 on a specific query)