41908: opt: introduce PushLimitIntoOrdinality rule r=savoie a=savoie

This optimizer rule is the first of several that will push limit
operators as far down the tree as possible, in order to remove the need
for logic in `opt_limits.go` that currently recurses down the plan
tree ensuring that leaf scan nodes have hard limits set even when a
limit operator is much higher up in the tree. The following explanation
is also included as a comment above the rule:

> PushLimitIntoOrdinality pushes the Limit operator into the Ordinality
> operator when the ordering associated with both operators allows it.
>
> Pushing the limit as far as possible down the tree shouldn't have
> negative effects, but will reduce the number of rows processed by
> operators higher up, and if the limit is pushed all the way down to a
> scan, the scan can be limited directly.
>
> In order to prevent this rule from affecting:
> 1. the set of rows kept by the limit,
> 2. the ordinals assigned to those rows by the ordinality, and
> 3. the final ordering of the rows,
> the rule should only be applied if the ordinality's ordering implies
> the original limit's ordering (see OrderingChoice.Implies).
>
> The new limit's ordering must also be "extended" to imply the
> ordinality's ordering, which is done by setting it to be the
> intersection of the old limit's ordering and the ordinality's ordering
> (see OrderingChoice.Intersection).

Release note: None

Co-authored-by: Céline O'Neil <celineloneil@gmail.com>

Previously, the heuristic planner function `applyLimit` propagated and set both
hard and soft limits. Hard limit propagation is now handled in the optimizer by
using rules to push Limit operators down the tree as far as is possible, and to
remove Limits by adding hard limits to child Scan operators. As a result, a
majority of the logic within `applyLimit` that supported propagating hard
limits was never exercised, since optimization rules would have already
eliminated situations in which Limit operators high up in the tree could be the
cause of hard limits further down the tree. The one remaining case for which
`applyLimit` was still responsible for propagating hard limits was eliminated
by the normalization rule introduced in cockroachdb#41908.

The entirety of `applyLimit` will eventually be removed and replaced by the
optimizer. Removing hard limit propagation from it entirely is a step in this
direction, and will make it easier to refactor soft limit propagation later.

This patch changes `applyLimit` into `applySoftLimit`, which no longer performs
any hard limit propagation; the responsibility for setting any hard limits on
scanNodes and spoolNodes is now left to the optimizer.

It also renames `setUnlimited` to `propagateSoftLimits` in order to indicate
more clearly that this function is not used to remove any limits that might
have been set on a node, but instead to trigger soft limit propagation in case
a limit hint can be introduced further down the tree.

In the case where the optimizer does not generate a limited Scan in place of a
Limit around a Scan (because the Scan cannot be constrained), previously
`applyLimit` would still set the `hardLimit` field of the scan.  This change
will result in the `softLimit` being set instead.  The end state of the
planNode tree is otherwise the same as before this refactor.

Release note: None

Previously, the heuristic planner function `applyLimit` propagated and set both
hard and soft limits. Hard limit propagation is now handled in the optimizer by
using rules to push Limit operators down the tree as far as is possible, and to
remove Limits by adding hard limits to child Scan operators. As a result, a
majority of the logic within `applyLimit` that supported propagating hard
limits was never exercised, since optimization rules would have already
eliminated situations in which Limit operators high up in the tree could be the
cause of hard limits further down the tree. The one remaining case for which
`applyLimit` was still responsible for propagating hard limits was eliminated
by the normalization rule introduced in cockroachdb#41908.

The entirety of `applyLimit` will eventually be removed and replaced by the
optimizer. Removing hard limit propagation from it entirely is a step in this
direction, and will make it easier to refactor soft limit propagation later.

This patch changes `applyLimit` into `applySoftLimit`, which no longer performs
any hard limit propagation; the responsibility for setting any hard limits on
scanNodes and spoolNodes is now left to the optimizer.

It also renames `setUnlimited` to `propagateSoftLimits` in order to indicate
more clearly that this function is not used to remove any limits that might
have been set on a node, but instead to trigger soft limit propagation in case
a limit hint can be introduced further down the tree.

In the case where the optimizer does not generate a limited Scan in place of a
Limit around a Scan (because the Scan cannot be constrained), previously
`applyLimit` would still set the `hardLimit` field of the scan.  This change
will result in the `softLimit` being set instead.  The end state of the
planNode tree is otherwise the same as before this refactor.

Release note: None

42086: sql: remove hard limit handling from heuristic planner r=savoie a=savoie

Previously, the heuristic planner function `applyLimit` propagated and set both
hard and soft limits. Hard limit propagation is now handled in the optimizer by
using rules to push Limit operators down the tree as far as is possible, and to
remove Limits by adding hard limits to child Scan operators. As a result, a
majority of the logic within `applyLimit` that supported propagating hard
limits was never exercised, since optimization rules would have already
eliminated situations in which Limit operators high up in the tree could be the
cause of hard limits further down the tree. The one remaining case for which
`applyLimit` was still responsible for propagating hard limits was eliminated
by the normalization rule introduced in #41908.

The entirety of `applyLimit` will eventually be removed and replaced by the
optimizer. Removing hard limit propagation from it entirely is a step in this
direction, and will make it easier to refactor soft limit propagation later.

This patch changes `applyLimit` into `applySoftLimit`, which no longer performs
any hard limit propagation; the responsibility for setting any hard limits on
scanNodes and spoolNodes is now left to the optimizer.

It also renames `setUnlimited` to `propagateSoftLimits` in order to indicate
more clearly that this function is not used to remove any limits that might
have been set on a node, but instead to trigger soft limit propagation in case
a limit hint can be introduced further down the tree.

In the case where the optimizer does not generate a limited Scan in place of a
Limit around a Scan (because the Scan cannot be constrained), previously
`applyLimit` would still set the `hardLimit` field of the scan.  This change
will result in the `softLimit` being set instead.  The end state of the
planNode tree is otherwise the same as before this refactor.

Release note: None

42181: storage/engine: port the final few MVCC benchmarks to Pebble r=petermattis a=petermattis

The `MVCCDeleteRange`, `ClearRange`, and `ClearIterRange` benchmarks
appear to give nonsensical results. I suspect the benchmarks themselves
are at fault and need to be rewritten.

```
name                          old time/op    new time/op    delta
MVCCScanTransactionalData-16    4.20ms ± 1%    3.39ms ± 0%  -19.15%  (p=0.000 n=9+9)
```

Release note: None

42185: cli/cliflags: add COCKROACH_STORAGE_ENGINE env var r=petermattis a=petermattis

`roachprod` passes through COCKROACH_* env vars allowing
`COCKROACH_STORAGE_ENGINE=pebble roachprod start` to work.

Fixes #41620

Release note (cli change): Add COCKROACH_STORAGE_ENGINE env var which
is tied to the `--storage-engine` flag. Allows selection of "pebble" as
an alternative to the default of "rocksdb".

Co-authored-by: Céline O'Neil <celineloneil@gmail.com>
Co-authored-by: Peter Mattis <petermattis@gmail.com>