Hi,
Unless I'm missing something, I do not understand why this apparent pattern is not easily optimized out:
X_3:bat[:oid,:lng] =<tmp_321264>[0] := bat.new(nil:oid,nil:lng); .. # X_3 is never used before this line. X_19=<tmp_331015>[23068647] := <some computation> X_20=<tmp_321264>[23068647] := bat.append(X_3=<tmp_321264>:bat[:oid,:lng][23068647], X_19=<tmp_331015>[23068647],true); # X_3 is never used after this line.
An empty BAT is created for the sole purpose of appending a BAT with millions of rows, discarding the original one.. instead of just using it!
This account for up to 30% of the time spent for some SQL queries! Like this one:
SELECT name, avg(value) FROM some_view GROUP BY name LIMIT 10;
I tried to manually remove these extra operation, and I got the exact same result, in less time. (I took the output of EXPLAIN, removed the lines in question, and added a bit of type information for the function to compile, before playing it in mclient -l mal.)
Regarding the setup, for our tests, we have 3 tables (say, a, b and c) of 11M rows each, then a view declared as:
CREATE VIEW some_view AS (SELECT * FROM a UNION ALL SELECT * FROM b UNION ALL SELECT * FROM c);
I've tested it with v11.17.21 and v11.19.0, with the "default" optimizer.
My question: is this extra operation required, or could it be optimized in MonetDB?