Show statistics min and max in explain

[NGA-TRAN]

Is your feature request related to a problem or challenge?

We want see statistics Min and Max in the query plan to verify whether the plan is optimized correctly. Currently, there are only statistics Rows and Bytes are displayed in the query plan. We want to add Min and Max.

Table for the reproducer

set datafusion.explain.show_statistics = true;


create table t1(state string, city string, min_temp float, area int, time timestamp) as values 
    ('MA', 'Boston', 70.4, 1, 50),
    ('MA', 'Bedford', 71.59, 2, 150),
    ('CA', 'SF', 79.0, 1, 300),
    ('MA', 'Boston', 75.4, 3, 250),
    ('MA', 'Andover', 69.5, 4, 250),
    ('MA', 'Bedford', 78.2, 2, 150),
    ('MA', 'Boston', 65.0, 2, 250),
    ('CA', 'SJ', 78.4, 1, 300),
    ('MA', 'Reading', 53.0, 4, 250),
    ('CA', 'SJ', 75.4, 5, 350);

explain select * from t1 where time <= to_timestamp(350);
+---------------+---------------------------------------------------------------------------------------------------+
| plan_type     | plan                                                                                              |
+---------------+---------------------------------------------------------------------------------------------------+
| logical_plan  | Filter: t1.time <= TimestampNanosecond(350000000000, None)                                        |
|               |   TableScan: t1 projection=[state, city, min_temp, area, time]                                    |
| physical_plan | CoalesceBatchesExec: target_batch_size=8192, statistics=[Rows=Absent, Bytes=Absent]               |
|               |   FilterExec: time@4 <= 350000000000, statistics=[Rows=Absent, Bytes=Absent]                      |
|               |     MemoryExec: partitions=1, partition_sizes=[1], statistics=[Rows=Exact(10), Bytes=Exact(2960)] |
|               |                                                                                                   |
+---------------+---------------------------------------------------------------------------------------------------+

Describe the solution you'd like

Note that Min and Max go with column. It will be clear if we show column name for the Min and max but since we do not store column names right now, we will also show a list of (min, max) pairs like

statistics=[Rows=Exact(10), Bytes=Exact(2960), [ (Min=Exact(1), Max=Exact(100)), (Min=Exact(..), Max=Exact(...)), ... ] ]

In the future if there is demand, we will add column names

Here is how they look in my local branch. I will open an PR for this soon

explain select * from t1 where time <= to_timestamp(350);
+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| plan_type     | plan                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| logical_plan  | Filter: t1.time <= TimestampNanosecond(350000000000, None)                                                                                                                                                                                                                                                                                                                                                                                              |
|               |   TableScan: t1 projection=[state, city, min_temp, area, time]                                                                                                                                                                                                                                                                                                                                                                                          |
| physical_plan | CoalesceBatchesExec: target_batch_size=8192, statistics=[Rows=Absent, Bytes=Absent, [(Column[0]: Min=Absent, Max=Absent, Null=Absent, Distinct=Absent),(Column[1]: Min=Absent, Max=Absent, Null=Absent, Distinct=Absent),(Column[2]: Min=Absent, Max=Absent, Null=Absent, Distinct=Absent),(Column[3]: Min=Absent, Max=Absent, Null=Absent, Distinct=Absent),(Column[4]: Min=Absent, Max=Absent, Null=Absent, Distinct=Absent)]]                        |
|               |   FilterExec: time@4 <= 350000000000, statistics=[Rows=Absent, Bytes=Absent, [(Column[0]: Min=Absent, Max=Absent, Null=Absent, Distinct=Absent),(Column[1]: Min=Absent, Max=Absent, Null=Absent, Distinct=Absent),(Column[2]: Min=Absent, Max=Absent, Null=Absent, Distinct=Absent),(Column[3]: Min=Absent, Max=Absent, Null=Absent, Distinct=Absent),(Column[4]: Min=Absent, Max=Absent, Null=Absent, Distinct=Absent)]]                               |
|               |     MemoryExec: partitions=1, partition_sizes=[1], statistics=[Rows=Exact(1), Bytes=Exact(2896), [(Column[0]: Min=Absent, Max=Absent, Null=Exact(0), Distinct=Absent),(Column[1]: Min=Absent, Max=Absent, Null=Exact(0), Distinct=Absent),(Column[2]: Min=Absent, Max=Absent, Null=Exact(0), Distinct=Absent),(Column[3]: Min=Absent, Max=Absent, Null=Exact(0), Distinct=Absent),(Column[4]: Min=Absent, Max=Absent, Null=Exact(0), Distinct=Absent)]] |
|               |                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set. Query took 0.008 seconds.


❯ explain select state, min_temp from t1 where time <= to_timestamp(350);
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| plan_type     | plan                                                                                                                                                                                                                                                                                                              |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| logical_plan  | Projection: t1.state, t1.min_temp                                                                                                                                                                                                                                                                                 |
|               |   Filter: t1.time <= TimestampNanosecond(350000000000, None)                                                                                                                                                                                                                                                      |
|               |     TableScan: t1 projection=[state, min_temp, time]                                                                                                                                                                                                                                                              |
| physical_plan | ProjectionExec: expr=[state@0 as state, min_temp@1 as min_temp], statistics=[Rows=Absent, Bytes=Absent, [(Column[0]: Min=Absent, Max=Absent, Null=Absent, Distinct=Absent),(Column[1]: Min=Absent, Max=Absent, Null=Absent, Distinct=Absent)]]                                                                    |
|               |   CoalesceBatchesExec: target_batch_size=8192, statistics=[Rows=Absent, Bytes=Absent, [(Column[0]: Min=Absent, Max=Absent, Null=Absent, Distinct=Absent),(Column[1]: Min=Absent, Max=Absent, Null=Absent, Distinct=Absent),(Column[2]: Min=Absent, Max=Absent, Null=Absent, Distinct=Absent)]]                    |
|               |     FilterExec: time@2 <= 350000000000, statistics=[Rows=Absent, Bytes=Absent, [(Column[0]: Min=Absent, Max=Absent, Null=Absent, Distinct=Absent),(Column[1]: Min=Absent, Max=Absent, Null=Absent, Distinct=Absent),(Column[2]: Min=Absent, Max=Absent, Null=Absent, Distinct=Absent)]]                           |
|               |       MemoryExec: partitions=1, partition_sizes=[1], statistics=[Rows=Exact(1), Bytes=Exact(2896), [(Column[0]: Min=Absent, Max=Absent, Null=Exact(0), Distinct=Absent),(Column[1]: Min=Absent, Max=Absent, Null=Exact(0), Distinct=Absent),(Column[2]: Min=Absent, Max=Absent, Null=Exact(0), Distinct=Absent)]] |
|               |                                                                                                                                                                                                                                                                                                                   |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Describe alternatives you've considered

No response

Additional context

No response

[NGA-TRAN]

@alamb I am actively working on this

[alamb]

FYI @berkaysynnada who I think will also be interested in this feature