Three ways to check the push-down of filters

[jan_zwickel]

In the following three different ways are described how to verify whether or not a filter is “pushed-down”. With “pushed-down” it is meant that the filter is applied at the lowest level possible – e.g., already when reading data from a table instead of filtering later in the data flow. This post will provide you with methods to check it but what leads to push-down is not in the current scope. If you are interested in how you can enforce the push-down of filters in certain situations you can find more information here

The three options to analyze the filter push-down: Explain Plan, Visualize Plan/Analyze SQL, and Debug View will be shown for the following scenario

Scenario

We use a Calculation View named S2EX1_SALES_CUSTOMER in the example.

Using the “Column Lineage” tool (button is highlighted in screenshot below) on this model shows that the column “PHONE_NUMBER” originates from table BusinessPartnerContacts.

 



Model used in example. Column lineage shows that Field "PHONE_NUMBER" is already available in node "Proj_BPC" (table "BusinessPartnerContacts")

 

Therefore, if a query is send with a filter on “PHONE_NUMBER” the filter could be applied to this table directly.

Here is an example query that puts a filter on PHONE_NUMBER:

SELECT

   "PartnerId",

   "TITLE",

   "LAST_NAME",

   "FIRST_NAME",

   "GENDER",

   SUM("NetAmount") AS "NetAmount"

FROM

   <...>::S2EX1_SALES_CUSTOMER"

WHERE

   PHONE_NUMBER='7412697340'

GROUP BY

   "PartnerId",

   "TITLE",

   "LAST_NAME",

   "FIRST_NAME",

   "GENDER"

Example Query to test filter on field "PHONE_NUMBER"

 

The following examples use this scenario to illustrate how a check can be done whether the filter '7412697340' on PHONE_NUMBER is already used when reading from table “BusinessPartnerContacts”

How to check filter-push down

a) Check filter push-down with Explain Plan

b) Check filter push-down with Visualize Plan (SAP HANA Studio)/Analyze SQL (Database Explorer)

c) Check filter push-down with Debug View