Ok, I was all excited about MERGE TABLEs moving up into the SQL layer, unfortunately my enthusiasm might be misplaced as I don’t see any improvement in query performance. Let me explain:

I have a fact table with ~300 M rows with 5 years of data.

I created 5 yearly tables, ordering by my date_key (and also created a year column). Checked storage to verify MonetDB knew they were sorted.

Created a Merge table over all 5 yearly tables.

I then ran test queries with various year subsets (using direct fact column filter and joining from date dimension), from 1 to all 5 and well I didn’t see any improvement in query speed when a subset of the 5 years was queried. In fact I usually saw 10-20% degradation of performance. I am no MAL expert, but looking at the PLAN and the EXPLAIN/TRACE outputs it appears to me tables that shouldn’t have data for the query are still scanned.

I ran this both on a 16GB 4 core Windows box and a 32GB 8 core EC2 Linux box, same behavior both places, using both July2015 and July2015SP1.

Is this expected behavior? Am I missing some magic to have MonetDB do the Oracle concept of Partition Elimination? Or are Merge tables really just meant for data maintenance?

Thanx,

Bryan

This transmission is confidential and intended solely for the use of the recipient named above. It may contain confidential, proprietary, or legally privileged information. If you are not the intended recipient, you are hereby notified that any unauthorized review, use, disclosure or distribution is strictly prohibited. If you have received this transmission in error, please contact the sender by reply e-mail and delete the original transmission and all copies from your system.