Query ES pushdown failed

1

We use the dremio query es to find the pushdown failed

SQL as follows:
SELECT a.apply_no, a.contract_no, a.product_code, c.name, c.id_no, c.cellphone,
c.unit_name, c.emp_phone, c.abode_city_name, al.workflow_code
FROM “es-test”. “Cif.apply_main”. “Default” a
left join “es-test”. “cif.apply_cust_info”. “default” c on a.apply_no = c.apply_no
left join “es-test”. “cif.appl”. “default” al on a.apply_no = al.BUS_APPL_ID
left join “es-test”. “cif.tom_orders”. “default” t on a.apply_no = t.app_id
left join “es-test”. “cif.apply_cust_contact”. “default” ac on a.apply_no = ac.apply_no
where
a.apply_no in (‘1111222233345263754’, ‘1111222233355257368’, ‘1111222233355258292’) and
limit 100

However, if the SQL can be pushed down this way
SELECT a.apply_no, a.contract_no, a.product_code, c.name, c.id_no, c.cellphone,
c.unit_name, c.emp_phone, c.abode_city_name, al.workflow_code
FROM “es-test”. “Cif.apply_main”. “Default” a
left join “es-test”. “cif.apply_cust_info”. “default” c on a.apply_no = c.apply_no
left join “es-test”. “cif.appl”. “default” al on a.apply_no = al.BUS_APPL_ID
left join “es-test”. “cif.tom_orders”. “default” t on a.apply_no = t.app_id
left join “es-test”. “cif.apply_cust_contact”. “default” ac on a.apply_no = ac.apply_no
where
a.apply_no in (‘1111222233345263754’, ‘1111222233355257368’, ‘1111222233355258292’) and
c.apply_no in (‘1111222233345263754’, ‘1111222233355257368’, ‘1111222233355258292’) and
al. BUS_APPL_ID in (‘1111222233345263754’, ‘1111222233355257368’, ‘1111222233355258292’) and
t.app_id in (‘1111222233345263754’, ‘1111222233355257368’, ‘1111222233355258292’) and
ac.apply_no in (‘1111222233345263754’, ‘1111222233355257368’, ‘1111222233355258292’)
limit 100

How to use it correctly?

2

Hi,

Just to make sure I have a full picture of what’s happening, could you upload the query profiles (one where the pushdown works and one where it doesn’t).

3

Dremio we use is the community version, did not find the upload button, how to upload it?

thx

4

You can download and then attach to this thread or email to steven@

5

Hi, you can use any of the ES join queries, are able to reproduce the problem, provided that: the connection conditions appear only once where conditions, such as: select * from es.a left join es.b on a.id = b. id where a.id in (xxx, xxx, xxx), but changed to be able to push down this: select * from es.a left join es.b on a.id = b.id where a.id in (xxx, xxx, xxx) and b.id in (xxx, xxx, xxx)

6

queryplan