github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/norm/rules/prune_cols.opt

# =============================================================================
# prune_cols.opt contains normalization rules that eliminate columns that are
# never used. For example:
#
#   SELECT x FROM (SELECT x, y, z FROM a) WHERE y = 10
#
# The "z" column is never referenced, either by the filter condition or by the
# final projection. Therefore, the query can be rewritten as:
#
#   SELECT x FROM (SELECT x, y FROM a) WHERE y = 10
#
# Unused columns are very common, especially when the SQL * symbol is used to
# select all columns in a table. They can be expensive to fetch and transfer, so
# it's important to retain only columns that are actually needed by the query.
#
# The rules work by "pushing down" a new Project operator into any child inputs
# that have unused columns. The Project only includes columns that are needed by
# some operator in its subtree. The new Project may then spawn a sub-Project of
# its own that gets pushed even further down the tree. Eventually, a Project
# recursively reaches an operator that is capable of filtering columns, like
# Scan or Project, and it will be merged into that operator.
#
# Pruning unused columns is not desirable if the Project operator "gets stuck"
# during push down, and is unable to merge with another column filtering
# operator. This situation causes the expression tree to become littered with
# extra Project operators that impede pattern matching. To avoid this problem,
# the RelationalProps.Rules.PruneCols property tracks columns which can be
# pruned without requiring extra Project operators. The Prune rules use this to
# only push down Project operators that are sure to merge into another operator
# at the end of their journey. See the PruneCols comment for more details.
# =============================================================================

# PruneProjectCols discards columns from a nested project which are not used by
# the outer project.
[PruneProjectCols, Normalize]
(Project
    $project:(Project)
    $projections:*
    $passthrough:* &
        (CanPruneCols
            $project
            $needed:(UnionCols
                (ProjectionOuterCols $projections)
                $passthrough
            )
        )
)
=>
(Project (PruneCols $project $needed) $projections $passthrough)

# PruneScanCols discards Scan operator columns that are never used. The needed
# columns are pushed down into the Scan's opt.ScanOpDef private.
[PruneScanCols, Normalize]
(Project
    $input:(Scan)
    $projections:*
    $passthrough:* &
        (CanPruneCols
            $input
            $needed:(UnionCols
                (ProjectionOuterCols $projections)
                $passthrough
            )
        )
)
=>
(Project (PruneCols $input $needed) $projections $passthrough)

# PruneSelectCols discards Select input columns that are never used.
#
# The PruneCols property should prevent this rule (which pushes Project below
# Select) from cycling with the PushSelectIntoProject rule (which pushes Select
# below Project).
[PruneSelectCols, Normalize]
(Project
    (Select $input:* $filters:*)
    $projections:*
    $passthrough:* &
        (CanPruneCols
            $input
            $needed:(UnionCols3
                (FilterOuterCols $filters)
                (ProjectionOuterCols $projections)
                $passthrough
            )
        )
)
=>
(Project
    (Select (PruneCols $input $needed) $filters)
    $projections
    $passthrough
)

# PruneLimitCols discards Limit input columns that are never used.
#
# The PruneCols property should prevent this rule (which pushes Project below
# Limit) from cycling with the PushLimitIntoProject rule (which pushes Limit
# below Project).
[PruneLimitCols, Normalize]
(Project
    (Limit $input:* $limit:* $ordering:*)
    $projections:*
    $passthrough:* &
        (CanPruneCols
            $input
            $needed:(UnionCols3
                (OrderingCols $ordering)
                (ProjectionOuterCols $projections)
                $passthrough
            )
        )
)
=>
(Project
    (Limit
        (PruneCols $input $needed)
        $limit
        (PruneOrdering $ordering $needed)
    )
    $projections
    $passthrough
)

# PruneOffsetCols discards Offset input columns that are never used.
#
# The PruneCols property should prevent this rule (which pushes Project below
# Offset) from cycling with the PushOffsetIntoProject rule (which pushes Offset
# below Project).
[PruneOffsetCols, Normalize]
(Project
    (Offset $input:* $offset:* $ordering:*)
    $projections:*
    $passthrough:* &
        (CanPruneCols
            $input
            $needed:(UnionCols3
                (OrderingCols $ordering)
                (ProjectionOuterCols $projections)
                $passthrough
            )
        )
)
=>
(Project
    (Offset
        (PruneCols $input $needed)
        $offset
        (PruneOrdering $ordering $needed)
    )
    $projections
    $passthrough
)

# PruneJoinLeftCols discards columns on the left side of a join that are never
# used.
[PruneJoinLeftCols, Normalize]
(Project
    $input:(Join $left:* $right:* $on:* $private:*)
    $projections:*
    $passthrough:* &
        (CanPruneCols
            $left
            $needed:(UnionCols4
                (OuterCols $right)
                (FilterOuterCols $on)
                (ProjectionOuterCols $projections)
                $passthrough
            )
        )
)
=>
(Project
    ((OpName $input)
        (PruneCols $left $needed)
        $right
        $on
        $private
    )
    $projections
    $passthrough
)

