We have a huge number of rows(tens of millions), and for each table we have an associated view that is a select with UNION ALL between that table and several others.
Now, we are doing a select from that view with some conditions(where). On Oracle and SQL Server the performance of the select is reasonable. We have given a try to Vertica, because it is supposed to be good with selects on large tables, but the performance of this particular select is really bad.
I understand that when you do a union you lose the indexes on columns, because you can't get back to them.
What do you think is the problem with Vertica, is it missing some optimization steps that the other databases do ?