Hi
I'm Newbie to *Calcite*...
I'd like to enhance the existing elasticsearch adapter to allow some join
optimization:
Consider the next query (joins 3 tables):
Tables:
customer - 10K docs
catalog_sales - 1M docks
inventory - 100K docs
Query:
"select "c_customer_sk", "c_first_name", "c_birth_country", "c_birth_year"
, "cs_sales_price", "inv_item_sk" from "customer_raw"."customer" "customer"
"join "catalog_sales"."catalog_sales" "catalog_sales" on
"catalog_sales"."cs_bill_customer_sk" = "customer"."c_customer_sk"
"join "inventory"."inventory" "inventory" on
"inventory"."inv_item_sk" = "catalog_sales"."cs_item_sk"
Now I'd like the execution plan to pushdown the results from the smallest
table as where clause (filter) parameters to the next joined table:
Optimzed query:
"select "c_customer_sk", "c_first_name", "c_birth_country", "c_birth_year"
, "cs_sales_price", "inv_item_sk" from "customer_raw"."customer" "customer
"
"join "catalog_sales"."catalog_sales" "catalog_sales" on
"catalog_sales"."cs_bill_customer_sk" = "customer"."c_customer_sk"
and "customer"."c_customer_sk" in ("13413","4341343"....)
"join "inventory"."inventory" "inventory" on
"inventory"."inv_item_sk" = "catalog_sales"."cs_item_sk" and
"catalog_sales"."cs_item_sk" in ("1343343","43213413"....)
Is it possible and is so what is the best way to do this ?
Thanks so much...
Lior
