Hi Frederik, Unless I misunderstand the problem, I think you can't just use X_3 because that is the bat that corresponds to the physical column of your table a. So you can't take it and append b to it. Union creates a copy of a, then appends b, then c. Roberto On 22 August 2014 17:42, Frédéric Jolliton < frederic.jolliton+monetdb@securactive.net> wrote:
Hi,
Unless I'm missing something, I do not understand why this apparent pattern is not easily optimized out:
X_3:bat[:oid,:lng] =
[0] := bat.new(nil:oid,nil:lng); .. # X_3 is never used before this line. X_19= [23068647] := <some computation> X_20= [23068647] := bat.append(X_3= :bat[:oid,:lng][23068647], X_19=
[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?
-- Frédéric Jolliton SecurActive _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list