Query ES pushdown failed

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?

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

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

thx

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

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)

https://drive.google.com/open?id=1IVCm7pZcPptzw5Nifg1rgYYxZeGZgk0K

queryplan