How to give Database Designer the best chance of success?
Context
When running Database Designer and then deploying the projections that it suggests I have noticed that my queries become significantly slower.
I am using Vertica 6.1.2 EE on a 4 node cluster.
I am running Database Designer as Query Specific mode, and giving it 3 queries to analyse that are representative of what I want to do.
Prior to running Database Designer my query is taking around 385 seconds to complete. After adding the projections suggested by the Database Designer the query takes approx. 520 seconds to complete. I have re-run it a number of times to confirm these results.
In each case I am dumping the results of my query to a temporary table that is scoped to the session so as to remove the cost of writing to disk. The query generates approx. 80 million rows.
The query I am running is one of the three supplied to the Database Designer.
No other use of the cluster is being made when I run these queries.
Question
1. What can I do to maximise the chances of the Database Designer providing projections that improve the performance of my queries?
2. What heuristic(s) does the Database Designer apply to determine the projections it suggests?
3. What are the known weaknesses of the Database Designer in terms of providing projections that enhance query performance?
When running Database Designer and then deploying the projections that it suggests I have noticed that my queries become significantly slower.
I am using Vertica 6.1.2 EE on a 4 node cluster.
I am running Database Designer as Query Specific mode, and giving it 3 queries to analyse that are representative of what I want to do.
Prior to running Database Designer my query is taking around 385 seconds to complete. After adding the projections suggested by the Database Designer the query takes approx. 520 seconds to complete. I have re-run it a number of times to confirm these results.
In each case I am dumping the results of my query to a temporary table that is scoped to the session so as to remove the cost of writing to disk. The query generates approx. 80 million rows.
The query I am running is one of the three supplied to the Database Designer.
No other use of the cluster is being made when I run these queries.
Question
1. What can I do to maximise the chances of the Database Designer providing projections that improve the performance of my queries?
2. What heuristic(s) does the Database Designer apply to determine the projections it suggests?
3. What are the known weaknesses of the Database Designer in terms of providing projections that enhance query performance?
0