First of all Welcome to Teiid. We are thrilled to have using Teiid. Absolutely this is the right place to ask. It is little slow as most of us vacation during holidays.
What you have system wise is enough. Are seeing slowness. BTW, Teiid already behaves exactly as mentioned in your question already.
Use the union without the where as transformation query for your view and build the VDB. Then when you issue the user query then use the where clause then Teiid will rewrite query as second form you showed automatically. You can also see a query plan on this gets done. Check the documentation for details. Ask any questions you may have.
Again Welcome to Teiid forums.
Ok, somehow I cloud show the logs where the planning query is and now I see that it is rebuild as I intend (just as you said), now is there a way I can see the performance for each query I run to my VDB?
Programmatically you can do this SET Statement · Teiid Documentation to get query plan
Or you can Designer and it has menu options to see the query plan.
or using web console you can you can enable trace logging
or you execute "SET SHOWPLAN DEBUG" on using SQL Client like Squirrel and that will spit out the query plan to log file.
I found that the main problem that is causing the excess RAM usage was that the connection to one of the data sources was lost and the query was restarted again and again.
This data source is after a VPN connection, for now solve it by bringing the data through a direct connection and not by VPN.
Thanks a lot.