Flag inputs to SELECT queries as monotonic if until is set. - #14607

I have verified that this produces monotonic query plans manually, as I can't think of a good way to introspect on this (the explained plans are with non-monotonic inputs, which seems good as we are plausibly explaining what an installed view would look like). I'm going to try and get materialize to admit that the performance is better by using some otherwise fairly silly queries, and I'll report back about that.

Also, just for further commentary, setting the inputs to monotonic is probably not the best answer here, as while true it won't unlock all of the good query plans, as some operators like Reduce with aggregates (i.e. not Distinct) are marked as non-monotonic on monotonic inputs (e.g. the count could increase). However, in a snapshot setting, their outputs would be monotonic (again, as with only one time there are not retractions).

@antiguru observes that this may be DOA as we are not assured that the data we get are consolidated, and we cannot afford to consolidate the data as it arrives (real append-only sources benefit by never needing their contents resident at once).

Closing for now!

Notes for future selves: it is plausible that an Arrangement could provide consolidated output, using a mechanism other than "decant each batch independently". Instead, given all the batches it could enumerate them as if a Cursor, producing the correct total for each record before emitting it. This is not the current behavior though, so all the more reason not to do this.

This PR can be a good starting point for work in epic MaterializeInc/database-issues#4125, including issue MaterializeInc/database-issues#5030.

@antiguru observes that this may be DOA as we are not assured that the data we get are consolidated, and we cannot afford to consolidate the data as it arrives (real append-only sources benefit by never needing their contents resident at once).

Closing for now!

Just to make sure this observation gets recorded: We already had consolidate_named calls in the code previously for both min/max and top-1 monotonic renderings. As such, adding a similar consolidation for monotonic top-k maintains consistency for now, but is not the final state we'd like to be in, as observed above. Follow-up work needs to be carried out to take the responsibility of deciding when to consolidate out of rendering and into the planner, e.g., in the context of MaterializeInc/database-issues#5062. This future work can ensure that consolidation is not done for append-only sources, but is inserted for one-off SELECTs.

For this PR, we argue that consolidating in all monotonic variants increases consistency and moves us forward regarding one-off SELECT improvements.

An evaluation of the effect of this PR can be found in the linked spreadsheet. The setup is of a local developer machine running the TPC-H load generator without updates at scale factor 1. The queries were designed to hit min/max aggregates, top-1, and top-k. The vast majority of the queries evaluated get meaningful speedups, some of over an order of magnitude, as expected. On average across the 7 queries evaluated, the speedup is around 5x.

@antiguru and I decided to run the slow SQL Logic Tests on this PR and got this output. Note that the only two tests that failed were due to the changes introduced in PR #18302. After rebasing to catch the changes in the latter PR, one of the tests was removed, and the other passed.

For further validation, I ran also a subset of the nightlies, namely all testdrive and SQLsmith ones along with a couple of others, and got this output. Here, we see that the only relevant errors reported by SQLsmith are instances of MaterializeInc/database-issues#5387.

From the test results, it appears that this PR is pretty safe. Additionally, we are using plans that can be produced for append-only sources already, except that we are making this plan usage more frequent by also including one-shot SELECTs. However, this PR might introduce an observability / performance debugging issue as the plans generated cannot be exposed currently by EXPLAIN. So to be conservative, I am going to make further changes here related to gating the generation of monotonic plans for one-off SELECTs behing a feature flag enable_monotonic_oneshot_selects. A discussion regarding the latter can be found in this Slack thread.

All committers have signed the CLA.

I am closing this PR again, as we have agreed to merge #18546 instead.