Use i64 as the only internal integer type

[andylokandy]

Currently, we have supported plenty of integer types, e.g., UInt8, Int8, UInt16, etc. It helps reduce the memory and storage footprint on small integer types but also comes with disadvantages:

1. Extra casts between types of integers, which result in additional memcopy.
2. Complex arithmetic function definitions. We've to use codegen to calculate all possible combinations of the types and calculate the output type.

Maybe we can cut them down to i64, or i64 and u64.

Reference:
snowflake only supports the decimal type
firebolt supports int & bigint type

[andylokandy]

cc @BohuTANG @leiysky @xudong963 @sundy-li

[sundy-li]

I do agree It shall make life much easier if we just use i64 integer datatype if we really do not care about the performance.

Yet some queries may work much slower if we use i64 in group by query (even in join query), for example:

databend > SELECT avg(number), max(number) FROM numbers(1000000000) GROUP BY number % 3, number % 4, number % 5 limit 1;

+-------------+-------------+
| avg(number) | max(number) |
+-------------+-------------+
|   499999980 |   999999960 |
+-------------+-------------+
1 row in set (0.600 sec)

databend > SELECT avg(number), max(number) FROM numbers(1000000000) GROUP BY to_int16(number % 3), to_int16(number % 4), to_int16(number % 5) limit 1;

+-------------+-------------+
| avg(number) | max(number) |
+-------------+-------------+
|   499999980 |   999999960 |
+-------------+-------------+
1 row in set (0.689 sec)

databend > SELECT avg(number), max(number) FROM numbers(1000000000) GROUP BY to_int(number % 3), to_int(number % 4), to_int(number % 5) limit 1;
+-------------+-------------+
| avg(number) | max(number) |
+-------------+-------------+
|   499999993 |   999999943 |
+-------------+-------------+
1 row in set (2.494 sec)

databend >
databend > SELECT avg(number), max(number) FROM numbers(1000000000) GROUP BY to_int64(number % 3), to_int64(number % 4), to_int64(number % 5) limit 1;

+-------------+-------------+
| avg(number) | max(number) |
+-------------+-------------+
|   499999991 |   999999941 |
+-------------+-------------+
1 row in set (3.906 sec)

[sundy-li]

As we will use multiple small memory/cpu machines to construct a cluster in cloud platform, so OOM may be a key point whether to use large int or not. cc @flaneur2020

[xudong963]

If the total length of group by keys in our current implementation exceeds 64 bytes, we will use Series::serialize instead of Series::fixed_hash when building keys later, which will result in a large performance difference. If we can support HashMethodKind::KeysU128, HashMethodKind::KeysU256, then group by can still build key with Series::fixed_hash even if it contains four u64 keys.

Of course, I'm not sure how many keys our users usually group by, but it shouldn't be too many?

FYI, perf information about SQL 1 and SQL 4 above

[sundy-li]

Yes, but even with fixed keys, there are still bad effects on performance.

databend >  SELECT max(number),  avg(number) FROM numbers(1000000000) GROUP BY to_uint16(number % 16) limit 1;
+-------------+-------------+
| max(number) | avg(number) |
+-------------+-------------+
|   999999984 |   499999992 |
+-------------+-------------+
1 row in set (0.269 sec)

databend >  SELECT max(number),  avg(number) FROM numbers(1000000000) GROUP BY to_uint64(number % 16) limit 1;
+-------------+-------------+
| max(number) | avg(number) |
+-------------+-------------+
|   999999984 |   499999992 |
+-------------+-------------+
1 row in set (0.496 sec)