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?

--
Frédéric Jolliton
SecurActive
_______________________________________________
users-list mailing list
users-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/users-list