> What I've noticed is that the query planner doesn't take that WHERE clause into account in our situation
The planner will always try to push predicates as far as possible.
> How can I influence the way the engine pushes down the WHERE clause to the source?
What version are you on and what source are you dealing with? You'll want to verify that it supports the necessary capabilities. Also the showplan debug output - Query Planner - Teiid 8.12 (draft) - Project Documentation Editor is helpful here because it should contain explicit lines such as "X is not supported by source pm1 ... was not pushed".
> I'd rather want it to always push down the WHERE clause in this situation. Is that possible?
If the source has the appropriate capabilities, then we'd have to see if there is something intervening in the plan. Otherwise it should always get pushed.
Unfortunately we're still at version 8.4 (Teiid) but are planning to upgrade. Our source system is a Netezza backend. What is interesting, perhaps, is that as soon as the model contains a virtual procedure the where clause is not pushed down to the source. The table I face this issue with is modeled as follows at the moment:
1. <<table>> EVENT_FACT --> 2. <<procedure>> EVENT_FACT_CONDITIONAL --> 3a. <<table>> EVENT_FACT --> 4a. <<table>> EVENT_FACT
--> 3b. <<table>> EVENT_FACT_SECURE --> 4b. <<table>> EVENT_FACT_SECURE
From left to right is top-down so EVENT_FACT at 1 is exposed to clients. Constraints residing at 3/4 are pushed down to the source, the WHERE clause on the same column as the constraints in 3/4 are not pushed down to the source. If I remove the procedure (leaving only 1 and 4 in the model) the WHERE clause of the client is being pushed to the source properly. The logging of my query planner does not contain "X is not supported by source... was not pushed" kind of statements. It just seems to "optimize" the query in multiple steps and then finally seems to omit the WHERE clause portion I have issued to the EVENT_FACT table in 1. I could post the query plan but it's very lengthy.
I am beginning to get increasingly frustrated by the Teiid Designer tooling that continuously resets the translator of my source model back to jdbc-simple causing me to troubleshoot non existing issues....
> I am beginning to get increasingly frustrated by the Teiid Designer tooling that continuously resets the translator of my source model back to jdbc-simple causing me to troubleshoot non existing issues....
Has that been captured as an issue?
Yes, please do.