Skip to content

Simplify col1 || 'a' || 'b' || col2 to col1 || 'ab' || col2 #17158

Description

@alamb

Basically when optimizing queries, doing operations on scalars at plan time is more efficient than doing them at query time

For eample, a query like this

select c1 || 'a' || 'b' || c2 from t;

Would be more efficient to do like this (evaluate the a || b at plan time to ab)

select c1 || 'ab' || c2 from t;

You can see that this currently does not happen:

> create table t (c1 varchar, c2 varchar);
0 row(s) fetched.
Elapsed 0.001 seconds.

> insert into t values ('a', 'b');
+-------+
| count |
+-------+
| 1     |
+-------+

The explain plan still shows the 'a' || 'b':

> explain select c1 || 'a' || 'b' || c2 from t;
+---------------+-------------------------------+
| plan_type     | plan                          |
+---------------+-------------------------------+
| physical_plan | ┌───────────────────────────┐ |
|               | │       ProjectionExec      │ |
|               | │    --------------------   │ |
|               | │ t.c1 || Utf8("a") || Utf8(│ |
|               | │       "b") || t.c2:       │ |
|               | │     c1 || a || b || c2    │ |
|               | └─────────────┬─────────────┘ |
|               | ┌─────────────┴─────────────┐ |
|               | │       DataSourceExec      │ |
|               | │    --------------------   │ |
|               | │         bytes: 368        │ |
|               | │       format: memory      │ |
|               | │          rows: 1          │ |
|               | └───────────────────────────┘ |
|               |                               |
+---------------+-------------------------------+
1 row(s) fetched.
Elapsed 0.003 seconds.

> explain format indent select c1 || 'a' || 'b' || c2 from t;
+---------------+------------------------------------------------------------------------------------------------------+
| plan_type     | plan                                                                                                 |
+---------------+------------------------------------------------------------------------------------------------------+
| logical_plan  | Projection: t.c1 || Utf8View("a") || Utf8View("b") || t.c2 AS t.c1 || Utf8("a") || Utf8("b") || t.c2 |
|               |   TableScan: t projection=[c1, c2]                                                                   |
| physical_plan | ProjectionExec: expr=[c1@0 || a || b || c2@1 as t.c1 || Utf8("a") || Utf8("b") || t.c2]              |
|               |   DataSourceExec: partitions=1, partition_sizes=[1]                                                  |
|               |                                                                                                      |
+---------------+------------------------------------------------------------------------------------------------------+
2 row(s) fetched.
Elapsed 0.001 seconds.

Interestingly, if you change the order, you can see that 'a' || 'b' has been collapsed

> explain format indent select 'a' || 'b' || c2 from t;
+---------------+----------------------------------------------------------------------+
| plan_type     | plan                                                                 |
+---------------+----------------------------------------------------------------------+
| logical_plan  | Projection: Utf8View("ab") || t.c2 AS Utf8("a") || Utf8("b") || t.c2 |
|               |   TableScan: t projection=[c2]                                       |
| physical_plan | ProjectionExec: expr=[ab || c2@0 as Utf8("a") || Utf8("b") || t.c2]  |
|               |   DataSourceExec: partitions=1, partition_sizes=[1]                  |
|               |                                                                      |
+---------------+----------------------------------------------------------------------+
2 row(s) fetched.
Elapsed 0.001 seconds.

Metadata

Metadata

Assignees

No one assigned

    Labels

    enhancementNew feature or requestperformanceMake DataFusion faster

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions