6 Replies Latest reply on Aug 31, 2015 2:51 AM by bpiepers

    Influence pushdown strategy of WHERE clauses

    bpiepers
    bpiepers Aug 27, 2015 10:08 AM

      I currently have a situation where I am using a constraint on a DATE column on a table that is very large. This condition is set to return data not older than 90 days. In some situations, users may also use this date field in their where clause. So say the table name is FOO and the column date column is called CREATED, a user may issue:

       

      SELECT * FROM view.FOO WHERE CREATED BETWEEN '2015-06-01' AND '2015-06-10';

       

      What I've noticed is that the query planner doesn't take that WHERE clause into account in our situation. It only pushes down the constraint to the database (retrieving a huge amount of data ) and then tries to filter based on the WHERE clause the user has given. So the source command is:

       

      SELECT * FROM source.FOO WHERE AS g_0 WHERE age({ts '2015-08-27 15:12:18.951'}, g_0."CREATED") <= '90 days'

       

      (Note: age is a UDF). Takes minutes to retrieve and then the engine executes the "BETWEEN" clause.

       

      How can I influence the way the engine pushes down the WHERE clause to the source? I'd rather want it to always push down the WHERE clause in this situation. Is that possible?

      • 210 Views
      • Tags:
        • 1. Re: Influence pushdown strategy of WHERE clauses
          shawkins
          shawkins Aug 27, 2015 1:22 PM (in response to bpiepers)

          > 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.

            • Actions
          • 2. Re: Influence pushdown strategy of WHERE clauses
            bpiepers
            bpiepers Aug 28, 2015 3:55 AM (in response to shawkins)

            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....

              • Actions
            • 3. Re: Influence pushdown strategy of WHERE clauses
              shawkins
              shawkins Aug 28, 2015 9:53 AM (in response to bpiepers)

              > 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?

                • Actions
              • 4. Re: Influence pushdown strategy of WHERE clauses
                bpiepers
                bpiepers Aug 28, 2015 12:52 PM (in response to shawkins)

                I could only find these very old issues that may be related:

                 

                [TEIIDDES-732]

                 

                [TEIIDDES-744]

                 

                Both closed, though. Am I allowed to report a new one once I have ascertained that it is indeed not something I'm doing wrong?

                  • Actions
                • 5. Re: Influence pushdown strategy of WHERE clauses
                  shawkins
                  shawkins Aug 28, 2015 1:02 PM (in response to bpiepers)

                  Yes, please do.

                    • Actions
                  • 6. Re: Influence pushdown strategy of WHERE clauses
                    bpiepers
                    bpiepers Aug 31, 2015 2:51 AM (in response to shawkins)

                    I have created issue [TEIIDDES-2629]

                     

                    Thanks

                      • Actions