Thank you Stefan,
So as I understand that the algebra.(left)fetchjoin() is the most optimized way in monetdb.
I was hoping in my case to gain some precious seconds by trying to instruct monetdb to use other algorithm,
I have a small table around 50k rows (that should fit easily in memory) that need to be joined with a big table of 12M rows, the join results is around 150M rows.
The join key is a char(21) column which is the first column of a primary key defined on both tables (I think this why its reflected in the algebra.(left)fetchjoin()).
On the join I am doing an aggregate on a combination of columns from both tables.
The execution time is around 80s, returning 250 rows, similar queries on different columns needs to be executed around 50 times.
I am trying to avoid to materialize the join results in a table and then execute the aggregation on the materialized table, the reason is that it will consume a 35GB of disk space.
Is it hard to achieve a better performance by relying only on the join?
Another performance issue i am facing is with range join on date columns, does monetdb has a specific method/index for this kind of join?
Regards.