Vertica joins on large data sets is extremely slow
170GB of memory available for transactions per node
I have this simple join query that runs for 11 minutes.
After implementing the suggested projections I got from the database designer, it was reduced to 7 mins. Not a very huge improvement.
Is vertica slow with joins on large data sets? Makes one question if this is really a big data database.
from fact_table01 m
JOIN fact_table02 s
ON m.id = s.id
JOIN fact_table03 i
ON m.id = i.id
and m.id = i.id
Fact tables in the query have at least 5B records.
The funny thing is, I checked the QUERY_EVENTS and I saw that there were suggestions like "Consider using a sorted projection." Which is pretty funny considering the projection used was from the database designer.