# PruneJoinRightCols discards columns on the right side of a join that are never
# used.
#
# The PruneCols property should prevent this rule (which pushes Project below
# Join) from cycling with the TryDecorrelateProject rule (which pushes Join
# below Project).
[PruneJoinRightCols, Normalize]
(Project
    $input:(Join $left:* $right:* $on:* $private:*)
    $projections:*
    $passthrough:* &
        (CanPruneCols
            $right
            $needed:(UnionCols3
                (FilterOuterCols $on)
                (ProjectionOuterCols $projections)
                $passthrough
            )
        )
)
=>
(Project
    ((OpName $input)
        $left
        (PruneCols $right $needed)
        $on
        $private
    )
    $projections
    $passthrough
)

# PruneSemiAntiJoinRightCols discards columns on the right side of a
# Semi or Anti join that are never used. This is similar to PruneJoinRightCols.
# PruneJoinRightCols normally prunes the RHS of a join but it can't do that
# in the case of Semi/Anti joins because the projection is eliminated after
# the LHS is pruned. This rule doesn't require a projection over the Semi/Anti
# join in order to prune the RHS.
[PruneSemiAntiJoinRightCols, Normalize]
(SemiJoin | SemiJoinApply | AntiJoin | AntiJoinApply
    $left:*
    $right:*
    $on:*
    $private:* &
        (CanPruneCols $right $needed:(FilterOuterCols $on))
)
=>
((OpName) $left (PruneCols $right $needed) $on $private)

# PruneAggCols discards aggregation columns in a GroupBy that are never used.
# Note that UpsertDistinctOn is not included here because its columns are always
# used.
[PruneAggCols, Normalize]
(Project
    $input:(GroupBy | ScalarGroupBy | DistinctOn
            | EnsureDistinctOn
        $innerInput:*
        $aggregations:*
        $groupingPrivate:*
    )
    $projections:*
    $passthrough:* &
        (CanPruneAggCols
            $aggregations
            $needed:(UnionCols
                (ProjectionOuterCols $projections)
                $passthrough
            )
        )
)
=>
(Project
    ((OpName $input)
        $innerInput
        (PruneAggCols $aggregations $needed)
        $groupingPrivate
    )
    $projections
    $passthrough
)

# PruneGroupByCols discards GroupBy input columns that are never used. Note that
# UpsertDistinctOn is not included here because its columns are always used.
[PruneGroupByCols, Normalize]
(GroupBy | ScalarGroupBy | DistinctOn | EnsureDistinctOn
    $input:*
    $aggregations:*
    $groupingPrivate:* &
        (CanPruneCols
            $input
            $needed:(UnionCols
                (AggregationOuterCols $aggregations)
                (NeededGroupingCols $groupingPrivate)
            )
        )
)
=>
((OpName)
    (PruneCols $input $needed)
    $aggregations
    (PruneOrderingGroupBy $groupingPrivate $needed)
)

# PruneValuesCols discards Values columns that are never used.
[PruneValuesCols, Normalize]
(Project
    $input:(Values)
    $projections:*
    $passthrough:* &
        (CanPruneCols
            $input
            $needed:(UnionCols
                (ProjectionOuterCols $projections)
                $passthrough
            )
        )
)
=>
(Project (PruneCols $input $needed) $projections $passthrough)

# PruneOrdinalityCols discards Ordinality input columns that are never used.
[PruneOrdinalityCols, Normalize]
(Project
    (Ordinality $input:* $ordinalityPrivate:*)
    $projections:*
    $passthrough:* &
        (CanPruneCols
            $input
            $needed:(UnionCols3
                (NeededOrdinalityCols $ordinalityPrivate)
                (ProjectionOuterCols $projections)
                $passthrough
            )
        )
)
=>
(Project
    (Ordinality
        (PruneCols $input $needed)
        (PruneOrderingOrdinality $ordinalityPrivate $needed)
    )
    $projections
    $passthrough
)

# PruneExplainCols discards Explain input columns that are never used by its
# required physical properties.
[PruneExplainCols, Normalize]
(Explain
    $input:*
    $explainPrivate:* &
        (CanPruneCols
            $input
            $needed:(NeededExplainCols $explainPrivate)
        )
)
=>
(Explain (PruneCols $input $needed) $explainPrivate)

# PruneProjectSetCols discards ProjectSet columns that are never used.
[PruneProjectSetCols, Normalize]
(Project
    $input:(ProjectSet $innerInput:* $zip:*)
    $projections:*
    $passthrough:* &
        (CanPruneCols
            $input
            $needed:(UnionCols3
                (ZipOuterCols $zip)
                (ProjectionOuterCols $projections)
                $passthrough
            )
        )
)
=>
(Project
    (ProjectSet (PruneCols $innerInput $needed) $zip)
    $projections
    $passthrough
)

# PruneWindowOutputCols eliminates unused window functions from a Window
# expression.
[PruneWindowOutputCols, Normalize]
(Project
    (Window $input:* $windows:* $private:*)
    $projections:*
    $passthrough:* &
        (CanPruneWindows
            $needed:(UnionCols
                (ProjectionOuterCols $projections)
                $passthrough
            )
            $windows
        )
)
=>
(Project
    (Window $input (PruneWindows $needed $windows) $private)
    $projections
    $passthrough
)

