optimizer: optimize count(*) with column pruning

[skyzh]

Related issues and PRs:

• expression: do not bind column for count(*) in binder #388
• optimizer: move column pruner from binder #203
• binder: plan not optimal for count(*) with filter #482
• count(*) get extra column #200
• feat(storage): support scan row handler only #447
• fix(planner): row handler not included when count(*) is used as subexpression #502

It seems that count(*) and row handlers are causing too many problems for us... The underlying cause it that:

• Which column to scan when doing count(*)? This is a hard question. For example, select count(*) from table requires us to scan arbitrary column, while select count(*) from table where x = 1 requires us to scan x, and select count(*) from a join b on xxx requires us to scan on the joined table...
• How to optimize count(*) with row handler? We have hand-wired a lot of rules in binder and planner.

Also, the RowHandler currently serves two purposes in our system:

• delete from table requires a rowhandler to insert delete vectors (aka. delete tombstones).
• count(*) can be optimized to issue no I/Os on user column by rowcount(rowhandler).

Therefore, I propose to move all such logics into the optimizer column pruning framework.

• For the RowHandler column, we should regard it the same as the user column in optimizer.
• For count(*), we should regard it as counting on all columns rowcount(a, b, c, ..., rowhandler) in binder, and later optimize it into either rowcount(rowhandler) or rowcount(other column). We should regard it as count(1).
• The column pruning framework should be able to prune columns in aggregation functions (like the case for count(*)).
• If there are no columns left at table scan plan, we add a RowHandler column.

Any ideas? cc @st1page @Fedomn @xxchan

[skyzh]

... this issue may also be discussed in a border context. For example, how to support hidden column in our optimizer?

[BowenXiao1999]

I think we can summarize the benefits of making optimizer/planner hidden-column aware, if any.

[st1page]

-1, I don't think here we need is a hidden column in computing, row handler is just "hidden" in binder and the optimizer will treat it and other columns in the same

[xxchan]

I think current solution of handling with_row_handler only in plan_select will be enough ... until we support sub-queries? Is there any other complex pruning cases now?

The proposal looks elegant to me.

[skyzh]

Yes, I'm quite concerned with sub-queries... At that time, the un-nested queries can be converted into things that normal people won't write in SQL. I guess we will miss some cases for row_handler.

[st1page]

Therefore, I propose to move all such logics into the optimizer column pruning framework.

• For the RowHandler column, we should regard it the same as the user column in optimizer.
• For count(*), we should regard it as counting on all columns rowcount(a, b, c, ..., rowhandler) in binder, and later optimize it into either rowcount(rowhandler) or rowcount(other column).
• The column pruning framework should be able to prune columns in aggregation functions (like the case for count(*)).

+1

[Fedomn]

The proposal looks great, meanwhile, it seems we already have the general prune_col method in PlanNode and future plan, just link #420 (comment)

[skyzh]

Yes, the column pruning framework mentioned in this discussion is exactly the prune_col functionality added days before.

[jiangzhe]

count(*) is a special syntax defined in SQL standard. It can be evaluated by refering to a NOT NULL column in the returned result set, but not necessary.
IMO, convert count(*) to count(1) is more convenient, as we always need to handle cases of aggregation function with constant value as its input, e.g. count(1), sum(100).
Regarding column pruning, there could be cases that all columns in child operator are pruned, and I think it's preferred from performance perspective, if execution layer could handle such result set (N rows 0 columns). Otherwise, a single column result set with constant value, e.g. 1 could be generated to satisfy the executor.

[skyzh]

Good idea! I've never thought about using a constant count to do count(*).

[likg227]

If there are no columns left at table scan executor, we add a RowHandler column.

Why do we handle this special case in executor instead of optimizer?

[skyzh]

This is expected to be handled in optimizer. I've changed it to

If there are no columns left at table scan plan, we add a RowHandler column.

[likg227]

I mean, after pruning the columns, we add rowhandler if there are no columns.

[xxchan]

After we regard count(*) as count(1), then the notion RowHandler is only used in Delete?

If so, maybe we don't even need to consider RowHandler as usual column in optimizer, because there are just no RowHandler columns... We just add an empty check in TableScanExecutor?

[skyzh]

Looks good!

[skyzh]

Adding empty check in TableScanExecutor may have some problems. If we don't process this in optimizer stage, the plan nodes don't have the correct schema?