crate
Merge two Limit operators together
Two following limit operators can be merged together similar to filters as an optimizer rule.
https://github.com/prestodb/presto/commit/dec0b63ecfb92264c3c34746e55013674728eec2
This is what you mean, right?
cr> create table t1(x int, y int);
CREATE OK, 1 row affected (0.356 sec)
cr> explain SELECT * FROM (select * from t1 order by x desc limit 20) AS tt1 limit 1;
+----------------------------------------------------+
| EXPLAIN |
+----------------------------------------------------+
| Rename[x, y] AS tt1 |
| └ Limit[1::bigint;0] |
| └ Fetch[x, y] |
| └ Limit[20::bigint;0] |
| └ OrderBy[x DESC] |
| └ Collect[doc.t1 | [_fetchid, x] | true] |
+----------------------------------------------------+
EXPLAIN 1 row in set (0.002 sec)
and could be optimized to:
cr> explain SELECT * FROM (select * from t1 order by x desc limit 20) AS tt1 limit 1;
+--------------------------------------------------+
| EXPLAIN |
+--------------------------------------------------+
| Rename[x, y] AS tt1 |
| └ Fetch[x, y] |
| └ Limit[1::bigint;0] |
| └ OrderBy[x DESC] |
| └ Collect[doc.t1 | [_fetchid, x] | true] |
+--------------------------------------------------+
Adding a rule like this is a bit problematic because during the optimizing phase we've limit and offset as Symbol - if it is a parameter (?) it wouldn't work.
Besides, it looks like this kind of optimization already happens as part of the execution plan building:
https://github.com/crate/crate/blob/3c6c3fb509029613da8631f9f3f87cb255d78aab/server/src/main/java/io/crate/planner/operators/Limit.java#L120-L125
Closing this for now - but can re-open if there are cases where not having this rule prevents other optimizations from kicking in.