# PruneWindowInputCols discards window passthrough columns which are never used.
# NB: This rule should go after PruneWindowOutputCols, or else this rule can get
# into a cycle.
[PruneWindowInputCols, Normalize]
(Project
    $input:(Window $innerInput:* $fn:* $private:*)
    $projections:*
    $passthrough:* &
        (CanPruneCols
            $input
            $needed:(UnionCols3
                (NeededWindowCols $fn $private)
                (ProjectionOuterCols $projections)
                $passthrough
            )
        )
)
=>
(Project
    (Window (PruneCols $innerInput $needed) $fn $private)
    $projections
    $passthrough
)

# PruneMutationFetchCols removes columns from the mutation operator's FetchCols
# set if they are never used. Removing FetchCols can in turn can trigger the
# PruneMutationInputCols rule, which can prune any input columns which are now
# unreferenced.
[PruneMutationFetchCols, Normalize]
(Update | Upsert | Delete
    $input:*
    $checks:*
    $mutationPrivate:* &
        (CanPruneMutationFetchCols
            $mutationPrivate
            $needed:(NeededMutationFetchCols
                (OpName)
                $mutationPrivate
            )
        )
)
=>
((OpName)
    $input
    $checks
    (PruneMutationFetchCols $mutationPrivate $needed)
)

# PruneMutationInputCols discards input columns that are never used by the
# mutation operator.
[PruneMutationInputCols, Normalize]
(Insert | Update | Upsert | Delete
    $input:*
    $checks:*
    $mutationPrivate:* &
        (CanPruneCols
            $input
            $needed:(NeededMutationCols $mutationPrivate $checks)
        )
)
=>
((OpName) (PruneCols $input $needed) $checks $mutationPrivate)

# PruneReturningCols removes columns from the mutation operator's ReturnCols
# set if they are not used in the RETURNING clause of the mutation.
# Removing ReturnCols will then allow the PruneMutationFetchCols to be more
# conservative with the fetch columns.
# TODO(ridwanmsharif): Mutations shouldn't need to return the primary key
# columns. Make appropriate changes to SQL execution to accommodate this.
[PruneMutationReturnCols, Normalize]
(Project
    $input:(Insert | Update | Upsert | Delete
        $innerInput:*
        $checks:*
        $mutationPrivate:*
    )
    $projections:*
    $passthrough:* &
        (CanPruneMutationReturnCols
            $mutationPrivate
            $needed:(UnionCols3
                (PrimaryKeyCols (MutationTable $mutationPrivate))
                (ProjectionOuterCols $projections)
                $passthrough
            )
        )
)
=>
(Project
    ((OpName $input)
        $innerInput
        $checks
        (PruneMutationReturnCols $mutationPrivate $needed)
    )
    $projections
    $passthrough
)

# PruneWithScanCols discards columns scanned from the WithScan that are never
# used.
[PruneWithScanCols, Normalize]
(Project
    $input:(WithScan)
    $projections:*
    $passthrough:* &
        (CanPruneCols
            $input
            $needed:(UnionCols
                (ProjectionOuterCols $projections)
                $passthrough
            )
        )
)
=>
(Project (PruneCols $input $needed) $projections $passthrough)

# PruneWithCols pushes a Project operator beneath a With. It's ok to
# unconditionally push this Project down, since in the pruning case, we're
# getting the project closer to the source of any prune requests, and if we
# just end up with a Project incidentally, it's safe to just always push it
# down.
[PruneWithCols, Normalize]
(Project
    (With $binding:* $input:* $private:*)
    $projections:*
    $passthrough:*
)
=>
(With
    $binding
    (Project $input $projections $passthrough)
    $private
)

# PruneUnionAllCols prunes columns from the left and right input relations that
# are never used. Since UNION ALL preserves duplicates, any column may be pruned
# if it is not needed, which is not generally true of set operators.
#
# Since UnionAll requires that both inputs have an equal number of columns,
# rather than using PruneCols to prune the left and right sides, this rule
# pushes down Projects on both sides to ensure that exactly the needed columns
# are passed as input to the UnionAll, to prevent situations where one side has
# more columns left over after PruneCols than the other (for instance, if $left
# is a normal scan where all columns may be pruned, but $right is a scan with a
# filter, leading to an additional column being kept on just the right side).
# If extraneous, these Projects may be cleaned up later by rules like
# EliminateProject.
[PruneUnionAllCols, Normalize]
(Project
    $union:(UnionAll $left:* $right:* $colmap:*)
    $projections:*
    $passthrough:* &
        (CanPruneCols
            $union
            $needed:(UnionCols
                (ProjectionOuterCols $projections)
                $passthrough
            )
        )
)
=>
(Project
    (UnionAll
        (Project $left [] (NeededColMapLeft $needed $colmap))
        (Project $right [] (NeededColMapRight $needed $colmap))
        (PruneSetPrivate $needed $colmap)
    )
    $projections
    $passthrough
)