hash_join_cardinality optimizer_switch Flag

MariaDB starting with 10.6.13

The hash_join_cardinality optimizer_switch flag was added in MariaDB 11.0.2, MariaDB 10.11.3, MariaDB 10.10.4, MariaDB 10.9.6, MariaDB 10.8.8 and MariaDB 10.6.13.

In MySQL and MariaDB, the output cardinality of a part of query has historically been tied to the used access method(s). This is different from the approach used in database textbooks. There, the cardinality "x JOIN y" is the same regardless of which access methods are used to compute it.


Consider a query joining customers with their orders:

select * 
  customer, orders, ...
  customer.id = orders.customer_id and ...

Suppose, table orders has an index IDX on orders.customer_id.

If the query plan is using this index to fetch orders for each customer, the optimizer will use index statistics from IDX to estimate the number of rows in the customer-joined-with-orders.

On the other hand, if the optimizer considers a query plan that joins customer with orders without use of indexes, it will ignore the customer.id = orders.customer_id equality completely and will compute the output cardinality as if customer was cross-joined with orders.

Hash Join

MariaDB supports Block Hash Join. It is not enabled by default, one needs to set it join_cache_level to 3 or a bigger value to enable it.

Before MDEV-30812, Query optimization for Block Hash Join would work as described in the above example: It would assume that the join operation is a cross join.

MDEV-30812 introduces a new optimizer_switch flag, hash_join_cardinality. In MariaDB versions before 11.0, it is off by default.

If one sets it to ON, the optimizer will make use of column histograms when computing the cardinality of hash join operation output.

One can see the computation in the Optimizer Trace, search for hash_join_cardinality.


Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.