BindableTableScan.computeSelfCost does not use projects or filters info into account

[Luis Fernando Kauer <lf...@yahoo.com.br.INVALID>]

At first I was using TranslatableTable for my adapters (read files in different formats), but I realized that many rules apply only to ProjectableFilterableTable, so I started using it to take advantage of all these builtin rules.
Restricting the projects that need to be scanned is very important because it can reduce a lot the time to scan the table.

Unfortunately I'm having trouble making Calcite select a plan that pushes the used projects to the table scan in simple queries that have a filter.  The plan selected has only the filters and no projects.
What seems to be happening is that BindableFilterRule and BindableProjectRule are applied generating different plans, some with only the filter, some with only the projects and some with both.
The planner has to decide which one to choose, but BindableTableScan.computeSelfCost just multiplies the cost by 0.01, without taking into account the restriction of projects or the existence of filters.
Since the different plans with and without the projects and filters end up with the same cost, the planner chooses the first plan, which in my case has been a BindableTableScan with only the filters, generated by the BindableFilterRule.
The query is something like:
select c1, c2 from t where c3=10

Changing BindableTableScan.computeSelfCost to calculate different costs according to projects and filters made the planner choose it right, but it would be nice to allow each adapter/table to define its own costs, since projects and filters can have different costs.

Re: BindableTableScan.computeSelfCost does not use projects or filters info into account

[Julian Hyde <jh...@apache.org>]

I agree, BindableTableScan.computeSelfCost should take projects and filters into account. Can you log a JIRA case for that.

Be aware that ProjectableFilterableTable is an “80:20” thing — easy, good enough for simple cases, but maybe not expressive enough for hard cases. TranslatableTable is probably better for the harder cases; don’t avoid it just because (being the path less traveled) it has a few bugs.

Julian


> On Aug 28, 2017, at 8:02 AM, Luis Fernando Kauer <lf...@yahoo.com.br.INVALID> wrote:
> 
> At first I was using TranslatableTable for my adapters (read files in different formats), but I realized that many rules apply only to ProjectableFilterableTable, so I started using it to take advantage of all these builtin rules.
> Restricting the projects that need to be scanned is very important because it can reduce a lot the time to scan the table.
> 
> Unfortunately I'm having trouble making Calcite select a plan that pushes the used projects to the table scan in simple queries that have a filter.  The plan selected has only the filters and no projects.
> What seems to be happening is that BindableFilterRule and BindableProjectRule are applied generating different plans, some with only the filter, some with only the projects and some with both.
> The planner has to decide which one to choose, but BindableTableScan.computeSelfCost just multiplies the cost by 0.01, without taking into account the restriction of projects or the existence of filters.
> Since the different plans with and without the projects and filters end up with the same cost, the planner chooses the first plan, which in my case has been a BindableTableScan with only the filters, generated by the BindableFilterRule.
> The query is something like:
> select c1, c2 from t where c3=10
> 
> Changing BindableTableScan.computeSelfCost to calculate different costs according to projects and filters made the planner choose it right, but it would be nice to allow each adapter/table to define its own costs, since projects and filters can have different costs.