Help optimizing query speed
I am running Vertica 7.2 on Ubuntu with 16 GB of RAM and 8 cores.
I have loaded 400 million rows of data, 69 columns wide into a single table.
I have partitioned the table by fiscal quarter.
The types of queries I am running are like:
SELECT avg(t1.age), t1.gender
WHERE country='JPN' and dt BETWEEN '2016-01-01' AND '2016-02-15'
GROUP BY customer_id) t1
GROUP BY gender;
The speed is ok, but no faster than Teradata.
I would like to create a partition for every day (about 5,000 days in total), but Vertica says the max is 1,024 partitions, and doesn't recommend more than 10-20.
I would like to create a few projections, but my queries never use the same columns to filter on - it's always a different combination of columns (ie country, product type, channel, etc).
What can I do to improve the speed?